Re: Slow SQL, too many logical reads ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 10 May 2007 16:46:14 +0100
Message-ID: <jLudnTvOJpwwo97bRVnyuAA@bt.com>

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1178624112.038988.189110_at_w5g2000hsg.googlegroups.com... TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

============================== =============== ===============
==============
T1        ENTITY_ID      0    2.54422546
T1        ENTITY_ID      1    2.54422546
T1        INVOICE_DATE      0       2451230
T1        INVOICE_DATE      1       2454229
T1        INVOICE_ID      0    2.50207622
T1        INVOICE_ID      1    2.50268469

It does appear that there is a histogram on the ENTITY_ID column.

Parameters from the 10053 trace on Oracle 10.2.0.2 (32 bit Windows): PARAMETERS WITH ALTERED VALUES

  optimizer_features_enable           = 10.2.0.1
  sort_area_size                      = 5242880
  sort_area_retained_size             = 262144

Charles,

I couldn't see any obvious reason from the parameters why your 10.2.0.2 should behave differently.

There isn't a histogram on entity_id in your output. A "histogram" with endpoint number (0,1) is simply recording the low/high - it's not a real histogram.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Thu May 10 2007 - 10:46:14 CDT