-
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?
-
-
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"
-
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.
-
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
-
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.
-
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"
-
Run both SQL statements with trace enabled at level 8, and attach the trace files.
-
cost means nothing okE!
dont rely on cost number to tune
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|