Gathering Workload Statistics

We refer in our upgrade talk to gather system (aka workload) statistics to give the optimizer some good knowledge about how powerful your IO-system might be. The optimizer since Oracle 9.2 takes CPU and IO costing into consideration. If you never have taken care on these stats you'll find default values in AUX_STATS$. These values have been defined a few years back. So it might be a good idea right a few days after upgrading to the new release to create them while a real workload is running. Taking these stats does not generate any overhead or performance degredation on your system.

2010_03_10_Perth_Stats.jpg

You'll create the system stats while there's workload on the system with:
exec DBMS_STATS.GATHER_SYSTEM_STATS('start');
exec DBMS_STATS.GATHER_SYSTEM_STATS('stop');

You could run this for several hours and you could revert with:
exec DBMS_STATS.DELETE_SYSTEM_STATS;

to the default values.

Now yesterday in Melbourne we had an interesting discussion after the workshop regarding the behaviour of DB_FILE_MULTIBLOCK_READ_COUNT when taking system stats. The Performance Tuning Guide has some interesting information regarding this so I'll quote it here:

13.4.1.2 Multiblock Read Count

If you gather workload statistics, then the mbrc value gathered as part of the workload statistics is used to estimate the cost of a full table scan. However, during the gathering process of workload statistics, Oracle Database may not gather the mbrc and mreadtim values if no table scans are performed during serial workloads, as is often the case with OLTP systems. However, full table scans occur frequently on DSS systems but may run parallel and bypass the buffer cache. In such cases, Oracle Database still gathers the sreadtim value because the database performs index lookup using the buffer cache.
If Oracle Database cannot gather or validate gathered mbrc or mreadtim values, but has gathered sreadtim and cpuspeed values, then the database uses only the sreadtim and cpuspeed values for costing. In this case, the optimizer uses the value of the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT to cost a full table scan. However, if DB_FILE_MULTIBLOCK_READ_COUNT is not set or is set to 0 (zero), then the optimizer uses a value of 8 for costing.

Once you have created system/workload stats it might be a good idea to duplicate them to your test system(s) as well even though the test system might have not this super-fast IO subsystem. But the optimizer will "believe" he would have ... Therefore use the procedure DBMS_STATS.CREATE_STAT_TABLE and transport the system stats with DBMS_STATS.EXPORT_SYSTEM_STATS and DBMS_STATS.IMPORT_SYSTEM_STATS procedures.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers