一、关于基础表
Oc_COJ^c680758
rd-A6z/&[1R1] H680758 Oracle 10G之前,启用AUTOTRACE功能需要手工创建plan_table表,创建脚本为$ORACLE_HOME/rdbms/admin /utlxplan.sql。但在10g中,已经默认创建了PLAN_TABLE$的基表,并以public用户创建了相应的同义词PUBLIC。ITPUB个人空间DR#IlHrT
ITPUB个人空间(iQ0xJga7_-j
SQL> select owner,object_name,OBJECT_TYPE from dba_objects where object_name like '%PLAN_TABLE%';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------- -------------------
SYS SQL_PLAN_TABLE_TYPE TYPE
PUBLIC SQL_PLAN_TABLE_TYPE SYNONYM
SYS PLAN_TABLE$ TABLE
PUBLIC PLAN_TABLE SYNONYM
EXFSYS EXF$PLAN_TABLE TABLE
SQL> select * from dba_synonyms where SYNONYM_NAME like '%PLAN_TABLE%';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
-------------- ---------------------- ------------------------------ ------------------------------ --------
PUBLIC SQL_PLAN_TABLE_TYPE SYS SQL_PLAN_TABLE_TYPE
PUBLIC PLAN_TABLE SYS PLAN_TABLE$ ITPUB个人空间SHlU.AC*Q.O
i5G1_]E7r680758 从上面的结果可以看出,PLAN_TABLE正是基于PLAN_TABLE$的同义词。
su�^9s+X:b;},h%e8_680758 ITPUB个人空间p"NK,W"zgN1ag^
二、关于plustrace角色ITPUB个人空间,@/SkvSq/
ITPUB个人空间v1diM [!xC-F
和Oracle9i一样,10g中plustrace角色默认也是disabled的。如果使用非授权用户打开Oracle trace功能会得到以下的错误。
"y3I%fh5D680758 ITPUB个人空间 {0[H1a#t
SQL> conn hr/hr
Connected.
SQL> set autotrace trace
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
@hP /E1@I`)M680758 ITPUB个人空间U~ ^4J4p&e4x%_
这时需要执行$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本,手工创建plustrace角色。
eC&b6q QO8LP:M$o~O&d680758
npN8MRyT6fs680758 SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql;
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
'ak'zN�Y"v$yX680758
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL> set echo off
`f D2kX}:m.C680758 ITPUB个人空间&K)tM8F^5U"B
接下来就可以手工将plustrace角色授予需要的角色或用户,或者直接将其授予public角色,这样所有public角色的用户都有使用autotrace功能的权限了。
M^-v,F2n"EO K680758
}tW0?+n`&F Ta680758 SQL> grant plustrace to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> set autotrace trace
{+Um }3`�G q680758 ITPUB个人空间 hN)f2F#[$x(e!Vx.F
三、关于autotrace功能内部操作ITPUB个人空间 H s^*w_U
ITPUB个人空间 x+^0z|&Qa1S
默认的,在启用autotrace功能时Oracle另启用一个session,专门用于收集和输出统计信息。
8`xz:_X7m4^TF680758 ITPUB个人空间+kOjfF'f V,?2K-x
SQL> set autotrace off
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
146 906 SYS
159 3 SYS
......
oz2S9v7z'N680758
SQL> set autotrace on exp
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
129 6 SYSMAN
135 4 DBSNMP
136 356 SYSMAN
143 45 SYSMAN
145 10 SYSMAN
146 906 SYS
149 38 DBSNMP
159 3 SYS
8 rows selected.
*k"pfRs$Ft9j8C680758 ITPUB个人空间 d6h%g| u1I/pz
Execution Plan
----------------------------------------------------------
Plan hash value: 3733760267
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 108 | 0 (0)| 00:00:01 |
|* 2 | FIXED TABLE FULL | X$KSUSE | 1 | 95 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("S"."KSUUDLNA" IS NOT NULL AND "S"."INST_ID"=USERENV('INSTANCE') AND
BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
3 - filter("S"."KSUSEOPC"="E"."INDX")
6? m&r5z }2M680758 ITPUB个人空间VXgb2i~"I q$WU
而收集统计信息的时候,则多出一个session。ITPUB个人空间y s:~'f%N!t/_
SQL> set autotrace on stat
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
126 692 SYS
129 6 SYSMAN
135 4 DBSNMP
136 356 SYSMAN
143 45 SYSMAN
145 10 SYSMAN
146 906 SYS
149 38 DBSNMP
159 3 SYS ITPUB个人空间,K,`0IG|7/g`2H
9 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
?d r7G7?"GJ3g680758
"S7k;D x8x@X5D680758 SQL> select a.sid,a.serial#,a.username,b.pid,b.spid
2 from v$session a,v$process b
3 where a.paddr=b.addr and a.username is not null;
SID SERIAL# USERNAME PID SPID
---------- ---------- ------------------------------ ---------- ------------
159 3 SYS 15 4261
146 906 SYS 18 6977
126 692 SYS 18 6977
......
9 rows selected.
$ ps -ef|grep -v grep|grep 6977
oracle 6977 6976 0 21:22 ? 00:00:00 oraclerandy (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) ITPUB个人空间*a*/.~de6b
%Q)F:UNrB680758 四、关于DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSIONITPUB个人空间?'j8P MhQ4[|L^
ITPUB个人空间8a1_QY:h D XU |
其实我们更多的时候需要跟踪的其他用户的进程,而很多这样的用户可能没有被授予或者不允许授予plustrace角色。这时可以使用 DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION过程来实现对进程的跟踪,该过程需要提供用户进程的sid和serial#。ITPUB个人空间O*M7? xK4D�z
ITPUB个人空间!vMB!tM0C xw)l1_
SQL> select sid,serial# from v$session where username ='HR';
SID SERIAL#
z#I&k/xJ-M-_680758 ---------- ----------
159 5
SQL> exec dbms_system.SET_SQL_TRACE_IN_SESSION(159,5,true);
PL/SQL procedure successfully completed.
SQL> select a.sid,a.serial#,a.username,b.pid,b.spid
2 from v$session a,v$process b
3 where a.paddr=b.addr and a.username='HR';
SID SERIAL# USERNAME PID SPID
---------- ---------- ------------------------------ ---------- ------------
159 5 HR 15 9080
xG*u(T~ q T1AdE680758 ITPUB个人空间"]{4qZ s?*m
回到用户HR的连接中执行SQL 语句,然后使用tkprof工具格式化USER_DUMP_FILE目录中的跟踪文件。
|:uaqD&IL680758
X'F%jE5@1W680758 $ tkprof randy_ora_9080.trc 9080.txt
$ more 9080.txt
......
select *
from
jobs
$RKr4j-T680758 ITPUB个人空间{#d@!nLz
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.02 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.04 6 18 0 38
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.02 0.06 6 18 0 38
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
19 TABLE ACCESS FULL JOBS (cr=9 pr=6 pw=0 time=42771 us)
......
1^,w/zI u7s680758 ITPUB个人空间hi3l r dd�y B3d!c
从跟踪文件中,可以发现很多关于Oracle在实现用户需求时的内部操作,深入研究确实会有很多的收获。不过这里我们关心的是上面摘录的部分,可以看到select * from jobs执行计划和相关的统计信息。
d:_M�L1w a680758 ITPUB个人空间o4O hLII)lipI7S*J9z
五、使用会话级10046事件ITPUB个人空间i/-{{'D4?u
,t }a#r#IX Q`4T8wV680758 除了上述的方法外,还可以使用会话级的10046事件来实现进程跟踪。
e _Q G `;j!S680758 ITPUB个人空间cP7|~;AjY [ h
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> alter session set events '10046 trace name context off';
6{wx-~0X X pv4@680758
8A!Q'R.@�R(yY680758 如果用户没有alter session的权限,可以使用DBMS_SYSTEM.SET_EV过程来实现对用户进程的跟踪。
(x9xY zu`680758 ITPUB个人空间l1pB#F,A*qT A$Dz,l!Js
SQL> desc DBMS_SYSTEM
......
PROCEDURE SET_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
......
几个参数分别是进程sid,serial#,event name,level,username。 ITPUB个人空间3uAU+_`;P0X:M
&b
Oracle SQL_TRACE使用小结
最新推荐文章于 2024-09-10 15:57:14 发布