|
Oracle10g Cost Base Optimizer -DBMS_STATS-
|
Hi, everyone. This time we want to talk about Export/Import. We want to discuss how to manage the system statistics data in order to execute the optimal execution plan according to the actual situation. Although CPU is not heavily used in online processing during the daytime, the patch processing at night may run many jobs at the same time and use the CPU to almost the limit. In that case, the system statistics may vary much according to the timing when the statistics are obtained. Therefore, we try to use the system statistics at OLTP time and patch-processing time separately.
Environment
Redhat Linux Advanced Server 2.1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
|
Experiment
We use the statistics at OLTP time and patch-processing time separately by executing Export/Import.
Create the statistics table OLTP_SYSTEM_STAT.
SQL> exec dbms_stats.create_stat_table
('SYSTEM','OLTP_SYSTEM_STAT','TS_RIN');
PL/SQL procedure completed.
|
Next, insert the statistics we obtained into OLTP_SYSTEM_STAT.
SQL> exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',
interval=>'1',stattab=>'OLTP_SYSTEM_STAT',statown=>'SYSTEM');
PL/SQL procedure completed.
|
Now, the statistics at OLTP time should have been obtained, and let's check it by DBMS_STATS.GET_SYSTEM_STATS. Let's focus on Single Block Read Time, here.
SQL> declare
2 status varchar2(20);
3 dstart date;
4 dstop date;
5 pvalue number;
6 begin
7 dbms_stats.get_system_stats
(status,dstart,dstop,'SREADTIM',pvalue,'OLTP_SYSTEM_STAT',null,'SYSTEM');
8 dbms_output.put_line('Status : '|| status );
9 dbms_output.put_line('Start : '|| to_char
(dstart,'YYYY/MM/DD HH24:MI:SS') );
10 dbms_output.put_line('Stop : '|| to_char
(dstop ,'YYYY/MM/DD HH24:MI:SS') );
11 dbms_output.put_line('SREADTIM : '|| pvalue );
12 end;
13 /
Status : COMPLETED
Start : 2005/02/08 16:22:00
Stop : 2005/02/08 16:23:00
SREADTIM : 5.581
|
Masaru Hayashi
|
|
