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.
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:
to the default values.
BUT: If you’d speak to our Real World Performance Team folks they’ll tell you to keep your hands better of those stats, especially when the system is performing well for a time, as gathering system stats will change the system’s behavior. I think you should give it a try during test before moving to a new HW and storage. But test carefully, please.
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.
For Exadata systems it’s important to gather systems stats with the call:
exec DBMS_STATS.GATHER_SYSTEM_STATS(‘Exadata’);
