SQL Query tuning - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: SQL Query tuning

  1. #11
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    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

  2. #12
    Join Date
    Mar 2001
    Posts
    71
    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

  3. #13
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    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

  4. #14
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  5. #15
    Join Date
    Mar 2001
    Posts
    71
    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

  6. #16
    Join Date
    Mar 2001
    Posts
    71
    Chris,

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

    np70

  7. #17
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  8. #18
    Join Date
    Mar 2001
    Posts
    71
    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

  9. #19
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  10. #20
    Join Date
    Mar 2001
    Posts
    71
    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
  •  


Click Here to Expand Forum to Full Width