Database Reference
In-Depth Information
the data dictionary base table
WRH$_SQLTEXT
and may be accessed through the view
DBA_HIST_
SQLTEXT
(script
awr_sqltext.sql
).
SQL> SET LONG 1048576
SQL> COLUMN sql_text FORMAT a64 WORD_WRAPPED
SQL> SELECT sql_id, sql_text
FROM dba_hist_sqltext
WHERE dbms_lob.instr(sql_text, '&pattern', 1, 1) > 0;
Enter value for pattern: FROM hr.employees emp, hr.employees mgr
SQL_ID SQL_TEXT
------------- -------------------------------------------------------------
1yw85nghurbkk SELECT emp.last_name, emp.first_name, j.job_title,
d.department_name, l.city,
l.state_province, l.postal_code, l.street_address, emp.email,
emp.phone_number, emp.hire_date, emp.salary, mgr.last_name
FROM hr.employees emp, hr.employees mgr, hr.departments d,
hr.locations l, hr.jobs j
WHERE emp.manager_id=mgr.employee_id
AND emp.department_id=d.department_id
AND d.location_id=l.location_id
AND emp.job_id=j.job_id
Having retrieved the
SQL_ID
, we may now search for AWR snapshots that captured the
statement. The view
DBA_HIST_SQLSTAT
not only contains the snapshot identifiers, but also
gives access to execution statistics, hash values of execution plans, and the optimizer environ-
ment used (script
awr_sqlstat.sql
).
SQL> SELECT st.snap_id,
to_char(sn.begin_interval_time,'dd. Mon yy hh24:mi') begin_time,
st.plan_hash_value, st.optimizer_env_hash_value opt_env_hash,
round(st.elapsed_time_delta/1000000,2) elapsed,
round(st.cpu_time_delta/1000000,2) cpu,
round(st.iowait_delta/1000000,2) iowait
FROM dba_hist_sqlstat st, dba_hist_snapshot sn
WHERE st.snap_id=sn.snap_id
AND st.sql_id='1yw85nghurbkk'
ORDER BY st.snap_id;
SNAP_ID BEGIN_TIME PLAN_HASH_VALUE OPT_ENV_HASH ELAPSED CPU IOWAIT
------- ---------------- --------------- ------------ ------- ------ ------
72 13. Oct 07 21:39 4095786543 611815770 1.28 .05 1.21
73 13. Oct 07 21:42 4095786543 611815770 .32 .06 .27
73 13. Oct 07 21:42 3985860841 3352456078 1.82 .38 1.60
81 15. Oct 07 11:24 4095786543 611815770 .16 .06 .10
The fact that the columns
PLAN_HASH_VALUE
and
OPT_ENV_HASH
in the query result are not
unique for the single
SQL_ID
“1yw85nghurbkk” proves that multiple plans for the same statement
have been used and that the statement was run with different optimizer parameter settings.
Actually, a single parameter used by the optimizer, namely
OPTIMIZER_INDEX_COST_ADJ
, which
