Guidelines for Oracle Performance tuning

An outline that is roughly written in the optimization content of the physical design and logical design of the DB, mainly physical design, logical design introduction of the content is not much, the outline of the physical structure design and example optimization of organic combination of the logical structure design and application adjustment together ...

Oracle Performance Tuning Guidance outline

Design of database physical structure and adjustment of instance level

Oracle Performance Optimization Methodology

1. Why (what) to optimize (the system is slow?) Slow is appearance)

2. How to optimize? (Need to find a slow cause)

A Is it a system problem?

b Is it a database problem?

3. Who is to optimize?

A System Architect (System architecture design problem, big trouble)

b System administrator (OS, storage issues)

C Database administrator (db issue)

D Application Designer (Application design issues)

E Application developers (code writing issues)

Who is responsible for the problem, find reasons to refuse to let them say no ...

4. When is the optimization? (need to deactivate when optimized: 7x24)

5. Make the appropriate adjustment strategy

6. People who build the adjustment

7. Start adjusting

8. Observe the changes in performance

9. Then adjust and observe until performance is acceptable

10. Adjust the end, write the adjustment report, the meeting summary, share the experience

Second, how does Oracle work (how Oracle Works)

How to Oracle Works

The following example describes the most basic level of operations, Oracle performs. This illustrates a Oracle configuration where the user and associated server process are on separate computers (connected Through a network).

An instance has started on the computer running Oracle (often called the host or database server).

A computer running a application (a local computer or client workstation) runs the application in a user process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.

The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a dedicated server process in behalf of the use R process.

The user runs a SQL statement and commits the transaction. For example, the user changes a name in a row of a table.

The server process receives the statement and checks the shared pool for all shared SQL area that contains a similar SQL s Tatement. If a shared SQL area is found, then the server process checks the user's access privileges to the requested data, and the Previously existing shared SQL area are used to process the statement. If not, then a new shared SQL.? allocated for the statement, so it can be parsed and processed.

The server process retrieves any necessary data values from the actual datafile (table) or those the SGA.

The server process modifies data in the system global area. The DBWN process writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the LGWR process immediately records the transaction in the Redo log file.

If the transaction is successful, then the server process sends a message across the network to the application. If It is not successful, then a error message is transmitted.

Throughout this is entire procedure, the other background processes run, and watching for conditions of that require. In addition, the database server manages is other users ' transactions and prevents contention between transactions that Reque St the same data.

Iii. Rational use of computer resources

Interacting with the computer is nothing more than interacting with the following 5 important resources:

1. File (disk)

Io is undoubtedly the biggest bottleneck in computer development, so be sure to consider IO throughput when deploying storage (throughout)

2. Memory (Why does the computer use memory?) Reduce physical IO)

3. Process

A Thread (Windows)

b Process (Unix/linux)

4. Network (*)

5. CPU (*)

1. SQL diagnostics and tuning for excessive CPU consumption

2.latch wait will consume a lot of CPU

3. Intensive resolution consumes CPU resources

Third, IO adjustment

1. Principle of the IO adjustment

A Minimize IO access (designed throughout the application)

b Balance the distribution of various documents (Ctl,dbf,redo,archivedlog,tempfile)

C Rational use of temporary tablespace (group)

2. Storage-Level adjustment

A Improper storage planning causes IO to be too centralized

b Looking for evidence.

1) Iostat

2) Topas

3) Select * from V$filestat

4) Select * from V$tempstat

C Sa (System administrator) or vendor-assisted adjustment

3. DB-level tuning

A Understanding the physical structure of a database

b Reasonable planning and IO-related file distribution

C Balanced (distributed) IO

D Principles of File distribution

1 controlfile separate storage, mirror not too much, 2 enough

2 redo separate storage, not on RAID 5, member not too much, 2 enough

3) Undo Separate storage

4 datafile as far as possible to be dispersed, especially the data files stored by the partitioned table and the partitioning index

5 tempfile files are stored separately, different users use different temporary table space, it is best to use temporary tablespace group

6 Index and data separation, the impact on Io is small, easy to manage (Index principle ROWID)

7 Archivelog and all other documents are separated, be sure and redo absolute separation

Four, memory adjustment

1. OS-level tuning

A Looking for evidence.

1) Vmstat (Change page Severity page in page out)

2) Topas

b Identify the cause of insufficient OS memory

2. DB-level tuning

A Understand the role and components of the SGA

1 The activation and use of Advisor

2) Adjust Sga_target

3) Adjust buffer cache

4) Adjust shared pool

b The function of parameter sga_max_size, 9.2 can adjust memory dynamically after introducing this parameter

C LOCK_SGA, prevents the SGA from being run out by OS swap, and does not work under Windows

D Understand the role of PGA

Adjust Pga_aggregate_target

E Understanding the important concepts of work area helps to understand and adjust the PGA

V. Process adjustment

1. Types and effects of processes

A) background

b) Server

c) User (client)

2. Private mode and shared mode

3. Reasonable adjustment of process parameters

4. The meaning of field process in Windows client V$session

Six, block space use adjustment

1. DMT and LMT

2. ASSM (bitmap) and MSSM (freelist)

3. The role of Pctfree and pctused and the relationship between the constraints

Seven, lock and latch scramble

1. Row level Lock

2. Table level Lock (compatibility of table lock)

3. Block (blocking) and deadlock

4. Latch (introduced in the Wait event)

Eight, statistics

1. What is statistical information and what is its role?

2. Instance-level statistical information v$sysstat