ORACLE 索引及性能相关系统视图使用
1、性能调优语句
1.1、批量更新（删除）以及MERGE INTO用法
merge into POOR.APPROVEDACCOUNTLIST A
using (SELECT * FROM FIAB.TEMP_OP_APPROVED) B
on (A.ENCOUNTERID = B.ENCOUNTERID
AND A.ORDERSOURCEFLAG = B.ORDERSOURCEFLAG
AND A.PARENTORDERID = B.PARENTORDERID
AND A.ORDERID = B.ORDERID
AND A.CHILDID = B.CHILDID
AND (A.CONSUMEABLECODEID <>B.CONSUMABLECODEID OR B.CONSUMABLEID <> A.CONSUMEABLEID))
when matched then
update set A.ROWVERSION = A.ROWVERSION -- 必须要有update，而且这里不能有where，否则后面的delete的where条件无效；
delete where 1=1 --上一行update过的记录，才会被delete
;
UPDATE ROLE.EMPLOYEE A SET A.ORG_NAME=(SELECT B.NAME FROM ENTITY.ORGANIZATION B WHERE B.ORGANIZATIONID = A.ORGANIZATIONID) WHERE A.ORGANIZATIONID IN (SELECT ORGANIZATIONID FROM ENTITY.ORGANIZATION B);
DELETE core.ParameterValue a
WHERE EXISTS (select * from core.ParameterValue b where a.ParameterId =31 and b.ParameterId=33 and a.Name=b.Name and a.Value=b.Value);
COMMIT;1.2、索引在线修改及手动（自动）收集统计信息
--手工收集统计
exec dbms_stats.gather_table_stats(ownname => 'prpa',tabname => 'encounterii' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);
--修改（DROP之后再CREATE）索引
DECLARE
cnt number(10):=0;
BEGIN
SELECT count(*) INTO cnt FROM dba_indexes WHERE lower(index_name)=lower('PLACERGROUP_CHARGETIME') AND lower(table_name)=lower('PLACERGROUP') AND OWNER='POOR';
IF cnt=1 THEN
EXECUTE immediate'drop index POOR.PLACERGROUP_CHARGETIME';
END if;
end;
/
create index POOR.PLACERGROUP_CHARGETIME on POOR.PLACERGROUP(CHARGETIME) tablespace jxindexspace online;
/
-- 查看表的收集比率
select dbms_stats.get_prefs('STALE_PERCENT','FIAB','OPACCOUNTLIST') stale_percent from dual;
--设置表的【统计信息过期阈值】为5%，（默认为10%）
exec dbms_stats.set_table_prefs('FIAB','OPACCOUNTLIST','STALE_PERCENT',5);1.3、GV$SQLAREA视图使用总览
--IO和内存消耗多的sql
SELECT * FROM ( select OPTIMIZER_COST, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_Avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,hash_value, PARSING_USER_ID,SORTS,COMMAND_TYPE,SQL_FULLTEXT
FROM gv$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC
)where ROWNUM<20 ;
--占IO的sql
SELECT * FROM ( select OPTIMIZER_COST, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_Avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,hash_value, PARSING_USER_ID,SORTS,COMMAND_TYPE,SQL_FULLTEXT
FROM gv$sqlarea
order BY disk_reads DESC ---可以改为其他排序
)where ROWNUM<20 ;
--平均执行慢的sql
SELECT * FROM ( select OPTIMIZER_COST, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_Avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,hash_value, PARSING_USER_ID,SORTS,COMMAND_TYPE,SQL_FULLTEXT
FROM gv$sqlarea
order BY cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) DESC
)where ROWNUM<20 ;
--总耗时多的sql
SELECT * FROM ( select OPTIMIZER_COST, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_Avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,hash_value, PARSING_USER_ID,SORTS,COMMAND_TYPE,SQL_FULLTEXT
FROM gv$sqlarea
order BY elapsed_time DESC
)where ROWNUM<20 ;
--占cpu的sql
SELECT * FROM ( select OPTIMIZER_COST, cast( elapsed_time/(case when executions is null or executions=0 then 1 else executions end) as number(18,0)) as elapsed_time_Avg
, elapsed_time, executions, cpu_time, buffer_gets, disk_reads,last_load_time,module,hash_value, PARSING_USER_ID,SORTS,COMMAND_TYPE,SQL_FULLTEXT
FROM gv$sqlarea
order BY cpu_time DESC
)where ROWNUM<20 ;1.4、查询最近（一段时间内）执行的500条SQL语句：
SELECT ROWNUM, t.* FROM (SELECT a.SQL_ID, a.parsing_schema_name AS SCHEMA, a.module AS MODULE, a.sql_text AS SQL_TEXT
, a.sql_fulltext AS SQL_FULLTEXT
, a.OPTIMIZER_COST AS OPTIMIZER_COST
, a.parse_calls AS PARSE_CALLS
, a.buffer_gets AS BUFFER_GETS
, a.disk_reads AS DISK_READS
, decode(a.executions,0,1,a.executions) AS EXECUTIONS
, round(a.cpu_time / 1000000, 2) AS CPU_TIME
, round(a.cpu_time / (decode(a.executions,0,1,a.executions) * 1000000), 2) AS CPU_TIME_PER_EXE
, round(a.elapsed_time / (decode(a.executions,0,1,a.executions) * 1000000), 2) AS ELAPSED_TIME_PER_EXE
, a.LAST_LOAD_TIME, a.LAST_ACTIVE_TIME
FROM gv$sqlarea a
WHERE ( a.LAST_LOAD_TIME > to_date('2023/04/24 12:18:02', 'yyyy/MM/dd hh24:mi:ss') OR a.LAST_ACTIVE_TIME > to_date('2023/04/24 12:18:02', 'yyyy/MM/dd hh24:mi:ss') )
ORDER BY a.LAST_ACTIVE_TIME DESC, a.LAST_LOAD_TIME DESC) t WHERE ROWNUM <= 500;1.5、获取SQL的绑定变量（参数）值
-- 1、v$sql_bind_capture
SELECT T.NAME, T.POSITION, T.DATATYPE_STRING, T.VALUE_STRING, T.VALUE_ANYDATA
FROM V$SQL_BIND_CAPTURE T
WHERE SQL_ID = '9gq78x8ns3q1x';
-- 2、DBMS_SQLTUNE.EXTRACT_BINDS( v$sql.BIND_DATA )
SELECT DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND
FROM V$SQL
WHERE SQL_ID = '9gq78x8ns3q1x';
-- 3、历史快照表找（DBA_HIST_SQLBIND）
SELECT SNAP_ID, NAME, POSITION, VALUE_STRING, LAST_CAPTURED, WAS_CAPTURED
FROM DBA_HIST_SQLBIND
WHERE SQL_ID = '9gq78x8ns3q1x';
----------snap_id, AWR报告的快照ID。
----------name, 绑定变量的名称
----------position，绑定值在SQL语句中的位置，以1，2，3进行标注
----------value_string，就是绑定变量值
----------last_captured，最后捕获到的时间
----------was_captured，是否绑定被捕获，where子句前面的绑定不进行捕获。
-- 4、系统表找（wrh$_sqlstat）
SELECT
dbms_sqltune.extract_bind(bind_data, 1).value_string AS a,
dbms_sqltune.extract_bind(bind_data, 2).value_string AS b
FROM wrh$_sqlstat
WHERE sql_id = '9gq78x8ns3q1x';1.6、查询没有使用绑定变量来的SQL语句
/*
识别没有使用绑定变量的SQL语句利用v$sql视图的FORCE_MATCHING_SIGNATURE字段，可以识别可能从绑定变量或CURSOR_SHARING获益的SQL语句。
如果SQL已使用绑定变量或者CURSOR_SHARING,则FORCE_MATCHING_SIGNATURE在对其进行标识时将给出同样的签名。
换句话说，如果两个SQL语句除了字面量的值之外都是相同的，它们将拥有相同的FORCE_MATCHING_SIGNATURE,这意味着如果为他们提供了绑定变量或者CURSOR_SHARING,它们就成了完全相同的语句。
使用FORCE_MATCHING_SIGNATURE识别没有使用绑定变来的SQL语句。
*/
WITH FORCE_MATHCES AS
(SELECT L.FORCE_MATCHING_SIGNATURE MATHCES,
MAX(L.SQL_ID || L.CHILD_NUMBER) MAX_SQL_CHILD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANKING
FROM GV$SQL L
WHERE L.FORCE_MATCHING_SIGNATURE <> 0
AND L.PARSING_SCHEMA_NAME <> 'SYS'
GROUP BY L.FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 10)
SELECT V.SQL_ID, V.SQL_FULLTEXT, V.PARSING_SCHEMA_NAME, FM.MATHCES, FM.RANKING
FROM FORCE_MATHCES FM, GV$SQL V
WHERE FM.MAX_SQL_CHILD = (V.SQL_ID || V.CHILD_NUMBER)
AND FM.RANKING <= 5
ORDER BY FM.RANKING DESC;
--利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL
SELECT COUNT(1), TXT FROM (
SELECT *
FROM (SELECT SQL_TEXT TXT, --SUBSTR(SQL_TEXT,0,80) TXT,
FORCE_MATCHING_SIGNATURE,
COUNT (1) OVER (PARTITION BY FORCE_MATCHING_SIGNATURE) RID
FROM GV$SQL
WHERE FORCE_MATCHING_SIGNATURE > 0
AND FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE)
WHERE RID > 100 )
GROUP BY TXT
ORDER BY COUNT(1) DESC;1.7、查询执行最慢的50条SQL语句：
SELECT *
FROM (SELECT
SA.FIRST_LOAD_TIME,
SA.LAST_ACTIVE_TIME,
SA.SQL_TEXT,
SA.SQL_FULLTEXT,
SA.EXECUTIONS "执行次数",
ROUND(SA.ELAPSED_TIME / 1000000, 2) "总执行时间",
ROUND(SA.ELAPSED_TIME / 1000000 / SA.EXECUTIONS, 2) "平均执行时间",
SA.COMMAND_TYPE,
SA.PARSING_USER_ID,
U.USERNAME,
SA.HASH_VALUE
FROM V$SQLAREA SA
LEFT JOIN ALL_USERS U
ON SA.PARSING_USER_ID = U.USER_ID
WHERE SA.EXECUTIONS > 0
--AND U.USERNAME=‘APPS’
ORDER BY SA.LAST_ACTIVE_TIME DESC,(SA.ELAPSED_TIME / SA.EXECUTIONS) DESC)
WHERE ROWNUM <= 50;1.8、查询当天执行慢（大于5秒）的SQL语句：
SELECT S.SQL_TEXT, S.SQL_FULLTEXT, S.SQL_ID, ROUND(ELAPSED_TIME / 1000000 / (CASE WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 1 ELSE EXECUTIONS END), 2) "执行时间’S’"
, S.EXECUTIONS "执行次数", S.OPTIMIZER_COST COST, S.SORTS, S.PARSING_SCHEMA_NAME, S.MODULE --连接模式（JDBC THIN CLIENT：程序）
, S.LOCKED_TOTAL, S.PHYSICAL_READ_REQUESTS "物理读请求", S.PHYSICAL_READ_BYTES "物理读", S.PHYSICAL_WRITE_REQUESTS "物理写请求", S.PHYSICAL_WRITE_BYTES "物理写"
, S.ROWS_PROCESSED "返回行数", S.DISK_READS "磁盘读", S.DIRECT_WRITES "直接路径写", S.PARSING_SCHEMA_NAME, S.LAST_ACTIVE_TIME
FROM GV$SQLAREA S
WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 1 ELSE EXECUTIONS END),2) > 5 --1000000微秒 = 1S
--AND S.PARSING_SCHEMA_NAME = USER -- USER-当前连接用户
--AND S.COMMAND_TYPE IN (2, 3, 5, 6, 189) -- 2-INSERT; 3-SELECT 6-UPDATE; 7-DELETE; 189-MERGE
AND (S.LAST_LOAD_TIME >= TRUNC(SYSDATE-1) OR S.LAST_ACTIVE_TIME >= TRUNC(SYSDATE-1))
ORDER BY "执行时间’S’" DESC;1.9、 查询执行次数最多前100条SQL语句
SELECT *
FROM (SELECT S.SQL_ID, S.SQL_TEXT, S.SQL_FULLTEXT, S.EXECUTIONS, S.PARSING_USER_ID, U.USERNAME
,RANK() OVER(ORDER BY EXECUTIONS DESC) EXEC_RANK FROM V$SQL S LEFT JOIN ALL_USERS U ON U.USER_ID = S.PARSING_USER_ID) T
WHERE EXEC_RANK <= 100;1.10、查询最近一分钟最消耗CPU的SQL语句
--最近一分钟最消耗CPU的语句
SELECT ASH.INST_ID, ASH.SQL_ID, (SELECT VS.SQL_TEXT FROM GV$SQLAREA VS WHERE VS.SQL_ID = ASH.SQL_ID AND ASH.INST_ID = VS.INST_ID) SQL_TEXT
, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME, ASH.SESSION_INFO, COUNTS, PCTLOAD * 100 || '%' PCTLOAD
FROM (SELECT ASH.INST_ID, ASH.SQL_ID, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME
, (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' || ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' || ASH.SESSION_TYPE) SESSION_INFO
, COUNT(*) COUNTS, ROUND(COUNT(1)/SUM(COUNT(1)) OVER(), 2) PCTLOAD, DENSE_RANK() OVER(ORDER BY COUNT(1) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> 'BACKGROUND' AND ASH.SESSION_STATE = 'ON CPU' AND SAMPLE_TIME > SYSDATE-1 / (24*60)
GROUP BY ASH.INST_ID, ASH.SQL_ID, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME
, (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' || ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' || ASH.SESSION_TYPE)
) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;
--最近一分钟最消耗CPU的会话
SELECT V.SESSION_ID,
COUNT(*)
FROM V$ACTIVE_SESSION_HISTORY V
WHERE V.SESSION_STATE = 'ON CPU'
AND V.SAMPLE_TIME > SYSDATE - 10/ (24 * 60)
GROUP BY V.SESSION_ID
ORDER BY COUNT(*) DESC;1.11、查询最近一分钟最消耗I/O的SQL语句
--最近一分钟最消耗I/O（资源）的SQL语句
SELECT ASH.INST_ID, ASH.SQL_ID
, (SELECT VS.SQL_TEXT FROM GV$SQLAREA VS WHERE VS.SQL_ID = ASH.SQL_ID AND ASH.INST_ID = VS.INST_ID) SQL_TEXT
, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME, ASH.SESSION_INFO, COUNTS, PCTLOAD * 100 || '%' PCTLOAD
FROM ( SELECT ASH.INST_ID, ASH.SQL_ID, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME
, (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' || ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' || ASH.SESSION_TYPE) SESSION_INFO
, COUNT(*) COUNTS, ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD, DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> 'BACKGROUND' AND ASH.SESSION_STATE = 'WAITING' AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24*60)
AND ASH.WAIT_CLASS = 'USER I/O'
GROUP BY ASH.INST_ID, ASH.SQL_ID, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME
,(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' || ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' || ASH.SESSION_TYPE)
) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;
--最近一分钟最消耗I/O（资源）的会话
SELECT ASH.SESSION_ID,
ASH.SESSION_SERIAL#,
ASH.USER_ID,
ASH.PROGRAM,
SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 0)) "CPU",
SUM(DECODE(ASH.SESSION_STATE, 'WAITING', 1, 0)) - SUM(DECODE(ASH.SESSION_STATE, 'WAITING', DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0), 0)) "WAITING",
SUM(DECODE(ASH.SESSION_STATE, 'WAITING', DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0), 0)) "IO",
SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1)) "TOTAL"
FROM V$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM
ORDER BY SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1)); 1.12、查询最近一天使用了全表扫描的语句
SELECT s.sql_text, P.OBJECT_OWNER,
P.SQL_ID,
P.OPERATION,
P.OPTIONS,
S.LAST_ACTIVE_TIME,
ROW_NUMBER() OVER(PARTITION BY P.SQL_ID ORDER BY P.SQL_ID) AS ROWNUMS
FROM GV$SQL_PLAN P, GV$SQLAREA S
WHERE S.SQL_ID = P.SQL_ID
AND P.OPERATION = 'TABLE ACCESS'
AND P.OPTIONS = 'FULL'
AND P.OBJECT_OWNER != 'SYS'
AND S.LAST_ACTIVE_TIME >= trunc(SYSDATE-1)
ORDER BY S.LAST_ACTIVE_TIME DESC;1.13、查看SQL执行计划（EXPLAIN PLAN）
-->>>> 第一步：set autotrace on
-->>>> 第二步：explain plan for select sysdate from dual;
-->>>> 第三步：dbms_xplan.DISPLAY_CURSOR
ALTER SESSION SET STATISTICS_LEVEL = ALL; --不设置无法获得A-ROWS等信息
SELECT SYSDATE FROM DUAL; --运行一个SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS')); --如果SQL_ID 输入NULL 则默认为之前运行的一条SQL，即SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR）
ALTER SESSION SET STATISTICS_LEVEL=TYPICAL;
-- 如果通过AWR已经获取了SQL_ID，则可以直接得到执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('1v1gv616u8jdb'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1v1gv616u8jdb',NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));1.14、使用执行计划视图（DBA_HIST_SQL_PLAN / GV$SQL_PLAN）定位执行成本高的SQL语句
SELECT A.SQL_ID, MODULE,PLAN_HASH_VALUE,
ID,
LPAD (' ', DEPTH) || OPERATION OPERATION,
OPTIONS,
OBJECT_OWNER,
OBJECT_NAME,
OPTIMIZER,
COST,
ACCESS_PREDICATES,
FILTER_PREDICATES
FROM DBA_HIST_SQL_PLAN A, (SELECT SQL_ID, MODULE FROM GV$SQL) B
WHERE A.SQL_ID=B.SQL_ID AND B.MODULE IN ('jsenv.exe','w3wp.exe')
AND COST > 20000
--AND OBJECT_NAME NOT IN ('PRINTCHECKTREATCREDENCEVIEW','PERSONALAPPLICATIONMEDIVIEW','PERSONALAPPLICATIONMEDIVIEW_CS','PERSONALAPPLICATIONMEDIVIEW_cs','QUERYCONTEXTIDENTITY','PLACERGROUPQUERYVIEW')
--AND FILTER_PREDICATES IS NOT NULL
--AND A.SQL_ID = 'f705bwx3q0ydq'
ORDER BY COST DESC;
SELECT '| Operation |Object Name | Rows | Bytes| Cost |'
AS "Explain Plan in library cache:" FROM DUAL
UNION ALL
SELECT RPAD('| '||SUBSTR(LPAD(' ',1*(DEPTH-1))||OPERATION||
DECODE(OPTIONS, NULL,'',' '||OPTIONS), 1, 35), 36, ' ')||'|'||
RPAD(DECODE(ID, 0, '----------------------------',
SUBSTR(DECODE(SUBSTR(OBJECT_NAME, 1, 7), 'SYS_LE_', NULL, OBJECT_NAME)
||' ',1, 30)), 31, ' ')||'|'|| LPAD(DECODE(CARDINALITY,NULL,' ',
DECODE(SIGN(CARDINALITY-1000), -1, CARDINALITY||' ',
DECODE(SIGN(CARDINALITY-1000000), -1, TRUNC(CARDINALITY/1000)||'K',
DECODE(SIGN(CARDINALITY-1000000000), -1, TRUNC(CARDINALITY/1000000)||'M',
TRUNC(CARDINALITY/1000000000)||'G')))), 7, ' ') || '|' ||
LPAD(DECODE(BYTES,NULL,' ',
DECODE(SIGN(BYTES-1024), -1, BYTES||' ',
DECODE(SIGN(BYTES-1048576), -1, TRUNC(BYTES/1024)||'K',
DECODE(SIGN(BYTES-1073741824), -1, TRUNC(BYTES/1048576)||'M',
TRUNC(BYTES/1073741824)||'G')))), 6, ' ') || '|' ||
LPAD(DECODE(COST,NULL,' ', DECODE(SIGN(COST-10000000), -1, COST||' ',
DECODE(SIGN(COST-1000000000), -1, TRUNC(COST/1000000)||'M',
TRUNC(COST/1000000000)||'G'))), 8, ' ') || '|' AS "Explain plan"
FROM GV$SQL_PLAN SP
WHERE SQL_ID='f705bwx3q0ydq' ;
2、查询阻塞事件、锁及杀锁
2.1、查询历史等待事件
--阻塞
SELECT SAMPLE_TIME,SESSION_ID,SQL_ID,EVENT,P1,BLOCKING_SESSION, BLOCKING_SESSION_STATUS,PROGRAM,MODULE,MACHINE,SQL_OPNAME,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS,SQL_EXEC_START
FROM GV$ACTIVE_SESSION_HISTORY -- DBA_HIST_ACTIVE_SESS_HISTORY
WHERE BLOCKING_SESSION IS NOT NULL
AND SAMPLE_TIME >TO_TIMESTAMP('2023-04-22 22:00:00','yyyy-mm-dd hh24:mi:ss')
AND SAMPLE_TIME <TO_TIMESTAMP('2023-04-23 22:30:00','yyyy-mm-dd hh24:mi:ss');
--事件
SELECT SAMPLE_TIME,SESSION_ID,SQL_ID,EVENT,P1,BLOCKING_SESSION, BLOCKING_SESSION_STATUS,PROGRAM,MODULE,MACHINE,SQL_OPNAME,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS,SQL_EXEC_START
FROM GV$ACTIVE_SESSION_HISTORY -- DBA_HIST_ACTIVE_SESS_HISTORY
WHERE (EVENT = 'enq: TX - row lock contention' OR EVENT LIKE 'library cache%')
AND SAMPLE_TIME >TO_TIMESTAMP('2023-04-22 22:00:00','yyyy-mm-dd hh24:mi:ss')
AND SAMPLE_TIME <TO_TIMESTAMP('2023-04-23 22:30:00','yyyy-mm-dd hh24:mi:ss');
--事件次数
WITH T AS(
SELECT S.SQL_ID, COUNT(1) CC
FROM DBA_HIST_ACTIVE_SESS_HISTORY S
WHERE S.EVENT LIKE 'latch: row cache objects%' -- LATCH: ROW CACHE OBJECTS等待事件
AND S.SNAP_ID IN(
SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT S
WHERE BEGIN_INTERVAL_TIME >TO_TIMESTAMP('2023-04-22 16:00:00','yyyy-mm-dd hh24:mi:ss')
AND BEGIN_INTERVAL_TIME <TO_TIMESTAMP('2023-04-23 22:30:00','yyyy-mm-dd hh24:mi:ss')) --快照时间
GROUP BY S.SQL_ID
)
SELECT S.SQL_ID, S.SQL_TEXT,CC
FROM DBA_HIST_SQLTEXT S, T
WHERE S.SQL_ID = T.SQL_ID
ORDER BY CC DESC;2.2、快速杀锁（包括临时表被占用导致无法修改表结构/存储过程被占用导致无法编译）
--快速杀锁
SELECT 'alter system kill session '''|| SID ||',' || SERIAL# || ',@'|| INST_ID ||''' immediate;'
FROM GV$SESSION WHERE SID IN (
SELECT BLOCKING_SESSION FROM GV$SESSION WHERE BLOCKING_SESSION IS NOT NULL);
--SELECT A.BLOCKING_SESSION ,A.* FROM GV$SESSION A WHERE A.BLOCKING_SESSION IS NOT NULL;
SELECT 'alter system kill session '''|| SID ||',' || SERIAL# || ',@'|| INST_ID ||''' immediate;',
SYS_CONNECT_BY_PATH(A.SID || '@' || A.INST_ID, ' <- ') TREE, -- TREE最后一个为最终阻塞者.
A.INST_ID, A.PROCESS, A.SID, A.SERIAL#, A.SQL_ID, A.EVENT, A.STATUS, A.PROGRAM,
A.MACHINE, CONNECT_BY_ISLEAF AS ISLEAF, LEVEL AS TREE_LEVEL
FROM GV$SESSION A
START WITH A.BLOCKING_SESSION IS NOT NULL
CONNECT BY (A.SID || '@' || A.INST_ID) = PRIOR
(A.BLOCKING_SESSION || '@' || A.BLOCKING_INSTANCE);
--等待事件
--从v$session_wait中查找有问题的wait
SELECT EVENT,COUNT(*) FROM GV$SESSION_WAIT WHERE EVENT NOT LIKE '%idle%' GROUP BY EVENT ORDER BY COUNT(0) DESC;
-- 如果有library cache lock时，查看lock的都是些什么语句
SELECT A.USERNAME, A.MACHINE, A.PROGRAM, A.SID, A.SERIAL#, A.INST_ID, A.STATUS,A.BLOCKING_SESSION,A.BLOCKING_INSTANCE,A.BLOCKING_SESSION_STATUS
, A.LOCKWAIT ,A.WAIT_TIME_MICRO,A.LOGON_TIME, A.SQL_EXEC_START ,A.PREV_EXEC_START
, B.SPID "OS Process ID", A.SQL_ID, C.SQL_FULLTEXT
FROM GV$SESSION A, GV$PROCESS B, GV$SQLAREA C
WHERE B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+)
AND A.BLOCKING_SESSION IS NOT NULL
--AND A.SID IN (SELECT SID FROM GV$SESSION_WAIT WHERE EVENT = 'cursor: pin S wait on X')
ORDER BY A.SID ;
--或--
SELECT A.USERNAME, A.MACHINE, A.PROGRAM, A.SID, A.SERIAL#, A.INST_ID, A.STATUS,A.BLOCKING_SESSION,A.BLOCKING_INSTANCE,A.BLOCKING_SESSION_STATUS
, A.LOCKWAIT ,A.WAIT_TIME_MICRO,A.LOGON_TIME, A.SQL_EXEC_START ,A.PREV_EXEC_START
, A.SQL_ID,C.SQL_FULLTEXT
FROM GV$SESSION A, GV$SQLAREA C
WHERE A.SQL_ADDRESS=C.ADDRESS
AND A.SID IN (SELECT SID FROM GV$SESSION_WAIT WHERE EVENT ='Disk file operations I/O' OR EVENT='enq: TX - row lock contention')
ORDER BY A.SID ;
--发现有Library语句我们需要进一步blocker会话是谁
--注意：X$KGLPN是ORACLE系统表，需要用system用户以normal身份登录才能看到（使用）
SELECT S.SID, KGLPNMOD "Mode", KGLPNREQ "Req", SPID "OS Process",A.BLOCKING_SESSION,A.BLOCKING_INSTANCE,A.BLOCKING_SESSION_STATUS
FROM GV$SESSION_WAIT W, X$KGLPN P, GV$SESSION S ,GV$PROCESS O
WHERE P.KGLPNUSE=S.SADDR
AND KGLPNHDL=W.P1RAW
AND W.EVENT LIKE '%cursor%'
AND S.PADDR=O.ADDR;
--临时表被占用，无法修改表结构
SELECT 'alter system kill session '''|| SID ||',' || SERIAL# || ',@'|| INST_ID ||''' immediate;'
FROM GV$SESSION WHERE SID IN
(SELECT SID FROM GV$ENQUEUE_LOCK T WHERE T.TYPE='TO'
AND ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE LOWER(OBJECT_NAME)='temp_tablename') ); --TEMP_TABLENAME是临时表名，不带用户名，如TEMP_QUERYORDER
--存储过程被占用，无法编译
SELECT * FROM (
SELECT 'alter system kill session '''|| SID ||',' || SERIAL# || ',@'|| INST_ID ||''' immediate;'
FROM GV$SESSION WHERE SID IN (
SELECT SESSION_ID FROM DBA_DDL_LOCKS
WHERE LOWER(NAME) = LOWER('PROC_NAME') --说明：PROC_NAME为存储过程名，不带用户名，比如SENDSURGERYREQUESTBY
)
UNION SELECT 'alter procedure OWNER.PROC_NAME compile;' --说明：OWNER.PROC_NAME为存储过程名，带用户名，比如POOR.SENDSURGERYREQUESTBY
FROM DUAL
) ORDER BY 1 DESC;
--oracle手动锁表
lock table tbl_t1 in row share mode nowait; --2
lock table tbl_t1 in share update mode nowait; --2
lock table tbl_t1 in row exclusive mode nowait; --3
lock table tbl_t1 in share mode nowait; --4
lock table tbl_t1 in share row exclusive mode nowait; --5
lock table tbl_t1 in exclusive mode nowait; --6
/*
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁
3：Row-X 行排他锁(RX)：用于行的修改
4：Share 共享锁(S)：阻止其他DML操作
5：S/Row-X 共享行排他(SRX)：阻止其他事务操作
6：exclusive 排他锁(X)：独立访问使用
*/2.3、检查是否有手工执行sql且锁表
SELECT
O.OWNER || '.' || O.OBJECT_NAME 被锁表名 , S.MACHINE 电脑, NVL(S.SQL_EXEC_START,S.PREV_EXEC_START) SQL执行时间, L.CTIME 已持有或者等待锁的时间, S.PROGRAM, O.OBJECT_TYPE,
'ALTER SYSTEM KILL SESSION '||''''|| S.SID ||','|| S.SERIAL# ||',@'|| S.INST_ID || ''' immediate;' AS 执行这个SQL可以KILL相应进程,
S.INST_ID, S.SID , S.SERIAL# , S.TERMINAL, S.OSUSER ,
S.MODULE, S.USERNAME, L.BLOCK, --是否阻塞其他会话锁申请 1:阻塞 0:不阻塞
DECODE(L.TYPE,'TM','TABLE LOCK', 'TX','ROW LOCK', 'UL','用户锁', L.TYPE) LOCK_LEVEL,
L.LMODE --会话保持的锁的模式。
-- 0＝NONE；
-- 1＝NULL ;
-- 2＝ROW-S (SS,行级共享锁，其他对象只能查询这些数据行)，SQL操作有SELECT FOR UPDATE、LOCK FOR UPDATE、LOCK ROW SHARE；
-- 3＝ROW-X (SX,行级排它锁,在提交前不允许做DML操作)，SQL操作有INSERT、UPDATE、DELETE、LOCK ROW SHARE；
-- 4＝SHARE(共享锁)，SQL操作有CREATE INDEX、LOCK SHARE；
-- 5＝S/ROW-X (SSX,共享行级排它锁)，SQL操作有LOCK SHARE ROW EXCLUSIVE；
-- 6＝EXCLUSIVE(排它锁)，ALTER TABLE、DROP TABLE、DROP INDEX、TRUNCATE TABLE、LOOK EXCLUSIVE等DDL
,S.SQL_EXEC_START,S.PREV_EXEC_START,S.STATUS,S.LOCKWAIT, S.CURRENT_QUEUE_DURATION,S.WAIT_TIME,S.WAIT_CLASS--, A.ACTION
,S.BLOCKING_SESSION,S.BLOCKING_INSTANCE,S.BLOCKING_SESSION_STATUS
,S.WAIT_TIME_MICRO,S.LOGON_TIME
,A.SQL_TEXT
FROM GV$SESSION S, GV$LOCK L, DBA_OBJECTS O, GV$SQLAREA A
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID(+)
AND S.USERNAME IS NOT NULL
AND S.PREV_SQL_ADDR = A.ADDRESS(+)
AND S.SQL_ADDRESS = A.ADDRESS(+)
AND O.OBJECT_TYPE ='TABLE'
AND L.TYPE IN ('TM','TX')
AND OWNER NOT IN ('SYS')
--AND L.BLOCK>0
AND L.LMODE IN (3, 5,6) --行级排它锁 / 共享行级排它锁 / 排它锁
AND PROGRAM IN ('SQL Developer','plsqldev.exe')
AND O.OBJECT_NAME<>'PLAN_TABLE';2.4、查询锁进程、主机、语句
SELECT -- 'ALTER SYSTEM KILL SESSION '||''''|| S.SID ||','|| S.SERIAL# ||',@'|| S.INST_ID || ''' immediate;',
S.INST_ID, S.SID , S.SERIAL# , S.MACHINE, S.PROGRAM, S.TERMINAL, S.OSUSER , L.CTIME, --已持有或者等待锁的时间
S.MODULE, S.USERNAME, L.BLOCK, --是否阻塞其他会话锁申请 1:阻塞 0:不阻塞
DECODE(L.TYPE,'TM','TABLE LOCK', 'TX','ROW LOCK', 'UL','用户锁', L.TYPE) LOCK_LEVEL,
L.LMODE --会话保持的锁的模式。 0＝NONE； 1＝NULL ; 2＝ROW-S (SS,行级共享锁，其他对象只能查询这些数据行)，SQL操作有SELECT FOR UPDATE、LOCK FOR UPDATE、LOCK ROW SHARE；
-- 3＝ROW-X (SX,行级排它锁,在提交前不允许做DML操作)，SQL操作有INSERT、UPDATE、DELETE、LOCK ROW SHARE；
-- 4＝SHARE(共享锁)，SQL操作有CREATE INDEX、LOCK SHARE；
-- 5＝S/ROW-X (SSX,共享行级排它锁)，SQL操作有LOCK SHARE ROW EXCLUSIVE；
-- 6＝EXCLUSIVE(排它锁)，ALTER TABLE、DROP TABLE、DROP INDEX、TRUNCATE TABLE、LOOK EXCLUSIVE等DDL
,O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
,S.SQL_EXEC_START,S.PREV_EXEC_START,S.STATUS,S.LOCKWAIT, S.CURRENT_QUEUE_DURATION,S.WAIT_TIME,S.WAIT_CLASS, A.ACTION
,S.BLOCKING_SESSION,S.BLOCKING_INSTANCE,S.BLOCKING_SESSION_STATUS
,S.WAIT_TIME_MICRO,S.LOGON_TIME
,A.SQL_FULLTEXT
FROM GV$SESSION S, GV$LOCK L, DBA_OBJECTS O, GV$SQLAREA A
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID(+)
AND S.USERNAME IS NOT NULL
--AND S.PREV_SQL_ADDR = A.ADDRESS(+)
AND S.SQL_ADDRESS = A.ADDRESS(+)
AND O.OBJECT_TYPE ='TABLE'
AND L.TYPE IN ('TM','TX')
--AND L.BLOCK>0
AND L.LMODE IN ( 5,6)
--AND EXISTS (SELECT * FROM GV$LOCK LB WHERE L.SID=LB.SID AND LB.BLOCK=1 )
; 2.5、查询正在执行的SQL语句
-- 正在执行的语句
SELECT 'ALTER SYSTEM KILL SESSION '||''''|| B.SID ||','|| B.SERIAL# || ',@' || B.INST_ID || ''' immediate;', B.USERNAME 登录ORACLE用户名
, B.SID , B.SERIAL#, SPID 操作系统ID, PADDR
, B.MACHINE 计算机名,B.PROGRAM, B.OSUSER
, B.SQL_EXEC_START,B.PREV_EXEC_START,B.STATUS,B.LOCKWAIT, B.CURRENT_QUEUE_DURATION,B.WAIT_TIME,B.WAIT_CLASS,B.BLOCKING_SESSION,B.BLOCKING_INSTANCE
, BLOCKING_SESSION_STATUS, SQL_TEXT 正在执行的SQL
FROM GV$PROCESS A, GV$SESSION B, GV$SQLAREA C
WHERE A.ADDR = B.PADDR
AND B.SQL_HASH_VALUE = C.HASH_VALUE
-- AND B.STATUS NOT IN ('KILLED','INACTIVE')
AND B.STATUS IN ('ACTIVE')
--AND LOWER(B.MACHINE) LIKE '%bj%'
ORDER BY B.SQL_EXEC_START;
-- 正在执行的语句（一段时间内）
SELECT SID, S.USERNAME 用户名, S.LAST_CALL_ET 持续时间, S.STATUS 状态, S.LOCKWAIT 等待锁, S.MACHINE, S.TERMINAL, S.PROGRAM, S.SQL_ADDRESS
, A.EXECUTIONS 同SQL_执行次数, A.DISK_READS 同SQL_磁盘读次数, A.PHYSICAL_READ_BYTES 同SQL_物理读字节
, A.DIRECT_WRITES 同SQL_DIRECT_WRITES, A.BUFFER_GETS 同SQL_BUFFER_GETS, A.APPLICATION_WAIT_TIME 同SQL_APPLICATION_WAIT_TIME
, A.CLUSTER_WAIT_TIME 同SQL_CLUSTER_WAIT_TIME, A.USER_IO_WAIT_TIME 同SQL_USER_IO_WAIT_TIME
, A.OPTIMIZER_COST 同SQL_OPTIMIZER_COST, S.LOGON_TIME 开始登入时间, A.SQL_TEXT
FROM GV$SESSION S, GV$PROCESS P, GV$SQLAREA A
WHERE S.PADDR = P.ADDR AND S.SQL_HASH_VALUE = A.HASH_VALUE
-- AND S.STATUS = 'ACTIVE'
-- AND S.USERNAME IS NOT NULL
--ORDER BY S.LAST_CALL_ET DESC;
AND S.STATUS NOT IN ('INACTIVE')
AND S.LOGON_TIME > SYSDATE-0.2
ORDER BY A.DISK_READS DESC;
-- 每个Session的CPU占用情况
SELECT SS.SID, SE.COMMAND, SS.VALUE CPU, SE.USERNAME, SE.PROGRAM,SE.MACHINE,SE.SQL_ID, SE.*
FROM V$SESSTAT SS, V$SESSION SE
WHERE SS.STATISTIC# IN
( SELECT STATISTIC#
FROM V$STATNAME
WHERE LOWER(NAME) = 'cpu used by this session' )
AND SE.SID = SS.SID
AND SS.SID > 6
ORDER BY CPU DESC ;2.6、查询分布式事务（2PC）遗留锁
-- 查询未提交的锁
SELECT * FROM DBA_2PC_PENDING;
SELECT * FROM SYS.PENDING_TRANS$; --要存在 DBA_2PC_PENDING.LOCAL_TRAN_ID 的一样记录；否则要补数据
SELECT * FROM SYS.PENDING_SESSIONS$ --要存在 DBA_2PC_PENDING.LOCAL_TRAN_ID 的一样记录；否则要补数据
--输出结果：LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIXED ADVICE TRAN_COMMENT FAIL_TIME FORCE_TIME RETRY_TIME OS_USER OS_TERMINAL HOST DB_USER COMMIT#
--1 18.0.229 SGSYTEST.4e936cdb.18.0.229 collecting no 2021/12/3 17:49:36 2023/1/10 18:00:05 luyanlin unknown luyanlin-5040 APPS 3696577323
--2 28.24.1850 SGSYTEST.4e936cdb.28.24.1850 collecting no 2022/5/7 10:39:30 2023/1/10 18:00:05 heshushu unknown Hss APPS 5298498559
-- 杀锁
SELECT 'rollback force ''' || LOCAL_TRAN_ID || '''' FROM SYS.PENDING_TRANS$;
-- 或
SELECT 'commit force ''' || LOCAL_TRAN_ID||'''' FROM SYS.PENDING_TRANS$;
-- 断开所有客户端连接
SELECT 'alter system kill session ''' || SID || ',' || SERIAL# || ''';' FROM V$SESSION WHERE SID>50;
--*****××××××××××××××× 如果DBA_2PC_PENDING找不到报错提示的LOCAL_TRAN_ID，则可能是数据不完整，要手工插入数据后再ROLLBACK *************************
-- ORACLE系统表X$KTUXE（以及X$打头的）需要用system的normal身份登录才可以看到（查询）
--1、查基表，确认是否存在报错的事务ID
SELECT KTUXEUSN, KTUXESLT, KTUXESQN /* TRANSACTION ID */
,KTUXESTA STATUS, KTUXECFL FLAGS
FROM X$KTUXE
WHERE KTUXESTA!='INACTIVE'
--AND KTUXEUSN= 72
;
-- 通过X$KTUXE这个基表，我们看到确实存在这个事务，而且是PREPARED状态。
--2、由于DBA_2PC_PENDING试图是依赖于PENDING_TRANS$这个表，同时事务是与SESSION关联在一起的，所以我们需要手工往PENDING_TRANS$和PENDING_SESSIONS$两个表中插入数据。
INSERT INTO SYS.PENDING_TRANS$ (LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID
,STATE, STATUS, SESSION_VECTOR, RECO_VECTOR,TYPE#, FAIL_TIME, RECO_TIME)
VALUES( '72.0.1608712', 306206, 'XXXXXXX.12345.1.2.3',
'prepared','P', HEXTORAW( '00000001' ), HEXTORAW( '00000000' ), 0, SYSDATE, SYSDATE );
INSERT INTO SYS.PENDING_SESSIONS$
VALUES( '72.0.1608712',
1, HEXTORAW('05004F003A1500000104'),
'C', 0, 30258592, '',
146 );
COMMIT;
--3、ROLLBACK
ROLLBACK FORCE '72.0.1608712';2.7、查询作业（执行情况）（ALL_SCHEDULER_JOBS; DBA_SCHEDULER_JOBS; DBA_SCHEDULER_RUNNING_JOBS; DBA_SCHEDULER_JOB_RUN_DETAILS）
declare
row_job ALL_SCHEDULER_JOBS%rowtype;
cursor cur_job
is select * from ALL_SCHEDULER_JOBS where "ENABLED"='TRUE' AND OWNER in ('APPS','CONCEPT','CONFIG','DATA','ESB','FIAB','GZRHIN','POOR','PORX','PRPA');
begin
open cur_job;
loop
fetch cur_job into row_job;
exit when cur_job%notfound;
dbms_output.put_line('begin');
dbms_output.put_line(' sys.dbms_scheduler.disable(name => ''' || row_job.OWNER || '.' || row_job.JOB_NAME || ''');');
dbms_output.put_line('end;');
dbms_output.put_line('/');
end loop;
close cur_job;
end;
/
-- 查看没有正常运行的作业
SELECT A.OWNER, A.JOB_NAME, A.COMMENTS, A.ENABLED ,A.STATE
FROM DBA_SCHEDULER_JOBS A
WHERE (A.ENABLED<>'TRUE' OR A.STATE<>'SCHEDULED')
--AND A.OWNER NOT IN ('SYS')
;
-- 查看job的运行日志
SELECT T.OWNER, T.JOB_NAME, T.STATUS, T.ERROR#, T.ERRORS, T.REQ_START_DATE, T.ACTUAL_START_DATE, T.RUN_DURATION, T.*
FROM DBA_SCHEDULER_JOB_RUN_DETAILS T
--WHERE T.JOB_NAME = 'JOB_BOOK1'
WHERE T.ACTUAL_START_DATE > SYSDATE-0.01
ORDER BY T.LOG_DATE DESC;
-- 查看job的最后运行时间
SELECT OWNER, JOB_NAME, STATUS, ERROR#, ERRORS, MAX(LOG_DATE) 最后运行时间
FROM DBA_SCHEDULER_JOB_RUN_DETAILS T
WHERE ACTUAL_START_DATE >TO_DATE('2023-04-01 00:00:00','yyyy-mm-dd HH24:mi:ss')
GROUP BY OWNER,JOB_NAME,STATUS,ERROR#,ERRORS
ORDER BY MAX(LOG_DATE) DESC;
-- 查看有哪些作业正在运行
SELECT * FROM DBA_SCHEDULER_RUNNING_JOBS;
--<<运行作业>>===============
grant execute on dbms_job to sys;
grant execute on dbms_job to fiab;
BEGIN
dbms_scheduler.run_job('"FIAB"."testjob"');
END;
--<<停止作业>>===============
--1、查看正在运行的job;
SELECT * FROM USER_SCHEDULER_RUNNING_JOBS ;
--2、根据SID查出对应的SESSION;
SELECT SID, SERIAL# FROM V$SESSION WHERE SID=690;
--3、KILL对应的SESSION;
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;2.8、ORACLE自带（系统）作业
select * FROM DBA_SCHEDULER_JOBS where job_name not in (select JOB_NAME FROM ALL_SCHEDULER_JOBS);
PROGRAM_NAME列这些内容要到下面的系统表找对应存储过程
SELECT * FROM dba_scheduler_programs; （譬如 dbms_scheduler.auto_purge 对应 SYS.DBMS_SCHEDULER 包里面的存储过程 auto_purge）
测试代码：
begin
-- Call the procedure
sys.dbms_scheduler.auto_purge;
end;
