2174fmfinal.qxd 11/24/03 4:02 PM Page i
Mastering Oracle
PL/SQL: Practical
Solutions
CONNOR MCDONALD, WITH CHAIM KATZ,
CHRISTOPHER BECK, JOEL R. KALLMAN, AND DAVID C. KNOX
2174fmfinal.qxd 11/24/03 4:02 PM Page ii
Mastering Oracle PL/SQL: Practical Solutions
Copyright © 2004 by Connor McDonald, with Chaim Katz, Christopher Beck, Joel
R. Kallman, and David C. Knox
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN (pbk): 1-59059-217-4
Printed and bound in the United States of America 12345678910
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Technical Reviewers: Jakob Hammer-Jakobsen, Torben Holm, Thomas Kyte, Connor McDonald
Technical Editor: Tony Davis
Editorial Board: Steve Anglin, Dan Appleman, Gary Cornell, James Cox, Tony Davis, John
Franklin, Chris Mills, Steven Rycroft, Dominic Shakeshaft, Julian Skinner, Martin Streicher, Jim
Sumser, Karen Watterson, Gavin Wray, John Zukowski
Assistant Publisher: Grace Wong
Project Manager: Tracy Brown Collins
Copy Editors: Nancy Depper, Nicole LeClerc
Production Manager: Kari Brooks
Production Editor: Janet Vail
Proofreader: Patrick Vincent
Compositor: Gina M. Rexrode, Point n’ Click Publishing, LLC
Indexer: Valerie Perry
Artist: Christine Calderwood, Kinetic Publishing Services, LLC
Cover Designer: Kurt Krames
Manufacturing Manager: Tom Debolski
Distributed to the book trade in the United States by Springer-Verlag New York, Inc., 175 Fifth
Avenue, New York, NY, 10010 and outside the United States by Springer-Verlag GmbH & Co. KG,
Tiergartenstr. 17, 69112 Heidelberg, Germany.
In the United States: phone 1-800-SPRINGER, email orders@springer-ny.com, or visit
http://www.springer-ny.com. Outside the United States: fax +49 6221 345229, email
orders@springer.de, or visit http://www.springer.de.
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219,
Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, email info@apress.com, or visit
http://www.apress.com.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the information contained in this work.
The source code for this book is available to readers at http://www.apress.com in the
Downloads section. You will need to answer questions pertaining to this book in order to successfully
download
the code.
2174fmfinal.qxd 11/24/03 4:02 PM Page iii
Contents at a Glance
Foreword to the OakTable Press Series . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xv
Setting Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
Chapter 1 Efficient PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
Chapter 2 Package It All Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59
Chapter 3 The Vexed Subject of Cursors . . . . . . . . . . . . . . . . . . . . .117
Chapter 4 Effective Data Handling . . . . . . . . . . . . . . . . . . . . . . . . . . .145
Chapter 5 PL/SQL Optimization Techniques . . . . . . . . . . . . . . . . . . .229
Chapter 6 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307
Chapter 7 DBA Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .367
Chapter 8 Security Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .415
Chapter 9 Web Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .463
Chapter 10 PL/SQL Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .505
Appendix A Building DEBUG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .559
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .587
2174fmfinal.qxd 11/24/03 4:02 PM Page iv
2174fmfinal.qxd 11/24/03 4:02 PM Page v
Contents
Foreword to the OakTable Press Series . . . . . . . . . . . . . . . . . . . .ix
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xv
Setting Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
Chapter 1 Efficient PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Why Use PL/SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
What is Efficient PL/SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
Achieving Efficiency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
Chapter 2 Package It All Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Basic Benefits of Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59
Standalone Procedures and the Dependency Crisis . . . . . . . . . . . . . . . .65
Breaking the Dependency Chain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75
Enabling Recursion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90
Why Have People Avoided Packages? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .91
When Not to Use Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .96
Delivered Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
v
2174fmfinal.qxd 11/24/03 4:02 PM Page vi
vi
Chapter 3 The Vexed Subject of Cursors . . . . . . . . . . . . . . . 117
Implicit vs. Explicit Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117
Cursor Management Across Architectures . . . . . . . . . . . . . . . . . . . . . . . . . .132
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143
Chapter 4 Effective Data Handling . . . . . . . . . . . . . . . . . . . . . . . 145
Taking Control of Your Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145
From Fields to Rows—Using %ROWTYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .155
From Records to Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .162
The Motivation for Collections in PL/SQL . . . . . . . . . . . . . . . . . . . . . . .176
Bulking Up with Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180
Passing Variables Between PL/SQL Programs . . . . . . . . . . . . . . . . . . . . . .197
Transaction Processing in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .212
Autonomous Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .218
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228
Chapter 5 PL/SQL Optimization Techniques . . . . . . . . . . . . 229
Minimizing Parsing and Memory Consumption . . . . . . . . . . . . . . . . . . . . . .229
Data Types: Tips and Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .254
Calling PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .267
SQL Within PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .287
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .306
Chapter 6 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Trigger Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307
DML Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .319
Instead-of Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .324
Mutating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .326
Data Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .336
Table Versioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .339
Oracle Streams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .342
Job Queue (Temporal Event Triggers) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .349
DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .355
Database Event Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .359
Logon Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .359
Don’t Re-Invent the Wheel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .363
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .366
2174fmfinal.qxd 11/24/03 4:02 PM Page vii
Chapter 7 DBA Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Alert File Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .368
Notification Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .394
Proactive Monitoring Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .399
Historical Data Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .406
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .413
Chapter 8 Security Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Design Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .415
Triggers for Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .440
Protecting the Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .453
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .462
Chapter 9 Web Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
PL/SQL Web Toolkit Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .463
The htp and htf Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .468
Using Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .470
Cookies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .476
Managing Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .478
Managing Tables Through the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .484
HTTP from the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .495
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .503
Chapter 10 PL/SQL Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505
Defensive Coding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .505
Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .512
A Custom DEBUG Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .540
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .558
Appendix A Building DEBUG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559
Database Design and Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .559
Package Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .562
Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .564
Troubleshooting DEBUG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .586
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587
vii
2174fmfinal.qxd 11/24/03 4:02 PM Page viii
2174fmfinal.qxd 11/24/03 4:02 PM Page ix
Foreword to the
OakTable Press Series
Put simply, the OakTable network is an informal organization consisting of a
group of Oracle experts who are dedicated to finding ever better ways of administering
and developing Oracle-based
systems.
We
have joined forces
with Apress
to
bring
you
the OakTable
Press
series
of Oracle-related
titles.
The members of the network have a few things in common. We take a scientific
approach
to working
with the Oracle
database.
We
don’t
believe anything
unless
we’ve
seen it thoroughly
tested and proved.
We
enjoy
“moving
boundaries,”
innovating,
finding new and better ways to do things.
We
like good
whiskey.
These,
in essence,
are
the ideals that we
want to bring
to the OakTable
Press
series
(well,
apart
from
the last one,
possibly). Every
book in the series
will
be
written
by
and/or technically reviewed
by
at least two members of the
OakTable
network.
It
is our goal to help each OakTable
Press
author produce
a
book
that is rigorous,
accurate,
innovative,
and fun. Ultimately,
we
hope that
each
book is as useful a tool as it can possibly be in helping make your
life easier.
Who Are the OakTable Network?
It all started sometime in 1998 when a group of Oracle experts, including Anjo
Kolk, Cary Millsap, James Morle, and a few others, started meeting once or twice
a year, on various pretexts. Each would bring a bottle of Scotch or Bourbon and
in return earn the right to sleep on the floor somewhere in my house.
We spent most of our time sitting around my dining table, with computers,
cabling, paper, and other stuff all over the place, discussing Oracle, relaying
anecdotes, and experimenting with new and better ways of working with the
database. By the spring of 2002, the whole thing had grown. One evening, I realized
that I had 16 world-renowned
Oracle
scientists sitting around
my dining
table.
We
were
sleeping three
or four to a room
and even had to borrow
the
neighbor’s
shower
in the mornings.
Anjo Kolk
suggested we
call ourselves the
“OakTable
network”
(after my dining table), and about 2 minutes later,
http://www.OakTable.net was registered.
Today, a total of 42 people have been admitted to the OakTable network,
with perhaps half of them working for Oracle (there’s an up-to-date list on the
website). A committee, consisting of James Morle, Cary Millsap, Anjo Kolk, Steve
Adams, Jonathan Lewis, and myself, reviews suggestions for new members.
ix
2174fmfinal.qxd 11/24/03 4:02 PM Page x
Foreword to the OakTable Press Series
x
You can meet us at various conferences and user group events, and discuss
technical issues with us or challenge the OakTable network with a technical
question. If we can’t answer your question within 24 hours, you get a T-shirt that
says, “I challenged the OakTable—and I won,” with the three last words printed
in very, very small type! We still meet twice a year in Denmark: in January for the
Miracle Master Class (2001: Cary Millsap, 2002: Jonathan Lewis, 2003: Steve
Adams, and 2004: Tom Kyte), when one of the members will present for three
days, and in September/October for the Miracle Database Forum, which is a
three-day conference for database people.
Many projects and ideas have come out of the OakTable network, with some
of them resulting in courses (such as the Hotsos Clinic), others resulting in new
software products, and one that resulted in the OakTable Press series. We hope
you’ll enjoy the books coming out of it in the coming years.
Best,
Mogens Nørgaard
CEO of Miracle A/S (
http://www.miracleas.dk/) and cofounder of the OakTable
network
2174fmfinal.qxd 11/24/03 4:02 PM Page xi
About the Authors
Connor McDonald, lead author, has been working with Oracle
since the early 1990s. His involvement with the Oracle database
started with versions 6.0.36 and 7.0.12. Over the last 11 years he
has worked with systems in Australia, the United Kingdom,
Southeast Asia, Western Europe, and the United States. Connor is
a member of the OakTable network and is a well-known personality
both on the Oracle
speaker circuit
and in online Oracle
forums. He hosts a hints and tips website (
http://www.oracledba.co.uk) to share
his passion for Oracle and as part of an endeavor to improve the way in which
Oracle software is used within the industry.
Chaim Katz is an Oracle Certified Professional who has worked with Oracle
products since Oracle version 4. He specializes in database administration and
PL/SQL development and, over the years, he has written numerous articles for
various Oracle technical journals. He has taught Logo to children and database
systems at the college level. He lives in Montreal, Quebec, where aside from his
9-to-5 job in information systems, he likes to study the Talmud, play clarinet,
and discuss eternal problems. He and his wife, Ruthie, are currently enjoying the
challenges of raising a large family.
Christopher Beck, who holds a bachelor’s degree in computer science from
Rutgers University, has worked in the industry for 13 years. Starting off as a
junior Ada software developer for a government contractor, he has spent the last
9 years with Oracle Corporation and is now a principal technologist. He specializes
in core
database technologies and Web
application development. When
he
isn’t
working
for Oracle
or spending time with his wife and four young
children,
he’s
tinkering
with Linux or playing a friendly
online game of Quake
III Arena.
Joel R. Kallman is a software development manager for Oracle Corporation.
Over the past 14 years, he has focused on database and content management,
from SGML databases and publishing systems to text and document management.
He
is currently
managing the development of Oracle
HTML DB,
a solution
that
allows
customers to easily build database-centric
Web
applications.
When the daily advances in computer technology aren’t consuming all his
time, Joel enjoys football, woodworking, investing, and working out at the local
“Y.” Joel is a proud alumnus of The Ohio State University, where he received his
bachelor’s degree in computer engineering. He and his wife, Kristin, reside in
Powell, Ohio.
xi
2174fmfinal.qxd 11/24/03 4:02 PM Page xii
About the Authors
xii
David C. Knox is the chief engineer for Oracle’s Information Assurance Center.
He joined Oracle Corporation in June 1995. While at Oracle, he has worked on
many security projects for various customers, including the U.S. Department of
Defense (DoD), intelligence agencies, state and local governments, financial services
organizations,
and healthcare
organizations.
His
computer security
expertise
derives
not only from
working
knowledge
and experience
with Oracle’s
security
products
and database security,
but also from
his academic studies in
the
areas
of multilevel security,
cryptography,
LDAP,
and PKI. David
earned
a
bachelor’s
degree
in computer science from
the University
of Maryland
and a
master’s
degree
in computer science from
Johns
Hopkins
University.
2174fmfinal.qxd 11/24/03 4:02 PM Page xiii
About the Technical
Reviewers
Jakob Hammer-Jakobsen was born in 1965. He earned his master’s degree in
1992 and has been working with Oracle since 1986 (starting with Oracle version
5). He has worked primarily as developer of business systems on Oracle (and
other databases), but over the last 5 years he’s moved to the DBA segment as
well. Jakob has taught all kinds of Oracle-related courses worldwide; his most
recent course was “Developing Java Portlets.” He is an Oracle Certified Developer
and a member of OakTable.net. Other organizations he’s worked for include
the Department of Higher Education, University of Roskilde; Denmark’s
International Student Foundation (housing); Tom Pedersen International (the
original distributor of Oracle in Europe); Oracle Denmark; Miracle Australia; and
Miracle Denmark.
Torben Holm is a member of the OakTable network. He has been in the computer
business as a developer since 1998, as a staff sergeant
in the Royal
Danish
Airforce.
He
has been working
with Oracle
since 1992—his first 4 years
as system
analyst
and application developer (Oracle
7 and Forms
4.0/Reports
2.0 and
DBA),
then 2 years
as developer (Oracle6/7,
Forms
3.0 and RPT,
and DBA). He
then
worked
2 years
in Oracle
Denmark
in the Premium
Services
group
as a
senior
principal
consultant, where
he performed
application development
and
DBA tasks.
He
worked
as an instructor
in PL/SQL, SQL, DBA, and WebDB
courses.
For
the last 3 years,
Torben
has worked
for Miracle
A/S (
http://www.
miracleas.dk/
)
as application developer and DBA. He
is Developer
6i
Certified
(and
partly
8i
Certified, for what it’s worth—he didn’t have the time to finish
that certification). His “main” language is PL/SQL.
Tom Kyte is VP, Core Technologies at Oracle Corporation, and he has over
16 years of experience designing and developing large-scale database and
Internet applications. Tom specializes in core database technologies,
application design and architecture, and performance tuning. He is a regular
columnist for Oracle Magazine and is the Tom behind the AskTom website
(
http://asktom.oracle.com/), where technical professionals can come to get
answers to their questions. He’s also the author of Effective Oracle by Design, an
Oracle best practices book, and Expert One-on-One Oracle, a book that describes
how to architect systems using core Oracle technologies, and he’s the coauthor
of Beginning Oracle, a book aimed at new Oracle developers.
xiii
2174fmfinal.qxd 11/24/03 4:02 PM Page xiv
xiv
Acknowledgments
First, I want to acknowledge the wonderful help afforded to me over the years by
the OakTable network. I’ve never met a group of people who are so generous
with their time and knowledge (and vast knowledge it is indeed!). It’s a privilege
to be associated with them. In particular, I want to thank Tom Kyte, Jonathan
Lewis, and Dave Ensor, all of whom inspired me to explore Oracle more deeply,
and Mogens Nørgaard, an OakTable network “founder,” for his amazing hospitality
and whiskey! Thanks
also to my editor,
Tony
Davis,
toward
whom my
emotions
have oscillated between
gratitude
and wanting to knock him senseless,
which
pretty
much means he’s
doing his job well.
I would also like to thank the companies I’ve worked with that use Oracle.
There is no better way of exploring the power of PL/SQL and other Oracle technologies
than to be challenged with solving problems
and optimizing the use of
the
Oracle
infrastructure.
Most important, I want to thank my wife, Gillian, for her support and tolerance
of the time I spend exploring
Oracle—time
I should be spending with her!
Exploring
technology often means being locked away for hours or days in a dark
room
in front
of a computer screen,
but knowing
that you’ve
got the most beautiful
and wonderful
woman in the world just a few steps away in the next room
makes
finding inspiration
easy.
—Connor McDonald
I would like to thank Tony Davis at Apress for sticking with me through this
process, even though my submissions were always very late. Sorry again, Tony. I
would also like to thank my wife, Marta, for her constant encouragement and
support. Ti amo.
—Christopher Beck
I would first like to thank my wife, Sandy, for supporting me in writing this book.
I would have never been able to do it without her allowing me to work on this
project during “vacation.” I would also like to acknowledge my colleagues at
Oracle—Tom Kyte and Patrick Sack, in particular—for their technical perspectives
and valuable
input. I would also like to thank my editor,
Tony
Davis;
my
fellow
authors; and the technical reviewers,
all of whom have helped to make
this
book a success.
—David C. Knox
2174fmfinal.qxd 11/24/03 4:02 PM Page xv
Introduction
I went to an online bookstore recently, typed PL/SQL in the Search box, and got
38 results back, excluding this book. Thirty-eight books! As far as I could see,
none of them was listed alongside the Harry Potter books as worldwide top-sellers,
so what on earth
would inspire
a group
of authors to come together to
produce
the thirty-ninth
book on this topic?
The reason is that, despite the plethora of available books, we still encounter
a great deal of poor quality or antiquated PL/SQL code in Oracle applications.
From a personal perspective, I’ve worked with Oracle systems around the world,
and although the applications, architectures, and methodologies have been very
diverse, I’ve found two common themes in almost all of these systems. Either
they steer away from Oracle-specific functionality altogether, or they use it in a
haphazard and less-than-optimal fashion. Nowhere is this more evident than in
the use of PL/SQL, which has been less “used” and more “abused” in many of the
systems that I’ve encountered.
At least part of the problem is that the majority of PL/SQL books are only
about syntax. They’ll show you how to code PL/SQL so that it will compile and
execute on your systems (some books extend themselves to giving guidelines for
good naming standards and coding structure). But, as with any programming
language, there’s a big difference between just using the language and using it
well. The key to building successful applications is the ability to take your syntax
knowledge and apply it intelligently to build programs that are robust, efficient,
and easily maintained. This is the motivation for our book and its title. We don’t
want to make you a PL/SQL programmer—we want to make you a smart PL/SQL
programmer.
What Does This Book Cover?
This book offers a wealth of tips, techniques, and complete strategies for maximizing
the benefits of PL/SQL within your
organization.
By
the end of this book,
you’ll
be as convinced as we
are
that PL/SQL isn’t
just a useful tool—it’s
an integral
part
of any Oracle
application you’ll
ever develop.
We’ll demonstrate techniques that are applicable for all versions of Oracle,
from 8i to 10g. The vast majority of the examples in this book were tested using
Oracle9i R2, and all you’ll need to run them is SQL*Plus.
The following is a chapter-by-chapter breakdown that summarizes some of
the key topics we’ll cover:
xv
2174fmfinal.qxd 11/24/03 4:02 PM Page xvi
Introduction
xvi
• Setting Up. The next section of this book shows you how to set up an
effective SQL*Plus environment and how to get up and running with the
performance tools that we use throughout the book, namely
AUTOTRACE,
SQL_TRACE, TKPROF, and RUNSTATS.
• Chapter 1: Efficient PL/SQL. This chapter defines what we mean by “efficient
PL/SQL”
and introduces
the book’s
pervading
theme of
demonstrability—that
is,
the need to prove
conclusively that your
code
meets
performance
targets
under all
reasonable conditions. It demonstrates
why PL/SQL is almost always the right
tool for programming
within
the
database,
but it also explores
situations in which PL/SQL might not be
appropriate,
by
presenting
a few innovative
uses of SQL as a means to
avoiding
procedural
code altogether.
• Chapter 2: Package It All Up. Packages are much more than just a “logical
grouping of procedures.” They offer numerous advantages, from overloading
and encapsulation to protection
from
dependency and recompilation
issues.
This
chapter clearly demonstrates
these advantages
and also discusses
interesting
uses for some of the Oracle-supplied
packages.
• Chapter 3: The Vexed Subject of Cursors. There is much debate and contention
surrounding
the issue of explicit versus implicit cursors.
This
chapter
demonstrates
why you
might not need explicit cursors as often as
you
may think. It
also looks at effective uses of cursor variables
and cursor
expressions
in distributed
applications.
• Chapter 4: Effective Data Handling. This chapter shows you how to maximize
the integration
between
the data structures
in the database and the
data
structures
in your
PL/SQL program,
leading to code that is more
robust
and resilient
to change.
It
also looks at how
to make effective use of
collections
in passing data in bulk from
your
program
to the database and
vice
versa.
• Chapter 5: PL/SQL Optimization Techniques. This chapter provides a
number of ready-made solutions to some commonly encountered problems
in PL/SQL development. It
shows
you
how
to avoid some of the
hidden
overheads
and highlights “gotchas”
that can trip
up the unwary.
• Chapter 6: Triggers. This chapter covers fundamental trigger concepts
and effective uses for some of the various types of triggers available. It also
delves into the relatively new topic of Oracle Streams and shows how to
use them to implement a centralized data audit trail.
2174fmfinal.qxd 11/24/03 4:02 PM Page xvii
• Chapter 7: DBA Packages. This chapter provides a “DBA toolkit”—a set of
packages that can be used to automate recurring administrative activities,
such as performance diagnosis and troubleshooting, backup and recovery,
and monitoring the database for faults.
• Chapter 8: Security Packages. This chapter looks at the use of PL/SQL
packages and triggers to implement effective security mechanisms in the
database. It covers fundamental issues such as use of the invoker and
definer rights models, package construction, and schema design, and then
it moves on to present specific solutions for such issues as auditing database
activity and protecting
your
source
code.
• Chapter 9: Web Packages. This chapter investigates a set of built-in database
packages,
collectively known
as the PL/SQL Web
Toolkit,
which allow
developers
to present
dynamic Web
pages from
directly
within the database.
It
covers
issues such as use of cookies,
management of tables and
files,
and how
to invoke a Web
Service
directly
from
within a PL/SQL
stored
procedure.
• Chapter 10: PL/SQL Debugging. Few people get it right first time, so this
chapter presents a range of techniques for effective debugging of your
PL/SQL code, from the simple use of
DBMS_OUTPUT to more complex packages
such as
DBMS_APPLICATION_INFO
and UTL_FILE. It culminates with the
development of
DEBUG, a sophisticated custom debugging utility.
• Appendix A: Building DEBUG. This appendix presents the full code listing
for the
DEBUG utility used in Chapter 10.
Who Should Read This Book?
This book is targeted primarily toward the DBA or developer charged with the
implementation of effective data handling, security, and database administration
mechanisms in the Oracle
database.
However,
it will also have great
appeal
to
any
developer whose applications rely on an Oracle database and who needs
a sound understanding of how to use PL/SQL effectively.
If you’re brand new to PL/SQL, then you’ll want to take some time to get
familiar with the language before tackling this book. It’s not for the total beginner.
But
once you’re
up and running,
we
believe you’ll
find our book an
invaluable
guide for ensuring
that the PL/SQL solutions you
build are
robust,
perform
well,
and are
easy to maintain.
—Connor McDonald
Introduction
xvii
2174fmfinal.qxd 11/24/03 4:02 PM Page xviii
2174fmfinal.qxd 11/24/03 4:02 PM Page xix
Setting Up
In this section we’ll describe how to set up an environment capable of executing
the examples in this book. We’ll cover the following topics:
• How to set up the
SCOTT/TIGER demonstration schema
• How to configure the SQL*Plus environment
• How to configure
AUTOTRACE, a SQL*Plus facility that shows you either how
Oracle performed a query or how it will perform the query, along with statistics
regarding
the processing
of that query
• How to set up to use
SQL_TRACE, TIMED_STATISTICS, and TKPROF, two parameters
and a command-line tool that will tell you
what SQL your
application
executed
and how
that SQL performed
• How to set up and use the
RUNSTATS utility
Note that we provide only basic setup instructions here for the various performance
tools,
so that you
may quickly configure
your
environment
to run
to
the
examples in this book. For
full instructions
and information
on how
to interpret
the data that these tools provide,
we
refer
you
to the Oracle
documentation
set
or to a relevant
book, such as Thomas
Kyte’s
Expert
One-on-One
Oracle
(Apress,
ISBN: 1-59059-243-3).
Setting Up the SCOTT/TIGER Schema
Many of the examples in this book draw on the EMP/DEPT tables in the SCOTT
schema. We recommend that you install your own copy of these tables in some
account other than
SCOTT to avoid side effects caused by other users using and
modifying the same data. To create the
SCOTT demonstration tables in your own
schema, simply perform the following:
1. From the command line, run
cd [ORACLE_HOME]/sqlplus/demo.
2. Log into SQL*Plus as the required user.
3. Run
@DEMOBLD.SQL.
xix
2174fmfinal.qxd 11/24/03 4:02 PM Page xx
xx
The DEMOBLD.SQL script will create and populate five tables for you. When it’s
complete, it exits SQL*Plus automatically, so don’t be surprised when SQL*Plus
disappears after running the script. If you would like to drop this schema at any
time to clean up, you can simply execute
[ORACLE_HOME]/sqlplus/demo/demodrop.sql.
The SQL*Plus Environment
The examples in this book are designed to run in the SQL*Plus environment.
SQL*Plus provides many useful options and commands that we’ll make frequent
use of throughout this book. For example, a lot of the examples in this book use
DBMS_OUTPUT in some fashion. In order for DBMS_OUTPUT to work, the following
SQL*Plus command must be issued:
SQL> set serveroutput on
Alternatively, SQL*Plus allows us to set up a LOGIN.SQL file, a script that is
executed each and every time we start a SQL*Plus session. In this file, we can set
parameters such as
SERVEROUTPUT automatically. An example of a LOGIN.SQL script
is as follows (you can edit it to suit your own particular environment):
define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
Furthermore, we can use this script to format our SQL*Plus prompt so that
we always know who we’re logged in as and on which database. For example, as
you work through this book, you’ll encounter prompts of the following format:
scott@oracle9i_test>
This tells you that you’re logged into the SCOTT schema on the ORACLE9I_TEST
database. The following is the code in the LOGIN.SQL script that achieves this:
column global_name new_value gname
set termout off
select lower(user) || '@' ||
2174fmfinal.qxd 11/24/03 4:02 PM Page xxi
global_name from global_name;
set sqlprompt '&gname> '
set termout on
This login script will only be run once, on startup. So, if you login on startup
as
SCOTT and then change to a different account, this won’t register on your
prompt:
SQL*Plus: Release 8.1.7.0.0 - Production on Sun Mar 16 15:02:21 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: scott/tiger
Connected to:
Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
scott@ORATEST> connect tony/davis
Connected.
scott@ORATEST>
The following CONNECT.SQL script will solve this:
set termout off
connect &1
@login
set termout on
Then you simply run this script (which connects, and then runs the login script)
every time you want to change accounts:
scott@ORATEST> @connect tony/davis
tony@ORATEST>
To get SQL*Plus to run the login script automatically on startup, you need to
save it in a directory (put
CONNECT.SQL in the same directory) and then set the
SQLPATH environment variable to point at that directory. If you’re working on
Windows, navigate to the Start button, select Run, and type regedit. Navigate to
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE and find the SQLPATH file (mine was in HOME0).
Double-click it and set the path to the directory where you stored the scripts (for
example,
C:\oracle\ora81\sqlplus\admin).
xxi
2174fmfinal.qxd 11/24/03 4:02 PM Page xxii
Setting Up
xxii
Setting Up AUTOTRACE in SQL*Plus
Throughout the book it will be useful for us to monitor the performance of the
queries we execute. SQL*Plus provides an
AUTOTRACE facility that allows us to see
the execution plans of the queries we’ve executed and the resources they used.
The report is generated after successful SQL DML. This book makes extensive
use of this facility. There is more than one way to configure the
AUTOTRACE facility,
but the following is a recommended route:
1. Access
cd $ORACLE_HOME/rdbms/admin.
2. Log into SQL*Plus as any user with
CREATE TABLE and CREATE PUBLIC
SYNONYM
privileges.
3. Run
@UTLXPLAN to create a PLAN_TABLE for use by AUTOTRACE.
4. Run
CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE, so that everyone
can access this table without specifying a schema.
5. Run
GRANT ALL ON PLAN_TABLE TO PUBLIC, so that everyone can use this
table.
6. Exit SQL*Plus and change directories as follows:
cd $ORACLE_HOME/sqlplus/admin.
7. Log into SQL*Plus as a
SYSDBA.
8. Run
@PLUSTRCE.
9. Run
GRANT PLUSTRACE TO PUBLIC.
You can test your setup by enabling
AUTOTRACE and executing a simple query:
SQL> set AUTOTRACE traceonly
SQL> select * from emp, dept
2 where emp.deptno=dept.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2174fmfinal.qxd 11/24/03 4:02 PM Page xxiii
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'DEPT'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
2 consistent gets
0 physical reads
0 redo size
2144 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> set AUTOTRACE off
For full details on the use of AUTOTRACE and interpretation of the data it provides,
see Chapter 11 of Oracle9i
Database Performance Tuning Guide and
Reference in the Oracle documentation set or Chapter 9 of SQL*Plus User’s Guide
and Reference.
Performance Tools
In addition to using AUTOTRACE, we make use of various other performance tools
throughout the book. We’ll present brief setup instructions in this section.
TIMED_STATISTICS
The TIMED_STATISTICS parameter specifies whether Oracle should measure the
execution time for various internal operations. Without this parameter set, there
is much less value to the trace file output. As with other parameters, you can set
TIMED_STATISTICS either on an instance level (in INIT.ORA) or on a session level.
The former shouldn’t affect performance, so it’s generally recommended. Simply
Setting Up
xxiii
2174fmfinal.qxd 11/24/03 4:02 PM Page xxiv
Setting Up
xxiv
add the following line to your INIT.ORA file and then the next time you restart the
database, it will be enabled:
timed_statistics=true
On a session level, you would issue this:
SQL> alter session set timed_statistics=true;
SQL_TRACE and TKPROF
Together, the SQL_TRACE facility and the TKPROF command-line utility enable
detailed tracing of the activity that takes place within the database. In short,
SQL_TRACE is used to write performance information on individual SQL statements
down
to trace
files in the file system of the database server.
Under
normal
circumstances,
these trace
files are
hard
to comprehend
directly.
For
that purpose,
you
use the
TKPROF
utility to generate text-based report files from the input
of a given trace file.
SQL_TRACE
The SQL_TRACE facility is used to trace all SQL activity of a specified database session
or instance down
to a trace
file in the database server
operating
system.
Each
entry
in the trace
file records
a specific operation
performed
while the
Oracle
server
process
is processing
a SQL statement.
SQL_TRACE
was originally
intended for debugging, and it’s still well suited for that purpose, but it can just
as easily be used to analyze the SQL activity of the database for tuning purposes.
Setting Up SQL_TRACE
SQL_TRACE can be enabled for either a single session or a whole database
instance. It is, however, rarely enabled at a database level, because that would
cause serious performance problems. Remember that
SQL_TRACE writes down
every SQL statement processed down to a log file, with accompanying I/O
activity.
To enable tracing for the current session, you should issue
ALTER SESSION, as
shown here:
SQL> alter session set sql_trace=true;
2174fmfinal.qxd 11/24/03 4:02 PM Page xxv
Enable tracing for a session at a selected interval and avoid having tracing in
effect for long periods of time. To disable the current trace operation, you execute
the following:
SQL> alter session set sql_trace=false;
Controlling the Trace Files
The trace files generated by
SQL_TRACE can eventually grow quite large. A few
global initialization parameters, set in
INIT.ORA for the database instance or session
settings,
affect the trace
files.
If
enabled,
SQL_TRACE
will write to a file in the
operating system directory indicated by the
USER_DUMP_DEST initialization parameter.
You
should note that trace
files for
USER
processes (dedicated servers) go
to the
USER_DUMP_DEST directory. Trace files generated by Oracle background
processes such as the shared servers used with MTS and job queue processes
used with the job queues will go to the
BACKGROUND_DUMP_DEST. Use of SQL_TRACE
with a shared server configuration isn’t recommended. Your session will hop
from shared server to shared server, generating trace information in not one but
in many trace files, rendering it useless.
Trace files are usually named
ora
.trc,
where is the server process ID of the session for which the trace was
enabled. On Windows, the following query may be used to retrieve your session’s
trace file name:
SQL> select c.value || '\ORA' || to_char(a.spid,'fm00000') || '.trc'
2 from v$process a, v$session b, v$parameter c
3 where a.addr = b.paddr
4 and b.audsid = userenv('sessionid')
5 and c.name = 'user_dump_dest';
On Unix, this query can be used to retrieve the session’s trace file name:
SQL> select c.value || '/' || d.instance_name || '_ora_' ||
2 to_char(a.spid,'fm99999') || '.trc'
3 from v$process a, v$session b, v$parameter c, v$instance d
4 where a.addr = b.paddr
5 and b.audsid = userenv('sessionid')
6 and c.name = 'user_dump_dest';
Setting Up
xxv
2174fmfinal.qxd 11/24/03 4:02 PM Page xxvi
Setting Up
xxvi
The size of the trace files is restricted by the value of the MAX_DUMP_FILE_SIZE
initialization parameter set in INIT.ORA for the database instance. You may also
alter this at the session level using the
ALTER SESSION command, for example:
SQL> alter session set max_dump_file_size = unlimited;
Session altered.
TKPROF
The TKPROF utility takes a SQL_TRACE trace file as input and produces a text-based
report file as output. It’s a very simple utility, summarizing a large set of detailed
information in a given trace file so that it can be understood for performance
tuning.
Using TKPROF
TKPROF is a simple command-line utility that is used to translate a raw trace file to
a more comprehensible report. In its simplest form,
TKPROF can be used as shown
here:
tkprof
To illustrate the joint use of TKPROF and SQL_TRACE, we’ll set up a simple example.
Specifically,
we’ll
trace
the query
we
used previously
in our
AUTOTRACE
example and generate a report from the resulting trace file. First, we log onto
SQL*Plus as the intended user and then execute the following code:
SQL> select c.value || '\ORA' || to_char(a.spid,'fm00000') || '.trc'
2 from v$process a, v$session b, v$parameter c
3 where a.addr = b.paddr
4 and b.audsid = userenv('sessionid')
5 and c.name = 'user_dump_dest';
C.VALUE||'\ORA'||TO_CHAR(A.SPID,'FM00000')||'.TRC'
-----------------------------------------------------------C:\oracle\admin\oratest\udump\ORA01528.trc
SQL> alter session set timed_statistics=true;
Session altered.
2174fmfinal.qxd 11/24/03 4:02 PM Page xxvii
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from emp, dept
2 where emp.deptno=dept.deptno;
SQL> alter session set sql_trace=false;
SQL> exit
Now, we simply format our trace file from the command line using TKPROF, as follows:
C:\oracle\admin\oratest\udump>tkprof ORA01528.TRC tkprof_rep1.txt
Now we can open the TKPROF_REP1.TXT file and view the report. We don’t
intend to discuss the output in detail here, but briefly, at the top of the report we
should see the actual SQL statement issued. Next, we get the execution report for
the statement. This report is illustrated for the three different phases of Oracle
SQL processing: parse, execute, and fetch. For each processing phase, we see the
following:
• The number of times that phase occurred
• The CPU time elapsed for the phase
• The real-world time that elapsed
• The number of physical I/O operations that took place on the disk
• The number of blocks processed in “consistent-read” mode
• The number of blocks read in “current” mode (reads that occur when the
data is changed by an external process during the duration of the statement
processing)
• The number of blocks that were affected by the statement
The execution report is as follows:
Setting Up
xxvii
2174fmfinal.qxd 11/24/03 4:02 PM Page xxviii
Setting Up
xxviii
call count cpu elapsed disk query current rows
------- ------ ------- -------- ------- ------- ------- -----Parse
1 0.01 0.02 0 0 0 0
Execute
1 0.00 0.00 0 0 0 0
Fetch
2 0.00 0.00 0 2 8 14
-------
------ ------- -------- ------- ------- ------- -----total
4 0.01 0.02 0 2 8 14
Following the execution report, we can see optimizer approach used and the
user ID of the session that enabled the trace (we can match this ID against the
ALL_USERS table to get the actual username):
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 52
Additionally, we see the number of times the statement wasn’t found in the
library cache. The first time a statement is executed, this count should be 1, but
it should be 0 in subsequent calls if bind variables are used. Again, watch for the
absence of bind variables—a large number of library cache misses would indicate
that.
Finally, the report displays the execution plan used for this statement. This
information is similar to that provided by
AUTOTRACE, with the important difference
that the number of actual rows
flowing
out of each step in the plan is
revealed
to us:
Rows Row Source Operation
-------- ---------------------------------------------------
14 MERGE JOIN
5 SORT JOIN
4 TABLE ACCESS FULL DEPT
14 SORT JOIN
14 TABLE ACCESS FULL EMP
For full details on use of SQL_TRACE and TKPROF, and interpretation of the trace
data, see Chapter 10 of Oracle9i Database Performance Tuning Guide and
Reference.
RUNSTATS
RUNSTATS is a simple test harness that allows comparison of two executions of
code and displays the costs of each in terms of the elapsed time, session-level
2174fmfinal.qxd 11/24/03 4:02 PM Page xxix
statistics (such as parse calls), and latching differences. The latter of these, latching,
is the key piece of information
that this tool provides.
NOTE The RUNSTATS tool was originally built by Tom Kyte, the man behind
the
http://asktom.oracle.com website. Full information and an example
usage of
RUNSTATS can be found at http://asktom.oracle.com/~tkyte/runstats.html
.
In
Chapter 4 we
provide
a useful customization of this tool that
makes
use of collections.
To r un this test harness, you must have access to V$STATNAME, V$MYSTAT, and
V$LATCH. You must be granted direct SELECT privileges (not via a role) on
SYS.V_$STATNAME, SYS.V_$MYSTAT, and SYS.V_$LATCH. You can then create the following
view:
SQL> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch;
View created.
All you need then is a small table to store the statistics:
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
The code for the test harness package is as follows:
create or replace package runstats_pkg
as
end;
/
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0 );
Setting Up
xxix
2174fmfinal.qxd 11/24/03 4:02 PM Page xxx
Setting Up
xxx
create or replace package body runstats_pkg
as
g_start number;
g_run1 number;
g_run2 number;
procedure rs_start
is
begin
delete from run_stats;
insert into run_stats
select 'before', stats.* from stats;
g_start := dbms_utility.get_time;
end;
procedure rs_middle
is
begin
end;
g_run1 := (dbms_utility.get_time-g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_time;
procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_time-g_start);
dbms_output.put_line
( 'Run1 ran in ' || g_run1 || ' hsecs' );
dbms_output.put_line
( 'Run2 ran in ' || g_run2 || ' hsecs' );
dbms_output.put_line
( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
'% of the time' );
dbms_output.put_line( chr(9) );
2174fmfinal.qxd 11/24/03 4:02 PM Page xxxi
insert into run_stats
select 'after 2', stats.* from stats;
dbms_output.put_line
( rpad( 'Name', 30 ) || lpad( 'Run1', 10 ) ||
lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
for x in
( select rpad( a.name, 30 ) ||
to_char( b.value-a.value, '9,999,999' ) ||
to_char( c.value-b.value, '9,999,999' ) ||
to_char( ( (c.value-b.value)-(b.value-a.value)), '9,999,999' ) data
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
and (c.value-a.value) > 0
and abs( (c.value-b.value) - (b.value-a.value) )
> p_difference_threshold
order by abs( (c.value-b.value)-(b.value-a.value))
) loop
dbms_output.put_line( x.data );
end loop;
dbms_output.put_line( chr(9) );
dbms_output.put_line
( 'Run1 latches total versus runs -- difference and pct' );
dbms_output.put_line
( lpad( 'Run1', 10 ) || lpad( 'Run2', 10 ) ||
lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
for x in
( select to_char( run1, '9,999,999' ) ||
to_char( run2, '9,999,999' ) ||
to_char( diff, '9,999,999' ) ||
to_char( round( run1/run2*100,2 ), '999.99' ) || '%' data
from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
sum( (c.value-b.value)-(b.value-a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
Setting Up
xxxi
2174fmfinal.qxd 11/24/03 4:02 PM Page xxxii
Setting Up
xxxii
end;
end;
/
)
) loop
and b.runid = 'after 1'
and c.runid = 'after 2'
and a.name like 'LATCH%'
dbms_output.put_line( x.data );
end loop;
Using RUNSTATS
To demonstrate the information that we can get out of RUNSTATS, we’ll compare
the performance of a lookup on a normal heap table (
HEAP) and an index-organized
table (
IOT).
We’ll
consider three
scenarios:
•Full table scan on small tables
•Primary key lookup on moderate tables
•Secondary index lookup on moderately sized tables
Full Scan on Small Tables
First we create our tables and indexes:
SQL> create table HEAP
2 as select * from DUAL;
Table created.
SQL> create table IOT ( dummy primary key)
2 organization index
3 as select * from DUAL;
Table created.
Now we analyze both tables to ensure consistency in our results:
2174fmfinal.qxd 11/24/03 4:02 PM Page xxxiii
SQL> analyze table HEAP compute statistics;
Table analyzed.
SQL> analyze table IOT compute statistics;
Table analyzed.
Next we perform a preliminary run to massage the cache:
SQL> declare
2 x varchar2(1);
3 begin
4 for i in 1 .. 10000 loop
5 select dummy into x
6 from HEAP;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> declare
2 x varchar2(1);
3 begin
4 for i in 1 .. 10000 loop
5 select dummy into x
6 from IOT;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
We then take a snapshot of our statistics before we run our tests:
SQL> exec RUNSTATS_PKG.rs_start;
PL/SQL procedure successfully completed.
Now we run our lookup code for the HEAP table:
Setting Up
xxxiii
2174fmfinal.qxd 11/24/03 4:02 PM Page xxxiv
Setting Up
xxxiv
SQL> declare
2 x varchar2(1);
3 begin
4 for i in 1 .. 10000 loop
5 select dummy into x
6 from HEAP;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
And now another snapshot:
SQL> exec RUNSTATS_PKG.rs_middle
PL/SQL procedure successfully completed.
And then we run our lookup code for the IOT table:
SQL> declare
2 x varchar2(1);
3 begin
4 for i in 1 .. 10000 loop
5 select dummy into x
6 from IOT;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Then we take our final snapshot and get our comparative statistics:
connor@ORATEST> exec RUNSTATS_PKG.rs_stop;
Run1 ran in 130 hsecs
Run2 ran in 74 hsecs
run 1 ran in 175.68% of the time
Name Run1 Run2 Diff
LATCH.checkpoint queue latch 1 2 1
STAT...calls to kcmgas 1 0 -1
2174fmfinal.qxd 11/24/03 4:02 PM Page xxxv
STAT...cleanouts and rollbacks 1 0 -1
STAT...immediate (CR) block cl 1 0 -1
STAT...parse time cpu 1 0 -1
No comments:
Post a Comment