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.
Hi Randolf,
ReplyDeleteI got a test about changing processes parameter and see what it effects after the change.
Initial value was 150 and increased value was 1500.
I got what I expected but I wasnt expecting for db_file_multiblock_read_count to decrease. Do you have an opinion why this parameter decreases ? Is it just for increasing concurrency for not competing for the same blocks ?
Here are my test results on 11.1.0.6(hope they will appear alright)
PD_NAME PD_VALUE PD_VALUE
------------------------------ ---------- ----------
processes 150 1500
sessions 170 1655
_messages 300 3000
_enqueue_locks 2300 19850
_enqueue_resources 968 7500
_enqueue_hash 375 3345
_db_handles 750 7500
db_file_multiblock_read_count 49 5
_db_file_exec_read_count 49 5
dml_locks 748 7280
transactions 187 1820
_cursor_db_buffers_pinned 53 3
Coskan,
ReplyDeletethanks for sharing this info.
I assume that you've left "db_file_multiblock_read_count" unset. Am I right?
What platform and default block size was this tested on?
What is your CPU_COUNT showing, what is your SGA/MEMORY_TARGET/PGA and physical memory?
I find the value of 49 for 150 processes a bit odd, since the default is usually 1MB on most platforms if left unset from 10.2 on, and 49 doesn't fit that with any of the usual block sizes.
In the 10.2 / 11.1 / 11.2 Reference manual it says for "db_file_multiblock_read_count": "Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers."
Although this leaves room for interpretation and at least in my opinion suggests that this is more of dynamic nature ("number of sessions"), your results suggest that it is simply derived from the "sessions" instance parameter and probably some others I guess.
I think it makes sense in general however, since you usually don't expect 1000+ sessions to perform huge table scans concurrently. This number of sessions suggests that it is a typical OLTP application with precise queries and small result sets which usually can be satisfied by index access paths.
It would be interesting to know what the "_db_file_optimizer_read_count" is set to in your case. I understand from your post that you've only included those parameters that changed when modifying the "processes" setting, right?
Furthermore your result suggests that if you have mixed workload of OLTP and batch processing with a large "processes"/"sessions" setting you might need to change the "_db_file_exec_read_count" to a larger value for some batch processing that relies on full table scans.
Randolf
Thank you for your answers Randolf. Everything starts to make sense especially after your excerpt from documentation.
ReplyDelete>>> assume that you've left "db_file_multiblock_read_count" unset. Am I right?
True
and here are the other values from my test system
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL>@p
NAME VALUE
------------------------------ ---------
cpu_count 2
sga_target 0
memory_target 524288000
memory_max_target 524288000
db_block_size 8192
_db_file_optimizer_read_count 8
pga_aggregate_target 0
Interestingly changing the processes parameter did not change the plans for your tests cases. do we need a workload for your last assumption about db_file_exec_read_count?
Coskan,
ReplyDeleteregarding your last question: You've answered this already yourself: The "_db_file_optimizer_read_count" is used in your particular case for the cost calculation, therefore the test cases will still show the same result if this doesn't get changed with the "processes" parameter change.
At runtime however, different number of blocks would be read per multi-block request depending on the "_db_file_exec_read_count".
My last point was may be a bit unclear. What I meant to say is that given this small "_db_file_exec_read_count" it might be reasonable to change this setting on "session" level temporarily if there is a large batch job that might benefit from larger multi-block read requests.
Again, please note that these settings only influence the actual execution, but not the optimizer's calculations.
I still would like to test what exactly influences the automatic adjustment of "_db_file_exec_read_count". Given your remaining parameters I can think of that Oracle really comes to the conclusion that your system will have a hard time supporting 1,500 concurrent processes/sessions and therefore reduces the "_db_file_exec_read_count" accordingly.
It would be interesting to see the different automatically adjusted "_db_file_exec_read_count" results on a more server-like hardware (more CPU/memory etc.) depending on the "processes" parameter; I haven't checked this yet in detail.
Randolf