With this post, I would like to share one tip related to SLOB Logical I/O testing when you need to find out the maximum “Logical read (blocks) per second” your “benchmark” is capable of.
The tip is the rule number two, but let’s describe the well known rule number one first.
Rule number one: When testing Logical I/O you have to ensure that the AWR report generated by SLOB shows a “Buffer Hit %” of 100% and a “DB CPU” event that is more than 99% of the DB Time. That is to say something like:
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Hit %: 100.00 In-memory Sort %: 100.00
-----------------------------------------------------------------
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
DB CPU 184. 99.6
control file sequential read 176 .1 0 .0 System I/O
Disk file Mirror Read 43 0 0 .0 User I/O
Disk file operations I/O 45 0 0 .0 User I/O
Question: Is that enough to ensure that the “benchmark” delivered the maximum ?
Answer: No, this is not enough.
For example during 2 Logical I/O tests (using only one reader), with those slob.conf parameters:
UPDATE_PCT=0
RUN_TIME=180
WORK_LOOP=0
SCALE=1000
I am able to generate (With rule number one followed, that is to say DB CPU=99.6% and Buffer Hit=100%) this load profile:
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 1.0 8.9 0.00 1.09
DB CPU(s): 1.0 8.8 0.00 1.09
Redo size (bytes): 7,098.4 61,501.9
Logical read (blocks): 35,568.6 308,171.6
Or this one:
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 1.0 8.8 0.00 0.69
DB CPU(s): 1.0 8.8 0.00 0.69
Redo size (bytes): 6,684.2 57,877.5
Logical read (blocks): 1,075,426.1 9,312,012.1
As you can see the number of Logical I/O per second delivered during the 2 benchmarks are completely different. Going from about 35 000 to about 1 million even if :
- The database did not record any wait events, meaning DB CPU is > 99%
- The Buffer Hit is 100%
- The number of reader is the same (One in this case: DB TIME = 1 second per second)
So, how can I check that the “benchmark” delivered the maximum ?
As slob launchs a query to generate the I/O, we have to check that the elapsed time(s) of the query is about “run time(s) * number of readers” that is to say 180 seconds in our case.
Let’s check the AWR “SQL ordered by Elapsed Time” section.
For the 35 000 Logical I/O run:
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
180.0 1 180.02 96.9 99.6 .0 0c1s032jgyrd7
Module: SQL*Plus
DECLARE v_default_schema_number PLS_INTEGER := '1'; v_update_pct PLS_INTEGER :=
'0'; v_max_loop_iterations PLS_INTEGER := '0'; v_seconds_to_run PLS_INTEGER := '
180'; v_scale PLS_INTEGER := '1000'; v_work_unit PLS_INTEGER := '1' ; v_redo_str
ess VARCHAR2(12) := 'HEAVY'; v_shared_data_modulus PLS_INTEGER := '0'; v_nt_arg
72.1 3,230,605 0.00 38.8 99.9 .0 bhdvtsvjhgvrh
Module: SQL*Plus
SELECT COUNT(C2) FROM CF1 WHERE ( CUSTID > ( :B1 - :B2 ) ) AND (CUSTID < :B1 )
For the 1000000 Logical I/O run:
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
180.0 1 180.03 96.9 99.6 .0 g41kknwap6na9
Module: SQL*Plus
DECLARE v_default_schema_number PLS_INTEGER := '1'; v_update_pct PLS_INTEGER :=
'0'; v_max_loop_iterations PLS_INTEGER := '0'; v_seconds_to_run PLS_INTEGER := '
180'; v_scale PLS_INTEGER := '1000'; v_work_unit PLS_INTEGER := '1000' ; v_redo_
stress VARCHAR2(12) := 'HEAVY'; v_shared_data_modulus PLS_INTEGER := '0'; v_nt_
172.1 195,156 0.00 92.7 99.7 .0 bhdvtsvjhgvrh
Module: SQL*Plus
SELECT COUNT(C2) FROM CF1 WHERE ( CUSTID > ( :B1 - :B2 ) ) AND (CUSTID < :B1 )
As you can see during the 1000000 Logical I/O run, the elapsed time of the SQL responsible of the Logical I/O is about 172 seconds while it is only about 72 seconds into the 35 000 Logical I/O run.
Which lead me to the rule number 2: We have to check that the elapsed time(s) of the SQL responsible of the Logical I/O is about “number of readers * run time(s)”.
For example for a run time of 180 seconds and 20 readers (./runit.sh 20) you should observe something like:
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
3,487.2 2,710,259 0.00 96.7 99.7 .0 bhdvtsvjhgvrh
Module: SQL*Plus
SELECT COUNT(C2) FROM CF1 WHERE ( CUSTID > ( :B1 - :B2 ) ) AND (CUSTID < :B1 )
Remarks:
- I had to play with the “WORK_UNIT” slob parameter to reach 1000000 Logical I/O per second.
- WORK_UNIT=256 per default (which was not enough on my system to deliver the maximum Logical I/O per second): You may need to increase it as well if the rule number 2 is not followed.
Conclusion:
To ensure that your SLOB benchmark delivers the maximum Logical I/O per second, you need to check:
- That the AWR report generated by SLOB shows a “Buffer Hit %” of 100% and a “DB CPU” event that is more than 99% of the DB Time.
- That the elapsed time(s) of the SQL responsible of the Logical I/O is about “number of readers * run time(s)”. If not, you may need to increase the WORK_UNIT slob parameter.