CURSOR_SHARING wouldn't happen to be set to FORCE would it? And how skewed
is the data?
Regards
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================
"Ralph" <rlro99_at_hotmail.com> wrote in message
news:e2c49cae.0202140234.3d6342b9_at_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 - 13:11:35 CST
