[Laskey99] Section 3.6. Tuning Oracle

来源：百度文库编辑：神马文学网时间：2024/04/28 18:10:50

3.6.
Perhapsno single aspect of the DBA's job consumes as much time astuning. Successful Oracle tuning requires a blend of knowledge andexperience, and can be both challenging and frustrating—oftenat the same time! Entire volumes have been written on Oracle tuning(see the appendix, Resources for the DBA), andwe cannot hope to cover all aspects of tuning in a single section.Instead, as we mentioned earlier, we will outline for you an approachto tuning that can be applied to a variety of situations.

3.6.1. A Structured Tuning Approach

Successful tuning of an Oracle database requires a careful,disciplined approach. Like overall system configuration, tuning mustaddress the following:

Hardware and operating system performance
Oracle instance performance
Individual transaction (SQL) performance

These should be addressed in sequence, since database performancetuning is not possible until the operating system and hardware havebeen well tuned, and an individual SQL statement cannot be properlytuned if Oracle is not running efficiently. When tuning any of theseareas, there are three distinct steps in the process:

Measure current performance.
Make appropriate changes.
Assess the result.

Some changes to the Oracle instance may result in the need for changes to the operating system environment. For example, allocating additional database buffers may cause the operating system to start paging, which may require additional operating system tuning to eliminate.

The tuning process is almost always an iterative one. That is, aftercompleting the three steps outlined above, the DBA must return tostep 1 and repeat the process. This continues until no additionalperformance gains are possible.

3.6.2. Oracle Instance Tuning

Mostperformance improvement at the Oracle instance level will be achievedby tuning two areas: memory utilization and disk I/O.

3.6.2.1. Memory utilization

It should come as no surprise thatmemory-based operations are much faster (sometimes by thousands oftimes) than disk operations. As a result, tremendous performanceimprovements may be achieved by replacing disk I/O with memory accessof data. The three primary ways in which this can be done aredescribed in the following list:

Allocate additional DB_BLOCK_BUFFERS: This is probably the single most effective method of improvingoverall performance, particularly on queries. Additional databasebuffers allow more data blocks to remain in memory, so the datacontained in these blocks can be accessed at memory speed with noneed for disk I/O. Buffers are allocated using theINIT.ORA parameter DB_BLOCK_BUFFERS, and thevalue is the number of database block buffers to be allocated. So, ifthe database block size is 8192, each DB_BLOCK_BUFFER will be 8192bytes. Note that changes to DB_BLOCK_BUFFERS do not take effect untilthe next time the database is started.

Be careful not to allocate so many DB_BLOCK_BUFFERS that the operating system begins to page; paging will eliminate any performance gain you may have achieved and will probably have an overall negative effect on performance.

Allocate additional shared pool

The shared pool size is controlled bythe INIT.ORA parameter SHARED_POOL_SIZE, whichspecifies a shared pool size in bytes. The primary contents of theshared pool are the dictionary cache and the shared SQL area. Sincethe various components of the dictionary cache are automaticallyallocated by Oracle, any increase in the size of the shared poolresults in additional memory for both the dictionary cache and theshared SQL area.

The shared SQL area contains copies of the most recently executed SQLstatements, along with associated information like their executionplans. With a larger shared pool, it is more likely that a particularSQL statement has already been parsed and is resident in the sharedSQL area, thereby saving the time required to reprocess thestatement. This can be of particular value in a transactionprocessing system, where the same SQL statements are executedmultiple times and where speed is a requirement.

Allocate additional log buffer space

The log buffer is used to hold data to bewritten to the online redo log file. The size of the log buffer iscontrolled by the INIT.ORA parameterLOG_BUFFER, and the value is expressedin bytes. By allocating additional memory to the log buffer, disk I/Owill be reduced, especially when transactions are long or numerous.

3.6.2.2. Disk I/O

Disk accessis the slowest operation on any computer system. As a databasesystem, Oracle relies heavily on disk access for storage of andaccess to data. Consider a typical SQL statement that updates a rowof a table. The following operations take place:

The data dictionary is read to get information about the table and row being manipulated.
The appropriate index is read to locate the row to be updated.
The data block containing the row is read.
Rollback information is written to a rollback segment.
Update information is written to the online log file.
The data block is rewritten.
The index block is rewritten.

All these operations potentially require disk I/O, although some maybe eliminated by efficient use of memory, as we described in theprevious section. By making disk I/O as efficient as possible,overall performance will be enhanced. The basic guidelines formaximizing disk I/O are the following:

Segregate I/O operations to separate disks wherever possible. In this way, there is no need to wait for one disk operation to finish before another is performed. For example, if both the rollback segment and the log file were on the same disk, the rollback record would be written; then the disk head would need to be moved to another part of the disk where the log file record would be written. This would be very time-consuming.
Place high-I/O-volume disks on different controllers. Most modern controllers can handle a limited number of concurrent operations, but using as many controllers as possible will eliminate any controller waits and will speed performance.
Place busy files and tablespaces (e.g., log files, rollback segments, some indexes) on the fastest available disks.

3.6.2.3. A note about RAID

Recent developments in disk technologyhave made RAID (Redundant Arrays of Inexpensive Disks) a popularoption on many systems. Often, when the term RAID is used, hardwareadministrators immediately think of RAID level 5 (or RAID-5), whichallows multiple disk devices to be combined to form one large device.By allocating one device for the storage of redundant data, a RAID-5disk array is protected from the failure of any single disk in thearray, and is often hot swappable, which meansthat a failing disk can be replaced even as the other drives continueto function, with no need to shut down the system.

RAID-5 is, in fact, very powerful and inexpensive. It is also atechnology to be avoided in most cases when configuring your Oracledatabase! This may seem a harsh statement, but the reality is thatalthough RAID-5 provides good levels of data protection at a lowmonetary cost, this comes at a very high cost for disk I/O. Inparticular, write operations on RAID-5 arrays can be orders ofmagnitude slower than the same operations on a single disk.

A good alternative to the RAID-5 array is the use of RAID level 1,commonly known as diskmirroring. Although more expensive than RAID-5 (one-half of the disksare used for storing redundant data), RAID-1 provides complete dataprotection with no sacrifice in I/O efficiency.

RAID-1 requires sufficient hardware resources. In particular, since each write operation actually results in two writes to disk, the load on the controller is doubled compared to non-RAID.

The best RAID performance available today is called RAID-0+1,sometimes called RAID-10. This level of RAID combines mirrored disks(as in RAID-1) withstriping (RAID-1) of data across multipledrives, which can eliminate any delay while waiting for disk headpositioning. While not available from all RAID controllers, RAID-0+1is well worth considering.

3.6.2.4. Operating system striping

Manyoperating systems offer automatic striping of disk sectors acrossmultiple devices. This striping permits disk I/O to continuesequentially without a delay for head positioning. While thistechnique provides better performance than that achieved on a singledisk, it has a disadvantage: combining disks into a single stripedunit means that the DBA is no longer able to control the location ofindividual files on separate devices. If you can only have a fewlarge disk devices on your system, you should consider operatingsystem striping, but multiple devices or multiple RAID-0+1 arrayswill usually yield better performance from Oracle.

3.6.2.5. Oracle striping

Asthe DBA, you can achieve results similar to operating system stripingby carefully allocating datafiles to individual devices or RAID-0+1arrays. For example, to set up Oracle striping across four disks, dothe following:

Create a tablespace with four datafiles, each located on a different device.
Create objects in the tablespace, specifying MINEXTENTS 4. Oracle will allocate the four extents on the four datafiles, thereby implementing striping. This action is not automatic; it can be accomplished by using the ALTER TABLE ... ALOCATE EXTENT command.

The Oracle striping technique is very powerful, especially whencombined with Parallel Query, which will allow query processing bymultiple CPUs.

3.6.3. SQL Tuning

Supposethat the host server and operating system are running smoothly atyour site, and you have configured and tuned Oracle to run at thepeak of perfection, but performance on your critical application isstill poor. Unfortunately, this is not an uncommon occurrence. Thesolution is to tune the application by examining and tuning the SQLstatements being executed.

SQL tuning is a subject that deserves a book of its own. In fact,there are several good books on the market that address tuning inmuch more detail than is available here. We urge you to check thesources listed in the appendix, Resources for theDBA. In this section, we'll offer some brief adviceand guidelines for tuning your SQL statements.

3.6.3.1. Query processing

Chapter 8, describes how Oracle creates a plan for aparticular SQL statement. Oracle currently uses one of two methodsfor determining how to execute a SQL statement:

Rule-based method: Applies a standard, inflexible (butoften efficient) set of rules to the statement
Cost-based method: Considers the availablestatistical information about the objects referenced by a SQLstatement (along with available indexes) and creates a plan based onthose statistics

The keys to tuning a SQL statement are understanding how the Oraclequery optimizers work and knowing how to change Oracle'sbehavior so it will process the statement more efficiently.

Of course, before you can tune a SQL statement, you must know what itis doing and how. There are many tools on the market today that willhelp with this task, and one of the most useful (if not theflashiest) is the EXPLAIN PLAN command available inSQL*Plus. By creating a plan table (usually known as PLAN_TABLE) andexamining the result of an EXPLAIN PLAN statement, you'lleasily see how Oracle executes a particular statement. For example,the SQL statement:

SELECT ename,loc,sal,hiredate
FROM   scott.emp, scott.dept
WHERE  emp.deptno=dept.deptno;

can be explained with the following command:

EXPLAIN PLAN SET STATEMENT_ID='DEMO' FOR
SELECT ename,loc,sal,hiredate
FROM   scott.emp, scott.dept
WHERE  emp.deptno=dept.deptno;

The results stored in PLAN_TABLE can be selected using a simple query:

SELECT LPAD(' ',2*level) || operation || '' || options || ' '||
       object_name EXPLAIN_PLAN
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id=1

and will look like this:

EXPLAIN_PLAN
-------------------------------
NESTED LOOPS
  TABLE ACCESSFULL DEPT
  TABLE ACCESSFULL EMP

This plan shows that both the DEPT and EMP tables will be accessedusing a full table scan. This is fine for two small tables like EMPand DEPT; in fact, we want them to be full table scans, because thetables will be cached in memory and no disk I/O will be required(after the first execution, at least). However, if the tables werelarge, this query could run for a long time, and so we would want tochange the way this query is performed.

There are three basic ways to modify the behavior of Oracle'squery optimizer:

Provide one or more indexes to be used in executing the query.
Rewrite the SQL to use a more efficient method.
Provide direction to the query optimizer in the form of hints.

If we try the first option and add an index on EMP(deptno), the planwill change as follows:

EXPLAIN_PLAN
---------------------------------------------
  NESTED LOOPS
    TABLE ACCESSFULL DEPT
    TABLE ACCESSBY ROWID EMP
      INDEXRANGE SCAN EMPDEPT_IX

You can now see that Oracle will use the index to retrieve rows fromEMP via the ROWID, which was obtained from the newly created index,and a full table scan is no longer necessary.

There is often more than one way to perform a particular functionusing SQL, and it is good programming practice to try several methods(with appropriate benchmarking) before settling on the correct SQLstatement to use. Chapter 8, provides moredetailed information on SQL tuning.

3.6.4. Other Useful Tuning Features

Oracle has continued to improve its database product by adding newfeatures that help boost performance. It is important to check theRelease Notes on even minor upgrades toOracle, since new performance features are often included. Some ofthe features and facilities you might find useful are listed in thissection.

3.6.4.1. Partitioned tables

Partitionedtables, which are available beginning with Oracle8, allow a table tobe created across multiple subtables, each of which holds aparticular subset of the table data. For example, a table could bepartitioned by year, with all data from 1998 in one partition, all1999 data in another, and so on. Partitioning is particularly usefulfor large tables, since queries involving only an identifiable subsetof data can operate on the data in the appropriate partitions withoutaccessing other partitions. For example, updating 1999 records wouldonly require Oracle to perform I/O operations on the 1999 partitionof the table. Partitioning is specified in the CREATE TABLE statement. In order to usethis feature, you must:

Identify the data field that will define the partition (for example, sales_year).
Specify the ranges of values in the CREATE TABLE ... PARTITION BY RANGE clause.
Specify a different tablespace (for best performance, place each on a separate disk) for each partition of the table. Note that separate tablespaces are not required, but this practice allows a partition of the table to be taken offline while maintaining access to the balance of the table.

Partitioned tables should usually be accompanied by a correspondingpartitioned index, as follows:

Use the LOCAL keyword in the CREATE INDEX command to tell Oracle to create a separate index for each partition of the indexed table.
Use the GLOBAL keyword in the CREATE INDEX command to tell Oracle to create a single index using values that may not correspond to the partitioning of the indexed tables. GLOBAL indexes may also be partitioned.

3.6.4.2. Index-only tables

Insome cases, all the data that would normally be stored in a table canbe stored in an index, and the table is not necessary. An index-onlytable, available starting with Oracle8, keeps the data sortedaccording to the primary key column. There are some limitations tothis type of object:

Since the data is not stored in a table, there are no ROWIDs available.
A primary key must be defined for the table.
No additional indexes can be created; only the primary key may be indexed.

An index-only table is created by using theORGANIZATION INDEX clause of the CREATETABLE command.

3.6.4.3. Bitmap indexes

Bitmap indexes can yield greatlyimproved performance when the data being indexed has lowcardinality—that is, if there are relatively few distinctvalues for the indexed column. An example of a good candidate for abitmap index would be GENDER, which would have values of"M" or "F". A poor candidate for a bitmapindex would be SALES_AMOUNT, which is likely to have a differentvalue for almost every row.

Creating a bitmap index is similar to creating a standard index; youinclude the keyword BITMAP in the CREATE INDEX statement. Forexample, to create a bitmap index on the GENDER column of anEMPLOYEE_MASTER table, you'd specify the following statement:

CREATE BITMAP INDEX empmast_ix ON employee_master(gender);

3.6.4.4. Temporary tablespaces

Oracle7introduced the concept of temporary tablespaces, which are usedexclusively for Oracle's sort segments. By eliminatingserialization of space management operations involved in theallocation and deallocation of sort space, all operations that usesorts can benefit from improved performance when sorts are too largeto fit in memory. These performance gains are particularlysignificant when running Oracle Parallel Server.

A temporary tablespace can be used only for sort segments; no permanent objects may be created in a temporary tablespace.

To create a temporary tablespace, use the keyword TEMPORARY in the CREATE TABLESPACEstatement. For example, the following statement will create atemporary tablespace called TEMP:

CREATE TABLESPACE TEMP
DATAFILE '/disk99/oracle/oradata/TEST/temp01.dbf' SIZE 50M
DEFAULT STORAGE (INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED)
TEMPORARY;

An existing non-temporary tablespace may be converted to a temporarytablespace by using the SQL statement if it contains no permanentobjects:

ALTER TABLESPACE tablespace TEMPORARY;

3.6.4.5. Unrecoverable operations

Beginningwith Oracle 7.2, it has beenpossible to create a table or index without writing redo log records.This option provides better performance, since significantly less I/Ois required. To take advantage of this feature, specify eitherUNRECOVERABLE (Oracle7 syntax) or NOLOGGING (Oracle8 syntax) in theobject creation statement. For example, suppose that you are movingdata from another database using a database link and that you use thestatement:

INSERT INTO newtable
SELECT * from oldtable@oldlink;

This method would certainly work, but redo log records would becreated for each insert, which could be costly. The same task couldbe accomplished with the following statement:

CREATE TABLE newtable AS
SELECT * from oldtable@oldlink
NOLOGGING;

The NOLOGGING option is particularly useful when rebuilding indexes.The inclusion of the NOLOGGING keyword can cut substantial time fromindex creation. The SQL statement would look similar to this:

CREATE INDEX indexname ON table(column)
NOLOGGING;

Note, however, that if you experience a system failure at some pointafter an unrecoverable statement has completed, you will be unable torecover the transactions using the roll forward mechanism. You mustrecognize that a system failure has occurred and rerun thestatement.

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint

Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next