RE: Odd Explain Plan output
- From: "Kenneth Naim" <kennaim@xxxxxxxxx>
- To: <Jay.Miller@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Feb 2009 13:49:03 -0500
The optimizer expects one row to be returned for each of 545k loops, so that
may be your issue, if the same data is fast and performing fewer loops.
Ken
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Wednesday, February 04, 2009 1:01 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Odd Explain Plan output
While investigating a process that's running longer than usual I came
across something I don't recall seeing before.
There's a SQL query that's part of a loop which if run with sample data
returns in less than .01 seconds. The access path is the same for both
real numbers and bind variables but the estimated rows returned is much
higher for the bind variables (I realize that bind variable peeking can
result in different access paths).
What I don't understand is that the estimate for the inner portion of
the access path is only 1 row but when it gets to the Nested Loops
portion it jumps to 545K rows and then 3M rows for the table access by
rowid. Since the access path is the same I'd guess that this isn't the
source of the performance problem but it's the only anomaly I've found
so far and I'd sort of like to understand it. How can the optimizer
think that the one row returned from the first table does a nested loop
to get one row from the second table and then returns 3 million rows?
Is this as odd as I think or do I have some fundamental misunderstanding
in readign the output?
This is 10.2.0.3 on Solaris 8.
With bind variables:
SQL> explain plan for
SELECT sum(end_liquidation_bal), sum(closed_accts)
FROM ci_dm.fact_acct_kpis_week a, ci_dm.load_dim_account_status b
WHERE b.client_id = :b3
AND fiscal_week_id = :b2
AND brand_id = :b1
AND a.account_id = b.account_id
GROUP BY client_id, fiscal_week_id, brand_id;
------------------------------------------------------------------------
--------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------
--------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 38 | 17 (0)| | |
| 1 | SORT GROUP BY NOSORT |
| 1 | 38 | 17 (0)| | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_ACCT_KPIS_WEEK
| 3002K| 60M| 13 (0)| | |
| 3 | NESTED LOOPS |
| 545K| 19M| 17 (0)| | |
| 4 | TABLE ACCESS BY INDEX ROWID | LOAD_DIM_ACCOUNT_STATUS
| 1 | 17 | 4 (0)| | |
| 5 | INDEX RANGE SCAN |
LOAD_DIM_ACCOUNT_STATUS_I_CLIE | 1 | | 3 (0)| |
|
| 6 | PARTITION RANGE ALL |
| 1 | | 12 (0)| 1 | 6 |
| 7 | INDEX RANGE SCAN | PK_ACCT_KPIS_WEEK
| 1 | | 12 (0)| 1 | 6 |
------------------------------------------------------------------------
--------------------------------------------------
With real numbers (this is what I'd expect to see):
SQL> explain plan for
SELECT sum(end_liquidation_bal), sum(closed_accts)
FROM ci_dm.fact_acct_kpis_week a, ci_dm.load_dim_account_status b
WHERE b.client_id = 48742
AND fiscal_week_id = 20090404
AND brand_id = 671
AND a.account_id = b.account_id
GROUP BY client_id, fiscal_week_id, brand_id;
------------------------------------------------------------------------
--------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------
--------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 38 | 17 (0)| | |
| 1 | SORT GROUP BY NOSORT |
| 1 | 38 | 17 (0)| | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_ACCT_KPIS_WEEK
| 2 | 42 | 13 (0)| | |
| 3 | NESTED LOOPS |
| 1 | 38 | 17 (0)| | |
| 4 | TABLE ACCESS BY INDEX ROWID | LOAD_DIM_ACCOUNT_STATUS
| 1 | 17 | 4 (0)| | |
| 5 | INDEX RANGE SCAN |
LOAD_DIM_ACCOUNT_STATUS_I_CLIE | 1 | | 3 (0)| |
|
| 6 | PARTITION RANGE ALL |
| 1 | | 12 (0)| 1 | 6 |
| 7 | INDEX RANGE SCAN | PK_ACCT_KPIS_WEEK
| 1 | | 12 (0)| 1 | 6 |
------------------------------------------------------------------------
--------------------------------------------------
Thanks,
Jay Miller
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
