这种方法它是事件基础框架的一部分，它有先天的优势能捕获包含在PL/SQL块中的SQL语句。下面显示了一个例子。
1.创建包的定义
SQL> create or replace package getcircarea as
2 function getcircarea(radius number)
3 return number;
4 end getcircarea;
5 /
Package created
2.创建包体
SQL> create or replace package body getcircarea as
2 function getcircarea (radius number) return number
3 is area number(8,2);
4 begin
5 select 3.142*radius*radius into area from dual;
6 return area;
7 end;
8 end getcircarea;
9 /
Package body created
3.调用过程
SQL> set serveroutput on size 100000;
SQL> declare
2 area number(8,2);
3 begin
4 area:= getcircarea.getcircarea(10);
5 dbms_output.put_line('Area is '||area);
6 end;
7 /
Area is 314.2
PL/SQL procedure successfully completed
4.查询PL/SQL中特定语句的sql_id
SQL> select sql_text, sql_id from v$sqlarea where sql_text like '%3.142%';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
select sql_text, sql_id from v$sqlarea where sql_text like '%3.142%'
0wu4zyqk0jkg2
SELECT 3.142*:B1 *:B1 FROM DUAL
9rjmrhbjuasav
5.清空shared_pool(备注：必须flush shared pool，否则trace不会生成)
SQL> alter system flush shared_pool;
System altered.
6.设置跟踪会话标识符
SQL> alter session set tracefile_identifier='PLSQL';
Session altered.
--也可以oradebug
--oradebug event trace[rdbms.SQL_Optimizer.*][sql:9rjmrhbjuasav]
7.开启跟踪
SQL> alter session set events 'trace[rdbms.SQL_Optimizer.*][sql:9rjmrhbjuasav]';
Session altered.
8.再次调用 getcircarea.getcircarea过程
SQL> set serveroutput on size 100000;
SQL> declare
2 area number(8,2);
3 begin
4 area:= getcircarea.getcircarea(10);
5 dbms_output.put_line('Area is '||area);
6 end;
7 /
Area is 314.2
PL/SQL procedure successfully completed.
9.关闭跟踪
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*]off';
Session altered.
10. 查找生成跟踪文件的位置
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/Oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_12089_PLSQL.trc
11.查看跟踪文件
SQL> host cat /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_12089_PLSQL.trc
Trace file /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_12089_PLSQL.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/11.2.0/db
System name: Linux
Node name: jyrac1
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
Instance name: jycs
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 12089, image: oracle@jyrac1 (TNS V1-V3)
*** 2014-08-15 10:47:46.809
*** SESSION ID:(146.49829) 2014-08-15 10:47:46.809
*** CLIENT ID:() 2014-08-15 10:47:46.809
*** SERVICE NAME:(SYS$USERS) 2014-08-15 10:47:46.809
*** MODULE NAME:(sqlplus@jyrac1 (TNS V1-V3)) 2014-08-15 10:47:46.809
*** ACTION NAME:() 2014-08-15 10:47:46.809
Registered qb: SEL$1 0xb0a521f0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=116 hint_alias="DUAL"@"SEL$1"
SPM: statement not found in SMB
SPM: statement not a candidate for auto-capture
**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=9rjmrhbjuasav) -----
SELECT 3.142*:B1 *:B1 FROM DUAL
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x7f525220 5 package body SYS.GETCIRCAREA
0x7f553b20 4 anonymous block
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
SLP - select list pruning
DP - distinct placement
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
128: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
is_recur_flags = 128
Bug Fix Control Environment
..........省略
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 9rjmrhbjuasav.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE bypassed: no order by to eliminate.
CVM: Considering view merge in query block SEL$1 (#0)
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 9rjmrhbjuasav.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
??
apadrv-start sqlid=11224790525316260187
:
call(in-use=1136, alloc=16344), compile(in-use=53272, alloc=58176), execution(in-use=3336, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=2b8cb0aa3880 bln=22 avl=02 flg=05
value=10
Bind#1
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=2b8cb0aa3898 bln=22 avl=02 flg=01
value=10
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT 3.142*:B1*:B2 "3.142*:B1*:B1" FROM "SYS"."DUAL" "DUAL"
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1136, alloc=16344), compile(in-use=54200, alloc=58176), execution(in-use=3512, alloc=4032)
kkoqbc-subheap (create addr=0x2b8cb0a5b698)
****************
QUERY BLOCK TEXT
****************
SELECT 3.142*:B1 *:B1 FROM DUAL
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=116 hint_alias="DUAL"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 2657 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: DUAL Alias: DUAL
#Rows: 1 #Blks: 1 AvgRowLen: 2.00
Access path analysis for DUAL
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for DUAL[DUAL]
Table: DUAL Alias: DUAL
Card: Original: 1.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 7271
Resp_io: 2.00 Resp_cpu: 7271
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: DUAL[DUAL]#0
***********************
Best so far: Table#: 0 cost: 2.0002 card: 1.0000 bytes: 0
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 2.0002 Degree: 1 Card: 1.0000 Bytes: 0
Resc: 2.0002 Resc_io: 2.0000 Resc_cpu: 7271
Resp: 2.0002 Resp_io: 2.0000 Resc_cpu: 7271
kkoqbc-subheap (delete addr=0x2b8cb0a5b698, in-use=11112, alloc=14296)
kkoqbc-end:
:
call(in-use=6400, alloc=32712), compile(in-use=54760, alloc=58176), execution(in-use=3512, alloc=4032)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=6400, alloc=32712), compile(in-use=55672, alloc=58176), execution(in-use=3512, alloc=4032)
Starting SQL statement dump
user_id=0 user_name=SYS module=sqlplus@jyrac1 (TNS V1-V3) action=
sql_id=9rjmrhbjuasav plan_hash_value=1388734953 problem_type=3
----- Current SQL Statement for this session (sql_id=9rjmrhbjuasav) -----
SELECT 3.142*:B1 *:B1 FROM DUAL
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x7f525220 5 package body SYS.GETCIRCAREA
0x7f553b20 4 anonymous block
sql_text_length=32
sql=SELECT 3.142*:B1 *:B1 FROM DUAL
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | FAST DUAL | | 1 | | 2 | 00:00:01 |
------------------------------------+-----------------------------------+
Predicate Information:
----------------------
Content of other_xml column
===========================
db_version : 11.2.0.1
parse_schema : SYS
plan_hash : 1388734953
plan_hash_2 : 308129442
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
END_OUTLINE_DATA
*/
............省略
Query Block Registry:
SEL$1 0xb0a521f0 (PARSER) [FINAL]
:
call(in-use=8672, alloc=32712), compile(in-use=80744, alloc=144008), execution(in-use=4712, alloc=8088)
End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
