CPU Costing and db_file_multiblock_read_count

In 10g whether you’re are aware of CPU Costing feature or not , you’re using it. You’re using it explicitly or implicitly varying depending on if you’ve gathered or set system statistics regarding disk i/o and cpu characteristics.

you gather system statistics by dbms_stats API:


execute dbms_stats.gather_system_stats('start');

-- after some meaningful workload --

execute dbms_stats.gather_system_stats('stop');

You can also set system statistics manually. In case that Oracle has trouble in gathering appropriate “Multi-block read speed” caused by READ-AHEAD cache of some disks, e.g. SAN disks, as described later in this article, you can gather statistics by some operating system utility and set proper values of single-block read speed and multi-block read speed into Oracle data dictionary manually.


begin
dbms_stats.set_system_stats( 'CPUSPEEDNW', 1559.026 );
dbms_stats.set_system_stats( 'IOSEEKTIM', 10 );
dbms_stats.set_system_stats( 'IOTFRSPEED', 4096 );
dbms_stats.set_system_stats( 'SREADTIM', 7.363 );
dbms_stats.set_system_stats( 'MREADTIM', 8.086 );
dbms_stats.set_system_stats( 'CPUSPEED', 1559 );
dbms_stats.set_system_stats( 'MBRC', 16 );
dbms_stats.set_system_stats( 'MAXTHR', 288768 );
commit;
end;
/

As result you will find table sys.aux_ststs$ filled with data like:

select * from sys.aux_stats$ where sname='SYSSTATS_MAIN';

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                       1559.026
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM                            7.363
SYSSTATS_MAIN                  MREADTIM                            8.086
SYSSTATS_MAIN                  CPUSPEED                             1559
SYSSTATS_MAIN                  MBRC                                   16
SYSSTATS_MAIN                  MAXTHR                             288768
SYSSTATS_MAIN                  SLAVETHR

If you have not done any action above, table sys.aux_stats$ is filled with some “NOWORKLOAD” statistics by default in 10g.


select * from sys.aux_stats$

SNAME                PNAME                     PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_INFO        STATUS                          COMPLETED
SYSSTATS_INFO        DSTART                          05-27-2010 06:43
SYSSTATS_INFO        DSTOP                           05-27-2010 06:43
SYSSTATS_INFO        FLAGS                         1
SYSSTATS_MAIN        CPUSPEEDNW           851.489985
SYSSTATS_MAIN        IOSEEKTIM                    10
SYSSTATS_MAIN        IOTFRSPEED                 4096
SYSSTATS_MAIN        SREADTIM
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED
SYSSTATS_MAIN        MBRC
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        SLAVETHR

Oracle Optimizer uses these system statistics to estimate cost. The following formula (cited from Cost-based Oracle Fundamental) explains how Optimizer figures out the cost.


Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim

where

#Srds = number of single block reads
#Mrds = number of multi-block reads, it equals to "total blocks of FTS or FFIS" / "multiblock count per read"
#CPU Cycles = total CPU operations
sreadtim: single block read speed
mreadtim: multi-block read speed
cpuspeed: CUP operations per second

it’s recommended to unset db_file_multiblock_read_count and leave Oracle choosing an suitable value based on characteristic of system workload and system capacity. Oracle will use a dynamical value to read multple blocks involved in FTS and FFIS.

We can unset db_file_multiblock_read_count in this way:


SQL> show parameter db_file_multiblock_read_count;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_file_multiblock_read_count        integer     16

SQL> alter system reset db_file_multiblock_read_count scope=spfile sid='*';

SQL> shutdown immediate;

SQL> startup

SQL> show parameter db_file_multiblock_read_count;

NAME                                 TYPE        VALUE
 ------------------------------------ ----------- -------
 db_file_multiblock_read_count        integer     115

SQL> select isdefault, ISMODIFIED, ISADJUSTED from v$parameter where name = 'db_file_multiblock_read_count';

ISDEFAULT ISMODIFIED ISADJ
--------- ---------- -----
TRUE      FALSE      FALSE

However, the CPU costing introduced in 9i has impact on this parameter.

If and only if the system statistics is not gathered (dbms_stats.gather_system_stats()) or is not set (dbms_stats.set_system_stats()), db_file_multiblock_read_count impacts optimizer’s cost calculation, that is, the bigger d_f_mb_r_c is, the lower cost calculated by Optimizer, until d_f_mb_r_c reaches a system limit of reading multiple blocks.

If system statistics present in database, either by gathered with meaningful workloads or by explicitly set with dbms_stats.set_system_stats(), the d_f_mb_r_c is ignored in cost calculation and system statistics value MBRC is used instead.

Check whether if system statistics present.

select * from sys.aux_stats$ where sname='SYSSTATS_MAIN';

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_MAIN                  CPUSPEEDNW                       1009.264
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

execute dbms_stats.gather_system_stats('start')

-- some meaningful workload --

execute dbms_stats.gather_system_stats('stop')

select * from sys.aux_stats$ where sname='SYSSTATS_MAIN';

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                       1559.026
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM                            7.363
SYSSTATS_MAIN                  MREADTIM                            8.086
SYSSTATS_MAIN                  CPUSPEED                             1559
SYSSTATS_MAIN                  MBRC                                   16
SYSSTATS_MAIN                  MAXTHR                             288768
SYSSTATS_MAIN                  SLAVETHR

Here is a demonstration:


guang@db10r2> set echo on
column pval2 format a30
column value format a20
column PLAN_TABLE_OUTPUT format a72 truncate
set linesize 1000

drop table t;
create table t as select * from all_objects where 1=0;
exec dbms_stats.set_table_stats( user, 'T', numrows=>100000, numblks => 10000  );

exec dbms_stats.delete_system_stats;
select * from sys.aux_stats$;
set serveroutput OFF
alter session set db_file_multiblock_read_count = 1;
select * from t T1 where object_id = 0;
select * from table(dbms_xplan.display_cursor);

alter session set db_file_multiblock_read_count = 64;
select * from t T2 where object_id = 0;
select * from table(dbms_xplan.display_cursor);

begin
dbms_stats.set_system_stats( 'CPUSPEEDNW', 1559.026 );
dbms_stats.set_system_stats( 'IOSEEKTIM', 10 );
dbms_stats.set_system_stats( 'IOTFRSPEED', 4096 );
dbms_stats.set_system_stats( 'SREADTIM', 7.363 );
dbms_stats.set_system_stats( 'MREADTIM', 8.086 );
dbms_stats.set_system_stats( 'CPUSPEED', 1559 );
dbms_stats.set_system_stats( 'MBRC', 16 );
dbms_stats.set_system_stats( 'MAXTHR', 288768 );
commit;
end;
/

select * from sys.aux_stats$;

set serveroutput OFF
alter session set db_file_multiblock_read_count = 1;
select * from t T3 where object_id = 0;
select * from table(dbms_xplan.display_cursor);

alter session set db_file_multiblock_read_count = 64;
select * from t T4 where object_id = 0;
select * from table(dbms_xplan.display_cursor);guang@db10r2> guang@db10r2> guang@db10r2> guang@db10r2> guang@db10r2> guang@db10r2>
drop table t
 *
ERROR at line 1:
ORA-00942: table or view does not exist

guang@db10r2>
Table created.

guang@db10r2>
PL/SQL procedure successfully completed.

guang@db10r2> guang@db10r2>
PL/SQL procedure successfully completed.

guang@db10r2>
SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    03-02-2011 15:34
SYSSTATS_INFO                  DSTOP                                     03-02-2011 15:34
SYSSTATS_INFO                  FLAGS                                   0
SYSSTATS_MAIN                  CPUSPEEDNW                       1146.333
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

guang@db10r2>
Session altered.

guang@db10r2>
no rows selected

guang@db10r2>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  0ar2zcdn01kq1, child number 0
-------------------------------------
select * from t T1 where object_id = 0

Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 10008 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  1000 |    97K| 10008   (1)| 00:02:01
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("OBJECT_ID"=0)

18 rows selected.

guang@db10r2> guang@db10r2>
Session altered.

guang@db10r2>
no rows selected

guang@db10r2>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  f83ma65zsx0h7, child number 0
-------------------------------------
select * from t T2 where object_id = 0

Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  1806 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  1000 |    97K|  1806   (1)| 00:00:22
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("OBJECT_ID"=0)

18 rows selected.

PL/SQL procedure successfully completed.

guang@db10r2> guang@db10r2>
SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    03-02-2011 15:34
SYSSTATS_INFO                  DSTOP                                     03-02-2011 15:34
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                       1559.026
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM                            7.363
SYSSTATS_MAIN                  MREADTIM                            8.086
SYSSTATS_MAIN                  CPUSPEED                             1559
SYSSTATS_MAIN                  MBRC                                   16
SYSSTATS_MAIN                  MAXTHR                             288768
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

guang@db10r2> guang@db10r2> guang@db10r2>
Session altered.

guang@db10r2>
no rows selected

guang@db10r2>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  gk1yfsrdfjtcz, child number 0
-------------------------------------
select * from t T3 where object_id = 0

Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   696 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  1000 |    97K|   696   (2)| 00:00:06
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("OBJECT_ID"=0)

18 rows selected.

guang@db10r2> guang@db10r2>
Session altered.

guang@db10r2>
no rows selected

guang@db10r2>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  7x8b13dvnmng6, child number 0
-------------------------------------
select * from t T4 where object_id = 0

Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   696 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  1000 |    97K|   696   (2)| 00:00:06
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("OBJECT_ID"=0)

18 rows selected.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: