> > 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."