Welcome to the Quest Experts Page, a supplement to
the Oracle DBA and PL/SQL Archives. This page will be periodically updated with
special contributions from some of Quest Software's Oracle and PL/SQL experts,
so check back regularly for updates.
|
Please Note: You may copy and distribute these materials freely for
non-commercial use only. You may not use any part of these materials
in commercial products and services, (such as on-line resources or
training materials), without the express permission of the author.
|
In this white paper, database expert and author Bert Scalzo addresses the top-five database benchmarking questions most frequently asked of him.
Gaining the best performance from your database applications is critical to the productivity of your IT infrastructure. A key component in achieving this is to validate the scalability of your database systems using benchmarking that is focused on the database.
This white paper answers the following quesions:
- Does Linux or Windows yield better benchmarking results?
- How many bits are best, 32 or 64?
- Which database platform provides the best performance benchmark - Oracle 10g,
SQL Server 2005 or MySQL 5.0?
- How do I determine the maximum concurrent OLTP users a server can sustain?
- How do I determine the maximum size data warehouse a server can sustain?
Top5DBBenchmarking.pdf Adobe Acrobat (770KB)
 |
By Ramesh Naidu, Quest Software |
The promise of Oracle’s ASM (Automatic Storage Management) is to lower the TCO (Total Cost
of Ownership) of the Oracle database RAC environment while increasing storage utilization and
DBA productivity.
Automatic Storage Management is a feature of Oracle Database 10G that provides integrated
cluster file system and volume management capabilities. ASM’s main purpose in life is to
manage disk resources for RAC and RAC in a grid environment, but it will work in single instance
implementations as well. With ASM the need for 3rd party file system and volume management
software for database files are eliminated. DBA productivity is increased because less time
is needed to manage the database storage environment and files.
The adoption rate of ASM is increasing and with that comes a need to understand the underlying
architecture and to provide monitoring of this underlying architecture. The whitepaper,
"Acing the ASM" will explain ASM functionalities and how the Foglight Oracle cartridge
will monitor this underlying infrastructure.
AcingASM.pdf Adobe Acrobat (706KB)
|
By Jim Wankowski, DB2 Product Manager, Quest Software |
IT departments today must deal with tightening budgets and shrinking staffs, and the luxury
of being a single platform DBA is becoming a thing of the past. In many cases, DBAs are being
required to support multiple DBMSs.
This whitepaper is geared towards the professional DBA who is now responsible for managing
both Oracle and DB2, or is transitioning from one to the other.
To make this transition easier, DB2 expert Jim Wankowski covers the essential architectural
and administrative differences between Oracle and DB2, and the similarities that bridge the
divide between the two databases.
DB2ForTheOracleDBA.pdf Adobe Acrobat (488KB)
|
By Eti Parnes, Quest Software |
This paper outlines the use of Quest's Performance Analysis to review and investigate locking and blocking behavior for a
monitored instance.
InvestigateLockingBehavior.pdf Adobe Acrobat (1235KB)
|
By Claudia Fernandez and Bernard Farrell, Quest Software |
This white paper presents a load testing methodology used to implement, manage and perform
full diagnostic capabilities of Oracle Real Application Clusters, in order to obtain
optimum user service times regardless of the demands placed on the RAC environment.
LoadTestingOracleRAC.pdf Adobe Acrobat (845KB)
PL/SQL is a great language. It’s relatively simple to learn, is well integrated with the Oracle
database, and can often be the most efficient way to perform complex or large scale database
operations. However, contrary to logical and reasonable expectations, a simple to learn yet robust
language like PL/SQL does not automatically guarantee programs which are readable, maintainable,
effective (i.e. correct) and efficient. In fact, some of the worst programs I’ve seen over the
past twenty years of Oracle development were written in PL/SQL. I have often been quite amazed at
just how easily one can "shoot themselves in the foot" with PL/SQL – and how often it goes
undetected until a major production crisis occurs.
So the question is "How do we engineer better PL/SQL?" This paper will examine some commonly
used manual methods and their shortcomings, and then will offer some more scientific advice
for how to improve upon the PL/SQL development process.
EngineeringBetterPLSQL.doc MS Word (732KB)
I often react to new Oracle releases like Steve Martin acted in the movie "The Jerk" when the
new phone books arrive. I cannot help but to get excited about all the new technology that
Oracle routinely delivers. But with the release of Oracle 10g, that enthusiasm cannot be
overstated. And while this Oracle version delivers numerous nifty new features, we’ll examine
just one, Automated Storage Management (ASM), which makes Linux database disk space management
a snap.
0104_A.htm HTML Page (31KB)
In Part 1, I offered suggestions for both an overall exception handling strategy and best
practices for raising exceptions in your programs. In this article, I complete my treatment
of error handling in PL/SQL, with a look at how best to handle exceptions once they have been
raised.
For handling exceptions, there are two main considerations:
- Deciding which errors should be handled and which can go unhandled in any given block
of code.
- Constructing reusable code elements that allow the handling (and logging) of errors in
consistent, useful ways.
I touch on both of these topics in the following best-practice recommendations.
1103_D.htm HTML Page (24KB)
Many programmers don't take the time to properly bulletproof
their programs. Most of us find it hard enough—and more than enough work—to simply write
the code that implements the positive aspects of an application: maintaining customers,
generating invoices, and so on.
You write applications that often assume the best of all possible worlds, hoping that your
programs are bug-free, that users will enter only the correct data in only the correct fashion,
and that all systems (hardware and software) will always be a "go."
No matter how hard you try, there will always be one more bug in your application. This
article, the first of two parts, will explore a set of best practices you should follow to
incorporate high-quality, comprehensive error handling in your PL/SQL-based applications.
This article will start by considering some overall best practices for error handling and
then focus on best practices relating to raising exceptions. The next article will move on
to how best to handle exceptions.
1003_E.htm HTML Page (19KB)
In this installment, I look at adopting modern
tablespace paradigms as yet another simple way to achieve optimal performance for Oracle on Linux.
For although Oracle
DBA's are good at optimizing their databases using time proven tuning tools and
techniques, sometimes a very simple and quite effective answer lies just
beneath the surface. Sometimes simply by embracing the newer tablespace
management paradigms of more recent Oracle versions, DBA's can obtain 10-20%
improvements in data loading throughput, index creations and sustainable
transactions per second for little or no cost.
0903_A.htm HTML Page (67KB)
Have you ever looked at a section of PL/SQL code
and wondered "What's wrong with this? How can I make this code
better?" This PowerPoint presentation highlights key areas of PL/SQL
code that can often be changed to enhance the overall functionality of the
code. In this presentation, you will see several code examples and learn
ways to identify and correct problem areas.
0502_h.ppt MS
PowerPoint (323KB)
Presented at the ODTUG conference in Las Vegas in June, 2002, this presentation addresses the following
nine areas
of change in Oracle 9i:
- Inheritance in Object Types
- Pipelined, Parallel-Executed Table Functions
- TIMESTAMP and INTERVAL
- New Operators: CASE, NVL2, and COALESCE
- Multi-Level Collections
- Associative Tables (Oracle 9i Release 2)
- Record-Based DML (Oracle 9i Release 2)
- The New and Improved UTL_FILE (Oracle 9i Release 2)
- The XMLType Datatype
NineNiftyNuances.ppt MS
PowerPoint (772KB)
Although data modeling
has been around for nearly 25 years, it ranks among the top areas from whence
database application problems arise. Moreover, the severity of the problems
ranges from totally incorrect functionality to freakishly miserable performance.
How can such an established technique yield such terrible results? The answer is
quite unnerving.
This paper is the first in a three part series intended to identify effective data
modeling techniques.
data-modeling.htm HTML Page (24KB)
In this paper, the second in the data modeling series, we’ll examine the
intricate complexities of relationships and their corresponding foreign keys.
Experience has shown that while many people can readily define entities and their unique identifiers,
a relatively small percentage are really any good at modeling the relationships between those entities.
This usually occurs as the chosen data modelers are:
- Analysts unsure of concepts such as cardinality, optionality, and normalization
- Information systems staff unsure as to the actual business rules and requirements
- Information systems staff more interested in the resulting database than the model
- Information systems staff more interested in the application design than the model
In other words, DBAs and programmers quite often make the worst data modelers! The motto has to be
effective first, then efficient. It doesn’t matter if it runs fast if the results are wrong. Likewise,
it doesn’t matter that in the old system it was done a certain way. Let the business requirements
dictate the data model – and don’t fight it.
data-modeling2.doc MS Word (219KB)
In this third and final paper, we’ll examine the often overlooked and under
utilized technique of entity super and sub typing, also known as generalization hierarchies or
inheritance – with the goal of answering two key questions:
- Where, when and why we should super and sub type our entities?
- How do we generate DDL that truly implements what’s been modeled?
data-modeling3.doc MS Word (110KB)
Like many people today,
the Linux movement enthralls me. Not just because I'm more of a UNIX based
DBA, but because of the amazing speed at which major vendors such as HP, Compaq,
Dell, IBM and Oracle have embraced this open source operating system. But
no matter how fashionable Linux may be, that popularity does not automatically
translate into nor guarantee performance. Believe it or not, it's quite
easy to get upwards of 1000% database improvement through proper Linux tuning
and database configuration for Linux. In this
paper, the first of two, we'll review some
very high ROI approaches.
linux1.htm HTML Page (45KB)
This is the second of two white papers on maximizing the
performance of Linux systems. The goal of these papers is to offer some
relatively simple yet highly effective tuning ideas. Moreover, all such
suggestions must adhere to the "KISS" ideal. All the tuning techniques must be
both easily implemented and easily measured. Our ideal technique must fit the
model of: perform a baseline benchmark, implement the proposed change, perform a
comparative benchmark, followed by a big smile.
Statistical analysis and testing was performed with the aid of Quest's Benchmark Factory, a
superior facility for database load testing and capacity planning.
linux2.htm HTML Page (60KB)
From time immemorial, Oracle performance tuning has seemed like witchcraft, secretly practiced by an
elite group of individuals, who appear to use voodoo to cure the performance problems of an Oracle
system. To compound this misperception, many thousands of pages of published material has been written that
propagate the idea of tuning Oracle with cache-hit ratios. There are many references to how performance
is good when the ratios are high and when those ratios are below a “desired percentage” there is great
cause for concern. All of this causes confusion, with very scarce mention about unearthing the actual
source of the bottlenecks that are inflicting performance pain on the entire system.
Oracle tuning efforts should be based on isolating and pinpointing bottlenecks (the disease) not
cache-hit ratios (the symptoms). The purpose of this paper is to dispel any misconceptions that
correlate Oracle database performance with cache-hit ratios. It also provides a methodology that
guarantees repeated success in unearthing the actual source of performance problems.
cachehit.doc MS Word (76KB)
It is hard to believe that the Oracle RDBMS has been in existence for over 22
years - a fascinating but
true fact. In this period of time, it has undergone significant change. Over the years, with core
functionality transformed, it has become imperative that we keep up with change. Every major release of
Oracle makes some of us feel as though we have to re-learn the concepts all over again.
In a world that requires us to support well-designed and great performing 24x7xForever commercial
applications, there is a dire need for us to keep up with technology. Failure to do so results in us
propagating old and potentially irrelevant technical information to others. That is how myths and
folklore are started and it also leads us to make inappropriate technical decisions. The myths and
folklore discussed in this paper are all performance-related. Although the list is not comprehensive,
it encompasses some of common ones that are out there within the context of Oracle and performance
tuning.
Oracle8iMyths.doc MS Word (113KB)
This advanced techniques class is designed to help PL/SQL developers come up to speed as quickly as
possible on the latest and greatest PL/SQL-related features of Oracle8i. It also covers crucial
advanced techniques available in all versions of Oracle from Oracle 7.3 forward. It builds on
Steven's earlier, popular Advanced Techniques with coverage of a number of exciting new features for
PL/SQL developers in Oracle8i, including Native Dynamic SQL, Autonomous Transactions, the Invoker
Rights Model, Calling Java from PL/SQL and Database and Schema Level Triggers. In addition to these
Oracle8i topics, this seminar explores advanced techniques for package design and working with
collections (index-by tables, nested tables and variable arrays).
Topics include...
- Package design: You may have heard of packages. You may even have built some yourself. This
course takes you beyond the basics and explores techniques like the initialization section,
overloading, and toggles and windows for improved flexibility and packaged cursors.
- Dynamic SQL: we will review the capabilities of DBMS_SQL, focusing on its more advanced usages.
Then we move to native dynamic SQL, a new implementation of dynamic SQL available in Oracle8i that is
faster and much easier to use than DBMS_SQL.
- Interoperability with Java and C: PL/SQL is no longer a closed language. You can, with a minimum
of effort, now call C and Java programs from within your PL/SQL code. This section includes an
introduction to the Java language.
- Collections: Oracle expanded upon PL/SQL tables to add nested tables and variable arrays. You now
have more options than ever for implementing array-like structures; all you have to do is figure out
how to use them properly!
- The invokers rights model: Oracle8i lets you define your stored programs so that they run under
the authority of the invoker instead of the owner. This feature allows you to maintain a single set
of code that works differently for different users. You can build shared dynamic SQL utilities that
automatically apply to the calling schema, rather than the owner schema.
- Autonomous transactions: a long-awaited capability, Oracle8i now allows developers to specify
that any DML in a specific PL/SQL block will be committed or rolled back without affecting the "main"
transaction. What can you do with this feature? Write errors to a log and commit those entries without
affecting the business transaction. Call functions from within SQL that update the database. Perform
a commit inside a trigger. And more...
- Advanced Queuing: Oracle8 introduced a new and very exciting feature: Oracle Advanced
Queuing.
Oracle AQ implements a robust and flexible messaging architecture for Oracle-based applications. Access to Oracle AQ from within PL/SQL occurs through calls to the DBMS_AQ and DBMS_AQADM packages.
- Row-Level Security: The DBMS_RLS package, along with other new 8i features, implements row-level
security or "fine grained access control" and offers exciting new possibilities for both developers
and DBAs. You no longer have to build complex architectures based on views. Put all your restrictions
in PL/SQL functions and let Oracle do all the work.
- Large Objects: Use the DBMS_LOB package to manipulate the new Large OBjects available in Oracle
since Version 8.0. Say goodbye to LONGs!
The zip file below contains the following files:
- Advanced Techniques Intro.doc (Table of Contents for Course) MS Word
- Advanced Techniques.ppt (Body of Course) MS PowerPoint
AdvancedTech.zip (396KB) You
may also wish to download the following file which contains companion files used to
demonstrate some of the techniques in this course. Click here for more information. demo.zip
(831KB)
(File last updated: 3/18/02)
The Oracle PL/SQL Tuning and Best Practices seminar offers a comprehensive treatment of tips and
techniques that will improve the performance and maintainability of your PL/SQL programs. While there
are a number of tools that help you tune the SQL side of your application, PL/SQL tuning has been
largely ignored - and it is largely up to the individual programmer to make it happen. This seminar
will show you how to take advantage of high performance PL/SQL features such as bulk array processing
in dynamic SQL and data caching in packages. Define your variable data structures for top efficiency.
Tuned code is, of course, just one component of high-quality software. This seminar also helps you
build applications that can be maintained effectively, are thoroughly tested and handle errors
robustly.
Topics include...
- Writing Maintainable Code: Most of us are so deadline driven that we end up writing
code that might, if we are lucky, meet today's requirements. We (or those who take over responsibility
for our code) almost never write code that can be easily modified and enhanced over time - even
though that's where most of the work will happen. This seminar demonstrates a variety of techniques
and standards that will allow you to both complete projects on time and maintain them effectively
over time.
- Effective Unit Testing: A unit test is a test that a developer creates to ensure that his
or her "unit", usually a single program, works properly. A unit test is very different from a system
or functional test; these latter types of test are oriented to application features or overall
testing of the system. You cannot properly or effectively perform a system test until you know that
the individual programs behave as expected. So of course you would therefore expect that programmers
do lots of unit testing and have a correspondingly high level of confidence in their programs. Ah, if
only that were the case! This seminar shows you how to improve dramatically the quantity and quality
of unit testing - and do it with a light-weight methodology and pre-built PL/SQL framework.
- Developing an Exception Handling Architecture: Trapping and handling errors is a crucial,
but neglected, aspect of any PL/SQL application. This section explores the features of PL/SQL
exception handling, identifies common problems with coding for exceptions, and offers an architecture
for consistent error handling throughout your code.
- Writing SQL in PL/SQL: It's very easy - perhaps too easy - to write SQL into your PL/SQL
application. Unfortunately, your data structures are always changing - and breaking your code. This
section shows how to write SQL in PL/SQL so that your code base adapts easily (in many cases,
automatically) to changes in the data design.
- Tuning Algorithms: It is really, really hard to tune PL/SQL code because it is not a
structured language, in the same sense as SQL. Most PL/SQL programs consist of algorithms (formulas
you devise to solve a problem). There are many different ways to write "correct" algorithms (they do
the right thing or return the right answer), but many of those algorithms will perform poorly. This
seminar offers tips on how to identify problem algorithms and how to straighten them out.
- Strategies for Implementing Best Practices: It's not enough to be handed a long list of
"shoulds" and "should nots". You've got to figure out a way to make it easier for developers to
follow the best practices. This section explores several topics, from code generation to conformance
scripts.
- Many other tuning tips: Effective use of data structures, analysis and optimization of
SGA memory management, elapsed time computation, code profiling capabilities, etc.
The zip file below contains the following files:
- Tuning and Best Practices Intro.doc (Table of Contents for Course) MS Word
- Tuning and Best Practices Appendix.doc (Quiz Problems and Solutions) MS Word
- Tuning and Best Practices.ppt (Body of Course) MS PowerPoint
- What Is Wrong with This Code (Slide Presentation from Atlantic Oracle
Training Conference) MS PowerPoint
TuningandBestPractices.zip
(908KB) You
may also wish to download the following file which contains companion files used to
demonstrate some of the techniques in this course. Click here for more information. demo.zip
(831KB)
(File last updated: 3/18/02)
This seminar, available here in both the one or two day format, introduces you to many of the latest
and greatest capabilities in the Oracle PL/SQL language, with a focus on new features in Oracle8i and
Oracle9i. After viewing this seminar, you will have a broader understanding of what is
possible in the PL/SQL language and how best to write code using these features.
The Wonders of Oracle9i PL/SQL
The improvements revealed in the Oracle9i PL/SQL language demonstrate clearly that Oracle is
committed to the PL/SQL language, in terms of both enhanced performance and feature robustness. We
will explore some of the most important advances in Oracle9i PL/SQL, including:
- Support for inheritance in Oracle object types. You can now (finally) define "class
hierarchies" with object types, which will make Oracle's object model much more attractive and
useful.
- Table functions and cursor expressions. You can now call a function from within the FROM clause
of a SQL query, greatly expanding the flexibility and in some cases performance of your application
(most especially in data warehousing applications).
- Enhanced date and timestamp functionality. You can now define timestamps with fractional second
precision to 9 digits. The new INTERVAL datatype allows you to store and manipulate with great
accuracy the amount of time between two dates or timestamps.
- Single, integrated SQL parser: Prior to Oracle9i, Oracle PL/SQL had its own parser, both for
PL/SQL statements and SQL statements. The consequence was that new features in SQL were sometimes not
immediately available inside a PL/SQL program. From Oracle9i onwards, the SQL and Pl/SQL statement
executors now rely on the same parser, so that as soon as a new feature is added to the SQL language,
it is immediately available in PL/SQL as well. For Oracle9i, this means that the new MERGE and CASE
statements are now ready to go in PL/SQL.
Leverage Java from within PL/SQL
This section includes a brief primer on writing simple Java classes, followed by a more in-depth look
at how to take advantage of Java functionality from within PL/SQL programs. As Steven says in his
class, "We all need to learn Java, but it turns out that you don't need to know very MUCH Java to
start using it inside PL/SQL programs."
Work with XML data inside PL/SQL
XML is rapidly becoming the de facto standard for the transmission of data between the many
distributed components that are deployed via the Internet to solve pressing business needs.
Oracle has moved aggressively to support XML inside the database. This seminar will introduce
you to the basics of XML documents and show how to parse, manipulate and transform data between
tables and XML documents.
Unit test PL/SQL programs with utPLSQL, a powerful open source framework
We all know we should do more to unit test our code, but it can be fiendishly difficult to find the
time and follow a clearly defined and effective process to get the testing done. Steven has developed
a unit testing framework, modeled after Junit and other open source testing mechanisms, that can
transform the way developers test their PL/SQL code. This seminar will introduce utPLSQL concepts and
demonstrate how utPLSQL is used.
Delight in the wonders of Native Dynamic SQL
In Oracle8i, Oracle introduced a new way of designing and executing dynamically (run-time)
constructed SQL and PL/SQL statements: native dynamic SQL or NDS. With NDS, you will find dynamic
SQL much easier and faster to write and execute. We will explore the new EXECUTE IMMEDIATE and OPEN
FOR statements and entertain ourselves with all the magical things you can do with NDS.
The zip files below contains the following files:
One Day Seminar
- Brave New World - One Day Intro.doc (Table of Contents for Course) MS Word
- Brave New World - One Day.ppt (Body of Course) MS PowerPoint
bnwone.zip (317KB)
Two Day Seminar
- Brave New World Intro.doc (Table of Contents for Course) MS Word
- Brave New World.ppt (Body of Course) MS PowerPoint
bnwtwo.zip (612KB)
You
may also wish to download the following file which contains companion files used to
demonstrate some of the techniques in this course. Click here for more information. demo.zip
(831KB)
(File last updated: 3/18/02)
For questions
or comments on this site: webmaster@quest-pipelines.com
All content Copyright
© Quest Software, Inc. All rights reserved.
|
