The best way to be sure it's the 'real' plan is to see if the A(ctual)-rows, A(ctual)-time, buffers, columns etc. So 'explain plan' and 'autotrace' can be misleading. I didnt use the index because my data types didn't match. SQL> select * from table(dbms_xplan.display_cursor) Let's see what *really* happens when I run it: So based on that, I might think my query is fine. Predicate Information (identified by operation id): egĢ select d.*, cast(object_id as varchar2(20)) abc The issue is that they are not always a realistic view of what will actually take place. Just remember: it is an estimate and only granular to one second! | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Note you need to multiply the values in the 'starts' column to those in the 'estimated' to get the value to compare to actual rows.įinal note: if you're trying to get the estimated time, explain plans include this: Then compare the estimates to the actuals and see if they're close. For instructions on how to create these, see: This shows you the number of rows processed. To figure this out, you need the execution plan. If the answer is yes for all steps of the plan, it's likely you have the 'best' plan for your query. 'Is the (estimated) cardinality roughly equal to the actual number of rows the statement processed?' The cost is useful for figuring out why the optimizer chose (e.g.) a full table scan over an index. A lower cost query can run faster than a higher cost one! Thus the cost is (usually) higher.Īll other things being equal, a query with a higher cost will use more resources and thus take longer to run. Cardinality is the estimated number of rows the step will return.Ĭost is the estimated amount of work the plan will do.Ī higher cardinality => you're going to fetch more rows => you're going to do more work => the query will take longer.

Toad for oracle cost