You have 2 different statements here --one has a literal value and the
other a bind variable. It is expected that the explain plans could be
different for each (since the optimizer has less to go on using the
bind variable). Verify first that the explain plans are the same or
different.
rlro99_at_hotmail.com (Ralph) 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 - 08:11:32 CST
