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
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

Report message to a moderator

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 messageGo to next message
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.

Report message to a moderator

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 messageGo to next message
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

Report message to a moderator

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
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;

Report message to a moderator

Previous Topic: Deadlock interpretation
Next Topic: Top wait event - control file sequential read
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 12 09:47:24 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.