Ugh, sluggish query

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 Received on Fri Aug 05 2005 - 13:27:03 CDT