Re: long running select min(timestamp) query

On Jan 30, 1:41 pm, "bobdu..._at_gmail.com" <bobdu..._at_gmail.com> wrote:
> On Jan 30, 1:16 pm, Charles Hooper <hooperc2..._at_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

No problem with masking the table and index names if needed.

A minor correction to a previous statement to avoid confusion with table or index statistics:
Collect <system> statistics when the system is relatively busy.

System statistics will help Oracle determine the correct (optimal) execution plan based on the actual performance characteristics of the server. As best I can tell, Oracle is coming up with the best plan, but is apparently not using the MIN/MAX optimization.

To prevent the use of the one index currently being used: /*+ NO_INDEX(packetlogs PACKETLOGS_LOGDATE) */

Do not use any index:
/*+ FULL(packetlogs) */

You can forward the 12MB trace file to my Yahoo email address, and I will try to pass it through a program I wrote for analysis.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Jan 30 2008 - 13:01:12 CST