Re: Ugh, sluggish query

"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

Try creating an index on bu_date, analyze the table and then see what an explain plan shows for an access method.. Since the table grows by 30K records per day, you probably will want to analyze the table every night (or every other night) during your least busy time..schedule a dbms_job to do it.. so that the statistics are current for the CBO to use... Received on Fri Aug 05 2005 - 13:47:41 CDT