10.2.0.3 中 v$session,v$active_session_history,dba_hist_active_sess_history增加了以下字段
1.
2. 查询ASH
查询活动会话历史调用了什么存储过程
conn / as sysdba
set pages 500 linesize 160
col calling_code for a70
select p1.object_name ||' '|| p1.procedure_name || ' ' ||
p2.object_name ||' '|| p2.procedure_name
"calling_code",
s.sql_id,
count(*)
from v$active_session_history s,
all_procedures p1,
all_procedures p2,
v$sql st
where s.plsql_entry_object_id = p1.object_id (+)
and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
and s.plsql_object_id = p2.object_id (+)
and s.plsql_subprogram_id = p2.subprogram_id (+)
and s.sql_id = st.sql_id(+)
and s.sample_time > sysdate - &minutes/(60*24)
group by p1.object_name, p1.procedure_name,
p2.object_name, p2.procedure_name,
s.sql_id
order by count(*)
/
SQL> conn / as sysdba
Connected.
SQL> set pages 500 linesize 160
SQL> col calling_code for a70
SQL> select p1.object_name ||' '|| p1.procedure_name || ' ' ||
2 p2.object_name ||' '|| p2.procedure_name
3 "calling_code",
4 s.sql_id,
5 count(*)
6 from v$active_session_history s,
7 all_procedures p1,
8 all_procedures p2,
9 v$sql st
10 where s.plsql_entry_object_id = p1.object_id (+)
11 and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
12 and s.plsql_object_id = p2.object_id (+)
13 and s.plsql_subprogram_id = p2.subprogram_id (+)
14 and s.sql_id = st.sql_id(+)
15 and s.sample_time > sysdate - &minutes/(60*24)
16 group by p1.object_name, p1.procedure_name,
17 p2.object_name, p2.procedure_name,
18 s.sql_id
19 order by count(*)
20 /
Enter value for minutes: 60*24
old 15: and s.sample_time > sysdate - &minutes/(60*24)
new 15: and s.sample_time > sysdate - 60*24/(60*24)
calling_code SQL_ID COUNT(*)
---------------------------------------------------------------------- ------------- ----------
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC cqjwytk1ghamg 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 4y1y43113gv8f 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 11zqjqwdjb6tp 1
b2kf2cxf30jh3 1
DBMS_SPACE AUTO_SPACE_ADVISOR_JOB_PROC DBMS_SPACE OBJECT_GROWTH_TREND_ 3h4d1uux6kd0x 1
CURTAB
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC b6usrg82hwsa3 1
fskdwb6ppw164 1
DBMS_SPACE AUTO_SPACE_ADVISOR_JOB_PROC ffrxyztt4415k 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 1fp87jmavgnvs 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 402690djv4v60 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC g5kzvfybhsr96 1
P_TEST2 dvt1n1r9p84vj 1
DBMS_SPACE AUTO_SPACE_ADVISOR_JOB_PROC 0jrz5kc6a3jry 1
DBMS_SPACE AUTO_SPACE_ADVISOR_JOB_PROC cvn54b7yz0s8u 1
32hbap2vtmf53 1
9zmwy5hkhq8h7 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC DBMS_SYS_SQL PARSE 3j1qd2tnzd26w 1
PRVT_HDM AUTO_EXECUTE 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 8wpwh54q0y4ky 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC STANDARD SYSDATE b6usrg82hwsa3 1
MGMT_CONFIG COLLECT_CONFIG cvn54b7yz0s8u 1
PRVT_ADVISOR DELETE_EXPIRED_TASKS 1
9babjv8yq8ru3 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC b4anb2n74m7rv 1
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 5ps3p5ma94bkh 2
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 0ybwd63u2any5 2
bh0cvm22fks6k 2
4gd6b1r53yt88 3
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC cydgw456rs7z0 3
DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC 8a1pvy4cy8hgv 7
3vaa5k7us1n4g 8
0syfh8fyz2x2g 122
P_TEST2 P_TEST dvt1n1r9p84vj 232
930
34 rows selected.
SQL>
3. 用触发器捕获存储过程名
conn / as sysdba
grant select on sys.v_$session to a;
grant select on sys.v_$sql to a;
conn a/a
drop table t1;
create table t1 (a int);
create or replace procedure p_test
is
begin
insert into t1 values (1);
end;
/
show err
create or replace procedure p_test2
is
begin
p_test;
commit;
end;
/
show err
create or replace trigger tr_t1
after insert or update on t1
for each row
declare
v_sid int;
v_usr varchar2(30);
v_po1 varchar2(30);
v_ps1 varchar2(30);
v_po2 varchar2(30);
v_ps2 varchar2(30);
v_sql varchar2(40);
begin
select s.sid, s.username,
p1.object_name, p1.procedure_name,
p2.object_name, p2.procedure_name,
substr(sql.sql_text,1,40)
into v_sid,v_usr,v_po1,v_ps1,v_po2,v_ps2,v_sql
from v$session s, all_procedures p1, all_procedures p2, v$sql sql
where s.sid = userenv('sid')
and s.plsql_entry_object_id = p1.object_id(+)
and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
and s.plsql_object_id = p2.object_id(+)
and s.plsql_subprogram_id = p2.subprogram_id(+)
and s.sql_id = sql.sql_id
;
dbms_output.put_line('sid:'||v_sid);
dbms_output.put_line('usr:'||v_usr);
dbms_output.put_line('po1:'||v_po1);
dbms_output.put_line('ps1:'||v_ps1);
dbms_output.put_line('po2:'||v_po2);
dbms_output.put_line('ps2:'||v_ps2);
dbms_output.put_line('sql:'||v_sql);
end;
/
show err
set serveroutput on
exec p_test2
SQL> conn / as sysdba
Connected.
SQL> grant select on sys.v_$session to a;
Grant succeeded.
SQL> grant select on sys.v_$sql to a;
Grant succeeded.
SQL> conn a/a
Connected.
SQL> drop table t1;
Table dropped.
SQL> create table t1 (a int);
Table created.
SQL> create or replace procedure p_test
is
begin
insert into t1 values (1);
end;
/
show err
2 3 4 5 6
Procedure created.
SQL> No errors.
SQL> create or replace procedure p_test2
is
begin
p_test;
commit;
end;
/
show err
2 3 4 5 6 7
Procedure created.
SQL> No errors.
SQL> create or replace trigger tr_t1
2 after insert or update on t1
3 for each row
4 declare
5 v_sid int;
6 v_usr varchar2(30);
7 v_po1 varchar2(30);
8 v_ps1 varchar2(30);
9 v_po2 varchar2(30);
10 v_ps2 varchar2(30);
11 v_sql varchar2(40);
12 begin
13 select s.sid, s.username,
14 p1.object_name, p1.procedure_name,
15 p2.object_name, p2.procedure_name,
16 substr(sql.sql_text,1,40)
17 into v_sid,v_usr,v_po1,v_ps1,v_po2,v_ps2,v_sql
18 from v$session s, all_procedures p1, all_procedures p2, v$sql sql
19 where s.sid = userenv('sid')
20 and s.plsql_entry_object_id = p1.object_id(+)
21 and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
22 and s.plsql_object_id = p2.object_id(+)
23 and s.plsql_subprogram_id = p2.subprogram_id(+)
24 and s.sql_id = sql.sql_id
25 ;
26 dbms_output.put_line('sid:'||v_sid);
27 dbms_output.put_line('usr:'||v_usr);
28 dbms_output.put_line('po1:'||v_po1);
29 dbms_output.put_line('ps1:'||v_ps1);
30 dbms_output.put_line('po2:'||v_po2);
31 dbms_output.put_line('ps2:'||v_ps2);
32 dbms_output.put_line('sql:'||v_sql);
33 end;
34 /
show err
Trigger created.
SQL> No errors.
SQL> set serveroutput on
SQL> exec p_test2
sid:1623
usr:A
po1:P_TEST2
ps1:
po2:
ps2:
sql:SELECT S.SID, S.USERNAME, P1.OBJECT_NAME
PL/SQL procedure successfully completed.
SQL>
4.例子:用SYSTEM用户触发器记录存储过程名
conn / as sysdba
revoke select on sys.v_$session from a;
revoke select on sys.v_$sql from a;
conn a/a
drop table t1;
create table t1 (a int);
create or replace procedure p_test
is
begin
insert into t1 values (1);
end;
/
show err
create or replace procedure p_test2
is
begin
p_test;
commit;
end;
/
show err
drop trigger tr_t1;
conn / as sysdba
grant select on sys.v_$session to system;
grant select on dba_procedures to system;
grant select on sys.v_$sql to system;
conn system/manager
drop table t_a;
create table t_a (
sid int not null,
username varchar2(30),
plsql_entry_obj_name varchar2(40),
plsql_entry_sub_name varchar2(40),
plsql_obj_name varchar2(40),
plsql_sub_name varchar2(40),
sqltext varchar2(100),
presqltext varchar2(100),
timestamp date default sysdate not null
)
/
create or replace trigger tr_t1
before insert or update on a.t1
for each row
declare
begin
insert into t_a (
sid, username,
plsql_entry_obj_name, plsql_entry_sub_name,
plsql_obj_name, plsql_sub_name, sqltext, presqltext)
select s.sid, s.username
,p1.object_name, p1.procedure_name
,p2.object_name, p2.procedure_name
,substr(sql.sql_text,1,100)
,substr(presql.sql_text,1,100)
from v$session s
,dba_procedures p1, dba_procedures p2
,v$sql sql, v$sql presql
where s.sid = userenv('sid')
and s.plsql_entry_object_id = p1.object_id(+)
and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
and s.plsql_object_id = p2.object_id(+)
and s.plsql_subprogram_id = p2.subprogram_id(+)
and s.sql_id = sql.sql_id(+)
and s.prev_sql_id = presql.sql_id(+);
end;
/
show err
SQL> conn / as sysdba
Connected.
SQL> grant select on sys.v_$session to system;
Grant succeeded.
SQL> grant select on dba_procedures to system;
Grant succeeded.
SQL> grant select on sys.v_$sql to system;
Grant succeeded.
SQL> conn system/manager
Connected.
SQL> drop table t_a;
Table dropped.
SQL> create table t_a (
2 sid int not null,
3 username varchar2(30),
4 plsql_entry_obj_name varchar2(40),
5 plsql_entry_sub_name varchar2(40),
6 plsql_obj_name varchar2(40),
7 plsql_sub_name varchar2(40),
8 sqltext varchar2(100),
9 presqltext varchar2(100),
10 timestamp date default sysdate not null
11 )
12 /
Table created.
SQL> create or replace trigger tr_t1
2 before insert or update on a.t1
3 for each row
4 declare
5 begin
6 insert into t_a (
7 sid, username,
8 plsql_entry_obj_name, plsql_entry_sub_name,
9 plsql_obj_name, plsql_sub_name, sqltext, presqltext)
10 select s.sid, s.username
11 ,p1.object_name, p1.procedure_name
12 ,p2.object_name, p2.procedure_name
13 ,substr(sql.sql_text,1,100)
14 ,substr(presql.sql_text,1,100)
15 from v$session s
16 ,dba_procedures p1, dba_procedures p2
17 ,v$sql sql, v$sql presql
18 where s.sid = userenv('sid')
19 and s.plsql_entry_object_id = p1.object_id(+)
20 and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
21 and s.plsql_object_id = p2.object_id(+)
22 and s.plsql_subprogram_id = p2.subprogram_id(+)
23 and s.sql_id = sql.sql_id(+)
24 and s.prev_sql_id = presql.sql_id(+);
25 end;
26 /
show err
Trigger created.
SQL> No errors.
SQL>
用户A运行p_test2和insert语句
conn a/a
exec p_test2
insert into t1 values (2);
commit;
SQL> conn a/a
Connected.
SQL> exec p_test2
PL/SQL procedure successfully completed.
SQL> insert into t1 values (2);
commit;
1 row created.
SQL>
Commit complete.
SQL>
SYSTEM查询
conn system/manager
select * from t_a;
SQL> select * from t_a;
SID USERNAME PLSQL_ENTRY_OBJ_NAME PLSQL_ENTRY_SUB_NAME
------- ---------- ---------------------------------------- ----------------------------------------
PLSQL_OBJ_NAME PLSQL_SUB_NAME SQLTEXT
---------------------------------------- ---------------------------------------- ----------------------------------------
PRESQLTEXT TIMESTAMP
---------------------------------------------------------------------------------------------------- ------------------
1623 A P_TEST2
INSERT INTO T_A ( SID, USERNAME, PLSQL_E
NTRY_OBJ_NAME, PLSQL_ENTRY_SUB_NAME, PLS
QL_OBJ_NAME, PLSQL_S
SELECT DECODE('A','A','1','2') FROM DUAL 18-FEB-09
1623 A TR_T1
insert into t1 values (2)
BEGIN p_test2; END; 18-FEB-09
SQL>
第1条记录: 调用存储过程插表，触发了SYSTEM的触发器, 存储过程名是P_TEST2
第2条记录: 运行SQL语句插表，触发了SYSTEM的触发器, 存储过程名是触发器名TR_T1
5. 使用dbms_utility.format_call_stack显示PL/SQL调用栈的信息
修改4
conn a/a
drop table t1;
create table t1 (a int);
create or replace procedure p_test
is
begin
insert into t1 values (1);
end;
/
show err
create or replace procedure p_test2
is
begin
p_test;
commit;
end;
/
show err
conn / as sysdba
grant select on sys.v_$session to system;
grant select on dba_procedures to system;
grant select on sys.v_$sql to system;
conn system/manager
drop table t_a;
create table t_a (
sid int not null,
audsid int not null,
username varchar2(30),
plsql_entry_obj_name varchar2(40),
plsql_entry_sub_name varchar2(40),
plsql_obj_name varchar2(40),
plsql_sub_name varchar2(40),
sqltext varchar2(100),
presqltext varchar2(100),
call_stack varchar2(2000),
timestamp date default sysdate not null
)
/
create or replace trigger tr_t1
before insert or update on a.t1
for each row
declare
begin
insert into t_a (
sid, audsid ,username
,plsql_entry_obj_name, plsql_entry_sub_name
,plsql_obj_name, plsql_sub_name, sqltext, presqltext
,call_stack)
select s.sid, s.audsid, s.username
,p1.object_name, p1.procedure_name
,p2.object_name, p2.procedure_name
,substr(sql.sql_text,1,100)
,substr(presql.sql_text,1,100)
,substr(dbms_utility.format_call_stack,1,2000)
from v$session s
,dba_procedures p1, dba_procedures p2
,v$sql sql, v$sql presql
where s.audsid = userenv('sessionid')
and s.plsql_entry_object_id = p1.object_id(+)
and s.plsql_entry_subprogram_id = p1.subprogram_id(+)
and s.plsql_object_id = p2.object_id(+)
and s.plsql_subprogram_id = p2.subprogram_id(+)
and s.sql_id = sql.sql_id(+)
and s.prev_sql_id = presql.sql_id(+);
end;
/
show err
conn a/a
exec p_test2
insert into t1 values (2);
commit;
conn system/manager
select * from t_a;
SQL> select * from t_a;
SID AUDSID USERNAME PLSQL_ENTRY_OBJ_NAME PLSQL_ENTRY_SUB_NAME
------- ---------- ---------- ---------------------------------------- ----------------------------------------
PLSQL_OBJ_NAME PLSQL_SUB_NAME
---------------------------------------- ----------------------------------------
SQLTEXT
----------------------------------------------------------------------------------------------------
PRESQLTEXT
----------------------------------------------------------------------------------------------------
CALL_STACK
--------------------------------------------------------------------------------------------------------------------------------------------
TIMESTAMP
------------------
1627 400061 A P_TEST2
TR_T1
INSERT INTO T1 VALUES (1)
----- PL/SQL Call Stack -----
object line object
handle number name
0xcfef6008 1 anonymous block
0xcff032f0 3 SYSTEM.TR_T1
0xde5c57f0 4 procedure A.P_TEST
0xde5ac390 4 procedure A.P_TEST2
0xde5a3000 1 anonymous block
18-FEB-09
1627 400061 A TR_T1
insert into t1 values (2)
BEGIN p_test2; END;
----- PL/SQL Call Stack -----
object line object
handle number name
0xcfef6008 1 anonymous block
0xcff032f0 3 SYSTEM.TR_T1
18-FEB-09
SQL>
6. 查询x$表得到正在运行的存储过程名
conn / as sysdba
grant execute on dbms_lock to a;
conn a/a
set pages 50000 line 160
set serveroutput on
create or replace procedure p_test
is
begin
dbms_lock.sleep(60);
end;
/
show err
create or replace procedure p_test2
is
begin
p_test;
end;
/
show err
create or replace procedure p_test3
is
begin
p_test2;
end;
/
show err
drop table t1;
create table t1 (a int);
create or replace trigger tr_t1
before insert on t1
for each row
declare
begin
p_test3;
end;
/
show err
insert into t1 values (1);
conn / as sysdba
set pages 50000 line 140
col owner for a10
col name for a20
col sid for 999999
col username for a10
col program for a20
col module for a20
col action for a20
col client_info for a20
select decode(o.kglobtyp,
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
12, 'TRIGGER',
13, 'CLASS') "TYPE",
substr(o.kglnaown,1,20) "OWNER",
substr(o.kglnaobj,1,35) "NAME",
s.indx "SID",
s.ksuseser "SERIAL",
s.ksuudnam "USERNAME",
s.ksuseapp "PROGRAM",
x.app "MODULE",
x.act "ACTION",
x.clinfo "CLIENT_INFO"
from sys.x$kglob o,
sys.x$kglpn p,
sys.x$ksuse s,
sys.x$ksusex x
where o.inst_id = userenv('Instance')
and p.inst_id = userenv('Instance')
and s.inst_id = userenv('Instance')
and o.kglhdpmd = 2
and o.kglobtyp in (7, 8, 9, 12, 13)
and p.kglpnhdl = o.kglhdadr
and s.addr = p.kglpnses
and x.inst_id = userenv('Instance')
and x.sid = s.indx
and x.serial = s.ksuseser
order by 1,2,3
/
SQL> set pages 50000 line 140
SQL> col owner for a10
SQL> col name for a20
SQL> col sid for 999999
SQL> col username for a10
SQL> col program for a20
SQL> col module for a20
SQL> col action for a20
SQL> col client_info for a20
SQL> select decode(o.kglobtyp,
2 7, 'PROCEDURE',
3 8, 'FUNCTION',
4 9, 'PACKAGE',
5 12, 'TRIGGER',
6 13, 'CLASS') "TYPE",
7 substr(o.kglnaown,1,20) "OWNER",
8 substr(o.kglnaobj,1,35) "NAME",
9 s.indx "SID",
10 s.ksuseser "SERIAL",
11 s.ksuudnam "USERNAME",
12 s.ksuseapp "PROGRAM",
13 x.app "MODULE",
14 x.act "ACTION",
x.clinfo "CLIENT_INFO"
15 16 from sys.x$kglob o,
17 sys.x$kglpn p,
18 sys.x$ksuse s,
19 sys.x$ksusex x
20 where o.inst_id = userenv('Instance')
21 and p.inst_id = userenv('Instance')
22 and s.inst_id = userenv('Instance')
23 and o.kglhdpmd = 2
24 and o.kglobtyp in (7, 8, 9, 12, 13)
25 and p.kglpnhdl = o.kglhdadr
26 and s.addr = p.kglpnses
27 and x.inst_id = userenv('Instance')
28 and x.sid = s.indx
29 and x.serial = s.ksuseser
30 order by 1,2,3
31 /
TYPE OWNER NAME SID SERIAL USERNAME PROGRAM MODULE ACTION
--------- ---------- -------------------- ------- ---------- ---------- -------------------- -------------------- --------------------
CLIENT_INFO
--------------------
PACKAGE SYS DBMS_LOCK 1627 271 A SQL*Plus SQL*Plus
PROCEDURE A P_TEST 1627 271 A SQL*Plus SQL*Plus
PROCEDURE A P_TEST2 1627 271 A SQL*Plus SQL*Plus
PROCEDURE A P_TEST3 1627 271 A SQL*Plus SQL*Plus
TRIGGER A TR_T1 1627 271 A SQL*Plus SQL*Plus
SQL>
7. 查询x$表得到运行过的SQL和存储过程的对应关系
见
what package/procedure did SQL come from?
Relationship between SQL statements in shared pool
SCRIPT: HOW TO IDENTIFY what packages are in the shared_pool and how many times have they been executed
外部链接:
-----
10.2.0.3新增字段
ASH – Active Session History Feel the Power
Action, Module, Program ID and V$SQL...
V$SESSION
V$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY
-----
X$表
How can I tell if a procedure/package is running?, 2
executing_packages.sql
How can I track the execution of PL/SQL and SQL?
该文也介绍了用dbms_application_info跟踪PLSQL的运行
Relationship between SQL statements in shared pool
SCRIPT: HOW TO IDENTIFY what packages are in the shared_pool and how many times have they been executed
Troubleshooting and Diagnosing ORA-4031 Error
解读X$表
Oracle X$ Tables
ORA-600 Lookup Error Categories
-----
PLSQL调用栈
FORMAT_CALL_STACK Function
How Can I find out who called me or what my name is
-fin-
Posts
