Hi,

Joe: when I tried to use index hint in query, explain plan
shows very high cost. So, I am still doing FTS on Big table.

Chris:
I will try to format query so it is more readable. Also, I made some changes to my previous query. Instead of

...........
D
)E
WHERE E.rn BETWEEN 1 and 15

I am using
..........
D
) WHERE rownum <= 15
)E
WHERE E.rn >= 1

This eliminated 700 Physical Reads for my query. With Between clause, query used to do 700 physical read everytime I run it. But By eliminating lots of rows in Inner view, I was
able to reduce physical read to 0.(I referred to your suggestion on post
[url]http://www.dbasupport.com/forums/showthread.php?threadid=5480&pagenumber=1)[/url] But to do this I had to hardcode this values in query, instead of using BIND variable. Because this whole SQL is run from procedure, It would be nice if I can use bind variable. I guess PL*SQL doesn't like if you try to bind value to rownum(e.g. rownum <= :vcallend). Please let me know if this is a bug or there is any workaround for this.

I have noticed there is 7800 Buffer read per each execution of this query. I tried to simulate 20 concurrent sessions using shell script. Unix m/c load went upto 3.5 (which is very high I think). Also, min execution time for procedure containing query was
8 sec. and max was 16 sec.


My new Query is as follows:
SELECT
E.list_id, ....., E.length FROM (
SELECT rownum rn, D.list_id, ....,
TO_NUMBER(NULL) length FROM (
SELECT a.list_id,title,short_desc,rate,
list_type_id,avg_grade, reliability_pct,
(calls_received+num_paid_emails-calls_failed) activity,
username
FROM tlisting a, tuserauth b
WHERE a.list_type_id <> 2
AND a.list_status_id = 0
AND a.user_id = b.user_id
AND a.category_id IN
(SELECT category_id
FROM tcategory
START with category_id = :icategoryid1
-- I am still using Bind Variable here
CONNECT BY PRIOR category_id = parent_id)
ORDER BY avg_grade desc, reliability_pct desc, activity desc
) D
WHERE rownum <= 1500 ) E
WHERE E.rn >= 1486 UNION SELECT
E.list_id,........,E.length FROM (
SELECT rownum rn, D.list_id,........,
D.length FROM (
SELECT a.list_id, title, short_desc, rate, list_type_id,
avg_grade, reliability_pct,
(calls_received+num_paid_emails-calls_failed) activity,
username, length
FROM tlisting a, tuserauth b, tlistrecording c
WHERE a.list_type_id = 2
AND a.list_status_id = 0
AND a.user_id = b.user_id
AND a.list_id = c.list_id(+)
AND a.category_id IN
(SELECT category_id
FROM tcategory
START with category_id = :icategoryid2
CONNECT BY PRIOR category_id = parent_id)
ORDER BY avg_grade desc, reliability_pct desc, activity desc
) D
WHERE rownum <= 1000 ) E
WHERE E.rn >= 991
ORDER BY list_type_id, avg_grade desc,
reliability_pct desc, activity desc

=====================================================
Explain Plan:
------------
OPERATION OPTIONS OBJECT_NAME POSITION CARDINALITY BYTES COST
--------------------------------------------- --------------- --------------- ---------- ----------- ---------- ----------
SELECT STATEMENT 1801 6642 1545372 1801
SORT UNIQUE 1 6642 1545372 1330
UNION-ALL 1
VIEW 1 4428 1014012 466
COUNT STOPKEY 1
VIEW 1 4428 947592 466
SORT ORDER BY STOPKE 1 4428 624348 466
Y

HASH JOIN 1 4428 624348 273
TABLE ACCESS FULL TUSERAUTH 1 1000 11000 2

OPERATION OPTIONS OBJECT_NAME POSITION CARDINALITY BYTES COST
--------------------------------------------- --------------- --------------- ---------- ----------- ---------- ----------
HASH JOIN 2 4428 575640 270
VIEW VW_NSO_1 1 13 169 5
SORT UNIQUE 1 13 65 5
CONNECT BY 1
INDEX UNIQUE SCAN ICATEGORY_PK 1 1 3
TABLE ACCESS BY USER ROWID TCATEGORY 2
TABLE ACCESS FULL TCATEGORY 3 14 70 1
TABLE ACCESS FULL TLISTING 2 46667 5460039 264
VIEW 2 2214 531360 406
COUNT STOPKEY 1
VIEW 1 2214 502578 406

OPERATION OPTIONS OBJECT_NAME POSITION CARDINALITY BYTES COST
--------------------------------------------- --------------- --------------- ---------- ----------- ---------- ----------
SORT ORDER BY STOPKE 1 2214 329886 406
Y

HASH JOIN 1 2214 329886 305
TABLE ACCESS FULL TUSERAUTH 1 1000 11000 2
HASH JOIN OUTER 2 2214 305532 302
HASH JOIN 1 2214 287820 270
VIEW VW_NSO_2 1 13 169 5
SORT UNIQUE 1 13 65 5
CONNECT BY 1
INDEX UNIQUE SCAN ICATEGORY_PK 1 1 3

OPERATION OPTIONS OBJECT_NAME POSITION CARDINALITY BYTES COST
--------------------------------------------- --------------- --------------- ---------- ----------- ---------- ----------
TABLE ACCESS BY USER ROWID TCATEGORY 2
TABLE ACCESS FULL TCATEGORY 3 14 70 1
TABLE ACCESS FULL TLISTING 2 23334 2730078 264
TABLE ACCESS FULL TLISTRECORDING 2 23333 186664 24

33 rows selected.

==============================================

Indexes On tables:
----------------------

INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ ------------------------------ --------------------
ICATEGORY_PK CATEGORY_ID TCATEGORY
LISTING_PK LIST_ID TLISTING
ILISTING_USERID_IDX USER_ID TLISTING
ILISTING_CATEGORYID_IDX CATEGORY_ID TLISTING
ILISTRECORDING_PK LIST_ID TLISTRECORDING
IUSERAUTH_PK USER_ID TUSERAUTH


Hope this info helps.

Thanks again for all your help.

np70