Re: Same index, different plan

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: paul.baumgartel@xxxxxxxxxxxxxxxxx, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 05 Apr 2008 00:22:53 +0800
Possibly the last time statistics were gathered, the "high value" for the column 
(HIGH_VALUE in USER_TAB_COLUMNS)  was lesser than today's SYSDATE
so Oracle thought a Fast Full Scan would suffice.
However, when you Hint to Oracle to use the Index, Oracle now believes that
it has to do a Range Scan ?

Hemant K Chitale
http://hemantoracledba.blogspot.com

"There is more to life than increasing its speed."
Mohandas Gandhi Quotes : http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html 

At 10:27 PM Friday, Baumgartel, Paul wrote:
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@xxxxxxxxxxxxxxxxx
<file://www.credit-suisse.com>www.credit-suisse.com





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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2008