TOP SQL’s
In this article,we will get notified for TOP SQL’s ORDERED BY ELAPSED TIME
Step 1 :
Create temporary tables to store TOP SQL’s
|
1 2 3 4 5 |
SYS>>CREATE TABLE dbmon.TOP_ELAPSEDTIME_SQLIDS ( SQL_ID VARCHAR2(20 CHAR), LASTMODIFIEDDATE DATE DEFAULT SYSDATE NOT NULL ); |
Another table to store ALL SQL’s.
|
1 2 3 4 5 |
SYS>>CREATE TABLE dbmon.ALL_ELAPSEDTIME_SQLIDS ( SQL_ID VARCHAR2(20 CHAR), LASTMODIFIEDDATE DATE DEFAULT SYSDATE NOT NULL ); |
Step 2 :
Create the below scripts to capture TOP SQL’s in tables
$ cat elapsed_query_details.sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
/* Formatted on 01/04/2019 14:08:36 (QP5 v5.227.12220.39724) */ SET SERVEROUTPUT ON LONG 99999 LINES 2000 PAGES 2000 FEEDBACK OFF HEAD OFF PAGESIZE 0 LINESIZE 2000 LONG 99999 DECLARE B_SQLFULLTEXT GV$SQLAREA.SQL_FULLTEXT%TYPE; I_SQL_ID dba_hist_sqlstat.SQL_ID%TYPE; I_SQLFULLTEXT dba_hist_sqltext.sql_text%TYPE; I_parsing_schema_name dba_hist_sqlstat.parsing_schema_name%TYPE; I_TOTALELAPSEDTIME VARCHAR2(4000); I_NUmberofExecs VARCHAR2(4000); I_RowsProcessed VARCHAR2(4000); I_avg_query_time VARCHAR2(4000); V_COUNT NUMBER :=0; CURSOR ELAPSED_QUERY_DETAILS IS select sub.sql_id, txt.sql_text, parsing_schema_name, sub.seconds_since_date as TOTALELAPSEDTIME, sub.execs_since_date as NUmberofExecs, sub.gets_rows_date as RowsProcessed, round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time from ( -- sub to sort before top N filter select sql_id, g.parsing_schema_name, round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date, sum(executions_delta) as execs_since_date, sum(buffer_gets_delta) as gets_since_date, sum(ROWS_PROCESSED_delta) as gets_rows_date, row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r from dba_hist_snapshot natural join dba_hist_sqlstat g where trunc(begin_interval_time) = trunc(sysdate-1) group by sql_id, g.parsing_schema_name) sub join dba_hist_sqltext txt on sub.sql_id = txt.sql_id where r <= 25 order by avg_query_time desc; BEGIN BEGIN DBMS_OUTPUT.PUT_LINE(' Started to Print sql ids which are newly created '); OPEN ELAPSED_QUERY_DETAILS; LOOP FETCH ELAPSED_QUERY_DETAILS INTO I_SQL_ID,I_SQLFULLTEXT,I_parsing_schema_name,I_TOTALELAPSEDTIME,I_NUmberofExecs,I_RowsProcessed,I_avg_query_time; EXIT WHEN ELAPSED_QUERY_DETAILS%NOTFOUND; DBMS_OUTPUT.PUT_LINE(' '); V_COUNT := 0; SELECT COUNT(*) INTO V_COUNT FROM dbmon.TOP_ELAPSEDTIME_SQLIDS WHERE SQL_ID = I_SQL_ID; IF V_COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('New ELAPSED sql id details details --> I_SQL_ID - '||I_SQL_ID ||' ::I_parsing_schema_name - '||I_parsing_schema_name||' ::I_TOTALELAPSEDTIME - '||I_TOTALELAPSEDTIME||':: I_NUmberofExecs - '||I_NUmberofExecs ||' ::I_RowsProcessed - '||I_RowsProcessed ||' ::I_avg_query_time - '||I_avg_query_time); END IF; DBMS_OUTPUT.PUT(CHR(10)); END LOOP; CLOSE ELAPSED_QUERY_DETAILS; DBMS_OUTPUT.PUT_LINE(' Ended to Print sql ids which are newly created '); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(' '); END; BEGIN BEGIN DELETE FROM dbmon.TOP_ELAPSEDTIME_SQLIDS; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(' '); END; DBMS_OUTPUT.PUT_LINE(' Started Executing to print the long running elapsed queries '); OPEN ELAPSED_QUERY_DETAILS; LOOP FETCH ELAPSED_QUERY_DETAILS INTO I_SQL_ID,I_SQLFULLTEXT,I_parsing_schema_name,I_TOTALELAPSEDTIME,I_NUmberofExecs,I_RowsProcessed,I_avg_query_time; EXIT WHEN ELAPSED_QUERY_DETAILS%NOTFOUND; DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' Insert long running elapsed query details into to Print sql ids which are newly created '); DBMS_OUTPUT.PUT_LINE('elapsed query details --> I_SQL_ID - '||I_SQL_ID ||' ::I_parsing_schema_name - '||I_parsing_schema_name||' ::I_TOTALELAPSEDTIME - '||I_TOTALELAPSEDTIME||':: I_NUmberofExecs - '||I_NUmberofExecs ||' ::I_RowsProcessed - '||I_RowsProcessed ||' ::I_avg_query_time - '||I_avg_query_time); INSERT INTO dbmon.TOP_ELAPSEDTIME_SQLIDS VALUES (I_SQL_ID,SYSDATE); INSERT INTO dbmon.ALL_ELAPSEDTIME_SQLIDS VALUES (I_SQL_ID,SYSDATE); COMMIT; I_parsing_schema_name := NULL; I_TOTALELAPSEDTIME := NULL; I_NUmberofExecs := NULL; I_RowsProcessed := NULL; I_avg_query_time := NULL; DBMS_OUTPUT.PUT(CHR(10)); DBMS_OUTPUT.PUT(CHR(10)); END LOOP; CLOSE ELAPSED_QUERY_DETAILS; DBMS_OUTPUT.PUT_LINE(' Started Executing to print the long running elapsed queries '); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(' '); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Error : '|| DBMS_UTILITY.format_error_stack() || CHR(10) || DBMS_UTILITY.format_error_backtrace()); END; / |
Note :
Step 3 :
Execute the below script
|
1 |
SYS>>@elapsed.sql |
Output : Once we executed the script the output will follow as below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
Started to Print sql ids which are newly created New ELAPSED sql id details details --> I_SQL_ID - 05s9358mm6vrr ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 11:: I_NUmberofExecs - 1 ::I_RowsProcessed - 1 ::I_avg_query_time - 10.891 New ELAPSED sql id details details --> I_SQL_ID - 50d3t2cnrp3j0 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 6:: I_NUmberofExecs - 1 ::I_RowsProcessed - 1 ::I_avg_query_time - 5.941 New ELAPSED sql id details details --> I_SQL_ID - dhpn35zupm8ck ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 2 ::I_RowsProcessed - 0 ::I_avg_query_time - 1.99 New ELAPSED sql id details details --> I_SQL_ID - 4bymnttwnjmw7 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 2 ::I_RowsProcessed - 2 ::I_avg_query_time - 1.99 New ELAPSED sql id details details --> I_SQL_ID - 5k5207588w9ry ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 6:: I_NUmberofExecs - 5 ::I_RowsProcessed - 5 ::I_avg_query_time - 1.198 New ELAPSED sql id details details --> I_SQL_ID - 7g7v8qmmv53y2 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 2 ::I_RowsProcessed - 2 ::I_avg_query_time - .995 New ELAPSED sql id details details --> I_SQL_ID - 5r8sf8qp40tj1 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 5 ::I_RowsProcessed - 5083 ::I_avg_query_time - .798 New ELAPSED sql id details details --> I_SQL_ID - 9ctt1scmwbmbg ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 3 ::I_RowsProcessed - 3 ::I_avg_query_time - .664 New ELAPSED sql id details details --> I_SQL_ID - 71rqmw80jcqcw ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 6 ::I_RowsProcessed - 2358 ::I_avg_query_time - .333 New ELAPSED sql id details details --> I_SQL_ID - 9wncfacx0nj9h ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 18 ::I_RowsProcessed - 18 ::I_avg_query_time - .222 New ELAPSED sql id details details --> I_SQL_ID - 10s3r3f17ccu3 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 3:: I_NUmberofExecs - 15 ::I_RowsProcessed - 477 ::I_avg_query_time - .2 New ELAPSED sql id details details --> I_SQL_ID - 8zc85a8249x81 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 34 ::I_RowsProcessed - 34 ::I_avg_query_time - .059 New ELAPSED sql id details details --> I_SQL_ID - 4phvdvx32a3mf ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 6:: I_NUmberofExecs - 338 ::I_RowsProcessed - 338 ::I_avg_query_time - .018 New ELAPSED sql id details details --> I_SQL_ID - a6ygk0r9s5xuj ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 5:: I_NUmberofExecs - 370 ::I_RowsProcessed - 0 ::I_avg_query_time - .014 New ELAPSED sql id details details --> I_SQL_ID - 7kmbrw7q8hn4g ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 5:: I_NUmberofExecs - 473 ::I_RowsProcessed - 473 ::I_avg_query_time - .011 New ELAPSED sql id details details --> I_SQL_ID - 6q9zvynq8f0h0 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 424 ::I_RowsProcessed - 13296 ::I_avg_query_time - .009 New ELAPSED sql id details details --> I_SQL_ID - 3un99a0zwp4vd ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 651 ::I_RowsProcessed - 2112 ::I_avg_query_time - .003 Ended to Print sql ids which are newly created Started Executing to print the long running elapsed queries Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 05s9358mm6vrr ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 11:: I_NUmberofExecs - 1 ::I_RowsProcessed - 1 ::I_avg_query_time - 10.891 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 50d3t2cnrp3j0 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 6:: I_NUmberofExecs - 1 ::I_RowsProcessed - 1 ::I_avg_query_time - 5.941 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - bxywuzvtp6wjg ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 5:: I_NUmberofExecs - 1 ::I_RowsProcessed - 0 ::I_avg_query_time - 4.95 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - dhpn35zupm8ck ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 2 ::I_RowsProcessed - 0 ::I_avg_query_time - 1.99 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 4bymnttwnjmw7 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 2 ::I_RowsProcessed - 2 ::I_avg_query_time - 1.99 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 0raht0h154b63 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 1 ::I_RowsProcessed - 0 ::I_avg_query_time - 1.98 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 8z6jf4nswsn2v ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 1 ::I_RowsProcessed - 0 ::I_avg_query_time - 1.98 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 5k5207588w9ry ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 6:: I_NUmberofExecs - 5 ::I_RowsProcessed - 5 ::I_avg_query_time - 1.198 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - fhf8upax5cxsz ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 7:: I_NUmberofExecs - 6 ::I_RowsProcessed - 6 ::I_avg_query_time - 1.165 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 7g7v8qmmv53y2 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 2 ::I_RowsProcessed - 2 ::I_avg_query_time - .995 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 5r8sf8qp40tj1 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 5 ::I_RowsProcessed - 5083 ::I_avg_query_time - .798 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 9ctt1scmwbmbg ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 3 ::I_RowsProcessed - 3 ::I_avg_query_time - .664 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 6ajkhukk78nsr ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 3:: I_NUmberofExecs - 9 ::I_RowsProcessed - 9 ::I_avg_query_time - .333 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 71rqmw80jcqcw ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 6 ::I_RowsProcessed - 2358 ::I_avg_query_time - .333 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 9wncfacx0nj9h ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 18 ::I_RowsProcessed - 18 ::I_avg_query_time - .222 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 10s3r3f17ccu3 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 3:: I_NUmberofExecs - 15 ::I_RowsProcessed - 477 ::I_avg_query_time - .2 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - d2xthxxp6r8da ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 24 ::I_RowsProcessed - 97 ::I_avg_query_time - .083 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 8zc85a8249x81 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 34 ::I_RowsProcessed - 34 ::I_avg_query_time - .059 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 4phvdvx32a3mf ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 6:: I_NUmberofExecs - 338 ::I_RowsProcessed - 338 ::I_avg_query_time - .018 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - a6ygk0r9s5xuj ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 5:: I_NUmberofExecs - 370 ::I_RowsProcessed - 0 ::I_avg_query_time - .014 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 7kmbrw7q8hn4g ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 5:: I_NUmberofExecs - 473 ::I_RowsProcessed - 473 ::I_avg_query_time - .011 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 6q9zvynq8f0h0 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 424 ::I_RowsProcessed - 13296 ::I_avg_query_time - .009 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 7hu2k3a31b6j7 ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 6:: I_NUmberofExecs - 1081 ::I_RowsProcessed - 15396 ::I_avg_query_time - .006 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 4y1y43113gv8f ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 4:: I_NUmberofExecs - 980 ::I_RowsProcessed - 13296 ::I_avg_query_time - .004 Insert long running elapsed query details into to Print sql ids which are newly created elapsed query details --> I_SQL_ID - 3un99a0zwp4vd ::I_parsing_schema_name - SYS ::I_TOTALELAPSEDTIME - 2:: I_NUmberofExecs - 651 ::I_RowsProcessed - 2112 ::I_avg_query_time - .003 Started Executing to print the long running elapsed queries Elapsed: 00:00:00.25 |
The a
Step 4 :
Check the tables for SQL Id’s
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DBMON>>select * from TOP_ELAPSEDTIME_SQLIDS; SQL_ID LASTMODIF -------------------- --------- bxywuzvtp6wjg 05-APR-19 dhpn35zupm8ck 05-APR-19 4bymnttwnjmw7 05-APR-19 0raht0h154b63 05-APR-19 8z6jf4nswsn2v 05-APR-19 5k5207588w9ry 05-APR-19 fhf8upax5cxsz 05-APR-19 7g7v8qmmv53y2 05-APR-19 5r8sf8qp40tj1 05-APR-19 9ctt1scmwbmbg 05-APR-19 6ajkhukk78nsr 05-APR-19 71rqmw80jcqcw 05-APR-19 9wncfacx0nj9h 05-APR-19 10s3r3f17ccu3 05-APR-19 d2xthxxp6r8da 05-APR-19 8zc85a8249x81 05-APR-19 4phvdvx32a3mf 05-APR-19 a6ygk0r9s5xuj 05-APR-19 7kmbrw7q8hn4g 05-APR-19 6q9zvynq8f0h0 05-APR-19 7hu2k3a31b6j7 05-APR-19 4y1y43113gv8f 05-APR-19 3un99a0zwp4vd 05-APR-19 05s9358mm6vrr 05-APR-19 50d3t2cnrp3j0 05-APR-19 25 rows selected. Elapsed: 00:00:00.00 |
Try to delete few records in temporary table and execute above script.
Then check the new SQL Id’s in temporary table.
Note: Please test scripts in Non Prod before trying in Production.
Loading...
