接到现场数据库维护厂商发出的警告,有一条 insert语句执行了几千秒。
开发排查,从select * from dba_source s where s.text like '%XXX%';查到是一个存储过程,于是找到存储过程,将其改名。
但问题没有解决,隔天又收到警告。
难道是存储过程改名有问题,我远程到现场看了下,并没有问题。再次查看数据库报告:
SQL Module 是 DBMS_SCHEDULER,确定无疑是Oracle JOB调用,奇怪的是SQL语句有 /* SQL Analyze(194, 1) */ 这样的提示。
如果想定位到这条SQL,最好是找到这条SQL历史执行的session,希望找到蛛丝马迹。
select * from dba_hist_active_sess_history s where s.sql_id='29yrhxqdk2d1q';
TOP_LEVEL_SQL_ID 为5zruc4v6y32f9:说明是sql_id为5zruc4v6y32f9的调用的这条sql
MODULE:DBMS_SCHEDULER
PLSQL_ENTRY_OBJECT_ID:9013
根据上面的信息再进行查询:
select * from dba_objects where object_id=9013;--结果是owner=sys object_name=dbms_sqltune
5zruc4v6y32f9
DECLARE
job BINARY_INTEGER := :job;
next_date TIMESTAMP WITH TIME ZONE := :mydate;
broken BOOLEAN := FALSE;
job_name VARCHAR2(30) := :job_name;
job_subname VARCHAR2(30) := :job_subname;
job_owner VARCHAR2(30) := :job_owner;
job_start TIMESTAMP WITH TIME ZONE := :job_start;
job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;
window_start TIMESTAMP WITH TIME ZONE := :window_start;
window_end TIMESTAMP WITH TIME ZONE := :window_end;
chain_id VARCHAR2(14) := :chainid;
credential_owner varchar2(30) := :credown;
credential_name varchar2(30) := :crednam;
destination_owner varchar2(30) := :destown;
destination_name varchar2(30) := :destnam;
job_dest_id varchar2(14) := :jdestid;
log_id number := :log_id;
BEGIN
DECLARE
ename VARCHAR2(30);
BEGIN
ename := dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK');
END;
:mydate := next_date;
IF broken THEN
:b := 1;
ELSE
:b := 0;
END IF;
END;
这是数据库SQL优化的包,自己调用的,终于明白/* SQL Analyze(194, 1) */的意思了,是标记这种优化包调用的。