| Oracle10g Cost Base Optimizer
-DBMS_STATS- |
After the procedures we
tested last week, let's execute GATHER_SYSTEM_STATS when the
system bears high-load.
SQL> exec dbms_stats.gather_system_stats
(gathering_mode=>'INTERVAL',
interval=>'1',statown=>'SYSTEM');
PL/SQL procedure completed.
SQL> select * from aux_stats$ where sname = 'SYSSTATS_MAIN';
SNAME PNAME PVAL1 PVAL2
--------------- --------------------------- ---------- --------------------
SYSSTATS_MAIN CPUSPEEDNW 566.369
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 45.327
SYSSTATS_MAIN MREADTIM 2.588
SYSSTATS_MAIN CPUSPEED 574
SYSSTATS_MAIN MBRC 15
SYSSTATS_MAIN MAXTHR 26624
SYSSTATS_MAIN SLAVETHR
|
In order
to return to OLTP time, let's execute IMPORT from
OLTP_SYSTEM_STAT.
SQL> exec dbms_stats.import_system_stats(stattab=>
'OLTP_SYSTEM_STAT',statown=>'SYSTEM');
PL/SQL procedure completed.
SQL> select * from aux_stats$ where sname = 'SYSSTATS_MAIN';
SNAME PNAME PVAL1 PVAL2
--------------- --------------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 02-08-2005 16:22
SYSSTATS_INFO DSTOP 02-08-2005 16:23
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 566.369
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 5.581
SYSSTATS_MAIN MREADTIM 54.426
SYSSTATS_MAIN CPUSPEED 574
SYSSTATS_MAIN MBRC 16
SYSSTATS_MAIN MAXTHR 26624
SYSSTATS_MAIN SLAVETHR
|
It is
successfully IMPORTed. With this procedure, we can use the system
statistics at OLTP time and patch-process time separately. This is
the last article for Oracle 10g Cost Base Optimizer series. Please
look forward to the next interesting topic.
Masaru
Hayashi
|
|
