DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: SQL Query tuning

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


  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


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

  4. #4
    Join Date
    Feb 2001
    Posts
    184
    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

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

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


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

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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]
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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?

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

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