I have a rather trivial cleanup deletion which goes so:
DELETE FROM af_route WHERE jobno = xyz;
where xyz is a number. I'll comment exhaustively about this table
later to avoid additional questions since the problem doesn't have
anything to do with the structure of the table in my opinion.
When executed from the command line of an sqlplus session the
job of removing some 70,000 rows from this table takes about
20 seconds. However if the same statement is executed within a
stored procedure it takes 200 seconds. I've traced the execution
and analyzed the results and simply can't understand why it does
so much more work within a stored procedure than necessary.
The execution plan is based on a range scan of index on "jobno"
in both cases. Here are the numbers from the profile:
sqlplus St.Proc.
5.07 33.89 CPU time consumed
21.11 228.49 elapsed time
14,886 43,198 disk accesses
3,226 14,923 query
115,771 1,109,786 current gets
68568 68568 rows processed
12,465 43,198 times waited for sequential reads
0.27 1.72 max time waited
13.76 191.03 total time waited
As you can see the CPU does about 6 times more actual work,
elapsed time is 10 times longer, there are 3 times as many disk
accesses, query count is about 4 times higher but current gets are
10 times more than necessary. Most of the elapsed time
differential can be easily explained by longer I/O waits but what
I would like to know is: Why it does so much more work when
it is executed from within a stored procedure than when it is
executed from sqlplus command line?
I've even tested EXECUTE IMMEDIATE of the above string within
the stored procedure with number "xyz" as "TO_CHAR(xyz)" but
it made no difference whatsoever.
The database is Oracle9i Enterprise Edition Release 9.2.0.1.0
running on a 2 GHz Pentium with 1.5 GB RAM, 5 separate
dedicated S-ATA storage devices. Indexes, tables, redo-logs
(even though the table and all indexes are NOLOGGING),
SYSTEM and UNDO tablespaces are all on their own media.
There are about 8 million rows in the table and each "jobno"
qualifies about 70,000 rows for deletion. The table has 10 fields
of which 7 are really foreign keys uniquely identifying each row
so that these 7 keys together are the primary key for the table,
but only 3 of them are enforced as foreign keys in respective
constraints. They are also individually indexed plus the index on
"jobno".
In order to improve the performance, the indexes have been
moved to a separate physical device and both the table and
all the indexes have been rebuilt in such a way that they
occupy consecutive extents in the tablespace, 123 extents,
82048 8k blocks for the table, 107 extents, 65664 blocks for
the primary uniqe index, 75 extents 32896 blocks for the other
indexes. The table and each of the indexes are about 40%
empty, PCTFREE is 30, INITRANS is 4, because it happened
often in the past that a clean-up job, of which this deletion is
a part, runs into an Oracle error that the transaction could not
be serialized and was rolled back so one of the suggestions in
the cook books was increase INITRANS and/or PCTFREE. That
did not save the day. Splitting of resources over more disks
did but it's still a puzzle and a big bother that it takes so long
from within a stored procedure.
Can anyone help?
