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