Odd Explain Plan output

From: <Jay.Miller_at_tdameritrade.com>
Date: Wed, 4 Feb 2009 13:00:36 -0500
Message-ID: <CA405610095C8F42B6FEBFAAA09A7A2A358E58_at_prdkcwsemlmb05.prod-am.ameritrade.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 04 2009 - 12:00:36 CST