There are 4 main areas of performance tuning:
1 SQL Tuning – Responsibility of the Developer
2 Database Tuning – Responsibility of the Database
Administrator
3 System Tuning – Responsibility of the System
Administrator
4 Network Tuning – Responsibility of the Network / LAN /
WAN Administrator.
Before you begin
Every Oracle developer should have a copy of the Oracle
Online Documentation – a series of books in HTML format forming a complete
reference of the RDBMS and other Oracle products. Critical to understanding
performance tuning are the manuals:
- Concepts
- Performance Tuning Guide and Reference
- SQL Reference
Do this before you start individual SQL statement tuning
This broad-brush approach can save thousands of hours
of tedious SQL tuning because you can hundreds of queries at once.
Remember, you MUST do this first, else later changes to the optimizer
parameters or statistics may un-tune your SQL.
Remember, you must ALWAYS start with system-level SQL
tuning, else later changes might undo your tuned execution plans:
- Optimize the
server kernel -
You must always tune your disk and network I/O subsystem (RAID, DASD
bandwidth, network) to optimize the I/O time, network packet size and
dispatching frequency.
- Adjusting your
optimizer statistics - You must always collect and
store optimizer statistics to allow the optimizer to learn more about the
distribution of your data to take more intelligent execution plans.
Also, histograms can hypercharge SQL in cases of determining optimal table
join order, and when making access decisions on skewed WHERE clause
predicates.
- Adjust optimizer
parameters
- Optimizer optimizer_mode, optimizer_index_caching,
optimizer_index_cost_adj.
- Optimize your
instance
- Your choice of db_block_size, db_cache_size, and OS
parameters (db_file_multiblock_read_count, cpu_count, &c), can
influence SQL performance.
- Tune your SQL Access workload with physical indexes and materialized views - Just as the 10g SQLAccess advisor recommends missing indexes and missing materialized views, you should always optimize your SQL workload with indexes, especially function-based indexes, a benefit for SQL tuning.
Oracle SQL tuning goals:
Oracle
SQL tuning is a phenomenally complex subject. There are some general guidelines
that every Oracle DBA follows in order to improve the performance of their
systems.
The
goals of SQL tuning focus on improving the execution plan to fetch the rows
with the smallest number of database "touches" (LIO buffer gets and
PIO physical reads).
1.
Remove unnecessary large-table full-table scans—Unnecessary
full-table scans cause a huge amount of unnecessary I/O and can drag-down an
entire database. The tuning expert first evaluates the SQL based on the number
of rows returned by the query. The most common tuning remedy for unnecessary
full-table scans is adding indexes. Standard b-tree indexes can be added to
tables, and bitmapped and function-based indexes can also eliminate full-table
scans. In some cases, an unnecessary full-table scan can be forced to use an
index by adding an index hint to the SQL statement.
2.
Cache small-table full-table scans—In cases where a
full-table scan is the fastest access method, the administrator should ensure
that a dedicated data buffer is available for the rows. In Oracle8 and
beyond, a small table can be cached by forcing it into the KEEP pool.
3.
Verify optimal index usage—Oracle sometimes
has a choice of indexes, and the tuning professional must examine each index
and ensure that Oracle is using the proper index.
4.
Materialize your aggregations and summaries for static
tables
- One features of the Oracle 10g SQLAccess advisor is recommendations
for new indexes and suggestions for materialized views. Materialized
views pre-join tables and pre-summarize data, a real silver bullet for data
mart reporting databases where the data is only updated daily. Again, see the
book "Oracle Tuning: The
Definitive Reference",
for complete details on SQL tuning with materialized views.
A strategic plan for
Oracle SQL tuning
Step 1: Identify high-impact
SQL
The SQL statements will be ranked according the number of executions and will be tuned in this order. The executions column of the v$sqlarea view and the stats$sql_summary or the dba_hist_sql_summary table can be used to locate the most frequently used SQL. Note that we can display SQL statements by:
The SQL statements will be ranked according the number of executions and will be tuned in this order. The executions column of the v$sqlarea view and the stats$sql_summary or the dba_hist_sql_summary table can be used to locate the most frequently used SQL. Note that we can display SQL statements by:
- Rows processed: Queries
that process a large number of rows will have high I/O and may also have
impact on the TEMP tablespace.
- Buffer gets: High
buffer gets may indicate a resource-intensive query.
- Disk reads: High
disk reads indicate a query that is causing excessive I/O.
- Memory KB: The
memory allocation of a SQL statement is useful for identifying statements
that are doing in-memory table joins.
- CPU secs: This
identifies the SQL statements that use the most processor resources.
- Sorts: Sorts
can be a huge slowdown, especially if they're being done on a disk in the
TEMP tablespace.
- Executions: The more frequently executed SQL statements should be tuned first, since they will have the greatest impact on overall performance.
Step 2:
Determine the execution plan for SQL
As each SQL statement is identified, it will be
'explained' to determine its existing execution plan. There are a host of
third-party tools on the market that show the execution plan for SQL
statements. The most common way of determining the execution plan for a SQL
statement is to use Oracle's explain plan utility. By using explain plan, the
Oracle DBA can ask Oracle to parse the statement and display the execution
class path without actually executing the SQL statement.
To see the output of an explain plan, you must first
create a 'plan table.' Oracle provides a script in $ORACLE_HOME/rdbms/admin
called utlxplan.sql. Execute utlxplan.sql and create a public synonym for the
plan_table:
Most relational databases use an explain utility that
takes the SQL statement as input, runs the SQL optimizer, and outputs the
access path information into a plan_table, which can then be interrogated to
see the access methods. Listing 1 runs a complex query against a database.
This
syntax is piped into the SQL optimizer, which will analyze the query and store
the plan information in a row in the plan table identified by RUN1. Please note
that the query will not execute; it will only create the internal access
information in the plan table. The plan tables contains the following fields:
- operation: The type
of access being performed. Usually table access, table merge, sort, or
index operation
- options:
Modifiers to the operation, specifying a full table, a range table, or a
join
- object_name: The name
of the table being used by the query component
- Process ID: The
identifier for the query component
- Parent_ID: The parent of the query component. Note that several query components may have the same parent.
Step 3: Tune the SQL statement
- Adding SQL 'hints' to modify the execution plan
- Re-write SQL with Global Temporary Tables
- Rewriting the SQL in PL/SQL. For certain queries this can result in more than a 20x performance improvement. The SQL would be replaced with a call to a PL/SQL package that contained a stored procedure to perform the query.
Using hints to tune Oracle SQL
Among the most common tools for tuning SQL statements are hints. A hint is a directive that is added to the SQL statement to modify the access path for a SQL query.
Among the most common tools for tuning SQL statements are hints. A hint is a directive that is added to the SQL statement to modify the access path for a SQL query.
Oracle
publishes many dozens of SQL hints, and hints become increasingly more
complicated through the various releases of Oracle and on into Oracle.
Note:
Hints are only used for de-bugging and you should adjust your optimizer
statistics to make the CBO replicate the hinted SQL. Let's look at the
most common hints to improve tuning:
1.
Mode hints: first_rows_10,
first_rows_100
2.
Oracle leading and ordered hints
Also see how to tune table join
order with histograms
3.
Dynamic sampling: dynamic_sampling
4.
Oracle SQL undocumented tuning hints
- Guru's only
5.
The
cardinality hint
6.
Self-order
the table joins - If you find that
Oracle is joining the tables together in a sub-optimal order, you can use the
ORDERED hint to force the tables to be joined in the order that they appear in
the FROM clause.
7.
Try a first_rows_n hint. Oracle has two
cost-based optimizer modes, first_rows_n and
all_rows. The first_rows mode will
execute to begin returning rows as soon as possible, whereas the all_rows mode
is designed to optimize the resources on the entire query before returning
rows.
SELECT
/*+ first_rows */
A case study in SQL
tuning
One of the historic problems with SQL
involves formulating SQL queries. Simple queries can be written in many
different ways, each variant of the query producing the same result, but with
widely different access methods and query speeds.
For example, a simple query such as 'What students received an A last semester'' can be written in three ways, as shown in below, each returning an identical result.
A standard
join:
SELECT *
FROM STUDENT, REGISTRATION
WHERE
STUDENT.student_id = REGISTRATION.student_id
AND
REGISTRATION.grade = 'A';
A nested
query:
SELECT *
FROM STUDENT
WHERE
student_id =
(SELECT
student_id
FROM REGISTRATION
WHERE
grade
= 'A'
);
A correlated subquery:
SELECT *
FROM STUDENT
WHERE
0 <
(SELECT count(*)
FROM REGISTRATION
WHERE
grade = 'A'
AND
student_id = STUDENT.student_id
);
Let's wind up with a review of the basic
components of a SQL query and see how to optimize a query for remote execution.
Space doesn't permit me to discuss every detail of Oracle tuning, but I can share some general rules for writing efficient SQL in Oracle regardless of the optimizer that is chosen. These rules may seem simplistic but following them in a diligent manner will generally relieve more than half of the SQL tuning problems that are experienced:
- Rewrite
complex subqueries with temporary tables - Oracle created the global
temporary table (GTT) and the SQL WITH operator to help divide-and-conquer
complex SQL sub-queries (especially those with with WHERE clause
subqueries, SELECT clause scalar subqueries and FROM clause in-line
views). Tuning
SQL with temporary tables (and materializations in the WITH
clause) can result in amazing performance improvements.
- Use minus instead of EXISTS subqueries - Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan.
- Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.
- Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join.
select book_key from book
where
book_key NOT IN (select book_key from sales);
book_key NOT IN (select book_key from sales);
Below we combine the outer join with a NULL
test in the WHERE clause without using a sub-query, giving a faster execution
plan.
select b.book_key
from book b, sales s
where b.book_key = s.book_key(+)
and s.book_key IS NULL;
where b.book_key = s.book_key(+)
and s.book_key IS NULL;
- Index your NULL values - If you have SQL that frequently tests for NULL, consider creating an index on NULL values. To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns.
- Leave column names alone - Never do a calculation on an indexed column unless you have a matching function-based index (a.k.a. FBI). Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF:
where
salary*5 >
:myvalue
where substr(ssn,7,4) = "1234"
where to_char(mydate,mon) = "january"
where substr(ssn,7,4) = "1234"
where to_char(mydate,mon) = "january"
- Avoid the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster (when appropriate).
- Avoid
the LIKE predicate = Always replace a "like" with an
equality, when appropriate.
- Never mix data types - If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates:
where cust_nbr =
"123"
where substr(ssn,7,4) = 1234
where substr(ssn,7,4) = 1234
- Use decode and case - Performing complex aggregations with the "decode" or "case" functions can minimize the number of times a table has to be selected.
- Don't fear full-table scans - Not all OLTP queries are optimal when they uses indexes. If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan. This depends on many factors, including your configuration (values for db_file_multiblock_read_count, db_block_size), query parallelism and the number of table/index blocks in the buffer cache.
- Use those aliases - Always use table aliases when referencing columns.