H.Tonguç YILMAZ Blog

Following tests were made on Oracle XE;

– connect with SYS in this first session and start gathering
– initially system statistics are not available
SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = ‘SYSSTATS_MAIN’;

PNAME                               PVAL1
—————————— ———-
CPUSPEED
CPUSPEEDNW                        495,977
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

9 rows selected.

SELECT TO_CHAR(sysdate,’DD-MON-YY HH24:MI:SS’) “Stop” FROM dual;
exec dbms_stats.gather_system_stats(’start’);
–
– in this period we will create some load from another HR session
–
– may use this to sleep this session for 3 minutes
– exec dbms_lock.sleep(180);
–
– connect with HR in this second session
– to simulate an olap load
create table tab_stress_me nologging parallel 2 as
select * from dba_source order by 1,2,3;
EXEC DBMS_STATS.gather_table_stats(user, ‘tab_stress_me’, estimate_percent => 50, block_sample => FALSE, degree => 2, granularity => ‘ALL’, cascade => TRUE) ;
commit;
drop table tab_stress_me purge;
execute dbms_random.seed(0)
create table t1 nologging parallel 2 as select
rownum                id,
trunc(dbms_random.value(1,50))    n1,
rpad(’x',100)            padding
from    dba_source ;
create unique index pk_t1_id on t1(id) nologging parallel 2;
EXEC DBMS_STATS.gather_table_stats(user, ‘t1′, estimate_percent => 50, block_sample => FALSE, degree => 2, granularity => ‘ALL’, cascade => TRUE) ;
commit;
–
– to simulate an oltp load
BEGIN
FOR i IN 1 .. 1000 LOOP
FOR c IN (select n1, padding from t1 where id = i) LOOP
NULL;
END LOOP;
END LOOP;
END;
/
drop table t1 purge;
–
– return to the session connected with SYS and stop gathering
exec dbms_stats.gather_system_stats(’stop’);
SELECT TO_CHAR(sysdate,’DD-MON-YY HH24:MI:SS’) “Stop” FROM dual;
commit;
–
– system statistics are now available
SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = ‘SYSSTATS_MAIN’;

PNAME                               PVAL1
—————————— ———-
CPUSPEED                              496
CPUSPEEDNW                        495,977
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR                            7029760
MBRC                                   88
MREADTIM                           26,065
SLAVETHR
SREADTIM                              4,8

9 rows selected.

References

Understanding System Statistics by Jonathan Lewis
http://www.oracle.com/technology/pub/articles/lewis_cbo.html
AskTom “CPU costing, mreadtim < sreadtim”
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:67994814192949
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) - 14.4 System Statistics
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#sthref1468
Playing With System Statistics
http://oraclesponge.blogspot.com/2005/12/playing-with-system-statistics.html