Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

dbms_stats.gather_system_stats and SQL optimizer behavior

Oracle Database Tips by Donald Burleson


In Oracle we now see 11g extended optimizer statistics, an alternative to dynamic_sampling for estimating result set sizes.

The dbms_stats.gather_system_stats procedure measures important timings within the database and adjusts the optimizers propensity to choose indexes vs. full-scans.  Andrew Holdsworth of Oracle Corporation notes that dbms_stats is essential to good SQL performance, and it should always be used before adjusting any of the Oracle optimizer initialization parameters:

'the payback from good statistics management and execution plans will exceed any benefit of init.ora tuning by orders of magnitude?

The dbms_stats.gather_system_stats procedure is especially useful for multi-mode Oracle shops that run OLTP during the day and DSS at night.  You invoke the dbms_stats.gather_system_stats procedure as an elapsed time capture, making sure to collect the statistics during a representative heavy workload:

execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');

The data collection mechanism of the dbms_stats.gather_system_stats procedure works in a similar fashion to my script that measures I/O times to optimizer the optimizer_index_cost_adj parameter.  The dbms_stats.gather_system_stats also related to the under-documented  _optimizer_cost_model parameter and your db_file_multiblock_read_count setting.

The output from dbms_stats.gather_system_stats is stored in the aux_stats$ table and you can query it as follows:

select pname, pval1 from sys.aux_stats$;
 
Here are the data items collected by dbms_stats.gather_system_stats:

No Workload (NW) stats:

  • CPUSPEEDNW - CPU speed
  • IOSEEKTIM - The I/O seek time in milliseconds
  • IOTFRSPEED - I/O transfer speed in milliseconds

Workload-related stats:

  • SREADTIM  - Single block read time in milliseconds
  • MREADTIM - Multiblock read time in ms
  • CPUSPEED - CPU speed
  • MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
  • MAXTHR - Maximum I/O throughput (for OPQ only)
  • SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)   
The dbms_stats.gather_system_stats procedure is very similar to my script for setting optimizer_index_cost_adj, where I compare the relative costs of sequential and scattered read times:
 
select
   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
   sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
   (
      sum(a.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c3,
   (
      sum(b.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c4,
  (
      sum(b.time_waited_micro) /
      sum(b.total_waits)) /
      (sum(a.time_waited_micro)/sum(a.total_waits)
   ) * 100 c5
from
   dba_hist_system_event a,
   dba_hist_system_event b
where
   a.snap_id = b.snap_id
and
   a.event_name = 'db file scattered read'
and
   b.event_name = 'db file sequential read';


Here is sample output from a real system showing an empirical test of disk I/O speed.  We always expert scattered reads (full-table scans) to be far faster than sequential reads (index probes) because of Oracle sequential prefetch according to out setting for db_file_multiblock_read_count:  Below we see a starting value for optimizer_index_cost_adj based on the relative I/O costs, very similar to dbms_stats.gather_system_stats:
 
- scattered read (full table scans) are fast at 13ms (c3)
- sequential reads (index probes) take much longer 86ms (c4)
- starting setting for optimizer_index_cost_adj at 36:


C1         C2         C3         C4         C5
---------- ---------- ---------- ---------- ----------
13,824     5,072      13         86         36

Oracle Notes on dbms_stats.gather_system_stats

The note below shows how the output from dbms_stats.gather_system_stats can change the cost-based optimizers choice of execution plans.  Oracle MOSC has detailed script listings in Note 149560.1 ?Collect and Display System Statistics (CPU and IO) for CBO usage?:

/* e.g. activate the DAY statistics each day at 7:00 am */

DECLARE

   I NUMBER;

BEGIN

   DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s

tatown => ''SYSTEM'', statid => ''DAY'');', trunc(sysdate) + 1 + 7/24, 'sysdate

+ 1');

END;

 

/* e.g. activate the NIGHT statistics each day at 9:00 pm */

DECLARE

   I NUMBER;

BEGIN

   DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s

tatown => ''SYSTEM'', statid => ''NIGHT'');', trunc(sysdate) + 1 + 21/24, 'sysdate

+ 1');

END;

 

*** ********************************************************

*** Initialize the OLTP System Statistics for the CBO

*** ********************************************************

 

1. Delete any existing system statistics from dictionary:

   

   SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS;

   PL/SQL procedure successfully completed.

 

2. Transfer the OLTP statistics from OLTP_STATS table to the dictionary tables:

 

   SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(-  

   >         stattab => 'OLTP_stats', statid => 'OLTP', statown => 'SYS');

 

   PL/SQL procedure successfully completed.

 

3. All system statistics are now visible in the data dictionary table:

 

   SQL> select * from sys.aux_stats$;

 

   SQL> select * from aux_stats$;

 

   SNAME                PNAME                   PVAL1 PVAL2

   -------------------- ------------------ ---------- --------------

   SYSSTATS_INFO        STATUS                        COMPLETED

   SYSSTATS_INFO        DSTART                        08-09-2001 16:40

   SYSSTATS_INFO        DSTOP                         08-09-2001 16:42

   SYSSTATS_INFO        FLAGS                       0

   SYSSTATS_MAIN        SREADTIM                7.581

   SYSSTATS_MAIN        MREADTIM               56.842

   SYSSTATS_MAIN        CPUSPEED                  117

   SYSSTATS_MAIN        MBRC                        9

 

   where 

   => sreadtim : wait time to read single block, in milliseconds

   => mreadtim : wait time to read a multiblock, in milliseconds

   => cpuspeed : cycles per second, in millions 

 

*** ********************************************************

*** CPU_COST and IO_COST in PLAN_TABLE table

*** ********************************************************

 

   SQL> explain plan for select * from oltp.test where c='AAAHxGAABAAAJS1AEZ';

   Explained.

 

   SQL> select operation, options, object_name, cpu_cost, io_cost

     2  from plan_table;

 

   OPERATION          OPTIONS              OBJECT_NAME    CPU_COST    IO_COST

   ------------------ -------------------- ------------ ---------- -

   SELECT STATEMENT                                          10500          1

   INDEX              UNIQUE SCAN          SYS_C002218       10500          1

 

   SQL> truncate table plan_table;

 

   SQL> explain plan for select * from oltp.test;

   Explained.

 

   SQL> select operation, options, object_name, cpu_cost, io_cost

     2  from plan_table;

 

   OPERATION          OPTIONS              OBJECT_NAME    CPU_COST    IO_COST

   ------------------ -------------------- ------------ ---------- ----------

   SELECT STATEMENT                                        2677480         27

   INDEX              FAST FULL SCAN       SYS_C002218     2677480         27

Important Note:  Prior to Oracle 10g, adjusting these optimizer parameters was the only way to compensate for sample size issues with dbms_stats.  As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important.  Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms.  For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference". 

Warning in 11.2 - Beware of  bug 9842771. This bug leads to wrong values in AUX_STATS$ for sreadtim and mreadtim by factor 1,000 therefore guiding the optimizer sometimes into the totally wrong direction. The workaround is to overwrite these values manually and divide them by 10,000. To make this change, use the dbms_stats.set_system_stats procedure. See this MOSC Note: 9842771.8 for further information. This issue is fixed in Oracle Database 11.2.0.3 and above.

Arup Nanda has a great article on extended statistics with dbms_stats, specialty histogram analysis using function-based columnar data:

Next, re-gather statistics on the table and collect the extended statistics on the expression upper(cust_name).

begin
  dbms_stats.gather_table_stats (
     ownname    => 'ARUP',
     tabname    => 'CUSTOMERS',
     method_opt => 'for all columns size skewonly for columns (upper(cust_name))'
  );
end;
 

Alternatively you can define the column group as part of the gather statistics command.

You do that by placing these columns in the method_opt parameter of the gather_table_stats procedure in dbms_stats as shown below:
 

begin
   dbms_stats.gather_table_stats (
      ownname         => 'ARUP',
      tabname         => 'BOOKINGS',
      estimate_percent=> 100,
      method_opt  => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS(HOTEL_ID,RATE_CATEGORY)',
       cascade         => true

 

 

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational