Re: Ugh, sluggish query

Chuck schrieb:
> 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
>

You have got some advices you can surely apply, however, i'd rather suppose, you have some IO related problems. You have a table of 300 000 rows of length (in worst case) approximately 300 bytes. That means the table is nearly 90 Mb. Well , pctfree etc., say 100 Mb . The data you select is 10% of that size - 10 Mb. You are doing sequential reads ( as you don't have any appropriated index ) - the most performant kind of reads any disk can have. Usually you can read at least 50 Mb/s by simple IDE disks, i assume , you have SCSI disks in your raid 0+1, so you should have at least about 150 Mb/s throughput. That means the FTS on your table should take about 1s, you have factor 90.
Of course, you don't specify , how are you executing this query. For example on my P III with 1 Ide disk i could execute this query ( in a CREATE TABLE AS SELECT ) in 6 s. If you simply fetch your rows into ram , then some other factors can be significant - array size, amount of free memory on your machine etc . But query itself on the Oracle server should *not* run 90s (provided your table size and your hardware).

Best regards

Maxim Received on Fri Aug 05 2005 - 15:12:26 CDT