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

Svend Jensen <Master_at_OracleCare.Com> wrote in message news:<3C6C0BDC.4000605_at_OracleCare.Com>...
> 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

I am rapidly coming to the same conclusion. There is no cursor_sharing , and the statement both with and without Binds generates the same explain plan.

I have taken a copy of the prodcution system and executed the same peice of code 20000 times and the average gets/execution is only 15. Our prod instance gets bounced on a sunday morning so I will keep a close eye on the stats next week.

I have done the requested work with v$mystat 20 executions of the statement causes an increase of exaxctly 60 consistant gets!

So yeah mr_right is the plan and mr_wrong is V$sql. Anybody got an explanation? Received on Fri Feb 15 2002 - 08:31:45 CST