Re: questions about cbo explain plan

"lsllcm" <lsllcm_at_gmail.com> wrote in message news:1133432154.730055.22140_at_o13g2000cwo.googlegroups.com...
> Hi Jonathan,
>
> I rewrite the sql. The following is 10046 trc file. Could you give some
> suggestion about this kind of sql?
>
> PARSING IN CURSOR #1 len=1144 dep=0 uid=42 oct=3 lid=42 tim=4338791544
> hv=2762625035 ad='23c8ee60'
> SELECT * FROM (SELECT /*+ USE_NL(B1PERMIT B3ADDRES) */ DISTINCT
> B1PERMIT.B1_PER_ID1, B1PERMIT.B1_PER_ID2, B1PERMIT.B1_PER_ID3,
> B1_ALT_ID,
> B1PERMIT.B1_PER_GROUP, B1PERMIT.B1_PER_TYPE,
> B1PERMIT.B1_PER_SUB_TYPE,B1PERMIT.B1_PER_CATEGORY,
> B1PERMIT.B1_FILE_DD, B1PERMIT.B1_SPECIAL_TEXT,
> B3ADDRES.B1_HSE_NBR_START,B3ADDRES.B1_HSE_NBR_END,
> B3ADDRES.B1_HSE_FRAC_NBR_START,
> B3ADDRES.B1_HSE_FRAC_NBR_END,B3ADDRES.B1_STR_DIR,
> B3ADDRES.B1_STR_NAME, B3ADDRES.B1_STR_SUFFIX,
> B3ADDRES.B1_STR_SUFFIX_DIR,
> B3ADDRES.B1_UNIT_START,B3ADDRES.B1_UNIT_END, B3ADDRES.B1_UNIT_TYPE,
> B3ADDRES.B1_SITUS_CITY, B3ADDRES.B1_SITUS_STATE, B3ADDRES.B1_SITUS_ZIP
> FROM B1PERMIT, B3ADDRES
> WHERE B1PERMIT.SERV_PROV_CODE = 'NOLA'
> AND B1PERMIT.REC_STATUS = 'A'
> AND B1PERMIT.SERV_PROV_CODE = B3ADDRES.SERV_PROV_CODE(+)
> AND B1PERMIT.B1_PER_ID1 = B3ADDRES.B1_PER_ID1(+)
> AND B1PERMIT.B1_PER_ID2 = B3ADDRES.B1_PER_ID2(+)
> AND B1PERMIT.B1_PER_ID3 = B3ADDRES.B1_PER_ID3(+)
> AND B1PERMIT.REC_STATUS = B3ADDRES.REC_STATUS(+)
> ORDER BY B1PERMIT.B1_FILE_DD DESC, B1PERMIT.B1_PER_ID2 ASC,
> B1PERMIT.B1_PER_ID3 ASC
> )
> WHERE ROWNUM < 101
> END OF STMT
> PARSE #1:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=4338791537
> BINDS #1:
> EXEC #1:c=0,e=2050,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=4338838879
> WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
> FETCH
> #1:c=4171875,e=4159542,p=0,cr=659077,cu=0,mis=0,r=1,dep=0,og=2,tim=4343002378
> WAIT #1: nam='SQL*Net message from client' ela= 1124 p1=1413697536 p2=1
> p3=0
> WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
> FETCH #1:c=0,e=2606,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4343011529
> WAIT #1: nam='SQL*Net message from client' ela= 921254 p1=1413697536
> p2=1 p3=0
> WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
> FETCH
> #1:c=15625,e=2626,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4343940614
> WAIT #1: nam='SQL*Net message from client' ela= 79948 p1=1413697536
> p2=1 p3=0
> WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
> FETCH #1:c=0,e=2587,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4344028328
> WAIT #1: nam='SQL*Net message from client' ela= 79688 p1=1413697536
> p2=1 p3=0
> WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
> FETCH #1:c=0,e=2582,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4344115808
> WAIT #1: nam='SQL*Net message from client' ela= 80108 p1=1413697536
> p2=1 p3=0
> WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
> FETCH #1:c=0,e=2574,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4344203574
> WAIT #1: nam='SQL*Net message from client' ela= 79555 p1=1413697536
> p2=1 p3=0
> WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
> FETCH #1:c=0,e=2594,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=4344290886
> WAIT #1: nam='SQL*Net message from client' ela= 84499 p1=1413697536
> p2=1 p3=0
> WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
> FETCH #1:c=0,e=2878,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=2,tim=4344383428
> *** 2005-12-01 18:09:03.992
> WAIT #1: nam='SQL*Net message from client' ela= 17914727 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='NESTED LOOPS OUTER '
> STAT #1 id=5 cnt=289518 pid=4 pos=1 obj=29837 op='TABLE ACCESS BY INDEX
> ROWID B1PERMIT '
> STAT #1 id=6 cnt=290076 pid=5 pos=1 obj=29838 op='INDEX RANGE SCAN
> B1PERMIT_ALTID_IX '
> STAT #1 id=7 cnt=39267 pid=4 pos=2 obj=29848 op='TABLE ACCESS BY INDEX
> ROWID B3ADDRES '
> STAT #1 id=8 cnt=39267 pid=7 pos=1 obj=29850 op='INDEX RANGE SCAN
> B3ADDRES_PK '
>

What sort of advice do you need ? The plan now seems to be doing what you wanted - i.e. take the path that the Rule Based Optimizer gave you.

If you want the thing to run faster, then you probably need to migrate to 10g, where the outer hash join can be reversed; or set a very large hash_area_size for 9i so that the driving table can hash in memory. As you saw from your first example, the hash join used a lot less CPU than the nested loop - and the time difference was due to the hash table spilling to the temp tablespace and being re-read.

The stats say that the query collects 290,000 rows from the first table, and joins all of them (inevitably) through the outer join, then sorts a lot of column data. It's going to take a lot of CPU. And if the data were not all in the buffer, it would take a lot of random I/O as well.

Unfortunately it's the nature of the query. You need to figure out how the data is going to grow, and what effect that will have on the performance of the possible execution plans. Then you can decide a strategy.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005