Get long lasting sql execution plans on email / Получить план долгих запросов на почту

This is upgraded version of nice utility procedure.
It sends execution plans if query is running longer then specific amount of time.
A trick was to split a big email into chunks as Oracle DB can’t send emails more than 32Kb.

Действия для SYS/SYS user actions:
1) Если у нас PDB, то выбираем необходимую:
PDBs action required:
alter session set container = OUR_PDB;

2) Обеспечиваем пользователя SYSTEM необходимыми правами. Хоть SYSTEM может селектить из вьюшек, гранты всё равно давать необходимо. Можно указать любого другого пользователя.
Provide required grants to system (or any other) user:

grant select on v_$sql to system;
grant select on v_$sql_plan to system;
grant select on v_$sql_plan_statistics_all to system;
grant select on V_$SESSION to system;
grant select on V_$instance to system;

3.1) Устанавливаем UTL_MAIL / Lets install UTL_MAIL:

@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

3.2) Указываем SMTP сервер/Configure smtp_out_server parameter:
alter system set smtp_out_server=’postfix.ourdomain.com:25′ scope=both;
3.3) Обеспечиваем пользователя SYSTEM необходимыми правами на выполнение UTL_MAIL
Provide SYSTEM user (or whatever you need) with UTL_MAIL execution grant:

grant execute on utl_mail to system;

4) Создаём ACL чтобы SYSTEM всё же смог отправлять письма
Create ACL for our SYSTEM user that let it to send emails:

-- Creating ACL:
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('system_utl_mail.xml','Allow mail to be send','SYSTEM', TRUE, 'connect');
commit;
-- Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('system_utl_mail.xml','SYSTEM', TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('system_utl_mail.xml' ,'SYSTEM', TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('system_utl_mail.xml','*',25);
commit;

--You also may check
select * from dba_network_acls;

Теперь под нашим юзером создаём структуру и процедуру для выполнения:
SYSTEM Actions:
0) Change schema:

ALTER SESSION SET CURRENT_SCHEMA = SYSTEM;
SET SERVEROUTPUT ON

1) —Create TABLE FOR tracking:

CREATE TABLE LONG_SQL (SQL_ID VARCHAR2(13), SQL_CHILD_NUMBER NUMBER, DETECTED_DATE DATE) ;

2) Create Email sending Procedure:

CREATE OR REPLACE Procedure LONG_SQL_ALERT_FOR_JOB (ALERT_SQL_DURATION NUMBER)
IS
--EMAIL SETTINGS:
RECEPIENT_LIST varchar2(200):='maksenov@ourdomain.com,anotherDBAsemail@ourdomain.com';

DatabaseName varchar(50);
HostName varchar(50);

--Full sql text
sql_text_clob CLOB;

CURSOR sql_id_cursor IS
select ses.sql_id, SQL_CHILD_NUMBER
from V$SESSION ses
where SES.STATUS = 'ACTIVE'
and SES.USERNAME is not null
and SES.USERNAME not in ('SYS','SYSTEM')
and (SYSDATE - SQL_EXEC_START) > ALERT_SQL_DURATION/(24*60*60) --SECONDS
--and Ses.AUDSID <> userenv('SESSIONID')
and ses.sql_id is not null;

--To Store SQL_ID_INPUT and SQL_CHILD_ID_INPUT
SQL_ID_INPUT VARCHAR2(13);
SQL_CHILD_ID_INPUT NUMBER;

-- sql_id_var V$SESSION.sql_id%TYPE;
-- SQL_CHILD_ID_INPUT NUMBER;
SQL_ALREADY_EXISTS NUMBER := 0; --IF WE HAVE SQL in SQL_LONG TABLE
tableExists NUMBER; -- if table exists

--CURSOR TO GET PLAN
row_to_get_plan varchar(500);
CURSOR cursor_to_get_plan (sql_id_to_get_plan varchar2, SQL_CHILD_NUMBER_to_get_plan number)
IS select plan_table_output from table(dbms_xplan.display_cursor(sql_id_to_get_plan, SQL_CHILD_NUMBER_to_get_plan));

--For Email
messageBody varchar2(32767);
chunkPosition NUMBER; --where we did chunk clob
chunkNumber NUMBER; --Chunk number
chunkLimit NUMBER := 32700 ; --Chunk Limit

--For delay between emails chunks:
seconds_to_wait INT :=4 ; --num seconds
v_now DATE;

BEGIN

--Getting Database Name and HostName
select global_name into DatabaseName from global_name;
select HOST_NAME into HostName from v$instance;

--CURSOR WITH LONG SQL
--PROCESSING EVERY LONG QUERY
FOR sql_id_row IN sql_id_cursor
LOOP
dbms_output.put_line( 'WORKING WITH SQL_ID: ' || sql_id_row.sql_id || ' , SQL_CHILD_NUMBER: ' || sql_id_row.SQL_CHILD_NUMBER || ' , NOW IS: ' || SYSDATE);

--CHECKING IF THIS SQL WAS ALREADY REPORTED
EXECUTE IMMEDIATE
'SELECT COUNT(*)
FROM LONG_SQL
WHERE LONG_SQL.SQL_ID = :1
AND LONG_SQL.SQL_CHILD_NUMBER = :2'
INTO SQL_ALREADY_EXISTS
USING sql_id_row.sql_id,sql_id_row.SQL_CHILD_NUMBER;
dbms_output.put_line( '- SQL_ID: ' || sql_id_row.sql_id || ' , SQL_CHILD_NUMBER: ' || sql_id_row.SQL_CHILD_NUMBER || ' , FOUND ' || SQL_ALREADY_EXISTS ||' TIMES IN SQL_ID TRACK TABLE');

--IF ALREADY REPORTED SQL
IF SQL_ALREADY_EXISTS > 0 THEN
dbms_output.put_line('- IT''S AN OLD LONG QUERY. LET''S DO NOTHING');
--IF NOT REPORTED SQL
ELSE

dbms_output.put_line('- NEW LONG QUERY DETECTED');
--Write SQL_ID into table
EXECUTE IMMEDIATE 'INSERT INTO LONG_SQL values (:1,:2,SYSDATE)' USING sql_id_row.sql_id,sql_id_row.SQL_CHILD_NUMBER;
dbms_output.put_line('- SQL_ID WROTE TO LONG_SQL TABLE');

--CREATE NEW MESSAGE
messageBody:='<body> <h3>Current SQL is executing on '||DatabaseName||' longer than '||ALERT_SQL_DURATION||' seconds</h3>';
--PUT PLAN IN THE MESSAGE, Line by line
OPEN cursor_to_get_plan(sql_id_row.sql_id,sql_id_row.SQL_CHILD_NUMBER);
LOOP
FETCH cursor_to_get_plan INTO row_to_get_plan;
EXIT WHEN cursor_to_get_plan%notfound;
messageBody:=messageBody||'<pre>'||row_to_get_plan||'</pre>'||chr(10);
dbms_output.put_line(row_to_get_plan);
END LOOP;
CLOSE cursor_to_get_plan;

select SQL_FULLTEXT
INTO sql_text_clob
from v$sql
where sql_id like sql_id_row.sql_id
and child_number = sql_id_row.SQL_CHILD_NUMBER;

messageBody:=messageBody||chr(10)||'<h4>Full SQL Text:</h4>'||chr(10)||'<pre>';
dbms_output.put_line('messageBodyTOTEST='||messageBody);

--dbms_output.put_line('---!!! messageBody LENGTH='||LENGTH(messageBody));

chunkPosition:=chunkLimit-LENGTH(messageBody);
messageBody:=messageBody||substr(sql_text_clob, 1,chunkPosition )||'</pre>';
messageBody:=messageBody||'</body>';

dbms_output.put_line('messageBody='||messageBody);

UTL_MAIL.send(sender => HostName||'@domain.com',
recipients => RECEPIENT_LIST,
subject => 'SQL Execution Plan for '||sql_id_row.sql_id||' SQL_ID on '||DatabaseName||' ('||HostName||')',
mime_type => 'text/html;',
message => messageBody);

IF LENGTH(messageBody) > chunkLimit THEN
chunkNumber:=2;
chunkPosition:=chunkPosition+1;
WHILE chunkPosition < LENGTH(sql_text_clob)
LOOP

--DELAY:
SELECT SYSDATE
INTO v_now
FROM DUAL;

LOOP
EXIT WHEN v_now + (seconds_to_wait * (1/86400)) <= SYSDATE; --Wait for seconds_to_wait seconds
END LOOP;

messageBody:='<pre>'||substr(sql_text_clob, chunkPosition,chunkLimit)||'</pre>';
UTL_MAIL.send(sender => HostName||'@domain.com',
recipients => RECEPIENT_LIST,
subject => ' Part '||chunkNumber||' SQL Execution Plan for '||sql_id_row.sql_id||' SQL_ID on '||DatabaseName||' ('||HostName||')',
mime_type => 'text/html;',
message => messageBody);
chunkNumber:=chunkNumber+1;
chunkPosition:=chunkPosition+chunkLimit;
END LOOP;
END IF;

END IF;

COMMIT;
END LOOP;

END;
/

 

3) Create job that runs every 5 minutes to check if queries older 600 seconds are executing:

declare n1 number := 600; --seconds of SQL Duration threshold
-- 3600 is 1 hour, 1800 is 30 minutes, 600 is 10 minutes, 60 is 1 minute 
begin
--DBMS_SCHEDULER.DROP_JOB('LONG_SQL_ALERT');
DBMS_SCHEDULER.CREATE_JOB('LONG_SQL_ALERT','STORED_PROCEDURE',
job_action => 'SYSTEM.LONG_SQL_ALERT_FOR_JOB',
number_of_arguments => 1,
start_date => SYSTIMESTAMP,
end_date => NULL,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
enabled => FALSE);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'LONG_SQL_ALERT',
argument_position => 1,
argument_value => n1);
DBMS_SCHEDULER.ENABLE('LONG_SQL_ALERT');
--DBMS_SCHEDULER.RUN_JOB('LONG_SQL_ALERT');
end;
/