10053 level 1 查看语句CBO算法

先清理下软解析，才能打出10053的trace文件。

alter system flush shared_pool;
alter session set tracefile_identifier='test';
SELECT      d.VALUE
         || '/'
         || LOWER (RTRIM (i.INSTANCE, CHR (0)))
         || '_ora_'
         || p.spid
         || '.trc'
            AS "trace_file_name"
  FROM   (SELECT   p.spid
            FROM   v$mystat m, v$session s, v$process p
           WHERE   m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
         (SELECT   t.INSTANCE
            FROM   v$thread t, v$parameter v
           WHERE   v.NAME = 'thread'
                   AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
         (SELECT   VALUE
            FROM   v$parameter
           WHERE   NAME = 'user_dump_dest') d;

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

with t as(select 2*level+1 a from dual connect by level<50), 
p as (select a from t where a>10 
minus 
select t1.a*t2.a from t t1,t t2 ) 
select p.a,p1.a,p2.a from p,p p1,p p2 
where p.a<p1.a and p1.a<p2.a 
and (p.a+p1.a)/2 in (select a from p) 
and (p1.a+p2.a)/2 in (select a from p) 
and (p.a+p2.a)/2 in (select a from p) 
and (p.a+p1.a+p2.a)/3 in (select a from p) 
;

ALTER SESSION SET EVENTS '10053 trace name context off';

tkprof无法整理10053的trace，只能硬读了。

硬读了一段，看到了解决的方案，问题出在子查询是否嵌套上了...

with t as(select 2*level+1 a from dual connect by level<50),
        p as (select a from t where a>10
        minus
        select t1.a*t2.a from t t1,t t2 )
        select p.a,p1.a,p2.a from p,p p1,p p2
       where p.a<p1.a and p1.a<p2.a
        and (p.a+p1.a)/2 in (select/*+no_unnest*/ a from p)
        and (p1.a+p2.a)/2 in (select/*+no_unnest*/  a from p)
        and (p.a+p2.a)/2 in (select/*+no_unnest*/  a from p)
     and (p.a+p1.a+p2.a)/3 in (select/*+no_unnest*/  a from p);

这样快多了