Re: long running select min(timestamp) query

On Jan 30, 1:16 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

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

SEGMENT_TYPE       TABLESPACE_NAME                  BLOCK_ID

------------------ ------------------------------ ----------

INDEX              USERS                             1917321

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