| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> optimizer_max_permutations not valid anymore in 10g
The optimizer_max_permutations was made for us, but in 10g it is not there
anymore. Who can tell if it has a successor?
Our query generator trows in a 24 table join, (almost) al of them outer joins. ora_prefetch has been disabled, so most likely the exec time is the (delayed) parse. And no, I can't change the query generator.
Thanks in advance,
Dick Westeneng
For those who are interested the tkprof output:
SELECT /*+ FIRST_ROWS INDEX(a ttdsls401570$idx1) */ a.t$acti,a.t$akcd,
a.t$amld,a.t$amod,a.t$bind,a.t$bkyn,a.t$bqco,a.t$bqua,a.t$ccty,a.t$cdis$1, a.t$cdis$2,a.t$cdis$3,a.t$cdis$4,a.t$cdis$5,a.t$cfrw,a.t$chan,a.t$citg, a.t$citt,a.t$clot,a.t$clyn,a.t$cmnf,a.t$comq,a.t$coop$1,a.t$coop$2, a.t$coop$3,a.t$copr$1,a.t$copr$2,a.t$copr$3,a.t$corn,a.t$corp,a.t$cors, a.t$cosn,a.t$cpcl,a.t$cpcp,a.t$cpln,a.t$cprj,a.t$cpva,a.t$crcd,a.t$crte, a.t$csgs,a.t$ctcd,a.t$ctrj,a.t$cups,a.t$cuqs,a.t$cvat,a.t$cvps,a.t$cvqs, a.t$cwar,a.t$cwoc,a.t$damt,a.t$ddta,a.t$disc$1,a.t$disc$2,a.t$disc$3,
a.t$disc$4,a.t$disc$5,a.t$dldt,a.t$dmde$1,a.t$dmde$2,a.t$dmde$3,a.t$dmde$4,
a.t$dmde$5,a.t$dmse$1,a.t$dmse$2,a.t$dmse$3,a.t$dmse$4,a.t$dmse$5, a.t$dmth$1,a.t$dmth$2,a.t$dmth$3,a.t$dmth$4,a.t$dmth$5,a.t$dmty$1, a.t$dmty$2,a.t$dmty$3,a.t$dmty$4,a.t$dmty$5,a.t$dqua,a.t$drct,a.t$dtrm, a.t$elgb,a.t$fcop$1,a.t$fcop$2,a.t$fcop$3,a.t$invd,a.t$invn,a.t$isss, a.t$item,a.t$ldam$1,a.t$ldam$2,a.t$ldam$3,a.t$ldam$4,a.t$ldam$5,a.t$leng, a.t$lsel,a.t$lseq,a.t$oamt,a.t$odat,a.t$ofbp,a.t$oltp,a.t$opol,a.t$opri, a.t$oqua,a.t$orno,a.t$pmde,a.t$pmse,a.t$pmsk,a.t$pono,a.t$prdt,a.t$pric, a.t$pror,a.t$ratd,a.t$ratf$1,a.t$ratf$2,a.t$ratf$3,a.t$rats$1,a.t$rats$2, a.t$rats$3,a.t$ratt,a.t$rdta,a.t$revi,a.t$scmp,a.t$scon,a.t$sdsc,a.t$shpm, a.t$sqnb,a.t$stad,a.t$stbp,a.t$stcn,a.t$stdc,a.t$stpr,a.t$thic,a.t$ttyp, a.t$txta,a.t$vcop$1,a.t$vcop$2,a.t$vcop$3,a.t$widt,d.t$acci,d.t$acom, d.t$cmgp,d.t$cpgs,d.t$csgs,d.t$cups,d.t$cuqs,d.t$cvat,d.t$cwar,d.t$ddfq, d.t$item,d.t$lmsp,d.t$ltsp,d.t$mnqa,d.t$mnqo,d.t$mnra,d.t$prir,d.t$pris, d.t$rbgp,d.t$rcom,d.t$retw,d.t$seak,d.t$txts,d.t$umsp,e.t$bpst,e.t$cadr, e.t$cbps,e.t$cbtp,e.t$ccal,e.t$ccnt,e.t$cfrw,e.t$clan,e.t$cwar,e.t$endt, e.t$ofbp,e.t$seak,e.t$stbp,e.t$stdt,e.t$txta,f.t$ackx,f.t$bppr,f.t$bpst, f.t$bpus,f.t$cadr,f.t$cbps,f.t$cbrn,f.t$cbtp,f.t$ccal,f.t$ccnt,f.t$cdec, f.t$chak,f.t$chan,f.t$clan,f.t$clgr,f.t$cofc,f.t$cpls,f.t$creg,f.t$crep, f.t$crtc,f.t$cspr,f.t$endt,f.t$itbp,f.t$lmsp,f.t$mcfr,f.t$odis,f.t$ofbp, f.t$osno,f.t$osrp,f.t$pldd,f.t$prio,f.t$rdec,f.t$scon,f.t$scqq,f.t$seak, f.t$sotp,f.t$stbp,f.t$stdt,f.t$txta,f.t$umsp,g.t$dsca,h.t$dsca,i.t$dsca, j.t$desc,k.t$dsca,l.t$dsca,m.t$dsca,n.t$dsca,o.t$nama,p.t$dsca,p.t$bpid, p.t$cadr,p.t$comp,p.t$cprj,p.t$cwar,p.t$nwrh,p.t$typw,q.t$ccur,q.t$agen, q.t$akcd,q.t$bkyn,q.t$bppr,q.t$bptx,q.t$cbrn,q.t$ccrs,q.t$ccty,q.t$cdec, q.t$cfcg,q.t$cfrw,q.t$clyn,q.t$cofc,q.t$corg,q.t$corn,q.t$cosn,q.t$cpay, q.t$cpls,q.t$crcd,q.t$creg,q.t$crep,q.t$crte,q.t$ctcd,q.t$ctrj,q.t$cvyn, q.t$cwar,q.t$ddat,q.t$fcrt,q.t$futo,q.t$itad,q.t$itbp,q.t$itcn,q.t$odat, q.t$odis,q.t$odno,q.t$odty,q.t$ofad,q.t$ofbp,q.t$ofcn,q.t$orno,q.t$osrp, q.t$paym,q.t$pfad,q.t$pfbp,q.t$pfcn,q.t$pldd,q.t$prdt,q.t$prno,q.t$ratd, q.t$ratf$1,q.t$ratf$2,q.t$ratf$3,q.t$rats$1,q.t$rats$2,q.t$rats$3,q.t$ratt, q.t$rcmp,q.t$refa,q.t$refb,q.t$retr,q.t$rtyp,q.t$sbim,q.t$scon,q.t$sotp, q.t$stad,q.t$stbp,q.t$stcn,q.t$txta,q.t$txtb,r.t$dsca,r.t$cprj,r.t$dscb, r.t$plnk,r.t$ppus,r.t$seab,r.t$seak,r.t$txta,s.t$cofc,s.t$cwar,s.t$cwoc, s.t$dsca,s.t$ngrl,s.t$ngsc,s.t$ngso,s.t$ngsq,s.t$serl,s.t$sesc,s.t$seso, s.t$sesq,s.t$sess,t.t$apur,t.t$blcs,t.t$cnsi,t.t$cnsr,t.t$coun,t.t$crdc, t.t$dsca,t.t$exmo,t.t$gmrc,t.t$ngsc,t.t$ngso,t.t$ngsq,t.t$pmsk,t.t$proc, t.t$reto,t.t$sesc,t.t$seso,t.t$sesq,t.t$sotp,t.t$sund,t.t$upls,t.t$upsh, t.t$wrhp,u.t$cuni,u.t$ccde,u.t$cean,u.t$citg,u.t$cmnf,u.t$cntr,u.t$cont, u.t$cpcl,u.t$cpcp,u.t$cpln,u.t$cprj,u.t$cpva,u.t$csel,u.t$csig,u.t$ctyo, u.t$ctyp,u.t$cwar,u.t$cwun,u.t$dfit,u.t$dsca,u.t$dscb,u.t$dscc,u.t$dscd, u.t$eitm,u.t$item,u.t$itmt,u.t$kitm,u.t$kltc,u.t$obid,u.t$repl,u.t$seab, u.t$seak,u.t$stoi,u.t$txta,u.t$umer,u.t$uset,u.t$wght,v.t$surn,w.t$nama, w.t$bpid,w.t$bptx,w.t$cadr,w.t$ccnt,w.t$ccur,w.t$cint,w.t$clan,w.t$cmid, w.t$ctit,w.t$edyn,w.t$endt,w.t$fovn,w.t$inrl,w.t$iscn,w.t$lgid,w.t$lvdt,w.t$prbp,w.t$prst,w.t$seak,w.t$sndr,w.t$stdt,w.t$txta,x.t$dsca,x.t$cadr, x.t$ccal,x.t$city,x.t$comp,x.t$cwoc,x.t$typd,b.t$ccnt FROM
baandb.ttdisa001570 d,baandb.ttccom111570 e,baandb.ttccom110570 f, baandb.ttdsls098570 g,baandb.ttdsls097570 h,baandb.ttdsls414570 i, baandb.ttdsls054570 j,baandb.ttcmcs066570 k,baandb.ttcmcs036570 l, baandb.ttcmcs004570 m,baandb.ttcmcs080570 n,baandb.ttccom130570 o, baandb.ttcmcs003570 p,baandb.ttdsls400570 q,baandb.ttcmcs052570 r, baandb.ttdsls012570 s,baandb.ttdsls094570 t,baandb.ttcibd001570 u, baandb.ttccom140570 v,baandb.ttccom100570 w,baandb.ttcmcs065570 x WHERE q.t$orno (+) = a.t$orno AND p.t$cwar (+) = a.t$cwar AND f.t$ofbp (+) = a.t$ofbp AND w.t$bpid (+) = f.t$ofbp AND e.t$stbp (+) = a.t$stbp AND d.t$item (+) = a.t$item AND r.t$cprj (+) = a.t$cprj AND s.t$cofc (+) = q.t$cofc AND x.t$cwoc (+) = s.t$cofc AND c.t$item (+) = a.t$item AND u.t$item (+) = c.t$item AND t.t$sotp (+) = q.t$sotp AND o.t$cadr (+) = a.t$stad AND b.t$bpid (+) = a.t$stbp AND b.t$ccnt (+) = a.t$stcn AND v.t$ccnt (+) = b.t$ccnt AND n.t$cfrw (+) = a.t$cfrw AND m.t$crte (+) = a.t$crte AND l.t$ccty (+) = a.t$ccty AND l.t$cvat (+) = a.t$cvat AND k.t$chan (+) = a.t$chan AND j.t$akcd (+) = a.t$akcd AND i.t$orno (+) =a.t$orno AND i.t$cosn (+) = a.t$cosn AND h.t$crcd (+) = a.t$crcd AND g.t$ctcd (+) = a.t$ctcd AND a.t$orno = :1 AND (a.t$pono = :2 AND a.t$sqnb
:3) ORDER BY 104,108,126
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 63 (USER001)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: HINT: FIRST_ROWS
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 TABLE ACCESS
MODE: ANALYZED (BY INDEX ROWID) OF
'TTDSLS401570' (TABLE)
0 INDEX MODE:
ANALYZED (RANGE SCAN) OF
'TTDSLS401570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS
MODE: ANALYZED (BY INDEX ROWID) OF
'TTDSLS400570' (TABLE)
0 INDEX MODE:
ANALYZED (UNIQUE SCAN) OF
'TTDSLS400570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF
'TTDSLS414570' (TABLE)
0 INDEX MODE:
ANALYZED (UNIQUE SCAN) OF
'TTDSLS414570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF
'TTDSLS097570' (TABLE)
0 INDEX MODE:
ANALYZED (UNIQUE SCAN) OF
'TTDSLS097570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF 'TTDSLS098570'
(TABLE)
0 INDEX MODE: ANALYZED
(UNIQUE SCAN) OF 'TTDSLS098570$IDX1'
(INDEX (UNIQUE))
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF 'TTDSLS054570'
(TABLE)
0 INDEX MODE: ANALYZED
(UNIQUE SCAN) OF 'TTDSLS054570$IDX1' (INDEX
(UNIQUE))
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF 'TTCMCS066570'
(TABLE)
0 INDEX MODE: ANALYZED
(UNIQUE SCAN) OF 'TTCMCS066570$IDX1' (INDEX
(UNIQUE))
0 TABLE ACCESS MODE: ANALYZED
(BY INDEX ROWID) OF 'TTCMCS004570' (TABLE)
0 INDEX MODE: ANALYZED
(UNIQUE SCAN) OF 'TTCMCS004570$IDX1' (INDEX
(UNIQUE))
0 TABLE ACCESS MODE: ANALYZED
(BY INDEX ROWID) OF 'TTCMCS080570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE
SCAN) OF 'TTCMCS080570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY
INDEX ROWID) OF 'TTDSLS012570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE
SCAN) OF 'TTDSLS012570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY
INDEX ROWID) OF 'TTDSLS094570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE
SCAN) OF 'TTDSLS094570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY
INDEX ROWID) OF 'TTCMCS036570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN)
OF 'TTCMCS036570$IDX1' (INDEX (UNIQUE))
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCCOM145570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX
ROWID) OF 'TTCCOM140570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCCOM140570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX
ROWID) OF 'TTCCOM130570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCCOM130570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'TTCMCS003570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCMCS003570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'TTCMCS065570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCMCS065570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TTCMCS052570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCMCS052570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TTCCOM111570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCCOM111570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TTCCOM110570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCCOM110570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TTCCOM100570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCCOM100570$IDX1' (INDEX (UNIQUE))
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTDISA001570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TTCIBD001570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'TTCIBD001570$IDX1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TTDISA001570' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'TTDISA001570$IDX1'
(INDEX (UNIQUE))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
SQL*Net message to client 18 0.00 0.00 SQL*Net more data to client 2 0.00 0.00 SQL*Net message from client 18 0.00 0.03 db file sequential read 11 0.000.00
 |
 |