> > > On Jan 29, 6:39 am, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:

> > > > On Jan 28, 7:17 pm, joel garry <joel-ga...@home.com> wrote:

> > > > > On Jan 28, 7:40 am, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:

> > > > > > Hi,

> > > > > > I have a basic event table in my 10g database, primary integer key,
> > > > > > and a nonnull timestamp "eventdate" field.  When executing the
> > > > > > following command:

> > > > > > select min(eventdate) from events;

> > > > > > It can occasionally take a VERY long time.  There is a standard btree
> > > > > > index on "eventdate", and roughly 20 other columns, a few of which
> > > > > > also have basic btree indexes.

> > > > > Well, I'm guessing that your 20 column index is just not the best one
> > > > > for your min.  What happens if you make a btree index on just that
> > > > > eventdate column?  It's been my experience that any table with more
> > > > > than a few columns in an index isn't properly normalized.

> > > > > There might be bug/patch issues, too.  timestamp is relatively new,
> > > > > and you are using an unpatched base release.

> > > > > How are you loading this table?  What is the exact definition of the
> > > > > index?

> > > > > jg
> > > > > --
> > > > > @home.com is bogus.http://www.spacefem.com/hydrox/

> > > > All the indexes on the table only span ONE column, not all!  Here's
> > > > the ddl (i've modified some names, none of the content has been
> > > > changed):

> > > Sorry 'bout that!

> > > Charles has bought up a good point about concurrency, and it makes me
> > > wonder if you've done mass deletes on this table.  Have you tried
> > > recreating the index?  (See Richard Foote's blog about indices, the
> > > general advice is you don't need to rebuild indices, but there are
> > > certain odd situations which you can and should check for if you are
> > > seeing noticeable issues.  I'm wondering if you've made a realllllly
> > > sparse index, and why Oracle wouldn't be smart enough to find the min
> > > right away.).

> > > I'm also idly wondering if delayed block cleanout can be involved
> > > (google for details).  Postulating a mass delete, followed by a simple
> > > inquiry that has to traverse all the deleted blocks and write them
> > > out.  If you are getting consistent results doing Charles' traces, it
> > > wouldn't be this.  But I'm focused on the description of "It can
> > > occasionally take a VERY long time" and am wondering what is different
> > > at those occasions.  Have you checked wait states at those times?
> > > Maybe there's more than one thing wrong here - for example, slow disks
> > > plus disks overwhelmed with with I/O while archiving logs plus
> > > concurrency issues.  Also, I have a vague thought of some process like
> > > MMON going nuts, are you seeing unexplained extremely high processor
> > > usage at times?

> > > > CREATE TABLE "MYSCHEMA"."EVENTS"
> > > > ( "EVENTID" NUMBER,
> > > > "EVENTDATE" TIMESTAMP(6) NOT NULL ,
> > > > "HOSTID" NUMBER NOT NULL ,
> > > > "DIRECTION" NUMBER NOT NULL ,
> > > > "INTERFACE" NVARCHAR2(17),
> > > > "FRAME" NUMBER NOT NULL ,
> > > > "SOURCEMAC" NVARCHAR2(17),
> > > > "DESTINATIONMAC" NVARCHAR2(17),
> > > > "PROTOCOL" NUMBER NOT NULL ,
> > > > "FLAGS" NVARCHAR2(64),
> > > > "SOURCEIP" NVARCHAR2(15),
> > > > "SOURCEPORT" NUMBER NOT NULL ,
> > > > "DESTINATIONIP" NVARCHAR2(15),
> > > > "DESTINATIONPORT" NUMBER NOT NULL ,
> > > > "PACKETSIZE" NUMBER NOT NULL ,
> > > > "TYPE" NUMBER NOT NULL ,
> > > > "ACTION" NUMBER NOT NULL ,
> > > > "ITEMID" NUMBER,
> > > > "REPEATCOUNT" NUMBER NOT NULL ,
> > > > "ENDEVENTDATE" TIMESTAMP(6),
> > > > "RANK" NUMBER,
> > > > "HOSTASSETVALUE" NUMBER(5),
> > > > "FILTERSEVERITYVALUE" NUMBER(5),
> > > > PRIMARY KEY ("EVENTID") VALIDATE )
> > > > TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE
> > > > ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING

> > > > CREATE INDEX "MYSCHEMA"."EVENTS_EVENTDATE" ON
> > > > "MYSCHEMA"."EVENTS" ("EVENTDATE") TABLESPACE "USERS" PCTFREE 10
> > > > INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT)
> > > > LOGGING LOCAL

> > > > Then there's a bunch of other indexes, 8 total, with the one above and
> > > > the one PK, 6 more look similar to the one below:

> > > > CREATE INDEX "MYSCHEMA"."EVENTS_ACTION" ON
> > > > "MYSCHEMA"."EVENTS" ("ACTION") TABLESPACE "USERS" PCTFREE 10 INITRANS
> > > > 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
> > > > LOCAL

> > > jg
> > > --
> > > @home.com is bogus.
> > > "Weather forecasts are horoscopes with numbers."

> > I definitely have done some large scale deletes.  When i say
> > occasionally, there have been times when the query has been fast, but
> > it hasn't been fast in a long time...  likely since the last large
> > scale delete operation actually.  The CPU never spikes on the oracle
> > box, its definitely always blocking on IO.  Mind you, the disk is
> > slow, but since it is occasionally speedy, i can't blame it all on
> > disk (can i?).

> Sure, when all else fails blame the hardware!  :-)  (There are people
> who don't tune and just throw hardware at it - I've done that myself,
> it can work - but it is generally stupid without figuring out what is
> really going on).  I have seen failing hardware be slow and sometimes
> fast.

> I think we've ruled out the delayed block cleanout issue if it hasn't
> been fast in a long time, that would be an occasional thing, anything
> that touches all blocks, like getting stats without sampling, would
> clean it out.  And we've ruled out chaining.

> So:  http://richardfoote.wordpress.com/2007/12/11/index-internals-rebuildi...

> Also, please post your top waits, and continue on the path Charles has
> started, it may very well also lead to the solution(s).  I/O binding
> can be caused by many things, from actually needing lots of data to
> tuning to bad code.  It's most often bad code.  Mass deletes can be
> done many ways, some of which may be considered bad at times.  Searchhttp://asktom.oracle.comfor mass delete.  Sometimes a better way is
> to create a new table with what you want to keep, then rename.

> > I just tried to get more stats via the following call, and then
> > checked again for chained rows (not sure if it would've affected, but
> > tried anyways):

> > SQL> exec
> > DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'EVENTS,CASCADE=>TRUE) ­­;

> > PL/SQL procedure successfully completed.

> > SQL> analyze table events list chained rows;

> > Table analyzed.

> > SQL> select owner_name, table_name, count(*)
> >   2  from chained_rows
> >   3  group by owner_name, table_name;

> > no rows selected

> jg
> --
> @home.com is bogus.http://www.signonsandiego.com/uniontrib/20080129/news_1b29lazar.html- Hide quoted text -