|
Oracle 10g AWR (Automatic Workload Repository) -Automatic SQL Tuning Feature-
|
Hello, everyone. Last time we confirmed that data on the disk in AWR (Automatic Workload Repository) is managed by partition. The data on the disk, here,?means the data which reminds even after Oracle is rebooted.
We would like to check the methods of managing such data and the performance when the log is obtained.
People who have a hard time dealing with the system performance may worry that obtaining so much information of log will deteriorate the system performance. The environment used for the test is RAC with 2 nodes.
By simply checking the space size, the size of SYSAUX tablespace is now 710MB, 6 times as large as the size before installation.
<Obtaining the size of SYSAUX tablespace before and after installation>
SQL> select t.name, d.name
2 , d.bytes/1024/1024 CURRENT_TIME_MB
3 , d.create_bytes/1024/1024 CREATED_TIME_MB
4 from v$tablespace t, v$datafile d
5 where t.ts#=d.ts#
6* and t.name='SYSAUX'
NAME NAME CURRENT_TIME_MB CREATED_TIME_MB
-------- ----------------------------------- --------------- ---------------
SYSAUX +DG01/ora10/datafile/sysaux.261.1 710 120
|
In the high-load environment (where there are many sessions or objects), more space will be required. Anyway, 710MB is pretty large size in a small-scale environment. We can control information acquisition by the following methods.
To control the acquisition of statistical information
By setting the parameter, we can control the acquisition of statistical information.
<Set up the following parameters>
statistics_level
BASIC - to turn the acquisition of statistical information OFF
TYPYCAL - to obtain a part of the necessary information
ALL - to obtain all acquirable statistical information
|
To control the acquisition of the information on the disk (preservation period)
When the statistical information on memory is obtained, it is possible to set up the frequency of writing the information on memory to the disk.
<DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure>
@To specify the preservation period of data by RETENTION
@To specify the interval of obtaining the statistical difference by ITERVAL
# Unit (Min.)
In the environment we use, the preservation period of statistical information is seven days,
and the interval of acquisition is 60 (i.e. 60minutes or 1 hour).
SQL> desc MODIFY_SNAPSHOT_SETTINGS
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
parameter type In/Out Default?
--------------- ----------- ------ --------
RETENTION NUMBER IN DEFAULT
INTERVAL NUMBER IN DEFAULT
DBID NUMBER IN DEFAULT
<Execute the commands to set the preservation period = 3 days,
and the interval of acquisition = 10 minutes>
SQL> exec dbms_workload_repository.modify_snapshot_settings(4320,10,3847444778);
4320 = 3[days]*24[hours]*60[minutes]
<When the information is not obtained, set INTERVAL as 0>
SQL> exec dbms_workload_repository.modify_snapshot_settings(4320,0,3847444778);
<To confirm the current setting from dba_hist_wr_control>
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION
----------- ------------------------------ ------------------------------
3847444778 +40150 00:00:00.0 +00003 00:00:00.0
(not obtained since it is 0) (keep the information of 3 days)
|
By the setup shown above, the log will be obtained at a frequency suitable for the system factors and then analyzed.
How long does each counting process take? We have tested the time required by the command which can obtain SQL traces. Of course the required time depends on the environment, but it takes 17 seconds even in the test environment with little load. By the way, the statistical information includes information of all nodes since it is a system of RAC architecture. This is the result of the test.
<The setting of SQL trace (ON)>
SQL> alter session set events='10046 trace name context forever, level 12';
<To create the snapshot manually>
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');
<The setting of SQL trace (OFF)>
SQL> alter session set events='10046 trace name context off';
<To confirm the trace file created in user_dump_dest>
-- We extracted the part related with recursive call ---
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ ------ ---------- ------ ------- ---------- ------
Parse 2216 0.88 1.49 13 1504 6 0
Execute 5414 6.70 13.67 243 17166 7996 2928
Fetch 9183 0.78 2.06 103 18736 40 12423
------- ------ ------ ---------- ------ ------- ---------- ------
total 16813 8.36 17.24 359 37406 8042 15351
(The elapsed time is about 17 seconds)
Misses in library cache during parse: 257
Misses in library cache during execute: 175
--- The waiting time of recursive call ---
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------- Waited ---------- ------------
library cache lock 426 0.03 0.30
library cache pin 252 0.76 1.60
row cache lock 1695 0.11 0.78
gc cr block 2-way 94 0.00 0.10
rdbms ipc reply 104 0.01 0.05
ksfd: async disk IO 367 0.00 0.00
db file sequential read 318 0.00 0.02
gc cr grant 2-way 53 0.00 0.01
gc current block 2-way 126 0.18 0.38
control file sequential read 49 0.00 0.00
enq: TM - contention 56 0.01 0.02
gc current grant busy 51 0.00 0.01
(eliminated)
|
Although much workload does not seem to appear in the test environment, it is predictable that higher activities may obviously add much workload to the environment. This setup should be executed based upon the pros and cons of obtaining log.
Yoshihiro Uratsuji
|
|
