| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Help me read my 10053 trace file
Shao:
I think your program has become a victim of the new costing model for bitmap indexes which was introduced in 8.0.6. Basically the new costing model assumes the 80% of the rows are costed in the old model and 20% of the rows are costed as they are spread across ALL blocks.
Let us assume you have a table with 100 blocks and each block contains 10 rows. In total you have 1000 rows and if your result set finds 100 blocks, the cost is calculated as 100/10=10 blocks. So while costing the bit map access cost, it just adds the cost for 10 blocks with the bitmap index access cost.
In the new costing model, it assumes the 80% of the blocks are accessed in the older model and the rest of the 20% blocks are spread across the __ENTIRE__ table (with the absence of the partition knowledge) and costs the table access costs accordingly.
You can use the event 10170 to tell the CBO to use the old costing model which works best for partition tables.
(In the above said example the new costing mode will say the table access cost as 28 blocks instead 10 blocks)
Please correct me (Esp:Jonathan Lewis) if I am wrong !!!
Best Regards,
K Gopalakrishnan
Bangalore, INDIA
-----Original Message-----
Chunning
Sent: Thursday, November 21, 2002 8:59 AM
To: Multiple recipients of list ORACLE-L
I have a query in which the predictate is like this: WHERE ln.metro_id = i_metro_id AND
l.metro_id = :b1 ANd
eld.metro_id(+) = :b1 AND
ln.status_desc = 'ACTIVE' AND
ln.daset_id = ld.daset_id (+) AND
ln.sect_id = ld.sect_id (+) AND
ln.life_id = ld.life_id (+) AND
ln.version_id = ld.version_id (+) AND
ln.daset_id = l.daset_id AND
ln.sect_id = l.sect_id AND
ln.life_id = l.life_id AND
ln.version_id = l.version_id AND
l.status_desc = 'ACTIVE' AND
l.ramp = 'Y' AND
l.daset_id = eld.daset_id (+) AND
l.sect_id = eld.sect_id (+) AND
l.life_id = eld.life_id (+) AND
l.version_id = eld.version_id (+) AND
eld.life_id IS NULL
If the table life is analyzed, the bitmap index is not used, and the optimizer choose a full table scan, and it take hours to run. But if the table life is not analyzed, bitmap index is used, and the query is takes only 1 minutes.
Here is some related info from event 10053
With table life ANALYZED
Column: METRO_ID Col#: 5 Table: LIFE Alias: L
NDV: 15 NULLS: 0 DENS: 6.6667e-02 LO: 1 HI: 48
Column: STATUS_DES Col#: 16 Table: LIFE Alias: L
NDV: 1 NULLS: 0 DENS: 1.0000e+00
Column: RAMP Col#: 6 Table: LIFE Alias: L
NDV: 2 NULLS: 0 DENS: 5.0000e-01
TABLE: LIFE ORIG CDN: 5485935 CMPTD CDN: 182865
Access path: tsc Resc: 1190 Resp: 1190
Access path: index (equal)
INDEX#: 3906609 TABLE: LIFE
CST: 134 IXSEL: 5.0000e-01 TBSEL: 5.0000e-01
******** Bitmap access path rejected ********
Cost: 5223 Selectivity: 0
With table LIFE not analyzed
...................
Omitted here for space saving
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 1 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 2 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 3 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 4 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 5 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 6 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 7 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 8 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 9 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 10 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 11 Table: LIFE Alias: L
NO STATISTICS (using defaults)
....................... omited some lines here for space saving
Column: RAMP Col#: 6 Part#: 16 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: RAMP Col#: 6 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 1177 NULLS: 0 DENS: 8.4966e-04
TABLE: LIFE ORIG CDN: 640261 CMPTD CDN: 1
Access path: tsc Resc: 1190 Resp: 1190
Access path: index (stp-guess)
INDEX#: 3906609 TABLE: LIFE
CST: 1 IXSEL: 1.0000e-04 TBSEL: 1.6000e-05
******** Bitmap access path accepted ********
Cost: 16 Selectivity: 0
Can somebody tell me, how does Oracle get the bitmap index cost of 5223 when table LIFE is analyzed, and cost of 16 when is not?
Thanks
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shao, Chunning INET: cshao_at_traffic.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Nov 22 2002 - 11:44:21 CST
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: K Gopalakrishnan INET: kaygopal_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |