You want to know how much pain you are going to inflict on the server and network before you run that ad hoc query? An explain plan can give you an idea of how long it might take to run, and tell you how much data it thinks will be involved.
Cardinality – Fancy Word, Simple Concept
From Wikipedia: “In mathematics, the cardinality of a set is a measure of the “number of elements of the set”.
Relational databases, as described by God…err, I mean Codd, are based on mathematics. Learning to think in sets allows you to excel at SQL. But before you fall asleep, all you need to know is that the cardinality of a plan tells you how many ROWS the database THINKS will be returned or processed by your query!
So:
1: Write Query
B: Explain Query
III: Look at cardinality.
The Code
[sql]select * from scott.emp;[/sql]
The Plan
Is it Right?
Maybe it is, maybe it isn’t. The cardinality is dependent on the statistics available for the underlying tables in the query. If your statistics are stale or missing, then all bets are off. Of course in 11g, the database optimizer is apparently intelligent enough to detect this and automatically fix it, but “insert big butt clause here” _________.
In my case, I run the query, count the rows, and it matches! 244 Cardinality = 244 rows returned.
Statistics Can Lie, Lie, Lie
AskTom sums it up perfectly right here!
“If the cardinality is way off — it can be disasterous,…”
Read the full question and answer here.
If you have not bookmarked ‘AskTom’ yet, then please turn in your license to drive the Internet.
Oracle Can Show You Estimated vs Actual Cardinality for a Query While It’s Running
Yeah, I talked about that in an earlier post. Here’s the picture summary:
You can learn more about this Diagnostic/Tuning pack feature here.
3 Comments
Hi Jeff,
I need some clarification regarding Estimated rows vs Actual rows.
Case 1: Estimated rows =1000. Actual rows =100000
Case 2: Estimated rows=100000 Actual rows =1000
What should be our action in above cases?
In my opinion, case 2 will run fine because we have gathered statistics for 100000 rows so optimizer will do just fine. But in case 1, statistics are stale and we need to gather statistics? Please clarify. Thanks.
look at the plans – if the optimizer is seeing there 1000 rows vs 100000 and it goes a nested loops vs a hash join – AND you’re not happy with the performance of your query, update your statistics
if you’re using AutoTrace in SQL Developer – make sure you have preferences enabled to fetch all rows so the actual number is accurate
Pingback: Getting Your Execution Plan, The Hard Way