일반
2012.02.21 15:00

10060 trace name context forever

댓글 0조회 수 23875
?

단축키

이전 문서

다음 문서

+ - Up Down Comment Print
?

단축키

이전 문서

다음 문서

+ - Up Down Comment Print
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  secret 김형조 2013.09.12
7 일반 ps 에서 보여주는 priority의 의미  secret 김형조 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
Board Pagination ‹ Prev 1 Next ›
/ 1
Designed by hikaru100

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소

SketchBook5,스케치북5

SketchBook5,스케치북5

SketchBook5,스케치북5

SketchBook5,스케치북5

O R A C L E L A B 로그인 :)