EVENT: 10060 dump predicates in optimizer (kko)

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.

Usage:
        - Available in 7.1.3 onwards when using CBO
        - NOTE: This does NOT do anything under RULE

        - After 7.1.3 you must create a special table called
          "kkoipt_table" before you set the event. This table should be
          in the schema of the person running under event 10060.
    
            create table kkoipt_table (
                    c1 integer, c2 varchar2(80)
            );            

        - To enable tracing:
            ALTER session set events '10060 trace name context forever';

        - Run the statement to be traced

        - Set formatting:
          set pages 999
          column c1 format 990
          column c2 format a75

        - select * from KKOIPT_TABLE;

        - Best used in conjunction with <Event:10053> trace and an
          execution plan.

KKOIPT_TABLE.C2 output information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
fptconst                      - Folding constants
fptrnum                       - Remove rownum predicates
fptwhr                        - Remove all where predicates except remaining
                                rownum predicates
frofkks (rowid lookup)        - Rowid Lookup
frofkks[i] (and-equal lookup) - Start Key (And-Equal)
frofkke[i] (and-equal lookup) - End Key   (And-Equal)
froiand                       - Index only predicate
frofkksm[i] (sort-merge)      - Sort-Merg Key
frosand (sort-merge)          - Sort-Merge Predicates
frojand (sort-merge)          - Join Predicates
frofkks[i] (index start key)  - Index Start Key
frofkke[i] (index stop key)   - Index End Key
frofand (hash part)           - Table Predicate (Hash)
froiand (index only filter)   - Index Only Predicate
frofand                       - Table Predicate
froutand                      - Outer Join Predicates

Example:
~~~~~~~
select a.* from memp a, memp b
where a.sal > 5050 and rownum < 4 and 1=1
  and a.empno (+) = b.empno;

Output: (from kkoipt_table)

         1 fptrnum
         2  4>ROWNUM
         3 Table:
         4 MEMP
         5 frofand
         6  "A"."SAL">5050
         7 Table:
         8 MEMP
         9 frofand
        10  "A"."EMPNO"="B"."EMPNO"

© 2006 – 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. EVENT:10218 dump uba of applied undo
2. Siebel CRM with Oracle® Cost-Based Optimizer (CBO)
3. EVENT:10210 check data block integrity
4. EVENT:10212 check cluster integrity
5. EVENT:10061 disable SMON from cleaning temp segment
6. Setting an Oracle event:The structure of the trace syntax
7. dump logfile prints minimal info for KTSFUNLK undo record
8. EVENT:10226 trace CR applications of undo for data
9. EVENT:10228 trace application of redo by kcocbk
10. Init.ora Parameter "SORT_MULTIBLOCK_READ_COUNT"