Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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
![]() |
![]() |