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.
Search This Blog
Monday, March 26, 2012
Gathering Oracle Database Statistics Using DBMS_STATS.
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment