On Jan 30, 1:16 pm, Charles Hooper <hooperc2
...@yahoo.com> wrote:
> On Jan 30, 12:24 pm, "bobdu
...@gmail.com" <bobdu
...@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
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
