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 '
Received on Thu Dec 01 2005 - 04:15:54 CST
