Understanding Optimizer Statistic’s
From the part I and part II of the oracle statistics we
have seen how the statistics are aiding to generate better execution plan for
the SQL performance. Review the first and second installments of the series
here.
Restoring Statistics: When we collect statistics using
DBMS_STATS the original statistics are automatically kept as a backup in
dictionary tables and can be easily restored by running DBMS_STATS.RESTORE_TABLE_STATS
if the newly gathered statistics lead to any kind of problem.
The example below restores the statistics for EMPLOYEES table as
of yesterday and also automatically invalidates all of the cursors referencing the EMPLOYEES
table in the SHARED_POOL.
BEGIN
DBMS_STATS.RESTORE_TABLE_STATS ( ownname => ‘HR’,
tabname => ‘EMPLOYEES’,
as_of_timestamp => SYSTIMESTAMP-1,
force => FALSE,
no_invalidate => FALSE );
END;
/
Export/Import Statistics: Exporting and Importing statistics for the CBO and the
systems stats (external system statistics for CPU, I/O. etc) and useful in:
·
Export production into test to make test systems
“look like” large systems for execution plan generation”.
·
System stats can be moved to a smaller server to
make it appear as if Oracle is executing on a large fast server.
This feature comes handy when testing the application in the environment where
the test systems are not identical to the production system in size and
hardware. By copying the Optimizer statistics from production database
to test database running on same Oracle version but a scaled-down database will
mimic to the optimizer the behavior of a production environment on the test environment.
The production statistics can be copied to the test database using
the procedures:
·
DBMS_STATS.EXPORT_SCHEMA_STATS
·
DBMS_STATS.IMPORT_SCHEMA_STATS
1. To export the statistics from the dictionary we need to a table
which is capable of holding table stats. The procedure below creates the table
required to hold the stats:
BEGIN
dbms_stats .create_stat_table ( ownname => ‘TOOLS’,
stattab => ‘TAB-STAT’,
tblspace => ‘TBS_TOOLS’,
global_temporary => FALSE );
END;
/
2. Once the table has been created we are ready to dump the
dictionary statistics into the stats table. The procedure below dumps the stats
into the stats table:
BEGIN
dbms_stats .export_schema_stats ( ownname
=> ‘HR’,
stattab
=> ‘TAB_STAT’,
statid => ‘C101’,
statown => ‘TOOLS’ );
END;
/
3. The stats table holding the stats can be shipped to another
database using oracle copy utility Expdp:
Expdp userid=tools/tools tables=TAB_STAT directory=DMP_DIR dumpfile=hr_schema_stats.dmp logfile=exp_hr_schema_stats.log
4. The stats table holding the stats can be shipped to another
database using oracle copy utility Impdp:
Impdp userid= tools/tools tables=TAB_STAT
directory=DMP_DIR dumpfile=hr_schema_stats.dmp logfile=imp_hr_schema_stats.log
5. The stats from the stats table can be copied to the dictionary
table by the procedure below:
BEGIN
dbms_stats .import_schema_stats ( ownname => ‘HR’,
stattab => ‘TAB_STAT’,
statid => ‘C101’,
statown => ‘TOOLS’,
force => FALSE,
no_invalidate => FALSE );
END;
/
Manually setting Statistics: This method is rarely used,
As its highly recommended not to use
this method of manually setting the statistics in dictionary because inaccurate
and inappropriate statistics can lead to poor performing execution plans causing
severe performance problems.
dbms_stats.set_table_stats ( ownname => 'HR',
tabname => 'EMPLOYEES',
numrows => 50000,
numblks => 1120,
avgrlen => 232,
force =>
TRUE );
System statistics:
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS ('start'); -- to
start collecting the system stats.
END;
/
--
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS ('stop'); -- to stop the stats collection after a
time.
END;
/
(or)
We can also use an
interval mode where the stats will collect for the duration of the interval
--
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS ('interval',
interval=>30);
-- The
stats will be collected for 30 min’s and automatically turned off the
collection.
END;
/
--
The output from dbms_stats.gather_system_stats is
stored in the aux_stats$ table and you can query it as follows:
SELECT * FROM SYS.AUX_STATS$;
SNAME
PNAME PVAL1 PVAL2
----------------------- ------------- ---------------- -----------------
SYSSTATS_INFO
STATUS COMPLETED
SYSSTATS_INFO
DSTART 08-08-2012 09:01
SYSSTATS_INFO
DSTOP 08-08-2012 10:46
SYSSTATS_INFO
FLAGS 1
SYSSTATS_MAIN
CPUSPEEDNW 751.050307
SYSSTATS_MAIN
IOSEEKTIM 10
SYSSTATS_MAIN
IOTFRSPEED 4096
SYSSTATS_MAIN
SREADTIM 1.61
SYSSTATS_MAIN
MREADTIM 12.09
SYSSTATS_MAIN
CPUSPEED 1139
SYSSTATS_MAIN
MBRC 33
SYSSTATS_MAIN
MAXTHR 233906176
SYSSTATS_MAIN
SLAVETHR 169984
Non-Workload related stats:
CPUSPEEDNW à CPU Speed non wok
related.
IOSEEKTIM à The I/O seeks time in
milliseconds.
IOTFRSPEED à
I/O transfer speed in milliseconds.
Workload related stats:
CPUSPEED à CPU Speed.
MAXTHR à
Maximum I/O throughput (for OPQ only).
MBRC à Average blocks read per multiblock read .
MREADTIM à Multiblock read time
in ms.
SLAVETHR à
OPQ Factotum (Slave) throughput (OPQ only).
SREADTIM à Single block read time
in ms.
To build an accurate/efficient execution plan by the Cost
Based Optimizer it must have correct statistics on all objects such tables and
indexes that are accessed in the SQL statement and also the system statistics
on which the SQL statement will run. Understanding and managing statistics are
key to optimal SQL execution. Knowing when and how to gather statistics is
critical to maintaining good performance.
A DBA can maintain an accurate set of statistics for optimizer and
system by using combination of the automatic statistics gathering job and the
DBMS_STATS package to aid optimizer in generated accurate and efficient
execution plans.
