Statistics , Statistics – its all in Statistics, if you are in ORACLE!!!

Got a call on Sat night from my boss – asking me to login immediately as our nightly job which runs for about 4:30Hrs is running for more than 6hrss and still not half way through.

My first question was – what’s changed? No one seems to know. Looked into the last analyzed time for all the tables and it showed me the FRIDAY)Friday is our weekly off. I again looked into it , nothing popped up, suddenly I see a parameter change in the gather stat execution script, we tried to take a broad-brush approach and we tried to gather histogram on all index columns...




Man!!! That was it. I then realized that has actually changed all the execution path and its running terribly slow. My quickest solution was to import statistics from last good backup. Luckily – we had a statistics backup of about 2 weeks old. We imported back the stats restarted the job and it went flying, as before!!!


MORAL of the story – FEED your CBO with TOP QUALITY stats else, you are going to have a bit of troublesome life!

Here in this post – I am trying to explain how Oracle CBO uses statistics and how important it is for you to have good quality stats. I will also give some working example of gathering stats, copying stats and modifying it.



Please read through this article published in scribed http://www.scribd.com/doc/2713822/Oracle-calculation-of-cost-of-nested-loop-joins, to understand better about the cost.


Now, as you understand that gathering stats is very important to run your DB superfast, but how often you are going to collect the stats. My answere is – it depends. If your shop is having a high update application you may need to gather stat every night, for shops with less update/change , once in a week or a month. The basic rule is – Oracle needs new statistics when the object stats become state. That is – if there is more that 10% data change in the object you need new stats.



There are shops , where you may need to check / test the predicated performance/response time of a newly developed query. You can copy the statistics from your Production to your test environment and can test that application too.



Please look into oracle documentation on DBMS_STATS package to see all its option. Below, I am giving some example of working script –

To gather schema stats


exec dbms_stats.gather_schema_stats( -

ownname => 'user1', -

estimate_percent => dbms_stats.auto_sample_size, -

method_opt => 'FOR ALL COLUMNS SIZE REPEAT', -

degree => 8, -

force => TRUE, -

options => 'GATHER STALE', -

cascade => TRUE -

);



The above will gather statistics for all objects with STALE statistics,



To delete schema stat -

EXEC DBMS_STATS.delete_schema_stats('user1');


To backup the statistics –

exec dbms_stats.create_stat_table(ownname=>'user1',stattab=>'user1_STAT');

EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('user1', 'user1_STAT');



You must also gather statistics for FIXED object , in some applications like Oracle Apps its recommended before and after every patch …



execute dbms_stats.gather_system_stats('start');

-- wait an hour or so

execute dbms_stats.gather_system_stats('stop');



So Far this much. Let me know if you need to know more. In my next post,. I will continue with AWR report and how to interpret it.


So long …



Arindam