Re: bad SQL, naughty SQL....

jason_at_seahorseNOSPAM.demon.co.uk (Jason Salter) writes:

> I've been looking into performance problems with a purchase orders
> package that my company uses.
>
> They run an end-of-period program that has taken on average 24-30
> hours to complete. During my investigation I found the usual stuff;
> redo logs too small thus archiving too often, block buffers too small,
> hot spots on discs etc etc. I managed to fix all of these and halved
> the time it took to run the EOP. But, twelve hours still seems a lot.
> So I started looking at what SQL was being performed by the EOP job.
>
> I've managed to track down the most offensive piece of SQL I've ever
> seen. Consider this :-
>
> Table A has 110,000 rows
> Table B has 120,000 rows
>
> To clear down purchase orders over a certain age this piece of SQL is
> used :-
>
> delete from tablea
> where rowid NOT in ( select rowid from tableb );
>
> By my calculations this is performing over 12 billion comparisons.
> I also noticed that during this time, the wait event 'db sequential
> file read' in v$session_wait, although this was the only process
> running on the server at that time. I must add that the system it runs
> on is an 4 processor, 3 Gb IBM SPH50 AIX box. As far as I could see
> there was no other process/session contending for disc IO's.
>
> Does anyone know if this wait happens when large amounts of CPU
> intensive SQL is being performed in memory?
>
> Also, anyone have any ideas how this statement can be re-written to
> make it more efficient?
>
> Regards,
> Jason.

The rowid stuff seems very strange. If what you are really trying to accomplish is deleting rows based on primary key, then search the documentation for antijoin. Oracle has some optimizations for:

where <something> not in ( select <something> from some_table )

You may be better off changing your approach rather than trying to optimize this for large tables.

--
Kelly R. Burkhart
kburk_at_sky.net

Just enjoy the Ozzy and keep your mouth shut

• George Plimpton (on SNL)