Need some 10053 Guidance to help me solve a puzzler

From: <Christopher.Taylor2_at_parallon.net>
Date: Thu, 11 Oct 2012 10:55:44 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8851607BDEE_at_NADCWPMSGCMS10.hca.corpad.net>



Env:
Oracle 10.2.0.4
**My Goal: To understand WHY Oracle is opting for the FTS over an INDEX in this case. Why/where Oracle determines the _PERF index is not the best choice.**

I have a fairly simple query where the optimizer is choosing a FTS and returns a result in ~10 minutes. If specify an INDEX hint, the optimizer returns the result set in ~50 seconds.

Statistics are up to date (on the table in question) with: estimate_percent=>100
method_opt=>'FOR ALL COLUMNS SIZE AUTO'
cascade=>TRUE

I have captured 10053 traces for both with and without the index.

I'm using 10053 viewer from lab128 **however** I'm a newbie when dealing with 10053 traces for all intents and purposes.

I have captured 10046 traces for both.

When Oracle chooses the FTS, the xplan looks like this (A-Rows (197K) much less than A-Rows (18M) in FTS):


| Id  | Operation                  | Name                           | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
... TABLE ACCESS FULL | MON_ACCOUNT_PAYER_CALC_SERVICE | 1 | 18M| 465M| | 165K (1)| 00:33:02 | | | 18M|00:09:44.73 | 763K| 682K|
...

When I specify the index, the xplan looks like this:


| Id  | Operation                   | Name                           | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| MON_ACCOUNT_PAYER_CALC_SERVICE | 1 | 2 | 54 | | 4 (0)| 00:00:01 | | | 197K|00:00:33.22 | 518K| 13 |
...
...
|* 18 | INDEX RANGE SCAN | MAPY_CALC_SVC_PERF1 | 160K| 2 | | | 2 (0)| 00:00:01 | | | 197K|00:00:04.39 | 321K| 0 |

Below are links to the actual SQL with the full XPLAN outputs if you're interested/available to help - I'm not sure how to proceed with the 10053 output files to identify why Oracle doesn't use the _PERF index by default?

SQL without index hint and Plan:
https://gist.github.com/3873038

SQL with Index Hint and Plan:
https://gist.github.com/3873133

Regards,
Chris

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 11 2012 - 17:55:44 CEST

Original text of this message