Re: long running select min(timestamp) query

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 30 Jan 2008 10:16:13 -0800 (PST)
Message-ID: <99b034c1-2395-4ae2-a018-d54b83122bc1@n20g2000hsh.googlegroups.com>

On Jan 30, 12:24 pm, "bobdu..._at_gmail.com" <bobdu..._at_gmail.com> wrote:
> I performed this test, heres what i got:
>
> BEFORE:
> NAME
> VALUE
> ----------------------------------------------------------------
> ----------
> consistent gets
> 1656576
> consistent
> changes                                                       49
> data blocks consistent reads - undo records
> applied                      43
> cleanouts and rollbacks - consistent read
> gets                            0
>
> AFTER:
> NAME
> VALUE
> ----------------------------------------------------------------
> ----------
> consistent gets
> 1775792
> consistent
> changes                                                       49
> data blocks consistent reads - undo records
> applied                      43
> cleanouts and rollbacks - consistent read
> gets                            0
>
> DIFF:
> NAME
> VALUE
> ----------------------------------------------------------------
> ----------
> consistent gets
> 119216
> consistent
> changes                                                       0
> data blocks consistent reads - undo records
> applied                      0
> cleanouts and rollbacks - consistent read
> gets                            0
>
> Looks like a lot of consistent gets...

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. Received on Wed Jan 30 2008 - 12:16:13 CST