10046 level 12 跟踪一个秀逗的SQL语句

执行语句语句:

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 '10046 trace name context forever, level 12';

--秀逗的语句
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 '10046 trace name context off'; 

tkprof整理：

tkprof.exe ora11gr2_ora_29306_test.trc sys=no
tkprof.exe ora11gr2_ora_29306_test.trc sys=no explain=test/test@10.0.1.1/ora11gr2

查看实际执行计划：

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TEMP TABLE TRANSFORMATION  (cr=13002475 pr=2 pw=2 time=0 us)
      0   LOAD AS SELECT  (cr=0 pr=0 pw=1 time=0 us)
     49    CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=144 us)
      1     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
      0   LOAD AS SELECT  (cr=11 pr=1 pw=1 time=0 us)
     21    MINUS  (cr=11 pr=1 pw=0 time=40 us)
     45     SORT UNIQUE (cr=5 pr=1 pw=0 time=0 us cost=3 size=13 card=1)
     45      VIEW  (cr=5 pr=1 pw=0 time=0 us cost=2 size=13 card=1)
     49       TABLE ACCESS FULL SYS_TEMP_0FD9D666D_5931596 (cr=5 pr=1 pw=0 time=0 us cost=2 size=13 card=1)
   1015     SORT UNIQUE (cr=6 pr=0 pw=0 time=253 us cost=5 size=26 card=1)
   2401      MERGE JOIN CARTESIAN (cr=6 pr=0 pw=0 time=1136 us cost=4 size=26 card=1)
     49       VIEW  (cr=3 pr=0 pw=0 time=48 us cost=2 size=13 card=1)
     49        TABLE ACCESS FULL SYS_TEMP_0FD9D666D_5931596 (cr=3 pr=0 pw=0 time=48 us cost=2 size=13 card=1)
   2401       BUFFER SORT (cr=3 pr=0 pw=0 time=463 us cost=4 size=13 card=1)
     49        VIEW  (cr=3 pr=0 pw=0 time=48 us cost=2 size=13 card=1)
     49         TABLE ACCESS FULL SYS_TEMP_0FD9D666D_5931596 (cr=3 pr=0 pw=0 time=48 us cost=2 size=13 card=1)
      1   NESTED LOOPS  (cr=13002464 pr=1 pw=0 time=0 us cost=14 size=91 card=1)
 250047    NESTED LOOPS  (cr=12252322 pr=1 pw=0 time=43082004 us cost=12 size=78 card=1)
4084101     MERGE JOIN CARTESIAN (cr=18 pr=1 pw=0 time=2890280 us cost=10 size=65 card=1)
 194481      MERGE JOIN CARTESIAN (cr=15 pr=1 pw=0 time=145220 us cost=8 size=52 card=1)
   9261       MERGE JOIN CARTESIAN (cr=12 pr=1 pw=0 time=6215 us cost=6 size=39 card=1)
    441        MERGE JOIN CARTESIAN (cr=9 pr=1 pw=0 time=330 us cost=4 size=26 card=1)
     21         VIEW  (cr=6 pr=1 pw=0 time=60 us cost=2 size=13 card=1)
     21          TABLE ACCESS FULL SYS_TEMP_0FD9D666E_5931596 (cr=6 pr=1 pw=0 time=40 us cost=2 size=13 card=1)
    441         BUFFER SORT (cr=3 pr=0 pw=0 time=210 us cost=4 size=13 card=1)
     21          VIEW  (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
     21           TABLE ACCESS FULL SYS_TEMP_0FD9D666E_5931596 (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
   9261        BUFFER SORT (cr=3 pr=0 pw=0 time=2278 us cost=4 size=13 card=1)
     21         VIEW  (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
     21          TABLE ACCESS FULL SYS_TEMP_0FD9D666E_5931596 (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
 194481       BUFFER SORT (cr=3 pr=0 pw=0 time=58529 us cost=6 size=13 card=1)
     21        VIEW  (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
     21         TABLE ACCESS FULL SYS_TEMP_0FD9D666E_5931596 (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
4084101      BUFFER SORT (cr=3 pr=0 pw=0 time=1150033 us cost=8 size=13 card=1)
     21       VIEW  (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
     21        TABLE ACCESS FULL SYS_TEMP_0FD9D666E_5931596 (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
 250047     VIEW  (cr=12252304 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
85766121      TABLE ACCESS FULL SYS_TEMP_0FD9D666E_5931596 (cr=12252304 pr=0 pw=0 time=12812801 us cost=2 size=13 card=1)
      1    VIEW  (cr=750142 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
5250987     TABLE ACCESS FULL SYS_TEMP_0FD9D666E_5931596 (cr=750142 pr=0 pw=0 time=786040 us cost=2 size=13 card=1)

oracle秀逗了，搞出个8500W的临时表....