array fetching for the SELEC T but not array
processing for the UPDATE. I need to revise
the code to do explicit array processing. As
a more concrete example, let’s look at the
stored procedure in Listing 3.
It reads every record in table T and performs some processing on it. It then updates
the row back in the table by ROWID, but it
does so with “slow by slow”—also known as
“row by row”—processing. The optimal way
to write that code would be to use the bulk
procedure in Listing 4.
Here I explicitly array-fetch a configurable
number of rows (I used N with a default of
100 to set the array size), and once I get N
rows, I process them. Then I take the 100
processed rows and bulk-update them back
into the database, using the FORALL statement with the UPDATE. The results can be
astounding: I ran the slow_by_slow and bulk
procedures with SQL_TRACE enabled, and
Listing 5 shows the TKPROF report.
Note the CPU drop from 21. 25 down to
12.83—that is all due to the array processing.
Instead of “bugging” the database kernel
71,824 times, the UPDATE sent it data only 719
times. That makes the switching from PL/SQL
to the SQL layer that much more efficient.
As a side note, I encourage you to spend
some time reverse-engineering your code,
writing a specification for it—as a set of
requirements—and then attempting to
develop a single SQL statement. If I did that
for my example above, the UPDATE would
look something like
UPDATE t SET object_name =
SUBSTR(object_name, 2) || SUBSTR(object_
name, 1, 1)
Listing 6 shows the TKPROF report. That
is probably what you need for your 27 million
rows: something that runs 10 to 20 times as
fast as what you already have (if not faster!).
Tom Kyte is a database
evangelist in Oracle’s
Server Technologies
division and has worked
for Oracle since 1993. He
is the author of Expert
Oracle Database Architecture (Apress, 2005,
2010) and Effective Oracle by Design (Oracle
Press, 2003), among other books.
ASK Tom
Tom Kyte answers your most difficult technology
questions. Highlights from that forum appear in
this column.
asktom.oracle.com
READ more Tom
Oracle Database Concepts 11g Release 2 ( 11. 2)
bit.ly/aonqPP
Expert Oracle Database Architecture: Oracle
Database 9i, 10g, and 11g Programming
Techniques and Solutions, Second Edition
amzn.to/ckGXaR
PRACTICAL SOLUTIONS THAT DELIVER MEANINGFUL RESULTS
Your job is to maximize the performance of your
data center. Our job is to ensure your success by
developing the right solutions that truly deliver.
EGI’s team of experienced IT professionals use
a mix of business savvy and industry expertise
to create effective solutions built on proven
technologies and best practices; all designed
to produce meaningful results.
BY LEVERAGING THE POWER AND RELIABILITY OF ORACLE®
APPLICATIONS, EGI WILL:
» Raise performance benchmarks of your multi-threaded
applications with Oracle’s SPARC T3 processor.
» Reduce transaction times with Oracle’s Sun Storage
F5100 Flash Array.
» Simplify data center management with Oracle’s Sun ZFS
Storage Appliance .
Discover how EGI can give you the competitive edge you’re seeking.
Visit
www.egiadvantage.com
© 2011 The Ergonomic Group, Oracle and/or its affiliates. All rights reserved. All company logos and product names mentioned herein may be trademarks and/or
registered trademarks of their respective companies.
