SQL Query tuning

Hi,

we have query using four tables. The main table is TLISTING
table which has approx. 500,000 rows. and would like to display 25 rows at a time for web page. Out of 25 rows, 15 rows has listing_type_id = 1 OR 3. 10 rows has listing_type_id = 2. Also, TCATEGORY table has 200 rows and we are doing hierarchical lookup on that. Please let me know
how do I tune following query to avoid Full scan and get better cost. I have index on category_id and parent_id column of TCATEGORY table, list_id and list_type_id column
of TLISTING and user_id column of TUSERAUTH.

I tried to create bitmap ondex on category_id and analyzed all four tables in query. But CBO doesn't use it. When I force it to use My cost of query increased from 500 to 2500.

TIA

np70

SELECT
E.list_id, E.title, E.short_desc, E.rate, E.list_type_id,
E.avg_grade, E.reliability_pct, E.activity, E.username, E.length FROM (
SELECT rownum rn, D.list_id, D.title, D.short_desc, D.rate, D.list_type_id,
D.avg_grade, D.reliability_pct, D.activity, D.username, TO_NUMBER(NULL) length FROM (
SELECT C.list_id, C.title, C.short_desc, C.rate, C.list_type_id,
C.avg_grade, C.reliability_pct, C.activity, C.username 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, tcategory b, tuserauth c
WHERE a.list_type_id <> 2
AND a.category_id = b.category_id
AND a.list_status_id = 0
AND a.user_id = c.user_id
AND a.category_id IN
(SELECT category_id
FROM tcategory
START with category_id = 10
CONNECT BY PRIOR category_id = parent_id)
) C
ORDER BY avg_grade desc, reliability_pct desc, activity desc
) D
) E
WHERE E.rn BETWEEN 1 and 15
UNION SELECT
E.list_id, E.title, E.short_desc, E.rate, E.list_type_id,
E.avg_grade, E.reliability_pct, E.activity, E.username, E.length FROM (
SELECT rownum rn, D.list_id, D.title, D.short_desc, D.rate, D.list_type_id,
D.avg_grade, D.reliability_pct, D.activity, D.username, D.length FROM (
SELECT C.list_id, C.title, C.short_desc, C.rate, C.list_type_id,
C.avg_grade, C.reliability_pct, C.activity, C.username, C.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, tcategory b, tuserauth c, tlistrecording d
WHERE a.list_type_id = 2
AND a.category_id = b.category_id
AND a.list_status_id = 0
AND a.user_id = c.user_id
AND a.list_id = d.list_id(+)
AND a.category_id IN
(SELECT category_id
FROM tcategory
START with category_id = 10
CONNECT BY PRIOR category_id = parent_id)
) C
ORDER BY avg_grade desc, reliability_pct desc, activity desc
) D
) E
WHERE E.rn BETWEEN 1 and 10
ORDER BY list_type_id, avg_grade desc,
reliability_pct desc, activity desc

Analyze those tables that you are querying on and then do an explain plan on the query to see what are the tables that gets the FTS and try to use index over them.

Sam

I have all four tables analyzed. But CBO doesn't use index
on category_id column on TLISTING table. When I forced it
to use index, I am getting higher cost than FTS. I am worried that when table gets even bigger and more users are
on system, performance might degrade.

thanks for your reply

Hi,

I can tell you one thing, Instead of Creating Bitmap Index have it Normal.

I have gone through this. The Bitmap is good if you numerous number of Rows with low cardinality, for smaller number of rows, this really does not work.

Try using Exists instead of using In.

AThanks

I tried using normal index but cost was very high compared to bitmap index. we gave approx. 150 distinct values in
category_id field. Also, I thought you should use exist only
if you are restrict # of rows in outer query. I have lots of rows in outer query. While inner query has only 20 to 30
rows.

thanks

How have to analyzed the table, for the one that does the FTS. Did you try using the estimate statistics or the hints like "ROWID(table)". It is sometime best to live with the optimal performance where there is FTS, rather than wasting time and energy to find a better one.

Good luck,
Sam

Sam,

I used compute statistics to analyze big table. I will be
accessing max 20% rows from big table. So, index scan should
result in less cost compared to FTS. If I accpet FTS, how
will performance be affected when many users are on DB?

thanks

In you r case FTS doesn't make any sense, it has to be an index scan. Make sure that you have the following criterias being satisfied in your query. They would have a good perfomance change.

When you are using the "AND" clause, oracle would use "BOTTOM-UP" approach. On that case, the first statement at the bottom of the and clause would be evaluated first and then the one above that and so on. I would suggest try revamping your query to choose the driving table and put your subquery at the top.

Example:
SELECT . . .
FROM emp E
WHERE emp_salary > 50000
AND emp_type = 'MANAGER'
AND 25 < ( SELECT COUNT(*)
FROM emp
WHERE emp_mgr = E.emp_no)

Changed it to be:
SELECT . . .
FROM emp E
WHERE 25 < ( SELECT COUNT(*)
FROM emp
WHERE emp_mgr = E.emp_no)
AND emp_salary > 50000
AND emp_type = 'MANAGER'



On the other hand when you use the OR it would be a top-dow approach that oracle would use to query the tables.


Now start tuning the queries individually, one by one before you make them to be a composit one. That way you would know the performance of each query and would give you a good leaway to juggle them accordingly.

Good luck,
Sam


[Edited by sambavan on 03-20-2001 at 11:37 AM]

SELECT category_id
FROM tcategory
START with category_id = 10
CONNECT BY PRIOR category_id = parent_id)

Do you have indexes on category_id and Parent_id?

Sam,

I have Cost Based Optimizer. I tried to rearrange WHERE
clause, but explain plan doesn't change. I can not use
where rownum < xx to limit #Ofrows from big table, because
I have to get all rows and sort in required order, before
selecting rows to display on web. I tried to run this
query for 20 sessions, and maximumu uptime on solaris was 3.7. For 50 sessions It went upto 7.4. So, I think with
lots of users my m/c is going to crash. V$SQLAREA has following statistics.

DISK_READS BUFFER_GETS ROWS_PROCESSED EXECUTIONS FIRST_LOAD_TIME
---------- ----------- -------------- ---------- -------------------
DISK_READS/EXECUTIONS BUFFER_GETS/EXECUTIONS ROWS_PROCESSED/EXECUTIONS
--------------------- ---------------------- -------------------------
211794 1175421 4075 163 2001-03-20/12:23:51
1299.34969 7211.17178 25

Do you think I have very high Disk reads and buffer gets?
================================================
tamilselvan:
I have indexes on both parent_id and category_id in
TCATEGORY table. Also, there is index on list_id and
category_id column of TLISTING.

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

thanks for your help...

np70

I read through the posts really quick so I someone else might have suggested this but ....

When you analyzed your tables did you:

1) analyze table estimate statistics...
AND
2) analyze table compute statistics for all indexed columns
AND
3) analyze index compute statistics;

I usually do an compute unless the table is just too large to make this feasible (like fact tables in a DSS environment). I also like to do a compute on all indexed columns as well - this will create histograms on the columns that are indexed which will help the optimizer out if your values are skewed (which may be your problem) and I ALWAYS do a compute on indexes - that usually runs pretty quickly even for large tables.

If your column, for example has a cardinality (distinct values) of say 3 (male, female, other) and the selectivity of each value is:

70% Male
29% Female
1% Other

You will not get an index scan if you don't create a histogram for the column. This is because the optimizer, in the absence of column statistics, assumes an equal distribution of data. Which means it will think that 33.33% of the values equate to Male, 33.33% Female and 33.33% Other. Obviously if this was truly the case, you would rather do a full scan and avoid the index because you're doing extra processing for no reason. When you create a histogram, the optimizer should (note should...not will - I have a problem w/ a very selective index w/ fresh stats and Oracle still hasn't figured out the problem) see that your constraint is highly selective and use the index.

Also, be aware that even with statistics there are a number of other parameters that can influence the optimizers decision including parameters like db_file_multiblock_read_count, among a host of others... This is outlined in the performance tuning and, I believe, the Oracle Administration manuals.

Joe

Hi folks,

I tried to analyze table, index and create histogram. But
query is still not using index on Big table. Also, I tried
to put big table in BUFFER_POOL_KEEP. But, still there is
physical read on this query. Reads are propotional to Number
of rows that satisfy category_id crieteria.

e.g. for category_id = 10, I have 20000 rows and 1400
physical reads.

for category_id = 9, I have 10000 rows and 800 physical
reads.

for category_id = 5, I have 2000 rows AND physical read=0.

I was thinking to put this table in KEEP pool to avoid
disk reads reducing overall COST of query. We need to run
this query every 2 minutes for multiple sessions.

thanks for all your help,

Nilesh

If you force the index with a hint does the query run faster? It may be that an FTS is more efficient. I'm not talking cost numbers, I'm talking about the actual time it takes the query to run.

Joe

I'm trying to work out a better query for you. If you get a chance, could you re-post your query with all the columns being aliased? You have columns in the SELECT clauses that do not contain aliases, so I am having trouble re-writing the query.

Also, any other info you could provide, such as all available indexes, what the explain plans are, etc would be most helpful.

Thanks,

- Chris

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
http://www.dbasupport.com/forums/sho...&pagenumber=1) 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

Chris,

Query in previous post is lost all formatting. I tried to indent but it's gone because of HTML.

np70

All good info, and I hate to be a pain, but:

SELECT
...
calls_received+num_paid_emails-calls_failed) activity,
username, length
...
FROM

...still doesn't tell me where all the columns come from. I'd like to abstract some of the tables to outside of the WHERE ROWNUM clause, but to do that I need to know where all the columns come from...

Thanks,

- Chris

Chris,

Following is table structure:

TLISTING:
LIST_ID NOT NULL NUMBER
CATEGORY_ID NUMBER
USER_ID NUMBER
LIST_STATUS_ID NUMBER
TITLE NOT NULL VARCHAR2(80)
SHORT_DESC NOT NULL VARCHAR2(150)
DESCRIPTION --(STORED OUTSIDE TABLE, IN OTHER TABLESPACE) NOT NULL CLOB
DESC_TYPE_FLAG VARCHAR2(1)
RATE NOT NULL NUMBER(10,2)
LIST_TYPE_ID NUMBER
CALLS_RECEIVED NUMBER
CALLS_FAILED NUMBER
NUM_PAID_EMAILS NUMBER
RELIABILITY_PCT NUMBER
AVG_GRADE NUMBER
CREATION_DATE DATE
========================================
TLISTRECORDING:
---------------
LIST_ID NOT NULL NUMBER
RECORDING_TYPE_ID NUMBER
LENGTH NOT NULL NUMBER
RECORDING NOT NULL BLOB

==================================================
TUSERAUTH:
-------------
USER_ID NOT NULL NUMBER
USERNAME NOT NULL VARCHAR2(30)
PASSWORD NOT NULL VARCHAR2(30)
PIN NUMBER
RESTRICTED_ACCESS VARCHAR2(1)
ACT_STATUS_ID NUMBER

==========================================================
TCATEGORY:
------------
CATEGORY_ID NOT NULL NUMBER
PARENT_ID NUMBER
NAME NOT NULL VARCHAR2(75)
LEAF_FLAG VARCHAR2(1)
DISPLAY_ORDER NOT NULL NUMBER
NUM_LISTINGS NUMBER

thanks,

np70

Okay, that took some work. You owe me 1/2 hour of billable time :)

Some caveats first.

- I don't have the model, so this may need some tweaking.
- There is a better solution IF you have Oracle 8.1.6.2 or better and IF you can use dynamic SQL. If both of these are true, then I'll re-write it to be even faster :)
- The crux of your issue had to do with the <> 2. Oracle basically cannot and will not use an index for an inequality. Changing that to IN (1,3) will allow the use of your bitmap index ( a regular B*Tree would not be used in either case - not selective enough)
- The other changes I made should help dramatically as well.

SELECT
---TFull.LIST_ID, TFull.TITLE, TFull.SHORT_DESC, TFull.RATE, TFull.LIST_TYPE_ID,
---TFull.AVG_GRADE, TFull.RELIABILITY_PCT, TFull.ACTIVITY,
---UA.USERNAME, DECODE ( TFull.LIST_TYPE_ID, 2, LR.LENGTH, NULL) AS ACTIVITY
FROM
---(
------SELECT
---------LIST_ID, TITLE, SHORT_DESC, RATE, LIST_TYPE_ID,
---------AVG_GRADE, RELIABILITY_PCT, ACTIVITY
------FROM
---------(
---------SELECT
------------ROWNUM RN, LIST_ID, TITLE, SHORT_DESC, RATE, LIST_TYPE_ID,
------------AVG_GRADE, RELIABILITY_PCT, ACTIVITY
---------FROM
------------(
------------SELECT
---------------L.LIST_ID, L.TITLE, L.SHORT_DESC, L.RATE, T.LIST_TYPE_ID,
---------------L.AVG_GRADE, L.RELIABILITY_PCT,
---------------(L.CALLS_RECEIVED + L.NUM_PAID_EMAILS - L.CALLS_FAILED) ACTIVITY,
------------FROM
---------------TLISTING------L
------------WHERE
---------------------L.LIST_TYPE_ID ---IN---(1, 3)
---------------AND---L.LIST_STATUS_ID ---=---0
---------------AND---L.CATEGORY_ID ------IN
---------------------------------------(
------------------------------------------SELECT
---------------------------------------------CATEGORY_ID
------------------------------------------FROM
---------------------------------------------TCATEGORY
------------------------------------------START WITH
---------------------------------------------CATEGORY_ID = 10
------------------------------------------CONNECT BY PRIOR
---------------------------------------------CATEGORY_ID = PARENT_ID
---------------------------------------)
---------------ORDER BY
------------------L.AVG_GRADE ---------------------------------------------DESC,
------------------L.RELIABILITY_PCT ---------------------------------------DESC,
------------------(L.CALLS_RECEIVED + L.NUM_PAID_EMAILS - L.CALLS_FAILED) DESC
------------)---T1
---------WHERE
------------ROWNUM <= 15 -- This would be variable obviously
---------)---T2
------WHERE
---------RN >= 1----- This would be variable obviously
------)---T3

---UNION ALL -- Can use all since they definitely have different list type id - saves time

------SELECT
---------LIST_ID, TITLE, SHORT_DESC, RATE, LIST_TYPE_ID,
---------AVG_GRADE, RELIABILITY_PCT, ACTIVITY
------FROM
---------(
---------SELECT
------------ROWNUM RN, LIST_ID, TITLE, SHORT_DESC, RATE, LIST_TYPE_ID,
------------AVG_GRADE, RELIABILITY_PCT, ACTIVITY
---------FROM
------------(
------------SELECT
---------------L.LIST_ID, L.TITLE, L.SHORT_DESC, L.RATE, T.LIST_TYPE_ID,
---------------L.AVG_GRADE, L.RELIABILITY_PCT,
---------------(L.CALLS_RECEIVED + L.NUM_PAID_EMAILS - L.CALLS_FAILED) ACTIVITY,
------------FROM
---------------TLISTING------L
------------WHERE
---------------------L.LIST_TYPE_ID ---=---2
---------------AND---L.LIST_STATUS_ID ---=---0
---------------AND---L.CATEGORY_ID ------IN
---------------------------------------(
------------------------------------------SELECT
---------------------------------------------CATEGORY_ID
------------------------------------------FROM
---------------------------------------------TCATEGORY
------------------------------------------START WITH
---------------------------------------------CATEGORY_ID = 10
------------------------------------------CONNECT BY PRIOR
---------------------------------------------CATEGORY_ID = PARENT_ID
---------------------------------------)
---------------ORDER BY
------------------L.AVG_GRADE ---------------------------------------------DESC,
------------------L.RELIABILITY_PCT ---------------------------------------DESC,
------------------(L.CALLS_RECEIVED + L.NUM_PAID_EMAILS - L.CALLS_FAILED) DESC
------------)---T4
---------WHERE
------------ROWNUM <= 10 -- This would be variable obviously
---------)---T5
------WHERE
---------RN >= 1----- This would be variable obviously
------)---T6
---)---------------TFull---,
---TUSERAUTH ------UA------,----- Moved this join until after the recordset is cut to 25 record
---TLISTRECORDING LR -------- ---to save time. Also removed TCATEGORY, since it wasn't being
-----------------------------------used
WHERE
---------UA.USER_ID------=---L.USER_ID---
---AND---D.LIST_ID---(+)=---L.LIST_ID---
ORDER BY
---LIST_TYPE_ID---------------------------------------------,
---AVG_GRADE ------------------------------------------DESC---,
---RELIABILITY_PCT ------------------------------------DESC---,
---DECODE ( TFull.LIST_TYPE_ID, 2, LR.LENGTH, NULL)---DESC


Change '---' to 3-char tab and all will line up nicely

Hope this helps (actually, hope this works :) )

- Chris

thanks chris.

I will try to run this query and see any performance change. We have 8.1.6.0, but planning to upgrade it to
8.1.6.2. Please let me know what will change if we upgrade.
Also, I am running this query from procedure, so I can take
advantage of NDS. But as in previous post, when I try to bind rownum in procedure, I don't get any results. So, I had to put values for rownum <= 15 while building SQL stmt.

thanks,

np70