Gathering Table Stats in Oracle11g

Hi Guy's,

• When we gather stats we are updating Optimizer to choose optimal execution plan which enhance the performance of the database.
• We can gather stats at database level,schema level, Table level,Index level.

Example for Table Level Stats :

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
         ownname =>'SCOTT',
         tabname =>'EMPLOYEE',
         degree => 2,
         cascade => TRUE,
         METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
         estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
         END;
         /


Parameters:-

•  Owner-name = Schema Name which holds the table.
• Tabname = Table Name
• Degree  = When you set degree to some value oracle will invoke slave process which speedup the performance.
• cascade = The cascade option can be used to analyze al the indexes.
• METHOD_OPT (FOR COLUMNS SIZE AUTO) = Auto is the Oracle default and this option will analyze histograms  on what the optimizer considers to be "important columns". The optimizer does this by examining your current workload when you run the dbms_stats command,interrogating all current SQL in the library cache to see which columns might benefit from a histogram.
•  estimate_percent = This argument is a new way to allow Oracle's dbms_stats to automatically   estimate  the "best" percentage of a segment to sample when gathering statistics.

Happy Learning...