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

Ralph wrote:

> 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
>

I believe the values shown in v$sql is wrong. Check it out, start a fresh session,

freze a copy of v_$mystat, execute statement (more than only once) and check v_$mystat again. Calculate the resources used. Evaluate who is mr_right
PS: For double checking (++) use also v$sesstat, v$ses_io, v$sysstat, v$session_event and v$session_wait.

/Svend Jensen Received on Thu Feb 14 2002 - 13:11:24 CST