RE: Need some 10053 Guidance to help me solve a puzzler
Date: Thu, 11 Oct 2012 11:34:01 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8851607BF19_at_NADCWPMSGCMS10.hca.corpad.net>
Also found this:
(Looks like because it CHOSE TableScan earlier in the process?)
Now joining: MON_ACCOUNT_PAYER_CALC_SERVICE[MAPCS]#3
NL Join
Outer table: Card: 162174.47 Cost: 6038811747.44 Resp: 6038811747.44 Degree: 1 Bytes: 45 Inner table: MON_ACCOUNT_PAYER_CALC_SERVICE Alias: MAPCS Access Path: TableScan
SM Join
Outer table:
resc: 6038811747.44 card 162174.47 bytes: 45 deg: 1 resp: 6038811747.44
Inner table: MON_ACCOUNT_PAYER_CALC_SERVICE Alias: MAPCS
HA Join
Outer table:
resc: 6038811747.44 card 162174.47 bytes: 45 deg: 1 resp: 6038811747.44
Inner table: MON_ACCOUNT_PAYER_CALC_SERVICE Alias: MAPCS
Best:: JoinMethod: Hash
Cost: 6039010867.83 Degree: 1 Resp: 6039010867.83 Card: 298800.96 Bytes: 72
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net
Sent: Thursday, October 11, 2012 11:17 AM
To: oracle-l_at_freelists.org
Subject: RE: Need some 10053 Guidance to help me solve a puzzler
Okay I found this in the 10053 trace without the index:
Looks like the Optimizer doesn't consider the PERF index at all really?
SINGLE TABLE ACCESS PATH
BEGIN Single Table Cardinality Estimation
Table: MON_ACCOUNT_PAYER_CALC_SERVICE Alias: MAPCS Card: Original: 18094159 Rounded: 18094159 Computed: 18094159.00 Non Adjusted: 18094159.00
END Single Table Cardinality Estimation
Access Path: TableScan
--> Cost: 165159.42 Resp: 165159.42 Degree: 0
Cost_io: 164769.00 Cost_cpu: 8801929733
Resp_io: 164769.00 Resp_cpu: 8801929733
******** Begin index join costing ********
- trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: IND_MODCOMP_06
resc_io: 117297.00 resc_cpu: 4454155348
ix_sel: 1 ix_sel_with_filters: 1
Cost: 117494.57 Resp: 117494.57 Degree: 0
Access Path: index (FullScan)
Index: MAPY_CALC_SVC_CESVCID
resc_io: 66218.00 resc_cpu: 4090399314
ix_sel: 1 ix_sel_with_filters: 1
Cost: 66399.43 Resp: 66399.43 Degree: 0
--> Access Path: index (FullScan)
--> Index: MAPY_CALC_SVC_PERF1
resc_io: 71352.00 resc_cpu: 4126960787 ix_sel: 1 ix_sel_with_filters: 1
--> Cost: 71535.06 Resp: 71535.06 Degree: 0
Access Path: index (FullScan) Index: MAPY_CALC_SVC_PK resc_io: 49418.00 resc_cpu: 3970759122 ix_sel: 1 ix_sel_with_filters: 1 Cost: 49594.13 Resp: 49594.13 Degree: 0 Access Path: index (FullScan) Index: MAPY_CALC_SVC_PK resc_io: 49418.00 resc_cpu: 3970759122 ix_sel: 1 ix_sel_with_filters: 1 Cost: 49594.13 Resp: 49594.13 Degree: 0 - finished trying bitmap/domain indexes ****** Access Path: index (FullScan) Index: IND_MODCOMP_06 resc_io: 117297.00 resc_cpu: 4454155348 ix_sel: 1 ix_sel_with_filters: 1 Cost: 117494.57 Resp: 117494.57 Degree: 0 ******** Cost index join ******** --> Index join: Considering index join to index MAPY_CALC_SVC_PK --> Index join: Joining index IND_MODCOMP_06 Ix HA Join Outer table: resc: 61992.66 card 18094159.00 bytes: 20 deg: 1 resp: 61992.66 Inner table: <no name> resc: 146868.21 card: 18094159.00 bytes: 27 deg: 1 resp: 146868.21 using dmeth: 2 #groups: 1 Cost per ptn: 61015.94 #ptns: 1 hash_area: 256 (max=128000) Hash join: Resc: 269876.81 Resp: 269876.81 [multiMatchCost=0.00] ******** Index join cost ******** --> Cost: 269876.81 ******** End index join costing ******** --> Best:: AccessPath: TableScan Cost: 165159.42 Degree: 1 Resp: 165159.42 Card: 18094159.00 Bytes: 0
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net
Sent: Thursday, October 11, 2012 10:56 AM
To: oracle-l_at_freelists.org
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 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 11 2012 - 18:34:01 CEST