Re: Need some 10053 Guidance to help me solve a puzzler
Date: Thu, 11 Oct 2012 18:25:49 +0100
Message-ID: <99F42794613A4A199DC8801659D284EC_at_Primary>
You don't need to look at the 10053 to answer your question - the answer you need is in the execution plan.
The indexed access path shows Oracle estimating 159K rows at line 3
(driving the NL).
This gives an index access cost of 2 for each row (branch + leaf) plus two
more for the table (2 random rows for each driving row). 159K * 4 = 636K,
which the CBO treats as assumed random disk reads (there's a fairly obvious
algorithm error there relating to index caching, and a less obvious defect
relating to table caching).
The table scan path shows a cost of 165K which (combined with the reads of 682K) make me think the table is probably about 682K blocks and largely uncached, and that your system stats and db_file_multiblock_read_count are left at default.
The dramatic difference in cost between the 165K and the 636K is enough to overwhelm any other factor in the costing; and the fact that the table is nearly completely cached for the indexed access path gives you the vastly better time compared to the estimate.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: <Christopher.Taylor2_at_parallon.net> To: <oracle-l_at_freelists.org> Sent: Thursday, October 11, 2012 4:55 PM Subject: Need some 10053 Guidance to help me solve a puzzler
| 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
|
|
|
|
| -----
| No virus found in this message.
| Checked by AVG - www.avg.com
| Version: 2012.0.2221 / Virus Database: 2441/5324 - Release Date: 10/11/12
|
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 11 2012 - 19:25:49 CEST