Oracle Expert Tuning Secrets

Document Sample
Oracle Expert Tuning Secrets Powered By Docstoc
					Oracle Expert Tuning Secrets
Reproduced in
In the past decade, Oracle has become the world's most professional
databases. For IT specialists is to ensure that the use of Oracle's powerful
features to improve their productivity. One of the most effective way is through
Oracle tuning. It has a large number of tuning parameters and techniques to improve
the performance of your Oracle database.

Oracle tuning is a complex subject. Tuning can be written on a whole book, however,
in order to improve Oracle database performance, there are some basic concepts that
every Oracle DBA should follow.

In this profile, we will briefly introduce the Oracle the following topics:

- External adjustment: we should remember that Oracle is not a separate operation. So
we will look by adjusting the Oracle server to get high performance.

- Row re-sequencing in order to reduce disk I / O: Oracle tuning we should know the
most important goal is to reduce the I / O.

- Oracle SQL adjustment. Oracle Oracle SQL adjustment is to adjust one of the most
important areas, as long as the adoption of some simple rules of SQL tuning can
dramatically improve the performance of SQL statements, this is not a little strange.

- Adjust Oracle Sort: Sort performance for Oracle is very much affected.

- Adjust Oracle's competition: table and index parameter settings for
UPDATE and INSERT performance has a great influence.

We first Oracle external environment from the beginning to adjust. If insufficient
memory and CPU resources, then any adjustment will not help Oracle.

External performance issues

Oracle is not a separate operation. Oracle database performance and external
environment have a great relationship. These external conditions include:

. CPU - CPU resources, slow down enough to make inquiries. When the query over
the Oracle server CPU performance, your database performance by the CPU on the

. Memory - the amount of memory available for Oralce will affect SQL performance,
particularly in the data buffer and memory sequencing.

. Network - a large number of Net8 communication so slow SQL performance.

Many novice mistake that should first adjust the Oracle database, not the first to
confirm the adequacy of external resources. In fact, if the external environment,
bottlenecks, no matter how much Oracle adjustment is not helpful.

Oracle's external environment in check, there are two aspects to note:

1, when the number of the run queue over the number of CPU servers, the server CPU
performance would be restrictions. Remedy is for the server, additional CPU
processing resources on or off requires a lot of components, such as Oracle Parallel

2, memory paging. When paging, the memory capacity has been inadequate, but the
memory page is on the disk swap and interact. Remedy is to add more memory,
reduce the size of the Oracle SGA, or shut down Oracle's multi-threaded

You can use a variety of standard server tools to get the server statistics, such as
vmstat, glance, top, and sar. DBA's goal is to ensure that the database
server has enough CPU and memory resources to handle Oracle's request.

Let us look at Oracle's row-resequencing is how to greatly reduce the disk I
/ O's.

Row-resequencing (line re-order)

As we mentioned above, experienced Oracle DBA know I / O is the largest
component of response time. One disk I / O special smart because when the Oracle
from the disk in a data file by a data block, the reading process must wait for physical
I / O operation is complete. Disk operation 10,000 times slower than the data buffer.
Therefore, if you can make I / O to minimize or reduce the file because the disk
bottleneck caused by competition, can greatly improve the performance of Oracle

If the system response is slow, by reducing the disk I / O can have a quick
improvement. If a transaction search through a certain range of primary-key index to
access the table, then re-organized table with CTAS method will you reduce the I /
O's primary strategy. By order of the physical will be OK and the same
primary-key index of the order, you can accelerate the speed of access to data.

Like disk load balancing, as the line is very simple reordering, but also quickly. DBA
with other management techniques used together, can in high I / O systems greatly
reduce the response time.

In the high-volume online transaction processing environment (online transaction
processing, OLTP), data is received by a primary index, re-sort the table can make a
continuous line of block order and as their primary index, so that you can in the index
driven table queries, the reduction of physical I / O and improve response time.
Choice in the application of this technique is only useful if multiple rows, or use the
index range search and application to issue multiple queries to be effective when
continuous key. Only for random primary-key (primary key) of the visit will not be
reordered by the line to benefit.

Let's look at how it works. Consider the following as a SQL query, it uses
an index to get 100 lines:

This query will use last_name_index, search for them to get the goal line in each row.
This query will use at least 100 physical disk reads because the employee's
line of data stored in different blocks.

However, if the table rows have been reordered and last_name_index for the same, the
same query will be handled? We can see that this query only needs three times the
disk I / O to read all 100 employees of the data (once used as an index of reading, two
for data block read), a decrease of 97 times the block read .

Reorder the extent of performance improvements brought about is when you start the
line of how out of order, and you need to access from the sequence number of rows.
As a table row with the index matching degree of the sort key, you can view data
dictionary views in dba_indexes and dba_tables be.

In dba_indexes's view, see clustering_factor out. If clustering_factor the
value and the number of blocks the table are similar, then your tables and indexes in
the order is the same. However, if clustering_factor the value of the number of rows
close to the table, it shows that the index in the table rows and the order is not the

Row re-ordering can not underestimate the role. The need for large-scale search of the
big table in the index, rows can be reordered so that the performance of query triple.

Once you have decided to re-sort the table rows, you can use one of the following
tools to re-organize the form.

. Using Oracle's Create Table As Select (CTAS) to copy the table syntax
. Oracle9i Form that comes with re-organization tools

The following, we look at the following SQL statement tuning.

SQL Tuning

Oracle's SQL tuning is a complex subject, and even need the whole book to
introduce Oracle SQL tuning nuances. But there are some basic rules that every
Oracle DBA needs to follow, these rules can improve their system performance. SQL
tuning goal is simple:

. Eliminate unnecessary large table full table search: unnecessary full table search
resulted in substantial unnecessary I / O, which slow down the performance of the
entire database. Tuning expert first to the number of rows the query returns to
evaluate the SQL. In an ordered table, if the query returns less than 40% of the line, or
a disorder of the table, returned less than 7% of the line, then this query can be
adjusted to use an index instead of full table search . Unnecessary search for a full
table, the most common tuning is to increase the index. Standards can be added in the
table B-tree index, you can also join the bitmap and function-based index. To decide
whether to remove a full table search, you can double-check the index search I / O
overhead and the cost of full table search their overhead and data blocks read and the
possible parallel implementation, and compare the two. In some cases, some of the
unnecessary search for the elimination of all forms can be forced to use an index to
achieve, only to join in the SQL statement, an index of tips on it.

. In the whole table search is one of the fastest access method, to search for a small
table full-table into the cache, tuning specialists should ensure that there is a dedicated
data buffer for line buffer. In Oracle7, you can use the alter table xxx cache statement
in Oracle8 or more, small table can be forced into the KEEP buffer pool.

. To ensure that the best index to use: to improve the speed of query, which is
particularly important. Sometimes Oracle may choose to query multiple indexes,
tuning experts must examine each index and ensure that Oracle use the correct index.
It also includes a bitmap and function-based index of the use.

. To ensure optimal JOIN operation: Some queries use NESTED LOOP join faster,
some are HASH join faster, some are faster sort-merge join.

These rules seem simple, but they account for SQL tuning task, 90%, and they do not
fully understand the inner workings of Oracle SQL. Here we come to a simple
overview of the following Oracle SQL optimization.

We briefly see Oracle's ranking, and take a look at the sorting operation is
how to influence performance.
Adjust the operation of Oracle's Sorting

Sorting is the SQL syntax in a small area, it is important to the adjustment in Oracle, it
is often overlooked. When using the create index, ORDER BY or GROUP
BY's statement, Oracle Database will automatically sort operation.
Typically, in the case of Oracle will be the following sort of operation:

Use Order by the SQL statement

Group by using the SQL statement

When you create an index

For table join, because of the shortage caused by the existing index SQL optimizer

When establishing a session with Oracle, the in memory will be allocated for the
session sort of a private area. If the connection is a dedicated connection (dedicated
connection), it will be according to the size of init.ora parameters in sort_area_size
memory allocation in a Program Global Area (PGA). If the connection is established
through multi-threaded server, then the sort of space is allocated in large_pool.
Unfortunately, for all of the session, the amount of memory used to sort must be the
same, we can not sort of operation that require a greater allocation of additional
sorting area. Therefore, the designer must make a balance in the allocation of
sufficient order to avoid a major regional sorting task, a disk sort (disk sorts), while
those who do not require a great sort of task, there will be a number of waste . Of
course, when the demand exceeds the sort of space the size of sort_area_size, when
TEMP table space will be paged to disk sort. Sort than the sort of memory disk about
14,000 times slower.

We have already mentioned above, the size of the private sort area is in the
sort_area_size init.ora parameters determined. The size of the space occupied by each
sort of sort_area_retained_size by the init.ora parameters. When the order can not be
completed in the allocated space, they will use the disk sort of way, that Oracle
instance in the temporary table space for.

Disk sort of overhead is huge, there are several reasons. First of all, and sort of
memory compared to their particularly slow; and disk sort consumes resources in the
temporary table space. Oracle must also allocate buffer pool blocks to keep the
temporary table space in the block. At any time, memory, disk sort order than the
good, the disk sorting will cause a slow task, and the Oracle instance will affect the
implementation of the current task. Also, excessive disk sorting will cause the value
of free buffer waits becomes higher, so that other tasks of the data blocks are removed
from the buffer.

Then, let's look at Oracle's competition and look at setting the
table storage parameters affect how the SQL UPDATE and INSERT statements of
Oracle's competition adjustment

One of the advantages of Oracle when it can manage each table space in the free
space. Oracle to deal with the space management tables and indexes, so that we do not
know how Oracle can table and index's internal operations. However, for
experienced Oracle tuning experts, he needs to know how to manage Oracle is the
extent and the idle table data blocks. The adjustment has a high insert or update the
system, it is very important.

To adjust the master object, you need to understand the behavior of freelists and
freelist groups, they and pctfree and pctused parameter related. Knowledge for
enterprise resource planning (ERP) application is particularly important, because in
these applications, the incorrect table setting is usually the reason behind the
implementation of DML statement.

For beginners, the most common error is that the default Oracle parameters for all the
objects are the best. Unless the consumption of the disk is not a problem, or set the
table pctfree and pctused parameters, we must consider the average of the president
and the database block size, so empty block will be effectively put freelists. When
these settings are not correct, those are the freelists is "dead"
blocks, because they do not have enough space to store the line, this will result in
significant processing delays.

Freelists for the effective re-use the Oracle table space, the space is very important, it
pctfree and pctused these two parameters to set directly related to the storage. By
pctused set to a high value, then the database will be re-used blocks as quickly as
possible. However, the high performance and efficient re-use of the table block is the
opposite. Oracle forms and in adjusting the index, you need to seriously consider what
you really need high performance or the effective spatial reuse, and accordingly to set
the table parameters. Below we look at how these freelists affect the performance of

When there is a request to insert a line to the table when, Oracle will be to the freelist
to find an adequate space to accommodate the line of block. You may know, freelist
string on the table or index is the first block, the block is also known as the first
segment (segment header). pctfree and pctused parameters for the sole purpose is to
control how freelists in and out of blocks. Although the freelist link and unlink a
simple Oracle functions, but set the freelist link (pctused) and unlink (pctfree) on the
exact impact on Oracle performance.
By the DBA to know the basics, pctfree parameters to control the freelist un-links the
(block by the freelists be removed). Set pctfree = 10 means that 10% of each block of
space reserved for row expansion. pctused parameters to control the freelist re-links
the. Set pctused = 40 means that only the use of less than 40% of the block will return
to form when the freelists.

Many novice back for a block after the treatment freelists are some misunderstandings.
In fact, once due to a delete operation and that block has been re-added to the freelist,
it will always remain in the freelist even the use of space in more than 60%, only
when reached will block pctfree are removed from the freelist .

Table and index storage parameters of the requirements set summary

Here are some rules are used to set freelists, freelist groups, pctfree and pctused
storage parameters. You know, pctused and pctfree values can be easily modified
through the alter table command, a good DBA should know how to set these
parameters, the best value.

Effective use of space and there is a contradiction between the high-performance, and
the table storage parameters that control this aspect of the contradiction:

. The need for effective re-use of space, you can set a high pctused value, but the side
effects is the need for additional I / O. A high value means that a relatively full
pctused will put the block in the freelist. Therefore, these blocks filled in again a few
lines before the record can only be accepted, resulting in more I / O.

. The pursuit of high performance, it can be pctused set to a low value, which means
that Oracle will not put freelists in block until it is almost empty. Then the block will
be received in full before the more rows, it can reduce the insertion of the I / O.
Oracle extension to remember than the performance of the new block to re-use the
existing blocks high. For Oracle, the expansion of a forms management freelists
consume less than the resources.

Let's look at an object storage parameters to set some common rules:

. Often pctused set to receive a new line. For not accept his party's free
blocks for us, is of no use. If so, will cause slow performance of Oracle, because
Oracle will extend the table to get an empty block before attempting to read the five
"dead" in the free block.

. The emergence of chained rows in the table means that pctfree too low or too little
db_block_size. In many cases, RAW and LONG RAW columns are great, even more
than the maximum block size of Oracle, then chained rows is avoided.
. If a table has also inserted the SQL statement, it needs also delete the statement. A
clear run a single job will be to all of the free block into a freelist, while no other
contain any free blocks freelists appear.

. freelist parameter should be set to update the table while the maximum value. For
example, if at any time, a table up to 20 users to perform insert operation, then the
parameters of the table should be set to freelists = 20.

Should be kept in mind that freelist groups parameter is only for Oracle Parallel
Server and Real Application Clusters is useful. For these Oracle, freelist groups
should be set to access the table the number of Oracle Parallel Server instance.