RE: Same index, different plan

What column(s) is T_DMS_DEAL_SECURITY_XREF_H_AK1 defined on? Have you collected system statistics?
Do you have db_file_multiblock_read_count set? To what value? Are stats on the table and index up to date?

-Mark

--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059
mark.bobak_at_proquest.com<mailto:mark.bobak_at_il.proquest.com>
www.proquest.com<http://www.proquest.com>
www.csa.com<http://www.csa.com>

ProQuest...Start here.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Baumgartel, Paul
Sent: Friday, April 04, 2008 10:27 AM
To: oracle-l
Subject: Same index, different plan


A colleague is asking for help with a 10.2.0.3 (Sun Solaris 8) optimizer issue.  A unhinted query

SELECT max(history_timestamp) AS MaxStamp
FROM dmsdb.t_dms_deal_security_xref_h
WHERE  history_timestamp<sysdate+1 AND eod_snapshot='N';

yields the plan

---------------------------------------------------------------------------------------------


| Id  | Operation             | Name                           | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT      |                                |     1 |    13 | 86560  (18)|

|   1 |  SORT AGGREGATE       |                                |     1 |    13 |            |

|   2 |   INDEX FAST FULL SCAN| T_DMS_DEAL_SECURITY_XREF_H_AK1 |    94M|  1168M| 86560  (18)|

---------------------------------------------------------------------------------------------

and runs in about 5 minutes.

However, the same query, with a hint specifying the index that's being used for the non-hinted version, yields

-----------------------------------------------------------------------------------------


| Id  | Operation         | Name                           | Rows  | Bytes | Cost (%CPU)|

-----------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT  |                                |     1 |    13 |   330K  (1)|

|   1 |  SORT AGGREGATE   |                                |     1 |    13 |            |

|   2 |   INDEX RANGE SCAN| T_DMS_DEAL_SECURITY_XREF_H_AK1 |    94M|  1168M|   330K  (1)|

-----------------------------------------------------------------------------------------

and runs in about 3 minutes.  Note that the access path has changed from fast full scan to range scan, and note the higher cost of the ran

Why would hinting to use an index that's being used anyway change the access path from fast full scan to range scan?


Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com<file:///\\www.credit-suisse.com>


==============================================================================

Please access the attached hyperlink for an important electronic communications disclaimer:



http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

==============================================================================

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