Re: long running select min(timestamp) query

On Jan 29, 2:53 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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."

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 Received on Tue Jan 29 2008 - 15:36:01 CST