Example Query to Find TOP 20 RESOURCE-INTENSIVE SQL


EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

SELECT *
FROM (SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "CPU Time"
+ "Disk Reads"
+ "Buffer Gets"
+ "Writes"
+ "Sorts"
+ "Parses" DESC) AS "Rank",
i1.*
FROM (SELECT TO_CHAR (hs.begin_interval_time,
'MM/DD/YY'
) "Snap Day",
shs.sql_id "Sql id",
REPLACE
(CAST
(DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40)
),
CHR (10),
''
) "Sql",
SUM (shs.executions_delta) "Execs",
ROUND ( (SUM (shs.elapsed_time_delta) / 1000000)
/ SUM (shs.executions_delta),
1
) "Time Ea Sec",
ROUND ( (SUM (shs.cpu_time_delta) / 1000000)
/ SUM (shs.executions_delta),
1
) "CPU Ea Sec",
ROUND ( (SUM (shs.iowait_delta) / 1000000)
/ SUM (shs.executions_delta),
1
) "IO/Wait Ea Sec",
SUM (shs.cpu_time_delta) "CPU Time",
SUM (shs.disk_reads_delta) "Disk Reads",
SUM (shs.buffer_gets_delta) "Buffer Gets",
SUM (shs.direct_writes_delta) "Writes",
SUM (shs.parse_calls_delta) "Parses",
SUM (shs.sorts_delta) "Sorts",
SUM (shs.elapsed_time_delta) "Elapsed"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id,
TO_CHAR (hs.begin_interval_time, 'MM/DD/YY'),
CAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40)
)
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 20 AND "Snap Day" = TO_CHAR (SYSDATE, 'MM/DD/YY'); 

  Rank Snap Day Sql id        Sql                                                Execs     Time Ea Sec      CPU Ea Sec  IO/Wait Ea Sec        CPU Time      Disk Reads     Buffer Gets          Writes          Parses           Sorts         Elapsed                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
------ -------- ------------- ---------------------------------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     1 06/17/08 6k597hz3kag93 update sh.sales set quantity_sold=quanti              55           438.8            56.1            42.5      3087889588         1154230       276753981               0              55               0     24131383168                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     2 06/17/08 808gd7djnxwaj select * from sh.sales                                59            22.2             5.8            12.4       339586347          363631        13332540               0              59               0      1308327658                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     3 06/17/08 409fvv6zjmt1s update sh.sales set quantity_sold=quanti               3           187.9            38.1              19       114372861           37922        12103794               0               3               0       563556631                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     4 06/17/08 1uk5m5qbzj1vt BEGIN dbms_workload_repository.create_sn              69            31.7             1.6             4.5       113623480           25686          277520             128              70               0      2187159421                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     5 06/17/08 bunssq950snhf insert into wrh$_sga_target_advice   (sn             116             1.2               1              .1       113460670             294            1368               0             116             116       138106558                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     6 06/17/08 b6usrg82hwsa3 call dbms_stats.gather_database_stats_jo               1           231.2           105.7            87.8       105711230           91603         6662691            2044               1               0       231174134                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     7 06/17/08 dqg0t4gm5snus select count(*) from sh.sales                      26524               0               0               0        82134720             199         2973337               0           26523               0        88807772                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     8 06/17/08 51hgwky1770tq begin      owa_vpd_ctx.set_ctx(1);                 43334               0               0               0        77796826             142             428               0           43330               0        88200293                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     9 06/17/08 40nnwvwh3ty4c  update sh.sales set quantity_sold=quant               1             411            60.2            44.5        60214590           24381         5214183               0               1               0       410999684                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    10 06/17/08 5frghhr2qp205 BEGIN  dbms_workload_repository.create_s              42             3.3             1.4             1.2        60537705            5984          145350             102              41               0       139751079                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    11 06/17/08 fjpwwjuapjak9 SELECT *  FROM (SELECT    RANK () OVER                33            24.9             1.6            13.5        52851681          465689           35503          442096              33             165       821308265                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    12 06/17/08 6mcpb06rctk0x call dbms_space.auto_space_advisor_job_p               1           102.1            39.3            59.7        39278469           33805         7207120               0               1               0       102086442                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    13 06/17/08 4nr4dqk9afjng DECLARE job BINARY_INTEGER := :job; next             498              .6              .1              .4        40113349           26800          587005               2             497               0       288170672                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    14 06/17/08 2ddqz0dd5apw2 begin :rc := sys . dbms_backup_restore .            4417               0               0               0        27674383               0               0               0            4417               0        30318846                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    15 06/17/08 459f3z9u4fb3u select value$ from props$ where name = '           39052               0               0               0        27150854              54          156214               0           39052               0        30395069                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    16 06/17/08 0ws7ahf1d78qa select SYS_CONTEXT('USERENV', 'SERVER_HO           39053               0               0               0        25427411               0               0               0           39053               0        26845437                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    17 06/17/08 8szmwam7fysa3 insert into wri$_adv_objspace_trend_data              61             1.2              .3              .9        19882466           31029         3286789               0              62               0        72173683                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    18 06/17/08 fyrbzparfb4gb SELECT OWNER, SEGMENT_NAME, PARTITION_NA             232              .1              .1               0        15027767             522         3620123               0               1               0        18140522                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    19 06/17/08 dc2p1qn4mcng0 DECLARE job BINARY_INTEGER := :job; next              84             1.1              .2              .6        18055342            3092           30527               0              84               0        92004707                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    20 06/17/08 9ba53yk2g8ysn SELECT i1.*, rank() over (partition by "              28              .8              .5              .2        15310283           15086           27384             574              28              84        22412180
20 rows selected.

SPOOL OFF;