Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> optimizer_max_permutations not valid anymore in 10g

optimizer_max_permutations not valid anymore in 10g

From: Dikkie Dik <dwesteneng_at_mail.com>
Date: Mon, 19 Apr 2004 16:40:16 +0200
Message-ID: <c60oen$6ne2j$1@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 Mon Apr 19 2004 - 09:40:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US