Oracle SQL Tuning Pocket Reference - Gurry_ Mark.pdf

(981 KB) Pobierz
15735514 UNPDF
Oracle SQL Tuning Pocket Reference
Publisher : O'Reilly
Pub Date : January 2002
ISBN : 0-596-00268-8
•
Ta b l e
o f
Pages : 108
• Index
• Reviews
•
• Errata
15735514.001.png
Chapter 1. Oracle SQL TuningPocket Reference
1.1 Introduction
This book is a quick-reference guide for tuning Oracle SQL. This is not a comprehensive Oracle
tuning book.
The purpose of this book is to give you some light reading material on my "real world" tuning
experiences and those of my company, Mark Gurry & Associates. We tune many large Oracle sites.
Many of those sites, such as banks, large financial institutions, stock exchanges, and electricity
markets, are incredibly sensitive to poor performance.
With more and more emphasis being placed on 24/7 operation, the pressure to make SQL perform in
production becomes even more critical. When a new SQL statement is introduced, we have to be
absolutely sure that it is going to perform. When a new index is added, we have to be certain that it
will not be used inappropriately by existing SQL statements. This book addresses these issues.
Many sites are now utilizing third-party packages such as Peoplesoft, SAP, Oracle Applications,
Siebel, Keystone, and others. Tuning SQL for these applications must be done without placing hints
on SQL statements, because you are unauthorized to touch the application code. Obviously, for
similar reasons, you can't rewrite the SQL. But don't lose heart; there are many tips and tricks in this
reference that will assist you when tuning packaged software.
This book portrays the message, and my firm belief, that there is always a way of improving your
performance to make it acceptable to your users.
1.1.1 Acknowledgments
Many thanks to my editor, Jonathan Gennick. His feedback and suggestions have added significant
improvements and clarity to this book. A hearty thanks to my team of technical reviewers: Sanjay
Mishra, Stephen Andert, and Tim Gorman.Thanks also to my Mark Gurry & Associates consultants
for their technical feedback. Special thanks to my wife Juliana for tolerating me during yet another
book writing exercise.
1.1.2 Caveats
This book does not cover every type of environment, nor does it cover all performance tuning
scenarios that you will encounter as an Oracle DBA or developer.
I can't stress enough the importance of regular hands-on testing in preparation for being able to
implement your performance tuning recommendations.
1.1.3 Conventions
UPPERCASE
Indicates a SQL keyword
lowercase
Indicates user-defined items such as tablespace names and datafile names
Constant width
Used for examples showing code
Constant width bold
Used for emphasis in code examples
[]
Used in syntax descriptions to denote optional elements
{}
Used in syntax descriptions to denote a required choice
|
Used in syntax descriptions to separate choices
1.1.4 What's New in Oracle9i
It's always exciting to get a new release of Oracle. This section briefly lists the new Oracle9 i features
that will assist us in getting SQL performance to improve even further than before. The new features
are as follows:
• A new INIT.ORA parameter, FIRST_ROWS_n, that allows the cost-based optimizer to
make even better informed decisions on the optimal execution path for an OLTP application.
The n can equal 1, 10, 100, or 1,000. If you set the parameter to FIRST_ROWS_1, Oracle
will determine the optimum execution path to return one row; FIRST_ROWS_10 will be the
optimum plan to return ten rows; and so on.
• There is a new option called SIMILAR for use with the CURSOR_SHARING parameter.
The advantages of sharing cursors include reduced memory usage, faster parses, and
reduced latch contention. SIMILAR changes literals to bind variables, and differs from the
FORCE option in that similar statements can share the same SQL area without resulting in
degraded execution plans.
• There is a new hint called CURSOR_SHARING_EXACT that allows you to share cursors
for all statements except those with this hint. In essence, this hint turns off cursor sharing for
an individual statement.
• There is a huge improvement in overcoming the skewness problem. The skewness problem
comes about because a bind variable is evaluated after the execution plan is decided. If you
have 1,000,000 rows with STATUS = `C' for Closed, and 100 rows with STATUS = `O' for
Open, Oracle should use the index on STATUS when you query for STATUS = `O', and
should perform a full table scan when you query for STATUS = `C'. If you used bind
variables prior to Oracle9 i , Oracle would assume a 50/50 spread for both values, and would
use a full table scan in either case. Oracle 9 i determines the value of the bind variable prior
to deciding on the execution plan. Problem solved!
• You can nowidentify unused indexes using the ALTER INDEX MONITOR USAGE
command.
• You can now use DBMS_STATS to gather SYSTEM statistics, including a system's CPU
and I/O usage. You may find that disks are a bottleneck, and Oracle will then have the
information to adjust the execution plans accordingly.
• There are new hints, including NL_AJ, NL_SJ, FACT, NO_FACT, and FIRST_ROWS(n).
All are described in detail in Section 1.7 of this reference.
• Outlines were introduced with Oracle8 i to allow you to force execution plans (referred to as
"outlines") for selected SQL statements. However, it was sometimes tricky to force a SQL
statement to use a particular execution path. Oracle9 i provides us with the ultimate: we can
now edit the outline using the DBMS_OUTLN_EDIT package.
1.2 The SQL Optimizers
Whenever you execute a SQL statement, a component of the database known as the optimizer must
decide how best to access the data operated on by that statement. Oracle supports two optimizers: the
rule-base optimizer (which was the original), and the cost-based optimizer.
To figure out the optimal execution path for a statement, the optimizers consider the following:
• The syntax you've specified for the statement
• Any conditions that the data must satisfy (the WHERE clauses)
• The database tables your statement will need to access
• All possible indexes that can be used in retrieving data from the table
• The Oracle RDBMS version
• The current optimizer mode
• SQL statement hints
• All available object statistics (generated via the ANALYZE command)
• The physical table location (distributed SQL)
• INIT.ORA settings (parallel query, async I/O, etc.)
Oracle gives you a choice of two optimizing alternatives: the predictable rule-based optimizer and
the more intelligent cost-based optimizer.
1.2.1 Understanding the Rule-Based Optimizer
The rule-based optimizer (RBO) uses a predefined set of precedence rules to figure out which path it
will use to access the database. The RDBMS kernel defaults to the rule-based optimizer under a
number of conditions, including:
• OPTIMIZER_MODE = RULE is specified in your INIT.ORA file
• OPTIMIZER_MODE = CHOOSE is specified in your INIT.ORA file, andno statistics exist
for any table involved in the statement
• An ALTER SESSION SET OPTIMIZER_MODE = RULE command has been issued

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin