Subscriber Count


Subscribe to Posts by Email


12c Database : Statistics Enhancements : CTAS and Insert as select now comes up with statistics

From 12c Onwards, Oracle statistics gathering has been improvised and after bulk load operations the statistics will be gather automatically. Earlier, Create as select , or Insert as select operations required manual statistics gathering, where in this has been removed now. The following bulk operations is supportive for automatic statistics gather on fly. CREATE TABLE […]

Find your statistics jobs windows-groups-status etc.

Atleast I found hard checking in many other areas,hence writing this

You may not find the no rows for gather_stats_job in 11g due to the enhancments in maintainence groups.

The collection of statistics along with some other maintainence tasks have been incorporated in to dba auto task admin client procedure

First you need to […]

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 ;



Quick Question: How to invalidate the cursor after statistics gathering

A long running job and you have identified that statistics of a table is causing sub optimal plan and you have gathered the statistics and asked teams to rerun the job. But still picking old plan or the statistics which should not, the reasons are and how to invalidate the cursor in library cache (SQL […]

Stats:- Change statistics stale percentage in 11g

Object statistics will become stale whenever there is an dml operations performed on those objects, that means new/deletion of rows, adding columns, updating columns may change the row count, number of blocks, index entries etc. This staleness of object statistics has been determined or hardcoded to 10% of the modifications to the objects(tables etc), for […]

Disabling automatic statistics gathering in 10g and 11g


Well, If your database environment has well established statistics gathering mechanism or your application has specific requirement of statistics gathering, you may need to disable the automatic statistics gathering, this is a bit different in 10g and 11g.

All of us aware starting 10g automatic statistics gather job will kicked off every night 10PM […]

CPU usage by Oracle, performance metrics

We all know that Oracle has a statistics to calculate the Oracle usage of CPU with “v$’CPU used by this session’”

But this statistic value got a problem, since the value will not be accounted/cumulated until the call on the cpu completed, For example, for a PL/SQL program if it takes one hour to complete […]

11g Compare statistics

Compare statistics, good idea, Ok, here is the scenario

You have a large table where you have collected the statistics with sample of 10%, and due to some reasons you want to collect the statistics with auto or 100% sample size, but you are unsure of the difference it make. Note its not about to […]