Subscriber Count


Subscribe to Posts by Email


Quick Question: Why statistics are not collected though the automatic job shown it has ran sucessfully

Why you statistics are not collecting via automatic stats gather job, though your job showing succeeded?

Well the answer is, coz of Duration of schedule window, yes

As you aware , the jobs now scheduled in windows Read here

The weekday windows start at 10PM and run for 8 hours. The weekend windows start […]

Oracle Scheduler maintenance windows: How long they can run?

With previous post you see the duration of stats job, but how long they can run?

Well the answer for that would be depends on the scheduler windows.

To understand scheduler windows, according to documentation:-

A maintenance window is a contiguous time interval during which automated maintenance tasks are run. Maintenance windows are Oracle […]

How much time does my stats job ran?

Here is the small script

column job_name format a20 column status format a12 column actual_start_date format a36 column run_duration format a14

select         job_name, status, actual_start_date, run_duration from         dba_scheduler_job_run_details where         job_name = ‘GATHER_STATS_JOB’ order by         actual_start_date ;



Why is that, I have different execution plans although sql profile has been picked up?

Today I learnt something new, on sql profiles.

Thanks to Kerry Osborne’s blog that let me understand on this stuff

Before going forward have a look at the below plans.

<font size="2">SQL&gt; select * from table(dbms_xplan.display_awr('2pg20pzpc6yb7')); PLAN_TABLE_OUTPUT- —————————————————————————————————– SQL_ID 2pg20pzpc6yb7 ——————– SQL Statement removed intentionally, and it is irrelevant to subject Plan hash value: 526584108 […]

12c database Architecture & Some wonderful posts – external links

Hello Readers,

Thanks to Kirill for allowing me to share his blog posts on 12c database. Nice write up where you found the preliminary information on 12c pluggable databases which is coming soon.

Architecture Overview:-

Features Insight:-

Some hands on:-

Plug & Unplug the databases in 12c:-

BTW, I missed to […]

How to find the optimizer settings for a given session or sql_id

During a query’s cost estimation the optimizer picks up many values or parameters to evaluate cost or invoke hints etc to produce an execution plan. v$sess_optimizer_env is a good view to know about any anamolies of a given statement. This can be useful when you comparing UAT and production environment where the query results are […]

What is the minimum execution time that a query/statement for a parallel execution

As per documentation its 30 seconds

The parameter is (from 11gr2) is parallel_min_time_threshold which invokes the automatic degree of parallelism when the query execution goes beyond 30 seconds.

Nevertheless, whether you have object parallelism (table/index degree) set or not, if parallel_degree_policy is set to Limited or Auto, Oracle uses parallelism for any queries depends on […]

Number of locks that can acquire with parallel DML statements

Came across this good table while scrolling the documentation,

When you need to do a parallel DML and that involves partitions, give a serious thought about the DML locks that can acquire. Below table gives you a handy info for the same.

Source:- here


Type of Statement Coordinator Process Acquires: Each Parallel Execution […]

Resolving Library cache: mutex X

According to notes 9282521.8 and 9239863.8 describing the patches, the enhancements should be used: When there is true contention on a specific library cache object….

For example:- A package that is so hot (heavily accessed ) in library cache will be contended and the sessions appear to be waited on Library Cache: mutex X.

There […]

RMAN incremental backups fails even though full backup exists

Originally posted here,

Asked why the incremental backup fails

oracle@linux64 ~]$ rman target /

Recovery Manager: Release – Production on Thu Feb 7 21:30:18 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup database plus archivelog;

Starting […]