Explain Plan question..

[sonaliak]

I have pasted 2 explain plans here for the queries doing the same thing. For the 1st query which is using EXISTS consistent gets is too high 151438 but the cost is low 4.
In the 2nd query with the flat structure the cost is high 29 but the consistent gets is low 109. So my question is which query is better one with the cost or one with the consistent gets. If the consistent gets is high what does that mean ?
thanks

SQL> SELECT Res_Name, Res_ID
2 FROM mwebRes
3 WHERE EXISTS(Select 1 from mwebExpense , MWEBWORK
4 where Expense_Entity_Type IN (1,4) AND Res_ID =Expense_Client_ID
5 AND Work_Par6=1117 AND Expense_Task_ID =Work_ID)
6 ORDER BY Res_Name ;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=83 Bytes=1494
)

1 0 SORT (ORDER BY) (Cost=4 Card=83 Bytes=1494)
2 1 FILTER
3 2 INDEX (FAST FULL SCAN) OF 'IX_RES_ENTITY' (NON-UNIQUE)
(Cost=2 Card=83 Bytes=1494)

4 2 NESTED LOOPS (Cost=22 Card=5 Bytes=70)
5 4 INDEX (RANGE SCAN) OF 'IX_WORK_PAR6' (NON-UNIQUE) (C
ost=2 Card=4 Bytes=24)

6 4 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBEXPENSE' (Cost
=5 Card=63 Bytes=504)

7 6 INDEX (RANGE SCAN) OF 'IX_EXPENSE_TASK' (NON-UNIQU
E) (Cost=1 Card=63)





Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
151438 consistent gets
0 physical reads
0 redo size
507 bytes sent via SQL*Net to client
739 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> SELECT Res_Name, Res_ID FROM mwebRes
2 WHERE Res_ID in(Select distinct Expense_Client_ID from mwebExpense
3 where Expense_Entity_Type IN (1,4)
4 AND Expense_Task_ID in(SELECT Work_ID from mwebwork where Work_Par6=1117))
5 ORDER BY Res_Name ;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=54 Bytes=167
4)

1 0 SORT (ORDER BY) (Cost=29 Card=54 Bytes=1674)
2 1 HASH JOIN (Cost=28 Card=54 Bytes=1674)
3 2 VIEW OF 'VW_NSO_1' (Cost=24 Card=54 Bytes=702)
4 3 SORT (UNIQUE) (Cost=24 Card=54 Bytes=756)
5 4 NESTED LOOPS (Cost=22 Card=95 Bytes=1330)
6 5 INDEX (RANGE SCAN) OF 'IX_WORK_PAR6' (NON-UNIQUE
) (Cost=2 Card=4 Bytes=24)

7 5 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBEXPENSE' (
Cost=5 Card=3393 Bytes=27144)

8 7 INDEX (RANGE SCAN) OF 'IX_EXPENSE_TASK' (NON-U
NIQUE) (Cost=1 Card=3393)

9 2 INDEX (FAST FULL SCAN) OF 'IX_RES_ENTITY' (NON-UNIQUE)
(Cost=2 Card=1646 Bytes=29628)





Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
102 consistent gets
0 physical reads
0 redo size
507 bytes sent via SQL*Net to client
745 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>

[sonaliak]

Can some one help on this one please ?

[tamilselvan]

Consistent gets means the required data already available in the buffer. Go for the SQL that has minimum cost.

[jmodic]

Originally posted by tamilselvan
Go for the SQL that has minimum cost.

Cost of two different SQL statements can in no way be compared! The one with lower cost might perform much much worse than the other one, or it might be the other way arround. Cost in explain plan can not be taken as some absolute or even relevant mesure of the efficiency.

Oracle's cost based optimizer evaluate many different possible execution paths for a query, and for each path it calculates its "cost". Then finally it chooses the one with the lowest cost. But this comparison of costs is valid only for a *single particular* query. It should in no way be used as a comparison method for *two different* queries. For two different queries cost simply can not be compared!

[svk]

Sonali,

You have tried this in sequence as first way followed by second. That too with sqltrace on.

Reverse the sequence and you may get to see different picture.

Try using explain plan instead to make you decision.

[sonaliak]

Jmodic: Why do you say that you cannot compare the costs of 2 queries. You will use explain plan for doing exactly that.
These 2 queries are not totally different just written in different ways to get to the same results. I thought even Oracle suggests that you should do a explain plan to compare 2 queries.

SVK: I am doing explain plan so it doesn't matter which query I do first and not TKPROF .

Thanks any ways
Sonali

[jmodic]

Originally posted by sonaliak
Jmodic: Why do you say that you cannot compare the costs of 2 queries. You will use explain plan for doing exactly that.

I say so because that's the way it is. Sorry if that's in contradiction with what you've belived, but the fact remains: COST from the explain plan is of *totaly no value* for determining if the query is efficient or if it is faster than another query. When you compare two execution plans you don't look for their costs, you look at their operations and access paths and conclud about efficiency from there...

These 2 queries are not totally different just written in different ways to get to the same results. I thought even Oracle suggests that you should do a explain plan to compare 2 queries.

No, for Oracle's optimizer this two queries are totaly different, although they return the same result set. And yes, Oracle suggests comparing explain plans, but you won't ever find any suggestion in the documentation to compare COSTs from different explain plans. As I said, comparing two explain plans means understanding what each of the phases in an explain plan mean.

SVK: I am doing explain plan so it doesn't matter which query I do first and not TKPROF .

I'm taking the liberty to ansver this question, although it wasn't addrest to me. Sonali, you are wrong again. You were not realy just doing explain plan for both queries - you were actualy executing both queries. That's where your statistics came from. If you were just doing explain plan for your queries you would not get any statistics (physical reads, consistent reads, ....) - you must actualy run the query to get those. And when you run two queries that access same tables/indexes the order of the executions could be very important, as the second one might get all the data blocks allready in the cache read from the first query. But in your example this was obviously not the case, as none of the queries performed any physical block read, meaning that all the data was allready in the cache.

[sonaliak]

What is db block gets and recursive calls. I ran the same query 4 times using
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS;
The values of db block gets and recursive calls remained same all the time.
I am just trying to figure out how to read explain plan in more detail than just looking at COST, in other words how to decide which query is better and not just the meanings of these terms which I read in some oracle book !

thanks Jmodic for your explanation !

[svk]

Thanks jmodic for explaining the things.