|
Oracle 10g AWR (Automatic Workload Repository) -Automatic SQL Tuning Feature-
|
Hi, everyone. From this week, we would like to start a new topic "AWR(Automatic Workload Repository)", which is related with the SQL tuning feature of the automatic management in Oracle 10g.
SQL tuning
How do you usually tune the SQL statements? When the end users complain that the response of the system is too slow, most DBAs first investigate the SQL statements which cause the response to be slow (to extract high-load SQL statements).
After confirming the access pass of those SQL statements, DBAs may rewrite SQL statements or create indices to execute SQL tuning.
There are two steps in this process:
(1) Extracting high-load SQL statements
(2) Tuning those SQL statements
These two tasks can be executed by the automatic feature in Oracle 10g. However, "automatic" does not mean that you can solve problems without doing anything.
Of the two steps, the step of extracting high-load SQL statements is more time-consuming. For a DBA who knows well about the application, it might be easy to find out the SQL statements causing the response to be low.
However, many DBAs would ask the end users to execute the command again and refer to the Oracle dictionary at that timing to specify the high-load SQL statements. It means that the operation must be repeated again to find out the problem.
Keep the log for future need
It is too late to take action after users complain about the response. Most experienced DBAs actually check if there is any high-load SQL statement executed.
When high-load SQL statement exists, it is better to record it in the log by writing scripts or using other tools. There are actually several merits.
(1) DBAs can take action before the users complain.
(2) DBAs can trace back and analyze the response slowdowns of the past.
Even the worst case can be investigated by tracing back to the past.
It is necessary for DBAs to keep not only the record of SQL statements but also many kinds of logs.
Log in Oracle 10g
The log is recorded automatically as default since Oracle 10g. This time we would like to test the AWR (Automatic Workload Repository).
As to the high-load SQL statements, the following tables can be used.
v$active_session_history
dba_hist_active_sess_history
AWR (Automatic Workload Repository)
This feature obtains statistics from the memory with the new process mmon in Oracle 10g and save the information as the log.
These are two main points:
(1) it keeps the log in the memory
(2) it keeps the log on the disk (information written in tables)
There are some main tables for (1) such as v$active_session_history and v$segment_statistics.
These v$ tables are based upon x$ tables. They, as the x$bh tables which are often mentioned in our articles, are also on-memory information.
The information in (2) is written into SYSAUX tablespace and is mainly stored in DBA_HIST_[???????] tables.
Precisely speaking, the original tables are stored as WR[?]_$[???????]. The reason why some information is also written on the disk is that the memory is not enough for keeping all information.
The information on v$active_session_history disk is dba_hist_active_sess_history.
There are two tables in the original table of dba_hist_active_sess_history.
(1) WRM$_SNAPSHOT
(2) WRH$_ACTIVE_SESSION_HISTORY
*/Confirm the original table of DBA_HIST_ACTIVE_SESS_HISTORYiVIEWj/*
SQL> select text from dba_views
2* where view_name = 'DBA_HIST_ACTIVE_SESS_HISTORY';
select ash.snap_id, ash.dbid, ash.instance_number,
[eliminated]
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash
where ash.snap_id = sn.snap_id
and ash.dbid = sn.dbid
and ash.instance_number = sn.instance_number
[eliminated]
union all
select ash.snap_id, ash.dbid, ash.instance_number,
[eliminated]
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY_BL ash
where ash.snap_id = sn.snap_id
and ash.dbid = sn.dbid
and ash.instance_number = sn.instance_number
[eliminated]
|
Is such information in SYSTEM tablespace? Let's check the tablespaces with WRH$_ACTIVE_SESSION_HISTORY.
*Select segments with DBA_HIST_ACTIVE_SESS_HISTORY
SQL> select tablespace_name, segment_name, partition_name
2 from dba_segments
3* where segment_name = 'WRH$_ACTIVE_SESSION_HISTORY'
TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME
---------------- ---------------------------- ------------------------------
SYSAUX WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1536
SYSAUX WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1560
SYSAUX WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1584
SYSAUX WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1608
SYSAUX WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1632
SYSAUX WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1680
SYSAUX WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1704
SYSAUX WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1728
SYSAUX WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN
|
The tablespace is SYSAUX tablespace. AUX is the shortened form of Auxiliary, and it is stored in the auxiliary tablespace of SYSTEM. By the way, these tables are managed by partition (PARTITION_NAME).
Just like we execute partitioning by application to simplify maintenance, it is better to delete the used data by partition. These partitions can be managed by the unit of one day as long as the interval of keeping log on the disk is set to an one-day unit.
Let's check this, too.
*/Select partition keys/*
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from dba_tab_PARTITIONS
2 where table_name = 'WRH$_ACTIVE_SESSION_HISTORY'
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ---------------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1536 511456179, 1560
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1560 511456179, 1584
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1584 511456179, 1608
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1608 511456179, 1632
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1632 511456179, 1680
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1680 511456179, 1704
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1704 511456179, 1728
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_511456179_1728 511456179, MAXVALUE
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN MAXVALUE, MAXVALUE
*/Select sample time (SAMPLE_TIME) with the key listed above/*
SQL> select snap_id ,max(SAMPLE_TIME)
2 from WRH$_ACTIVE_SESSION_HISTORY
3 where dbid=511456179
4 and snap_id in (1560,1584,1608,1632,1680,1704,1728)
5 group by snap_id
SNAP_ID MAX(SAMPLE_TIME)
---------- ------------------------------
1560 04-08-19 02:00:25.013
1584 04-08-20 02:00:36.383
1608 04-08-21 02:00:52.894
1632 04-08-22 02:00:17.953
1680 04-08-24 02:00:37.378
1704 04-08-25 02:00:39.370
1728 04-08-26 02:00:48.546
|
The unit of partitioning is one day. Such management is good because log is managed without adding load on the system.
Yoshihiro Uratsuji
|
|
