Re: Ugh, sluggish query

Sybrand Bakker wrote:
> On 5 Aug 2005 11:27:03 -0700, "Chuck" <chuck.carson_at_gmail.com> wrote:
>
> >
> >I have the following table. Currently this table has about 300k rows
> >and grows by 30k each day. The only constraint on this table is bu_id,
> >which is a primary key.
> >
> >SQL> desc dm.bu_notify;
> > Name Null? Type
> > ----------------------------------------- --------
> >----------------------------
> > BU_ID NOT NULL NUMBER(16)
> > BU_SERVER NOT NULL VARCHAR2(12)
> > BU_HOST NOT NULL VARCHAR2(32)
> > BU_CLASS NOT NULL VARCHAR2(64)
> > BU_DATE NOT NULL DATE
> > BU_DURATION NOT NULL NUMBER(10)
> > BU_SIZE NOT NULL NUMBER(10)
> > BU_STREAM NOT NULL NUMBER(3)
> > BU_STATUS NOT NULL NUMBER(3)
> > BU_MEDIA_USED NOT NULL VARCHAR2(128)
> > BU_SCHED NOT NULL VARCHAR2(16)
> > BU_TYPE NOT NULL VARCHAR2(10)
> > BU_RETENTION NOT NULL NUMBER(3)
> >
> >The host is a 8 cpu / 8gb ram Sun system running Solaris 9 .(sparc II
> >based) Database is oracle 10.1.0.3. The tablespace this table lives in
> >is on it's own raid 0+1 volume consisting of 6 disks. (3 + 3 for
> >mirror)
> >
> >The following query runs sluggish: (grab entries for last 24 hours)
> >SELECT bu_class, to_char(bu_date, 'MM-DD-YY HH24:MI'), bu_duration,
> >bu_host, bu_retention, bu_sched, bu_server, bu_size, bu_status,
> >bu_stream, bu_type FROM DM.BU_NOTIFY WHERE bu_date > (SYSDATE - 1)
> >ORDER BY bu_date DESC
> >
> >is there anything obvious I can do to improve the performance? Im not a
> >SQL expert or anything, actually an SA wirting some network management
> >applications.
> >
> >While this query is running, I can see no visible bottlenecks in system
> >resources, disk i/o is not maxed, cpu/memory are not maxed. There is
> >nothing else contending with this query, this is the first heavily hit
> >piece of the database. I tried playing around with parrallelization but
> >only made the query slower.
> >
> >My sga:
> >SQL> show sga
> >
> >Total System Global Area 801112064 bytes
> >Fixed Size 1304472 bytes
> >Variable Size 260839528 bytes
> >Database Buffers 536870912 bytes
> >Redo Buffers 2097152 bytes
> >
> >I'm sure there is some SGA tuning needed but can't imagine it would
> >give me the order of magnitude improvement I need here. (since there
> >isn't anything else in the database to contend with this query)
> >
> >Currently this query takes about 90 seconds when ran from a sqlplus
> >session on the same host as the db. This particuliar query returns
> >about 30k rows, +/- 1000.
> >
> >Thanks for any tips,
> >CC
>
> SGA tuning seems to be mandatory, however to trim things down. You
> have a buffer cache of 536 M. At this size, this is going to shoot you
> in the foot, as in order to find a free block, Oracle needs to
> traverse the buffer cache by means of a hash structure, the size of
> the hash structure being proportional to the size of the buffer cache.

Also, there are bugs regarding the hash structure and buffer cache. In particular, bug 3611471 is worked around by making the cache smaller. Chuck, consider a patch upwards.

But don't do any tuning until you listen to Sybrand, I'll bet any noticeable problem will go away when you fix this:

>
> The biggest problem here is you have a fact table without any
> *appropiate* indexes. An index on bu_date is almost mandatory, to
> prevent the full table scans you are now experiencing.
> As the index likely grows at one end, this probably should be a
> reverse key index.
> You don't specify any parameter like pga_aggregate_target_size. You
> might be hit from inefficient sort operations, but due to lack of
> details no advice on that is possible.
>

Unless of course your pga_aggregate_target_size is way off or hitting some bug (which would cause obvious swapping). Seems like an awful lot of box for not much database. Is there something else running you are not telling us?

jg

--
@home.com is bogus.
http://research.microsoft.com/~gbell/Digital/DECMuseum.htm