WITH sql_app_waits AS
(SELECT sql_id, SUBSTR(sql_text, 1, 80) sql_text,
application_wait_time/1000 app_time_ms,
elapsed_time,
ROUND(application_wait_time * 100 /
elapsed_time, 2) app_time_pct,
ROUND(application_wait_time * 100 /
SUM(application_wait_time) OVER (), 2) pct_of_app_time,
RANK() OVER (ORDER BY application_wait_Time DESC) ranking
FROM v$sql
WHERE elapsed_time > 0
AND application_wait_time>0 )
SELECT sql_text, app_time_ms, app_time_pct,
pct_of_app_time, ranking
FROM sql_app_waits
WHERE ranking <= 20
ORDER BY ranking asc ;
| SQL_TEXT | APP_TIME_MS | APP_TIME_PCT | PCT_OF_APP_TIME | RANKING |
| SELECT * FROM "NRIV" WHERE "CLIENT"=:A0 AND "OBJECT"=:A1 AND "SUBOBJECT"=:A2 AND | 1.059.603 | 95 | 58 | 1 |
| SELECT * FROM "TBTCO" WHERE "JOBNAME"=:A0 AND "JOBCOUNT"=:A1 FOR UPDATE | 155.705 | 54 | 9 | 2 |
| UPDATE "S067" SET "CMWAE"=:A0,"OLIKW"="S067"."OLIKW"+:A1,"AOLIW"="S067"."AOLIW"+ | 140.990 | 99 | 8 | 3 |
| SELECT * FROM "QSENDDEST" WHERE "MANDT"=:A0 AND "DEST"=:A1 FOR UPDATE | 114.905 | 89 | 6 | 4 |
| INSERT INTO "ZARLOG_MIXTA" ("BATCH","BINPT","CALLD","CPROG","TCODE","TCODE_CALC" | 58.997 | 92 | 3 | 5 |
| SELECT * FROM "TMSALOG" WHERE "CNT"=:A0 AND "SETSYS"=:A1 FOR UPDATE | 47.632 | 32 | 3 | 6 |
| UPDATE "WTAK" SET "WT_BS01"="WTAK"."WT_BS01"+:A0,"WT_WT01"="WTAK"."WT_WT01"+:A1, | 36.839 | 94 | 2 | 7 |
| DELETE FROM "RIS_PROG_TADIR" WHERE "PROGRAM_NAME"=:A0 | 29.741 | 95 | 2 | 8 |
| SELECT * FROM "QIWKTAB" WHERE "MANDT"=:A0 AND "TYPE"=:A1 AND "QNAME"=:A2 FOR UPD | 26.412 | 59 | 1 | 9 |
| UPDATE "CRMGENSTR" SET "CHARFLAG"=:A0,"MTABNAME"=:A1,"R3MAPFUNC"=:A2,"CRMMAPFUNC | 24.722 | 100 | 1 | 10 |
| UPDATE "VARINUM" SET "VNUMBER"="VARINUM"."VNUMBER"+:A0 WHERE "MANDT"=:A1 AND "NU | 15.173 | 29 | 1 | 11 |
| UPDATE "ZARLOG_MIXTA" SET "BATCH"=:A0,"BINPT"=:A1,"CALLD"=:A2,"CPROG"=:A3,"TCODE | 13.524 | 61 | 1 | 12 |
| INSERT INTO "JVOO1" VALUES(:A0,:A1,:A2,:A3,:A4,:A5,:A6,:A7,:A8,:A9,:A10,:A11,:A1 | 12.249 | 31 | 1 | 13 |
| SELECT /*+ FIRST_ROWS (1) */ * FROM "NRIV" WHERE "CLIENT"=:A0 AND "OBJECT"=:A1 A | 11.246 | 6 | 1 | 14 |
| UPDATE "TPLOGNAMES" SET "BEGINTIME"=:A0,"ENDTIME"=:A1 WHERE "DIRTYPE"=:A2 AND "L | 7.037 | 97 | 0 | 15 |
| UPDATE "J_1ASNR" SET "EBRCH"=:A0,"ANX3_DATE"=:A1,"ANX3_SALES_DATE"=:A2,"ANX3_SER | 7.037 | 94 | 0 | 16 |
| UPDATE "USR02" SET "BCODE"=:A0,"GLTGV"=:A1,"GLTGB"=:A2,"USTYP"=:A3,"CLASS"=:A4," | 6.945 | 5 | 0 | 17 |
| DELETE FROM "ARFCRSTATE" WHERE "ARFCIPID"=:A0 AND "ARFCPID"=:A1 AND "ARFCTIME"=: | 6.595 | 4 | 0 | 18 |
| SELECT * FROM "TRBAT" WHERE "TRKORR"=:A0 AND "FUNCTION"=:A1 FOR UPDATE | 4.536 | 94 | 0 | 19 |
DELETE FROM
"TST01" WHERE "DCLIENT"=:A0 AND "DNAME"=:A1 AND
"DPART"=:A2 |
4.107 | 5 | 0 | 20 |
Comments