댓글 쓰기 권한이 없습니다. 로그인 하시겠습니까?
일반
2012.02.21 15:00
10060 trace name context forever
댓글 0조회 수 27889
Event:10060
Text: dump predicates in optimizer (kko)
-------------------------------------------------------------------------------
Explanation:
This is a special EVENT code.
It should *NOT* be used unless explicitly requested by ST support.
This event can be useful in conjunction with an explain plan
to confirm which operations occur where in the execution plan.
It dumps the predicates into a special table.
최초 parsing시에만 발생됨
11gR2에서 Test함.
[참고] EVENT: 10060 "dump predicates in optimizer (kko)" (Doc ID 21168.1)
create table kkoipt_table ( c1 integer, c2 varchar2(80) );
ALTER session set events '10060 trace name context forever';
--ALTER session set events '10053 trace name context forever, level 1';
--<sampe 1>
--select i.table_name, o.object_name , o.object_id
--from user_objects o,
-- user_indexes i
--where o.object_type= 'INDEX'
--and i.table_name in ('TXT_SQL')
--and i.index_name = o.object_name
--and rownum <=10
--;
--<sample 2>
SELECT ZSOO.OPER_OBJ_CL_CD
,ZSOO.SVSET_REQ_OP_PRTY_RNK
,ZSOO.OPER_OP_TYP_CD
,ZSOO.OPER_STA_DTM
FROM ZOSS_SVSET_OPER_ORD ZSOO
,ZOSS_SVSET_REQ ZSR
,ZORD_WIRE_SVC_RCV SVC
,ZOSS_OPER_CO CO
,ZOSS_OPER_GRP GRP
WHERE ZSOO.OPER_ORD_FNSH_YN = 'N'
AND ZSOO.OPER_CO_ID = '0002030621'--1
AND ZSOO.OPER_GRP_ID LIKE '1002040575'||'%' --2
AND ZSOO.SVSET_REQ_NUM = ZSR.SVSET_REQ_NUM
AND SVC.SVC_MGMT_NUM = ZSR.SVC_MGMT_NUM
AND SVC.RCV_SEQ = ZSR.RCV_SEQ
AND ZSOO.OPER_CO_ID = CO.OPER_CO_ID
AND ZSOO.OPER_CO_ID = GRP.OPER_CO_ID
AND ZSOO.OPER_GRP_ID = GRP.OPER_GRP_ID
AND NVL(SVC.CO_CL_CD,'B') LIKE DECODE(SVC.CO_CL_CD,'A','%', DECODE(nvl('B','B') ,'T','T','%' ))
AND ROWNUM <=10
;
--ALTER session set events '10053 trace name context off';
ALTER session set events '10060 trace name context off';
select c1,c2||case when description is not null then ' - ['||description||']' end as description
from (
select
c1,
c2,
--lpad('['||c1||']',c1*2,' ')||' '||c2 ,
case
when c2 like 'fptconst%' then 'Folding constants'
when c2 like 'fptrnum%' then 'Remove rownum predicates'
when c2 like 'fptwhr%' then 'Remove all where predicates except remaining rownum predicates'
when c2 like 'frofkks (rowid lookup)%' then 'Rowid Lookup'
when c2 like 'frofkks[i] (and-equal lookup)%' then 'Start Key (And-Equal)'
when c2 like 'frofkke[i] (and-equal lookup)%' then 'End Key (And-Equal)'
when c2 like 'froiand%' then 'Index only predicate'
when c2 like 'frofkksm[i] (sort-merge)%' then 'Sort-Merg Key'
when c2 like 'frosand (sort-merge)%' then 'Sort-Merge Predicates'
when c2 like 'frojand (sort-merge)%' then 'Join Predicates'
when c2 like 'frofkks[i] (index start key)%' then 'Index Start Key'
when c2 like 'frofkke[i] (index stop key)%' then 'Index End Key'
when c2 like 'frofand (hash part)%' then 'Table Predicate (Hash)'
when c2 like 'froiand (index only filter)%' then 'Index Only Predicate'
when c2 like 'frofand%' then 'Table Predicate'
when c2 like 'froutand%' then 'Outer Join Predicates'
else null
end as description
from kkoipt_table )
;
--truncate table kkoipt_table ;
C1 DESCRIPTION
1 fptrnum [Remove rownum predicates]
2 ROWNUM<=10
3 Table:
4 ZOSS_OPER_CO
5 frofkks[i] (index start key) [Index Start Key]
6 "CO"."OPER_CO_ID"='0002030621'
7 frofkke[i] (index stop key) [Index End Key]
8 "CO"."OPER_CO_ID"='0002030621'
9 Table:
10 ZOSS_SVSET_OPER_ORD
11 frofkks[i] (index start key) [Index Start Key]
12 "ZSOO"."OPER_CO_ID"='0002030621'
13 frofkks[i] (index start key) [Index Start Key]
14 "ZSOO"."OPER_GRP_ID" LIKE '1002040575%'
15 frofkks[i] (index start key) [Index Start Key]
16 "ZSOO"."OPER_ORD_FNSH_YN"='N'
17 frofkke[i] (index stop key) [Index End Key]
18 "ZSOO"."OPER_CO_ID"='0002030621'
19 frofkke[i] (index stop key) [Index End Key]
20 "ZSOO"."OPER_GRP_ID" LIKE '1002040575%'
21 frofkke[i] (index stop key) [Index End Key]
22 "ZSOO"."OPER_ORD_FNSH_YN"='N'
23 froiand (index only filter) [Index only predicate]
24 "ZSOO"."OPER_ORD_FNSH_YN"='N' AND "ZSOO"."OPER_GRP_ID" LIKE '1002040575%'
25 Table:
26 ZOSS_OPER_GRP
27 frofkks[i] (index start key) [Index Start Key]
28 "GRP"."OPER_CO_ID"='0002030621'
29 frofkks[i] (index start key) [Index Start Key]
30 "ZSOO"."OPER_GRP_ID"="GRP"."OPER_GRP_ID"
31 frofkke[i] (index stop key) [Index End Key]
32 "GRP"."OPER_CO_ID"='0002030621'
33 frofkke[i] (index stop key) [Index End Key]
34 "ZSOO"."OPER_GRP_ID"="GRP"."OPER_GRP_ID"
35 froiand (index only filter) [Index only predicate]
36 "GRP"."OPER_GRP_ID" LIKE '1002040575%'
37 Table:
38 ZOSS_SVSET_REQ
39 frofkks[i] (index start key) [Index Start Key]
40 "ZSOO"."SVSET_REQ_NUM"="ZSR"."SVSET_REQ_NUM"
41 frofkke[i] (index stop key) [Index End Key]
42 "ZSOO"."SVSET_REQ_NUM"="ZSR"."SVSET_REQ_NUM"
43 Table:
44 ZORD_WIRE_SVC_RCV
45 frofkks[i] (index start key) [Index Start Key]
46 "SVC"."SVC_MGMT_NUM"="ZSR"."SVC_MGMT_NUM"
47 frofkks[i] (index start key) [Index Start Key]
48 "SVC"."RCV_SEQ"="ZSR"."RCV_SEQ"
49 frofkke[i] (index stop key) [Index End Key]
50 "SVC"."SVC_MGMT_NUM"="ZSR"."SVC_MGMT_NUM"
51 frofkke[i] (index stop key) [Index End Key]
52 "SVC"."RCV_SEQ"="ZSR"."RCV_SEQ"
53 frofand [Table Predicate]
54 NVL("SVC"."CO_CL_CD",'B') LIKE DECODE("SVC"."CO_CL_CD",'A','%','%')
| 번호 | 분류 | 제목 | 글쓴이 | 날짜 |
|---|---|---|---|---|
| 8 | RAC |
Testing for Changing hostname via "hostname" cmd on Uinux
 |
김형조 | 2013.09.12 |
| 7 | 일반 |
ps 에서 보여주는 priority의 의미
|
김형조 | 2013.09.12 |
| » | 일반 | 10060 trace name context forever | 김형조 | 2012.02.21 |
| 5 | 일반 | Reference Link - Active Data Guard | 김형조 | 2012.02.16 |
| 4 | 일반 | about SPA testing | 김형조 | 2012.02.16 |
| 3 | 일반 | DBA HIST desc all | 김형조 | 2012.02.16 |
| 2 | 일반 | event trace - method | 관리자 | 2012.02.16 |
| 1 | 일반 | Known schemas in Oracle | 관리자 | 2012.02.15 |
Copyright 2003-2012. All rights reserved. @XE