Re: optimizer_max_permutations not valid anymore in 10g

My comments probably won't help you (24 table join), but they may make others feel better.

I've noticed that starting with the 9i optimizer, it seems like Oracle has gotten much better at picking the driving table for queries. Being able to set the # of permutations hasn't been very useful to me.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

"Dikkie Dik" <dwesteneng_at_mail.com> wrote in message news:<c60oen$6ne2j$1_at_ID-154318.news.uni-berlin.de>...
> 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.ttdsls401570 a,baandb.ttccom145570 b,baandb.ttdisa001570 c,
> 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
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> Parse 1 0.00 0.01 0 0 0
> 0
> Execute 9 0.00 59.38 0 0 0
> 0
> Fetch 9 0.00 0.01 11 450 0
> 9
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> total 19 0.00 59.41 11 450 0
> 9
>
> 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
> ----------------------------------------
> 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.00
> 0.00
> ****************************************************************************
> ****
Received on Fri Apr 23 2004 - 00:29:50 CDT