-
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 <tablename> estimate statistics...
AND
2) analyze table compute statistics for all indexed columns
AND
3) analyze index <index name> 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
_________________________
Joe Ramsey
Senior Database Administrator
dbaDirect, Inc.
(877)687-3227
-
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
_________________________
Joe Ramsey
Senior Database Administrator
dbaDirect, Inc.
(877)687-3227
-
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
[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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|