SI on souhaite mettre en place une supervision simple pour détecter les requêtes consommatrices et candidates au tuning alors voici une fonction simple pour cela :
- la fonction pourra être schedulé dans un job
- Elle devra permettre d'exclure des user que nous voulons pas superviser comme SYS et SYSTEM par exemple.
- déterminer le seuil (en seconde) d'alerte pour qualifier de long une exécution.
- la fonction de supervision devra loger dans une table temporaire. Les requêtes longues avec une rétention de 30 jours.
- on pourra recevoir un mail avec le reporting sur les requêtes capturées.
- un seul mail sera envoyé par heure s'il s'agit du même user et de la même requête.
- pour les sessions parallèle on envoi un unique mail.
- La supervision est compatible avec le RAC.
- création de la table qui va contenir les requêtes capturées :
dba_long_sqlstat:
CREATE TABLE "SYS"."DBA_LONG_SQLSTAT"
( "INST_ID" NUMBER,
"USERNAME" VARCHAR2(30 BYTE),
"OSUSER" VARCHAR2(30 BYTE),
"MACHINE" VARCHAR2(64 BYTE),
"PROGRAM" VARCHAR2(48 BYTE),
"SQL_ID" VARCHAR2(13 BYTE),
"LAST_CALL_ET" NUMBER,
"SQL_TEXT" CLOB
);
dba_hist_long_sqlstat(table d'historisation):
CREATE TABLE "SYS"."DBA_HIST_LONG_SQLSTAT"
( "CAPTURE_TIMESTAMP" DATE DEFAULT SYSDATE,
"INST_ID" NUMBER,
"USERNAME" VARCHAR2(30 BYTE),
"OSUSER" VARCHAR2(30 BYTE),
"MACHINE" VARCHAR2(64 BYTE),
"PROGRAM" VARCHAR2(48 BYTE),
"SQL_ID" VARCHAR2(13 BYTE),
"LAST_CALL_ET" NUMBER,
"SQL_TEXT" CLOB
);
création de la procédure :
create or replace procedure long_running_sql
is
v_db_name v$parameter.value%type;
crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
begin
dbms_utility.exec_ddl_statement('truncate table dba_long_sqlstat');
insert into dba_long_sqlstat
select
s.inst_id, s.username, s.osuser, s.machine, s.program, s.sql_id,l_et,st.sql_fulltext
from
(
select distinct inst_id, username, osuser, machine, program, sql_id, max(last_call_et) as l_et
from gv$session
where
username is not null and
username not in ('SYS','SYSTEM','DBSNMP') and
status='ACTIVE'
and last_call_et > 180
group by
inst_id, username, osuser, machine, program, sql_id
) s
left outer join gv$sql st
on s.sql_id = st.sql_id;
commit;
select value into v_db_name from v$parameter where name = 'db_name';
for rec in
(
select * FROM dba_long_sqlstat WHERE ROWID IN
(SELECT MAX(ROWID) FROM
(SELECT inst_id, username, osuser, machine, program, sql_id, last_call_et, sql_text
FROM (
SELECT inst_id, username, osuser, machine, program, sql_id, last_call_et, sql_text, rank() over (partition by sql_id order by last_call_et desc) rnk
FROM dba_long_sqlstat
where sql_id not in (select sql_id from dba_hist_long_sqlstat where capture_timestamp between TRUNC(SYSDATE, 'HH24') and sysdate)
)
WHERE rnk = 1
)
group by sql_id)
)
loop
declare
v_from varchar2(80) := v_db_name;
v_recipient varchar2(80) := 'xxxx@gmail.com';
v_subject varchar2(80) := 'long running sql on '||v_db_name;
v_mail_host varchar2(30) := 'localhost';
v_mail_conn utl_smtp.connection;
crlf varchar2(2) := chr(13)||chr(10);
begin
v_mail_conn := utl_smtp.open_connection(v_mail_host,25);
utl_smtp.helo(v_mail_conn, v_mail_host);
utl_smtp.mail(v_mail_conn, v_from);
utl_smtp.rcpt(v_mail_conn, v_recipient);
utl_smtp.data(v_mail_conn, 'Content-Type: text/html;' || crlf ||
'To: ' || v_recipient || crlf ||
'Subject: '|| v_subject || crlf ||
'The following sql is running for '|| rec.last_call_et || ' seconds in database '|| v_db_name ||'<br><br></html>' ||
'DB User: '|| '<html><font color="darkblue">'|| rec.username || '</font><br></html>' ||
'On Instance: '|| '<html><font color="darkblue">'|| rec.inst_id || '</font><br></html>' ||
'OS User: '|| '<html><font color="darkblue">'|| rec.osuser || '</font><br></html>' ||
'Source Machine: '|| '<html><font color="darkblue">'|| rec.machine || '</font><br></html>' ||
'Program: '|| '<html><font color="darkblue">'|| rec.program || '</font><br><br></html>' ||
'SQL ID: '|| '<html><font color="red">'|| rec.sql_id || '</font><br><br></html>' ||
'<html><body><font face="courier" size="1" color="maroon">'|| rec.sql_text ||'</font></body></html>');
utl_smtp.quit(v_mail_conn);
end;
end loop;
insert into dba_hist_long_sqlstat(
inst_id,
username,
osuser,
machine,
program,
sql_id,
last_call_et,
sql_text)
select * from dba_long_sqlstat;
delete from dba_hist_long_sqlstat where capture_timestamp < (sysdate - 30);
commit;
end long_running_sql ;
2.Scheduler le job selon notre besoin:
Par exemple pour tourner toute les 2 minutes en semaine et dans la plage horaire pertinente pour nous.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'long_running_sql_check_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN long_running_sql; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;BYHOUR=12,13,14,15,16,17,18,19,20,21,22;BYDAY=MON,TUE,WED,THU,FRI',
end_date => NULL,
enabled => TRUE,
comments => 'sup requêtes en semaine ');
END;
Pour tester on peut se connecter bien évidemment avec un user qui n'est pas exclu de la supervision et on execute :
BEGIN
DBMS_LOCK.sleep(seconds => 500);
END;
/
A tester bien évidemment avant de le mettre en production.
