Sunday, May 17, 2009

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 3

Back to part 2 Forward to part 4

System statistics in 9i

In 10g the CPU costing mode is enabled by default and is supported by the default NOWORKLOAD system statistics.

But you can use system statistics already in 9i, although you have to enable them explicitly.

Oracle 9i is out of support, but I guess there are still a lot of systems out there that are using the 9.2 release, therefore I find it worth to mention what you can do in 9i with system statistics. This can be helpful if you consider to test your application already in the 9i environment with system statistics before upgrading to 10g.

In most descriptions about 9i and system statistics only WORKLOAD system statistics are mentioned, but 9i also supports NOWORKLOAD system statistics, although not in the same full flavour as 10g does.

You can activate CPU costing in 9i by running DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD'), but this seems to work differently than in 10g and later.

Whereas in 10g and later this actually measures the IOSEEKTIM and IOTFRSPEED values, this seems to activate in 9i something that is comparable with the default NOWORKLOAD system statistics of 10g.

The SYS.AUX_STATS$ does not show any actual values, but tests revealed that 9i (at least 9.2.0.8) in that case seems to measure the CPU speed (in 10g this is the CPUSPEEDNW value) and uses the same default values for IOSEEKTIM and IOTFRSPEED as 10g does (10ms and 4096 bytes/ms resp.).

Running some tests showed that you arrive at the same I/O cost as you do in 10g with the default NOWORKLOAD system statistics.


exec dbms_stats.delete_system_stats;

exec dbms_stats.gather_system_stats('NOWORKLOAD')

alter session set "_table_scan_cost_plus_one" = false;

explain plan for
select
max(val)
from
t1;


Since you can't modify the NOWORKLOAD system statistics values in 9i (i.e. you can't manipulate CPUSPEEDNW, IOSEEKTIM and IOTFRSPEED using DBMS_STATS.SET_SYSTEM_STATS), see the following excerpt from the 9.2.0.8 10053 trace file for the 10,000 blocks MSSM table with 8kb blocksize when using NOWORKLOAD system statistics:


Join order[1]: T1[T1]#0
Best so far: TABLE#: 0 CST: 2715 CDN: 10000 BYTES: 40000
Final - All Rows Plan:
JOIN ORDER: 1
CST: 2715 CDN: 10000 RSC: 2714 RSP: 2714 BYTES: 40000
IO-RSC: 2709 IO-RSP: 2709 CPU-RSC: 72914400 CPU-RSP: 72914400


The I/O cost calculated seems to correspond to what 10g calculates when using default NOWORKLOAD system statistics.

Other variants also seem to correspond to the 10g default NOWORKLOAD model. E.g. using a DB_FILE_MULTIBLOCK_READ_COUNT = 16 we get the following cost calculation:


Join order[1]: T1[T1]#0
Best so far: TABLE#: 0 CST: 2194 CDN: 10000 BYTES: 40000
Final - All Rows Plan:
JOIN ORDER: 1
CST: 2194 CDN: 10000 RSC: 2193 RSP: 2193 BYTES: 40000
IO-RSC: 2188 IO-RSP: 2188 CPU-RSC: 72914400 CPU-RSP: 72914400


If you gather or set WORKLOAD system statistics 9i calculates the same cost as 10g does, based on the SREADTIM, MREADTIM and MBRC values.

You can manipulate the WORKLOAD system statistics using DBMS_STATS.SET_SYSTEM_STATS like you can do in 10g. So the following code snippet mimics the default NOWORKLOAD SREADTIM and MREADTIM values for a 8kb blocksize with a DB_FILE_MULTIBLOCK_READ_COUNT = 8.


drop table t1;

create table t1
pctfree 99
pctused 1
-- tablespace test_2k
-- tablespace test_4k
tablespace test_8k
-- tablespace test_16k
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 3000
)
select
/*+ ordered use_nl(v2) */
rownum id,
trunc(100 * dbms_random.normal) val,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 10000
;


begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

begin
dbms_stats.set_system_stats('MBRC',8);
dbms_stats.set_system_stats('MREADTIM',26.0);
dbms_stats.set_system_stats('SREADTIM',12.0);
dbms_stats.set_system_stats('CPUSPEED',1000000);
end;
/

alter session set "_table_scan_cost_plus_one" = false;

alter session set tracefile_identifier = '9i_workload_system_stats';

alter session set events '10053 trace name context forever, level 1';

explain plan for
select max(val)
from t1;

alter session set events '10053 trace name context off';


Which should arrive at the same cost as the default NOWORKLOAD system statistics. And indeed it gives you this 10053 trace file excerpt:


Join order[1]: T1[T1]#0
Best so far: TABLE#: 0 CST: 2710 CDN: 10000 BYTES: 40000
Final - All Rows Plan:
JOIN ORDER: 1
CST: 2710 CDN: 10000 RSC: 2709 RSP: 2709 BYTES: 40000
IO-RSC: 2709 IO-RSP: 2709 CPU-RSC: 72914400 CPU-RSP: 72914400


If the WORKLOAD system statistics are invalid (e.g. the MREADTIM is not greater than the SREADTIM value or some values are missing) then 9i falls back to traditional I/O cost based costing for the I/O cost but adds obviously CPU costs, which is different from 10g which falls back to NOWORKLOAD system statistics.

If you change in the above code snippet the corresponding code block like this:


begin
dbms_stats.set_system_stats('MBRC',8);
dbms_stats.set_system_stats('MREADTIM',12.0);
dbms_stats.set_system_stats('SREADTIM',12.0);
dbms_stats.set_system_stats('CPUSPEED',1000000);
end;
/


You'll see the following output from the 10053 trace file:


Join order[1]: T1[T1]#0
Best so far: TABLE#: 0 CST: 1519 CDN: 10000 BYTES: 40000
Final - All Rows Plan:
JOIN ORDER: 1
CST: 1519 CDN: 10000 RSC: 1518 RSP: 1518 BYTES: 40000
IO-RSC: 1518 IO-RSP: 1518 CPU-RSC: 72914400 CPU-RSP: 72914400


This an interesting mixture of traditional I/O costing and CPU costing.

Quirks and oddities

While running my tests I came across several quirks and oddities:

- When gathering NOWORKLOAD system statistics with 10.2.0.4 and 11.1.0.7 on my test environment it happened regularly that the IOTFRSPEED was left unchanged at the default of 4096, but sometimes it also changed to a value that looked more like a measured one.

- You obviously can't set the CPUSPEEDNW/IOSEEKTIM/IOTFRSPEED values using DBMS_STATS.SET_SYSTEM_STATS in 9i for the NOWORKLOAD system statistics, so you're bound to the default values used by Oracle.

- If you're on 10g and later and use OPTIMIZER_FEATURES_ENABLE = '9.2.0.8' the NOWORKLOAD system statistics are not used properly. Although the 10053 optimizer trace suggests otherwise by mentioning the NOWORKLOAD values the final cost calculated falls back to traditional I/O based costing (and no CPU costing at all). This is probably caused by the fact that 9i doesn't support the NOWORKLOAD parameters in the SYS.AUX_STATS$ table that are always present in 10g. System statistics work however in 9.2.0.8 compatibility mode if you're using WORKLOAD system statistics with their corresponding values in SYS.AUX_STATS$.

- Altering the SYSTEM value of db_file_multiblock_read_count: This seems to be buggy because the value set is not getting used in the session that altered the system. You need to set other optimizer related values in the same session to make the change of the parameter effective. For more information, see Dion Cho's blog post about the issue.

The next part of the series will cover my "favorite" topic using objects in multiple block sizes and effects on costing, in particular when using system statistics.