Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Cant figure out why query is so poor, or is it?

Cant figure out why query is so poor, or is it?

From: Ralph <rlro99_at_hotmail.com>
Date: 14 Feb 2002 02:34:09 -0800
Message-ID: <e2c49cae.0202140234.3d6342b9@posting.google.com>


Oracle 8.1.6 Sun Sparc Solaris 2.7

We have a query runing in our production database that is showing an average
number of buffer gets per execution of 156. We want to tune this query but when
we do "set autotrace on" it shows only 3 consistant gets -- the query is so
basic, this is what you would expect. The only trigger on the table is for the insert of new rows. I am stuck for a reason why this is happening and therefore how to tune it. Here are the facts...

1 delete from premium_risk_split
2* where policy_line_seq_no = 137009
PRODCOPY>/ 3 rows deleted.

Execution Plan



0 DELETE STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=28) 1 0 DELETE OF 'PREMIUM_RISK_SPLIT'
2 1 INDEX (RANGE SCAN) OF 'T1015_UDX' (UNIQUE) (Cost=3 Card= 4 Bytes=28)

Statistics



0 recursive calls
6 db block gets
3 consistent gets
1 physical reads
1100 redo size
284 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
3 rows processed

Which is what you would expect however.... SQL_TEXT



EXECUTIONS BUFFER_GETS BUFFER_GETS/EXECUTIONS
---------- ----------- ----------------------
DELETE FROM PREMIUM_RISK_SPLIT WHERE POLICY_LINE_SEQ_NO = :b1 129787 20354790 156.83227
This is not the result you would expect if a full table scan was being carried
out either as there are 51000 blocks in the table.

Any ideas?

Thanks

Ralph Received on Thu Feb 14 2002 - 04:34:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US