Re: questions about cbo explain plan

From: lsllcm <lsllcm_at_gmail.com>
Date: 29 Nov 2005 17:58:09 -0800
Message-ID: <1133315889.038925.79830@z14g2000cwz.googlegroups.com>

Thanks for your answer, it is very good,

Yes, It is not the run text. I just replace the table and column name.

I add the hint nest loop and re-run the sql, could you please give some suggestion to tune the sql?

---

PARSING IN CURSOR #1 len=1143 dep=0 uid=42 oct=3 lid=42 tim=4207437406 hv=2068472713 ad='23d323c4'
SELECT /*+ USE_NL(T1 T2) */ * FROM (SELECT DISTINCT     T1.ID1, T1.ID2, T1.ID3
    T2.COL1, T2.COL2
  FROM T1, T2

  WHERE  T1.ID1 = T2.ID1(+)
   AND   T1.ID2 = T2.ID2(+)
   AND   T1.ID3 = T2.ID3(+)

  ORDER BY T1.CREATED_DATE DESC, T1.ID2 ASC, T1.ID3 ASC   )
  WHERE ROWNUM < 101
END OF STMT
PARSE #1:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=4207437401 BINDS #1:

EXEC #1:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=4207437779
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
WAIT #1: nam='direct path write' ela= 10585 p1=201 p2=86921 p3=7
WAIT #1: nam='direct path write' ela= 7211 p1=201 p2=86935 p3=7
WAIT #1: nam='direct path write' ela= 7357 p1=201 p2=86949 p3=7
WAIT #1: nam='direct path write' ela= 7206 p1=201 p2=86963 p3=7
WAIT #1: nam='direct path write' ela= 5436 p1=201 p2=86977 p3=7
WAIT #1: nam='direct path write' ela= 9680 p1=201 p2=86991 p3=7
WAIT #1: nam='direct path write' ela= 7101 p1=201 p2=87005 p3=7
WAIT #1: nam='direct path write' ela= 7303 p1=201 p2=87019 p3=7
WAIT #1: nam='direct path write' ela= 5527 p1=201 p2=87033 p3=7
WAIT #1: nam='direct path write' ela= 7478 p1=201 p2=87049 p3=7
WAIT #1: nam='direct path write' ela= 9629 p1=201 p2=87063 p3=7
WAIT #1: nam='direct path write' ela= 7276 p1=201 p2=87077 p3=7
WAIT #1: nam='direct path write' ela= 5191 p1=201 p2=87091 p3=7
WAIT #1: nam='direct path write' ela= 7289 p1=201 p2=87105 p3=7
WAIT #1: nam='direct path write' ela= 9436 p1=201 p2=87119 p3=7
WAIT #1: nam='direct path write' ela= 7359 p1=201 p2=87133 p3=7
.....................
WAIT #1: nam='direct path read' ela= 374 p1=201 p2=91001 p3=7
WAIT #1: nam='direct path read' ela= 4194 p1=201 p2=86891 p3=7
WAIT #1: nam='direct path read' ela= 7802 p1=201 p2=87319 p3=7
WAIT #1: nam='direct path read' ela= 1581 p1=201 p2=87326 p3=7
WAIT #1: nam='direct path read' ela= 7355 p1=201 p2=87552 p3=7
WAIT #1: nam='direct path read' ela= 5179 p1=201 p2=87703 p3=7
WAIT #1: nam='direct path read' ela= 7242 p1=201 p2=87922 p3=7
WAIT #1: nam='direct path read' ela= 6909 p1=201 p2=90838 p3=7

WAIT #1: nam='direct path read' ela= 384 p1=201 p2=90845 p3=7 WAIT #1: nam='direct path read' ela= 3289 p1=201 p2=86835 p3=7

......................

FETCH
#1:c=1656250,e=8779383,p=4375,cr=57992,cu=0,mis=0,r=1,dep=0,og=2,tim=4216217225 WAIT #1: nam='SQL*Net message from client' ela= 1386 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 FETCH #1:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4216218888 WAIT #1: nam='SQL*Net message from client' ela= 81304 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 FETCH #1:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4216300341 WAIT #1: nam='SQL*Net message from client' ela= 84727 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 FETCH #1:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4216385222 WAIT #1: nam='SQL*Net message from client' ela= 81071 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 FETCH #1:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4216466452 WAIT #1: nam='SQL*Net message from client' ela= 80571 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 FETCH #1:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4216547188 WAIT #1: nam='SQL*Net message from client' ela= 80571 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 FETCH #1:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4216627898 WAIT #1: nam='SQL*Net message from client' ela= 79895 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 FETCH #1:c=0,e=136,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=2,tim=4216708009 *** 2005-11-30 09:40:28.624
WAIT #1: nam='SQL*Net message from client' ela= 57922214 p1=1413697536 p2=1 p3=0

STAT #1 id=1 cnt=100 pid=0 pos=1 obj=0 op='COUNT STOPKEY '
STAT #1 id=2 cnt=100 pid=1 pos=1 obj=0 op='VIEW  '
STAT #1 id=3 cnt=100 pid=2 pos=1 obj=0 op='SORT UNIQUE STOPKEY '
STAT #1 id=4 cnt=291462 pid=3 pos=1 obj=0 op='HASH JOIN OUTER '
STAT #1 id=5 cnt=289518 pid=4 pos=1 obj=29837 op='TABLE ACCESS BY INDEX

ROWID T1 '
STAT #1 id=6 cnt=290076 pid=5 pos=1 obj=29838 op='INDEX RANGE SCAN T1_ALTID_IX '
STAT #1 id=7 cnt=39267 pid=4 pos=2 obj=29848 op='TABLE ACCESS BY INDEX ROWID T2 '
STAT #1 id=8 cnt=39903 pid=7 pos=1 obj=29850 op='INDEX RANGE SCAN T2_PK ' Received on Tue Nov 29 2005 - 19:58:09 CST