|
Oracle 10g AWR (Automatic Workload Repository) -Automatic SQL Tuning Feature-
|
Hello, everyone. We have checked the following two tables so far.
V$ACTIVE_SESSION_HISTORY
The history of active session waits and CPU usage is recorded. Recording is carried out every minute as default by the Oracle background process.
DBA_HIST_ACTIVE_SESS_HISTORY (WRH$_ACTIVE_SESSION_HISTORY)
This is the table where the data of v$active_session_history is written. As the default, 1/10 of the v$active_session_history data is stored in each time unit. Memory is insufficent to keep all data so that writing into the table is necessary.
We would like to use OEM (Oracle Enterprise Manager 10g) and DBMS_SQLTUNE feature which is a package for SQL tuning to specify the problematic SQL. Let's check how OEM takes SQL from v$active_session_history. Here is the test environment.
OS :HP-UX hp11i B.11.11
Oracle:Release 10.1.0.2.0
Execute certain OLTP transactions in this environment
|
Specify the problematic SQL by OEM (Priority SQL)
By OEM we can check SQL of SPOT(current) and PERIOD(certain period in the past). How long is the period of SPOT? It is the period of time for which v$active_session_history is kept.
Judging from the test, we feel that SPOT(now) extracts SQL statements from v$active_sessi on_history by the following SQL statements.
SELECT
event#, sql_id, max(sql_plan_hash_value), max(sql_opcode)
, session_id, p1
, current_obj#, module, action, client_id
, DECODE(wait_time, 0, 'W', 'C')
, COUNT(*),SUM(time_waited)
, 'I', service_hash, session_serial#, max(user_id), max(program)
FROM v$active_session_history
WHERE sample_time > :3
AND sample_time <= :4
GROUP BY event#, sql_id, session_id, p1, current_obj#
, module, action, client_id, DECODE(wait_time, 0, 'W', 'C')
, service_hash, session_serial#
|
Let's take out the important part and rewrite it.
<Obtain the data of active sessions within the last 5 minustes>
SELECT
sql_id
, DECODE(wait_time, 0, 'WAITING', 'ON CPU')
, COUNT(*)
, SUM(time_waited)
FROM v$active_session_history
WHERE sample_time > sysdate - 5/60/24
AND sample_time < sysdate
GROUP BY
sql_id
, DECODE(wait_time, 0, 'WAITING', 'ON CPU')
SQL_ID EVENT COUNT TIME_WAITED
---------------- --------- ---------- ----------------
ON CPU 327 0
WAITING 3 83375
0rb68wt93hyk5 ON CPU 3 0
22v14pywpjbrn ON CPU 6 0
2b064ybzkwf1y ON CPU 1 0
2jka7qqgy3xw7 ON CPU 2 0
2umvfdn2qu9tt ON CPU 4 0
37nvf97dxf5jg ON CPU 1 0
37nvf97dxf5jg WAITING 1 299744
^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^
38scc1w4puyms ON CPU 1 0
3918g7wvyb5bt ON CPU 2 0
3s8mjd045m52c WAITING 1 1373
4wv642fgmjmdm ON CPU 21 0
7u1s6ht5bx6mp ON CPU 5 0
83c1u36qb77zh ON CPU 58 0
8a6vguhsfapdx ON CPU 1 0
9gguxsq7w8cyq ON CPU 2 0
arq6hxb184y28 ON CPU 1 0
axsbhy26rk90y ON CPU 1 0
SQL_ID : the column to identify each unique SQL statement
TIME_WAITED : the unit is 1/10000 second
|
When EVENT is WAITNG, SQL is in the status of waiting, and the total of time waited (TIME_WAITED) at the sampling instant can be confirmed.
Is the SQL with long TIME_WAITED problem?
Looking at the SQL above, which one do you think has the problem? By instinct, SQL with the longest TIME_WAITED (SQL_ID: 37nvf97dxf5jg) should have the problem. Let's check v$active_session_history to see what is causing the waits.
The TIME condition is left off, here.
<Check v$active_session_history to confirm the waiting event of SQL (SQL_ID: 37nvf97dxf5jg)>
select SAMPLE_TIME, TIME_WAITED, EVENT
from v$active_session_history
where TIME_WAITED > 0
and DECODE(wait_time, 0, 'WAITING', 'ON CPU') = 'WAITING'
and SQL_ID = '37nvf97dxf5jg'
^^^^^^^^^^^^^
SAMPLE_TIME TIME_WAITED EVENT
----------------------- ----------- ------------------------------
04-08-20 16:37:12.461 299744 enq: TX - row lock contention
04-08-20 16:19:37.691 532661 enq: TX - row lock contention
04-08-20 16:03:40.811 612359 enq: TX - row lock contention
04-08-20 16:01:18.671 354846 enq: TX - row lock contention
|
The SQL with SQL_ID 37nvf97dxf5jg waited because of row lock contention.
What is WAIT% of SQL in OEM?
In OEM we can see the overall WAIT% of SQL. An SQL with high WAIT% is supposed to have the problem. However, how is WAIT%calculated in OEM?
Is WAIT% calculated from TIME_WAITED?
Simply speaking, the calculation is based on TIME_WAITED to compute time waited of each SQL from the overall time waited. Therefore, WAIT% of lock wait SQL is almost 100. Please ignore the SQL statements without SQL_ID.
However, the SQL statement with time waited 299744 (SQL_ID: 37nvf97dxf5jg) and another with time waited 1373 (SQL_ID: 3s8mjd045m52c) in OEM seem to have the same WAIT%. The reason lies in COUNT (frequency).
What does COUNT mean?
It means the number of record by grouping of SQL_ID and EVENT, i.e. the frequency of sampling. High frequency implies that the execution time of SQL is long or SQL itself is executed many times.
We are not sure if either of ideas is correct since it probably happens by chance. Because v$active_session_history takes active sessions only once a second, the waits that are shorter than 1 second may not be picked up.
However, such reliability issue can be improved by increasing the sampling frequency. Of course it is necessary to consider the performance degradation as a side effect.
Yoshihiro Uratsuji
|
|
