Re: Ugh, sluggish query

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.

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.

--
Sybrand Bakker, Senior Oracle DBA