When an oracle explained plan is consider good? I'm try to refactor a DB Schema, and there are so many query in view and packages that are so slow.
For example, this is one of the most orrible query, and give me this explain plan:
Plan ALL_ROWSCost: 18,096 Bytes: 17 Cardinality: 1
I don't ask how to fix a query, just how to consider the explain plan as good. Thanks!!
Before considering the result of an Explain Plan we need to understand following terminologies,
Cardinality– Estimate of the number of rows coming out of each of the operations.
• Access method – The way in which the data is being accessed, via either a table scan or index
access.
• Join method – The method (e.g., hash, sort-merge, etc.) used to join tables with each other.
• Join type – The type of join (e.g., outer, anti, semi, etc.).
• Join order – The order in which the tables are joined to each other.
• Partition pruning – Are only the necessary partitions being accessed to answer the query?
• Parallel Execution – In case of parallel execution, is each operation in the plan being
conducted in parallel? Is the right data redistribution method being used?
By reviewing the four key elements of: cardinality estimations, access methods, join methods, and join orders; you can determine if the execution plan is the best available plan. This white paper will help you, http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf