tuning the SQL..Reduce the response time
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Thread: tuning the SQL..Reduce the response time

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    tuning the SQL..Reduce the response time

    Hi All,
    I am using oracle 8.1.7,solaris 2.8.
    I have this query which is taking 3 min for execution.

    SELECT COUNT(*) FROM CART_STATS WHERE COMPANY_ID = 8170926 AND ( ((SHIPMENT_STATUS = 'N' or PAYMENT_CLEARED = 'N')
    and ARCHIVE != 'D' and STATUS != 'D' and STATUS != 'X' and NUM_TRANSACTIONS > 0) OR ((((SHIPMENT_STATUS = 'Y'
    and PAYMENT_CLEARED = 'Y' and STATUS != 'D') OR (STATUS = 'X')) and ARCHIVE != 'D')
    and NUM_TRANSACTIONS > 0 and DATE_MODIFIED >= (sysdate - 60)) )

    and the execution plan is

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=283 Card=1 Bytes=25)
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CART_STATS' (Cost=283 Card=1
    29 Bytes=3225)

    3 2 INDEX (RANGE SCAN) OF 'CART_PS_IDX' (NON-UNIQUE) (Cost
    =5 Card=129)

    Its using the index which is built on the following columns(composite index)

    CART_PS_IDX COMPANY_ID
    CART_PS_IDX PAYMENT_CLEARED
    CART_PS_IDX SHIPMENT_STATUS
    CART_PS_IDX STATUS
    CART_PS_IDX ARCHIVE
    CART_PS_IDX CART_ID

    No of distinct values in each of these columns are
    SQL> select count(distinct company_id) from cart;

    COUNT(DISTINCTCOMPANY_ID)
    -------------------------
    88342


    SQL> select count(distinct PAYMENT_CLEARED) from cart;

    COUNT(DISTINCTPAYMENT_CLEARED)
    ------------------------------
    2

    SQL> select count(distinct shipment_status) from cart;

    COUNT(DISTINCTSHIPMENT_STATUS)
    ------------------------------
    2

    SQL> select count(distinct ARCHIVE) from cart;

    COUNT(DISTINCTARCHIVE)
    ----------------------
    3

    SQL> select count(distinct status) from cart;

    COUNT(DISTINCTSTATUS)
    ---------------------
    6

    How can i reduce the time take by this query, will generation of histograms help on this. Please advice.

    regards
    anandkl
    anandkl

  2. #2
    Join Date
    Sep 2003
    Location
    Bangalore
    Posts
    36

    Thumbs up Possible solution

    Hi,
    TO tune the query, first of all you can use count(1) INSTEAD of count(*). Secondly create an index on company_id and analyze the table.
    I hope this should work. Such heavy composite indexes are usually useless and moreover cause contention than actually help. Try to giving hint /*+INDEX(table_name the index on company_id) */ and try running the query and see if the response time has reduced.
    Thanks

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: Possible solution

    Originally posted by apunhiran
    Hi,
    TO tune the query, first of all you can use count(1) INSTEAD of count(*).
    what the...?!?!

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Count(1) vs. Count(*) is a urban myth, and I don't really think the complex index will be a significant overhead.

    The plan looks OK to me. So, how many rows are selected by:
    SELECT COUNT(*) FROM CART_STATS WHERE COMPANY_ID = 8170926;

    It could be that you are having to read a vast number of rows in order to do the rest of the logic . . . ?

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Total no of records from this query is 54712

    and the table has 14.5M records

    regards
    anandkl
    anandkl

  6. #6
    Join Date
    Apr 2003
    Posts
    353

    Re: Possible solution

    Originally posted by apunhiran
    Hi,
    TO tune the query, first of all you can use count(1) INSTEAD of count(*).
    I read somewhere in Tom's guide
    that count(*) and count(1) will work on same method.

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    So, there are a lot of rows . . . all 54'712 have to be read from the table - that could be enough to explain 3 minutes if most are not in buffers (depends on your hardware - I make that 3ms/row).

    (check my logic here) I think you can rewrite the query as

    Code:
    SELECT COUNT(*) 
    FROM  CART_STATS 
    WHERE COMPANY_ID = 8170926
    and   ARCHIVE != 'D' 
    and   NUM_TRANSACTIONS > 0
    and   
    (
     (LOTS OF STUFF)
     OR 
     (LOTS OF STUFF)
    )
    in which case an index on (COMPANY_ID, ARCHIVE, NUM_TRANSACTIONS) could be useful if these extra columns are selective.

    So how many rows from:
    SELECT COUNT(*)
    FROM CART_STATS
    WHERE COMPANY_ID = 8170926
    and ARCHIVE != 'D'
    and NUM_TRANSACTIONS > 0
    Last edited by DaPi; 12-18-2003 at 06:26 AM.

  8. #8
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    The count is

    145591
    anandkl

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    That must be a typing error - how do you get more than 54,712?

  10. #10
    Join Date
    Nov 2002
    Posts
    80
    how about changing

    NUM_TRANSACTIONS > 0

    NOT NUM_TRANSACTIONS = 0

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