执行语句语句:
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的临时表....