Re: Monster Update

I ran tkprof with EXPLAIN=USR/PWD

Matt

"DJ" <nospamplease_at_goaway.com> wrote in message news:<9hM3c.2900$vz.1261_at_newsfe3-win.server.ntli.net>...
> "Matt" <mccmx_at_hotmail.com> wrote in message
> news:cfee5bcf.0403100109.69a839d_at_posting.google.com...
> > Bit of a strange one this....
> >
> > SQLPLUS AUTOTRACE reports that the SELECT does and Index FFS, and the
> > UPDATE does an Index full scan...
> >
> > But Tkprof reports that the SELECT does an index FFS and the UPDATE
> > does a Full Table Scan....
> >
> > I was trusting the output from SQLPLUS autotrace....
> >
> > So it sounds like the update is full scanning the table.
> >
> > Incidentally, tkprof reports 2 plans, the ROW SOURCE OPERATION, and
> > EXECUTION PLAN..
> >
> > Which one does it actually use during the query.
> >
> > Thanks for you time
> >
> > Matt
> >
> > Daniel Morgan <damorgan_at_x.washington.edu> wrote in message
> news:<1078845040.615642_at_yasure>...
> > > Matt wrote:
> > >
> > > > I have an update statement which appears to be doing far more work
> > > > than required.
> > > >
> > > > The following statement hits the disk repeatedly (via multiblock read)
> > > > for an index fast full scan.
> > > >
> > > > UPDATE PS_TY_RECRUITMENT SET APP_DT = TO_DATE(SYSDATE,'YYYY-MM-DD')
> > > > WHERE APPLID = 'A0021198' AND APP_DT =
> > > > TO_DATE('2001-11-07','YYYY-MM-DD')
> > > >
> > > > The elapsed time is 30 seconds, of which over 17 seconds is spent
> > > > waiting on multiblock I/O.
> > > >
> > > > The strange part is that when I run a query which should effectively
> > > > perform the same lookup it returns instantly with no I/O wait
> > > > occuring.
> > > >
> > > > This is the SQL which returns immediately:
> > > >
> > > > SELECT APP_DT FROM PS_TY_RECRUITMENT
> > > > WHERE APPLID = 'A0021198' AND APP_DT =
> > > > TO_DATE('2001-11-07','YYYY-MM-DD')
> > > >
> > > > Both statements perform an index FFS so why does the update take so
> > > > much longer.....
> > > >
> > > > Any ideas would be greatly appreciated....
> > > >
> > > > Matt
> > >
> > > Run explain plan and a trace ... find out what is different.
>
> tkprof doesnt lie - how did you use it, with explain=y? That will tell you
> what it will do if you ran it now, tkprof on its own will tell you what it
> actually did.
Received on Thu Mar 11 2004 - 02:45:36 CST