> On Jan 30, 1:16 pm, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > A lot of consistent gets, and likely also a lot of physical reads, and
> > 0 undo blocks.  That certainly is not what I expected.  The wait
> > events that you posted looked similar to the following:
> > WAIT #14: nam='db file sequential read' ela= 12382 file#=4
> > block#=1918069 blocks=1 obj#=92627 tim=3655578822

> > The above indicates that Oracle is performing a single block read in
> > file number 4 starting at block 1918069.  What object is at that
> > location?
> > SELECT
> >   DE.OWNER,
> >   DE.SEGMENT_NAME,
> >   DE.SEGMENT_TYPE,
> >   DE.TABLESPACE_NAME,
> >   DE.BLOCK_ID,
> >   DE.BLOCKSFROM
> >   DBA_EXTENTS DE
> > WHERE
> >   DE.FILE_ID= 4
> >   AND 1918069 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;

> > The DBMS Xplan is showing that Oracle is performing roughly 118,000
> > reads, while the plan that you originally posted indicated that Oracle
> > predicted that it would perform roughly 4 reads.  The DBMS XPlan is
> > also indicating that the query required nearly 15 minutes to execute,
> > rather than 0.01 seconds.  I wonder if the extra hints that you are
> > providing:
> >   ordered use_nl(events) index(events)
> > Are affecting whether or not Oracle is able to use the MIN/MAX
> > optimization, so instead of Oracle visiting 3 or 4 blocks, it is
> > actually scanning 118,000 blocks in the index?  Maybe you can try
> > flushing the shared pool and re-executing the query without the extra
> > hints.

> > To determine if system statistics have been gathered:
> > SELECT
> >   *
> > FROM
> >   SYS.AUX_STATS$;

> > If system statistics have not been gathered, there might be entries in
> > a couple of the statistics.  If system statistics have been gathered,
> > you will see entries for SREADTIM (single block read time) and
> > MREADTIM (multi-block read time).  Collect statistics when the system
> > is relatively busy.

> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.

> OK!!! Note - i've given up on masking the table/index/schema name,
> table events=packetlogs, column logdate=eventdate - i was trying to
> solve the problem generally, but now am getting lazy:

> SQL> SELECT
>   2    DE.OWNER,
>   3    DE.SEGMENT_NAME,
>   4    DE.SEGMENT_TYPE,
>   5    DE.TABLESPACE_NAME,
>   6    DE.BLOCK_ID,
>   7    DE.BLOCKS
>   8  FROM
>   9    DBA_EXTENTS DE
>  10  WHERE
>  11    DE.FILE_ID= 4
>  12    AND 1918069 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;

> OWNER
> ------------------------------
> SEGMENT_NAME
> --------------------------------------------------------------------------- ­-----
> SEGMENT_TYPE       TABLESPACE_NAME                  BLOCK_ID
> BLOCKS
> ------------------ ------------------------------ ----------
> ----------
> DSM_JAN08
> PACKETLOGS_LOGDATE
> INDEX              USERS                             1917321
> 1024

> So that is the spot in the index in question, as expected i guess.

> I did as you said, ran:
> ALTER SYSTEM FLUSH SHARED_POOL;

> Reran with only the gather statistics hint, and same result - its
> taking a darn long time.

> I selected system stats, its pretty sparse, those SREADTIM and
> MREADTIM are blank, and the one date is from 2005 (before this
> instance of oracle was installed!).  So i think my next step will be
> to gather system statistics, but i'm not clear on how that will help
> this one lowly query.  What hint can i give to tell it to NOT use the
> index?

> Looking in the console, this is what it says about the index:

> Last Analyzed           2008-01-30 10:02:07
> Blevel          3
> Distinct Keys           870
> Clustering Factor               455854
> Leaf Blocks             2947
> Average Leaf Blocks Per Key             3
> Average Data Blocks Per Key             523
> Number of Rows          523030
> Sample Size             523030