Search This Blog

Monday, March 26, 2012

Gathering Oracle Database Statistics Using DBMS_STATS.

Gathering Table Statistics.
Check the table for when it was last analyzed.

SQL> select last_analyzed, sample_size from dba_tables where owner='SCOTT' and
     table_name='EMP';

LAST_ANAL SAMPLE_SIZE
--------- -----------
19-MAR-12          14



EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP', estimate_percent => 25, cascade => true,
 degree => 2);



Gathering Schema statistics.

EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 25, cascade => true,
 degree => 2);

Gathering Database statistics.

EXEC DBMS_STATS.gather_database_stats(estimate_percent=>25, cascade=> true, degree => 2);


"ESTIMATE_PERCENT" parameter is used to take a small sample of data instead of the whole
thing, thereby reducing the overhead of the operation.

"CASCADE" parameter tells the DBMS_STATS procedure to gather index statistics also.

"DEGREE" parameter can be used to use parallelism while gathering statistics.


Gathering system statistics.

By gathering system statistics information regarding CPU and system I/O is 
is made available to Cost Based Optimizer which results in better performance.

System stats should be collected when there is moderate to heavy load on
your database.

Also, System statistics should only be gathered once or after a significant
hardware or software change.

Before gathering system statistics collect statistics on fixed objects.

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);

--Fixed objects gathers statistics on the x$ tables. Since statistics on these tables
--are not gathered automatically they need to be gathered manually once and after
--any software or hardware changes.

Then gather system stats.

DBMS_STATS.gather_system_stats('interval', interval => 60);

--Start gathering statistics for upto 60 minutes, starting right after the execution
--of the above command. 


You check for system statistics by executing the following query.

SQL> SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     2696.05568
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

9 rows selected.




0 comments:

Post a Comment