Home » RDBMS Server » Performance Tuning » How to retrieve SQLs and execution plans from awr history (oracle database 10R2)
How to retrieve SQLs and execution plans from awr history [message #436487] |
Wed, 23 December 2009 16:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
anurag_telenor
Messages: 2 Registered: December 2009 Location: Oslo
|
Junior Member |
|
|
I have to collect the past execution plan of specific statement(which runs somewhere around midnight everyday).
Then I will have to compare the last execution plans of the 3 same statement . I run AWR stats collection every hour and have history upto 30 days.
I am trying to run the following but it is not showing me even the last 3 executions of specific statement ( identified by .... like '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'.......)
SELECT to_char(sql_text),command,snap_id,timestamp FROM
(
SELECT X.SNAP_ID, X.SQL_ID,SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) AVG_ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) AVG_CPU_TIME_SEC
,X.ELAPSED_TIME TOTAL_ELAPSED_TIME_MIC_SEC
,X.CPU_TIME TOTAL_CPU_TIME_MIC_SEC
,X.EXECUTIONS_DELTA EXECUTIONS
,decode(DHST.COMMAND_TYPE,3,'Select',47,'pl/sql',2,'Insert') command
,DHSP.timestamp
FROM
DBA_HIST_SQLTEXT DHST,
DBA_HIST_SQL_PLAN DHSP,
(SELECT DHSS.SNAP_ID,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
AND PARSING_SCHEMA_NAME='CCDW'
GROUP BY DHSS.SQL_ID,DHSS.SNAP_ID
) X
WHERE X.SQL_ID=DHST.SQL_ID and X.EXECUTIONS_DELTA>0
and DHSP.SQL_ID=X.SQL_ID
ORDER BY AVG_ELAPSED_TIME_SEC DESC
)
WHERE upper(DBMS_LOB.Substr(sql_text, 4000, 1)) like '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'
group by to_char(sql_text),command,snap_id,timestamp
order by timestamp desc ;
Thanks,
Anurag Vidyarthi
|
|
|
Re: How to retrieve SQLs and execution plans from awr history [message #436488 is a reply to message #436487] |
Wed, 23 December 2009 16:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
I won't waste moreof my time trying to decipher & fix unformatted & broken SQL
1 SELECT to_char(sql_text),command,snap_id,timestamp FROM
2 (
3 SELECT X.SNAP_ID, X.SQL_ID,SQL_TEXT
4 ,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) AVG_ELAPSED_TIME_SEC
5 ,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) AVG_CPU_TIME_SEC
6 ,X.ELAPSED_TIME TOTAL_ELAPSED_TIME_MIC_SEC
7 ,X.CPU_TIME TOTAL_CPU_TIME_MIC_SEC
8 ,X.EXECUTIONS_DELTA EXECUTIONS
9 ,decode(DHST.COMMAND_TYPE,3,'Select',47,'pl/sql',2,'Insert') command
10 ,DHSP.timestamp
11 FROM
12 DBA_HIST_SQLTEXT DHST,
13 DBA_HIST_SQL_PLAN DHSP,
14 (SELECT DHSS.SNAP_ID,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
15 , SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
16 FROM DBA_HIST_SQLSTAT DHSS
17 AND PARSING_SCHEMA_NAME='CCDW'
18 GROUP BY DHSS.SQL_ID,DHSS.SNAP_ID
19 ) X
20 WHERE X.SQL_ID=DHST.SQL_ID and X.EXECUTIONS_DELTA>0
21 and DHSP.SQL_ID=X.SQL_ID
22 ORDER BY AVG_ELAPSED_TIME_SEC DESC
23 )
24 WHERE upper(DBMS_LOB.Substr(sql_text, 4000, 1)) like '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'
25 group by to_char(sql_text),command,snap_id,timestamp
26* order by timestamp desc
SQL> /
AND PARSING_SCHEMA_NAME='CCDW'
*
ERROR at line 17:
ORA-00907: missing right parenthesis
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
|
|
|
Re: How to retrieve SQLs and execution plans from awr history [message #436489 is a reply to message #436487] |
Wed, 23 December 2009 17:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
anurag_telenor
Messages: 2 Registered: December 2009 Location: Oslo
|
Junior Member |
|
|
sorry for typo, correct statement is as follows :
SELECT to_char(sql_text),command,snap_id,timestamp FROM
(
SELECT X.SNAP_ID, X.SQL_ID,SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) AVG_ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) AVG_CPU_TIME_SEC
,X.ELAPSED_TIME TOTAL_ELAPSED_TIME_MIC_SEC
,X.CPU_TIME TOTAL_CPU_TIME_MIC_SEC
,X.EXECUTIONS_DELTA EXECUTIONS
,decode(DHST.COMMAND_TYPE,3,'Select',47,'pl/sql',2,'Insert') command
,DHSP.timestamp
FROM
DBA_HIST_SQLTEXT DHST,
DBA_HIST_SQL_PLAN DHSP,
(SELECT DHSS.SNAP_ID,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
where PARSING_SCHEMA_NAME='CCDW'
GROUP BY DHSS.SQL_ID,DHSS.SNAP_ID
) X
WHERE X.SQL_ID=DHST.SQL_ID and X.EXECUTIONS_DELTA>0
and DHSP.SQL_ID=X.SQL_ID
ORDER BY AVG_ELAPSED_TIME_SEC DESC
)
WHERE upper(DBMS_LOB.Substr(sql_text, 4000, 1)) like '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'
group by to_char(sql_text),command,snap_id,timestamp
order by timestamp desc ;
BR,
Anurag
|
|
|
Re: How to retrieve SQLs and execution plans from awr history [message #436492 is a reply to message #436489] |
Wed, 23 December 2009 18:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
CUT & PASTE avoids typos
SELECT To_char(sql_text),
command,
snap_id,
TIMESTAMP
FROM (SELECT x.snap_id,
x.sql_id,
sql_text,
Round(x.elapsed_time / 1000000 / x.executions_delta,
3) avg_elapsed_time_sec,
Round(x.cpu_time / 1000000 / x.executions_delta,
3) avg_cpu_time_sec,
x.elapsed_time total_elapsed_time_mic_sec,
x.cpu_time total_cpu_time_mic_sec,
x.executions_delta executions,
Decode(dhst.command_type,3,'Select',
47,'pl/sql',
2,'Insert') command,
dhsp.TIMESTAMP
FROM dba_hist_sqltext dhst,
dba_hist_sql_plan dhsp,
(SELECT dhss.snap_id,
dhss.sql_id sql_id,
Sum(dhss.cpu_time_delta) cpu_time,
Sum(dhss.elapsed_time_delta) elapsed_time,
Sum(dhss.executions_delta) executions_delta
FROM dba_hist_sqlstat dhss
WHERE parsing_schema_name = 'CCDW'
GROUP BY dhss.sql_id,
dhss.snap_id) x
WHERE x.sql_id = dhst.sql_id
AND x.executions_delta > 0
AND dhsp.sql_id = x.sql_id
ORDER BY avg_elapsed_time_sec DESC)
WHERE Upper(dbms_lob.Substr(sql_text,4000,1)) LIKE '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'
GROUP BY To_char(sql_text),
command,
snap_id,
TIMESTAMP
ORDER BY TIMESTAMP DESC;
|
|
|
Goto Forum:
Current Time: Fri Aug 23 22:06:16 CDT 2024
|