Re: long running select min(timestamp) query

On Jan 29, 6:39 am, "bobdu..._at_gmail.com" <bobdu..._at_gmail.com> wrote:
> On Jan 28, 7:17 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
>
>
> > On Jan 28, 7:40 am, "bobdu..._at_gmail.com" <bobdu..._at_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."