> > 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?).

> 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