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

Thread: Performance tuning

  1. #1
    Join Date
    Oct 2007
    Posts
    11

    Performance tuning

    I am little bit confused with oracle behavior about a query execution plan. My table is having 1000000 records. Table name is TEST_NORMAL. One column name is SAL number(6).
    There are 6001 distinct salaries into this table. So this column has been indexed by Bitmap Index.

    I wrote two different queries To finding third highest sal.

    Query 1:-

    select a.sal from test_normal a where
    3=(select count(distinct b.sal) from test_normal b where b.sal>=a.sal)

    Execution Plan of Query-1
    COST CARDINALITY BYTES
    SELECT STATEMENT, GOAL = CHOOSE 404 10000 30000
    FILTER
    BITMAP CONVERSION TO ROWIDS
    BITMAP INDEX FAST FULL SCAN SCOTT NORMAL_SAL_DX
    SORT GROUP BY 1 3
    BITMAP CONVERSION TO ROWIDS
    BITMAP INDEX RANGE SCAN SCOTT NORMAL_SAL_DX

    Query -1 Result time is 576 secs.

    Query 2:-

    select tn.sal,tn.ename
    from (select sal,ename,dense_rank() over (order by sal desc) "DRANK" from test_normal) tn
    where tn.DRANK = 3

    Execution Plan of Query-2
    COST CARDINALITY BYTES
    SELECT STATEMENT, GOAL = CHOOSE 7138 1000000 43000000
    VIEW SCOTT 7138 1000000 43000000
    WINDOW SORT PUSHED RANK 7138 1000000 33000000
    TABLE ACCESS FULL SCOTT TEST_NORMAL 943 1000000 33000000

    Query -1 Result time is 3.5 secs.

    It is quite visible that there is very high cost, cardinality and bytes into Query-2 , but still this query is much faster than query-1. Why?

  2. #2
    Join Date
    Oct 2007
    Posts
    11
    Reply Please.

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    cost is just a number generated and specific to a query.. no use in comparing costs of different queries..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Oct 2007
    Posts
    11
    Then How would you tune a query. I mean to say that to find a exact excution plan and best qurry performance what parameters need to be checked, If we are simulating a issue of production server.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    by understanding your data, understanding how it shoukd be accessed, using knowdledge of the sql library, by seeing ho long things take. not by comparing a cost of two very different queries

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    In the fast query, it was doing 1 index FFS and one index range scan and then filter the data. Where as in other case it was only one FTS. I think if you remiove this bitmap index and just try a B-tree index then the timing should be less.
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    absolutely wrong..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Run both SQL statements with trace enabled at level 8, and attach the trace files.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    cost means nothing okE!

    dont rely on cost number to tune

  10. #10
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    I think the sorts makes the main confusion here. In the first case you have sort group by, in the second you have "WINDOW SORT PUSHED RANK' - not sure what it means but seems it performs better.
    Why don't you try the classical top-n analysis
    Try
    select sal from (select sal from test_normal order by sal ) where rownum<3
    and
    select sal from (select sal from test_normal order by sal ) where rownum=3

    How it performs? I guess that should work well

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