System Statistics « Oracle Scratchpad

April 30, 2007

System Statistics

Filed under: CBO, Statistics, Tuning — Jonathan Lewis @ 8:21 pm UTC Apr 30,2007

In chaper 2 of Cost Based Oracle – Fundamentals, I made the following comment about system statistics.

“… you could simply calibrate your hardware (or at least the I/O subsystem) for absolute performance figures …”

I recently received an email asking me how to ‘calibrate the hardware’, and to my surprise I discovered that I had never explained in the book what I meant.

The idea is simple – to my mind, the purpose of system statistics is to tell Oracle how the hardware can behave when under stress; and for most people the most critical hardware is their set of discs themselves.

So my theory is that we would like to tell Oracle about the load that the discs can bear when under pressure. However, for various reasons – including SAN caches, read-ahead algorithms and the like - Oracle loses sight of the discs and can easily gather very misleading statistics about the mreadtim particularly.

To calibrate the devices independently of Oracle, simply create a huge data file (a few hundred gigabytes would be realistic nowadays) and start doing a lot of random reads.

To start with, make the random reads the same size as your Oracle block size, using offsets from the start of file that are always a multiple of the Oracle block size. This is to emulate ramdon indexed access.

Then pick your db_file_multiblock_read_count and change the read size to db_file_multiblock_read_count * db_block_size, still calculating the offsets as a multiple of the single block size. This is to emulate Oracle’s multiblock reads, but avoiding the confusion caused by read-ahead and predictive caching. We do this because the physical I/O still takes place and still impacts on all other I/O activity, even though one individual process may benefit from the read-ahead.

Set the MBRC to your chosen db_file_multiblock_read_count, use the average read time from the first test as the sreadtim, and the average read time from the second test as the mreadtim.

Footnote: in 10g, if you don’t create system statistics, then Oracle “invents” them, based on your db_file_multiblock_read_count and (usually notional) values for the seek time and transfer rate of your disks; and the default values used by Oracle seem to work quite well. If in doubt, setting MBRC to 8, sreadtim to 12 and mreadtim to 26 – the effective defaults for 10g – will probably be better than taking no action in 9i for most OLTP systems.

Warning: System statistics have a global impact on your database. You don’t shouldn’t change them on a production system without going through your usual acceptance test cycle first.

[See also: Systems Stats Strategy]

Comments (9)

9 Comments »

This is another very interesting post and is something I’d like to try out. How do you you usually go about populating your huge tablespace, do you use something that uses existing database in the database or do you use your own scripts which manufacturers data ?.

Comment by Chris Adkin — May 2, 2007 @ 8:58 am UTC May 2,2007 | Reply
uhm…

I think that Jonathan is talking about “off db” testing e.g. a little C program or something like that.

Or not?

Comment by Antonio — May 2, 2007 @ 12:23 pm UTC May 2,2007 | Reply
You can use a utility like Filebench to help gather this kind of info for Solaris as well as define your IO subsystem performance characteristics.

http://www.solarisinternals.com/wiki/index.php/FileBench

Easier than writing C code !

Comment by Jeroen Wilms — May 3, 2007 @ 6:01 am UTC May 3,2007 | Reply
@Jeroen

but it’s only for Solaris!

I want to be…portable! ^_^

Really, I hope that Jonathan can explain a bit more: is he talking of an external (O.S. program, not an Oracle one) or an internal one (a PL/SQL program, a particular query etc…)

Jeroen, thank you for the uri, I’ll check it as soon as possible!

Comment by Antonio — May 3, 2007 @ 12:03 pm UTC May 3,2007 | Reply
This one could help too.

http://www.iometer.org

Comment by Oscar de la Torre — May 3, 2007 @ 2:26 pm UTC May 3,2007 | Reply
The intention was to do a completely “off-db” test, using an external program. I used to use a pair of very simple C programs I wrote about 10 years ago (and still available at this URL on my website) to do this, but since then IOZone has been published, and Oracle recently launched Orion to do something similar. (I haven’t tested either product).

Comment by Jonathan Lewis — May 4, 2007 @ 5:59 am UTC May 4,2007 | Reply
While reading Oracle 11g New Feature Guide paragraph 1.10.6.1 I/O Calibration, I have immediately remembered this blog entry.
It looks like Oracle is listening to it’s users more and more. In 11g there is new procedure dbms_resource_manager.CALIBRATE_IO(). That does exactly what was described in this blog entry

Best regards,
Edgar

Comment by Edgar Chupit — August 10, 2007 @ 2:30 pm UTC Aug 10,2007 | Reply
Jonathan, I think I´m wrong when understanding how System Statistics work. I have done a little test which shows me different results against theory.

Oracle version: 10.2.0.3 on Windows XP.

SQL>
SQL> show parameter db_file_multiblock_read_count

NAME TYPE VALUE
———————————— ———– ——————————
db_file_multiblock_read_count integer 4
SQL>
SQL> show parameter block_size

NAME TYPE VALUE
———————————— ———– ——————————
db_block_size integer 8192
SQL>

SQL> select substr(a.ksppinm,1,20) “Parameter”,
2 substr(b.ksppstvl,1,20) “Session Value”,
3 c.ksppstvl “Instance Value”
4 from x$ksppi a, x$ksppcv b, x$ksppsv c
5 where a.indx = b.indx and a.indx = c.indx
6 and a.ksppinm like
7 ‘%_db_file%’;

Parameter Session Value Instance Value
——————– ——————– ——————–

_db_file_direct_io_count 1048576 1048576

_db_file_exec_read_count 4 4

_db_file_optimizer_read_count 4 4

_db_file_noncontig_mblock_read_count 11 11

_db_file_format_io_buffers 4 4

SQL>

My System Statistics are noworkload.

SQL> select * from aux_stats$;

SNAME PNAME PVAL1 PVAL2
—————————— —————————— ———- ———————————————-

SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 11-05-2007 14:14
SYSSTATS_INFO DSTOP 11-05-2007 14:14
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1019,59637
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 filas seleccionadas.

SQL> drop table t cascade constraints;

Tabla borrada.

SQL>
SQL> create table t(n number) tablespace no_assm;

Tabla creada.

SQL>
SQL> exec dbms_stats.set_table_stats(user,’T',numblks => 1000,numrows =>100);

Procedimiento PL/SQL terminado correctamente.

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select * from t;

Plan de Ejecuci¾n
———————————————————-
Plan hash value: 1601196873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 100 | 1300 | 377 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| T | 100 | 1300 | 377 (1)| 00:00:05 |
————————————————————————–

SQL>

Now, I´ll change the value of db_file_multiblock_read_count parameter but
the cost of the query remains constant until I restart the instance so Oracle re-negotiates
the max I/O with the O.S. in my context.

SQL> alter system set db_file_multiblock_read_count=32;

Sistema modificado.

SQL>
SQL> set autotrace off
SQL> select substr(a.ksppinm,1,20) “Parameter”,
2 substr(b.ksppstvl,1,20) “Session Value”,
3 c.ksppstvl “Instance Value”
4 from x$ksppi a, x$ksppcv b, x$ksppsv c
5 where a.indx = b.indx and a.indx = c.indx
6 and a.ksppinm like
7 ‘%_db_file%’;

Parameter Session Value Instance Value
——————– ——————– ——————–

_db_file_direct_io_c 1048576 1048576

_db_file_exec_read_c 32 32 —–> The same as dbfmbrc. Are always the same as dbfmbrc ?

_db_file_optimizer_r 32 32 —–> The same as dbfmbrc. Are always the same as dbfmbrc ?

_db_file_noncontig_m 11 11

_db_file_format_io_b 4 4

SQL>
SQL> Although I changed the value of dbfmbrc, the cost doesn´t change…
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select * from t;

Plan de Ejecuci¾n
———————————————————-
Plan hash value: 1601196873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10000 | 126K| 377 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| T | 10000 | 126K| 377 (1)| 00:00:05 |
————————————————————————–

SQL>
SQL> …Until I restart the instance

SQL> shutdown immediate
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>
SQL>
SQL> startup

Instancia ORACLE iniciada.
Base de datos montada.
Base de datos abierta.
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select * from t;

Plan de Ejecuci¾n
———————————————————-
Plan hash value: 1601196873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10000 | 126K| 195 (1)| 00:00:03 |
| 1 | TABLE ACCESS FULL| T | 10000 | 126K| 195 (1)| 00:00:03 |
————————————————————————–

SQL>
New cost has been calculated. It looks that Oracle negotiates the new I/O based on
(as you explained in you great book) the db_file_multiblock_read_count value.

SQL> spool off
SQL>

Things that I can´t understand.

1. In the page 20 of the book, I read the following:

“If the optimizer uses these noworkload statistics, it takes the preceding values, the db_block_size, and the db_file_multiblock_read_count, and synthesizes some values for the sreadtim, mreadtim, and MBRC.
• MBRC is set to the actual value of db_file_multiblock_read_count.
….”

but I am not able to see this.

2. What is the real effect of the parameters _db_file_exec_read_count and _db_file_optimizer_read_count? When I change the value of dbfmbrc, these parameters change too to the same value. Is there any case where they take another different value?

I found an article: http://hungrydba.com/10gsystemstats.aspx Are you agree with it?

My conclusion when there is noworkload System Statistics are:

Oracle calculates the full scan table (index fast full scan) cost using an adjusted dbf_mbrc calculated at the instance start and based on the value of db_file_multiblock_read_count. This adjusted value will be always used although I change the value of db_file_multiblock_read_count until instance is restarted so the full scan cost will be (num of blocks / adjusted dbfmbrc) (of course with the round problem). On the other hand, I haven´t found the
real influence of the underscore parameters _db_file_exec_read_count and _db_file_optimizer_read_count.

Best regards, Ricardo.

Comment by Ricardo — February 14, 2008 @ 4:09 pm UTC Feb 14,2008 | Reply
In reference to one of the earlier comments, as of April 2009, Filebench is now available for Linux and MacOSX as well as for Solaris.

Comment by Andy Zitelli — April 11, 2009 @ 12:48 am UTC Apr 11,2009 | Reply