An overview of the basic methods of _oracle performance optimization in methodology

2014-12-18 Baoxinjian

I. Summary

Overview of basic methods for Oracle performance optimization

• 1) Set up a reasonable performance optimization target.
• 2) measure and record current performance.
• 3) Determine the current Oracle performance bottleneck (what Oracle waits for and what SQL statements are the components of the wait event).
• 4) record the wait event in the trace file.
• 5) Determine the current OS bottleneck.
• 6) Optimize the required ingredients (application, database, I/O, contention, OS, etc.).
• 7) Track and implement the change control process.
• 8) measure and record current performance
• 9) Repeat steps 3 through 7 until the optimization target is met

Second, analysis

Step1. Set up a reasonable performance optimization target

Focus: The most important point about setting goals is that they must be quantifiable and achievable.

Method: The target must be a declarative statement of the current performance and required performance. Just fill in the blanks in the following statement.

Cost (Hours/minute/sec), but requires it to execute within (hours/minute/second).

Used (the amount of resources), but it cannot be used over.

Step2. Measure and record current performance

Focus:

• (1). Need to get the current system performance snapshot at peak activity time
• (2). The key is to collect information during the time period when the performance problem occurs
• (3). Must be collected in a reasonable period of time, generally during the peak period of several 15-minute snapshots

Method: Execute Statspack

(1). Create a performance snapshot table space

 as SYSDBA Create ' /u02/oradata/dbnms/perfstat.dbf ' size 500M extent management local;

(2). Install Statspack

@ $ORACLE _home/RDBMS/admin/spcreate.sql;

(3). Get performance data that can generate multiple snapshots

Execute statspack.snap;

(4). Generate reports for performance snapshots

Select min Max  from stats$snapshot, @ $ORACLE _home /RDBMS/admin/spreport;

(5). This report has important information about performance, such as the first 5-bit wait events, cache size, hit rate of various memory structures, per-second and per-transaction logic, number of physical read and write data blocks, worst-performing SQL statements, etc.

Step3. Identify current Oracle Performance bottlenecks

Focus: Get wait events from Oracle wait Interfaces V$system_event, V$session_event, and v$session_wait to identify objects and SQL statements that affect performance

Method:

--First, use the V$system_event view to perform the following query to view some common wait events in the database:

SELECT * FROM V$system_event

where event in (' Buffer busy Waits ',

' DB file sequential read ',

' db file scattered read ',

' Enqueue ',

' Free buffer waits ',

' Latch free ',

' Log file parallel write ',

' Log file Sync ');

--Then, using the queries under the V$session_event and v$session views, we study a session with a wait event that has contributed to the content shown above:

Select Se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait

From V$session s,v$session_event SE

where S.sid = Se.sid

and se.event not like ' sql*net% '

and s.status = ' ACTIVE '

And s.username is not null;

--Use the following query to find the current wait events related to the connected session. This information is dynamic and needs to be executed multiple times in order to see what the most awaited event for a session is.

Select Sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait sec_in_wait

From V$session s,v$session_wait SW

where S.sid = Sw.sid

and sw.event not like ' sql*net% '

And s.username is not NULL

ORDER BY sw.wait_time Desc;

--Query the details of the session wait event

Select Sid,event,p1text,p1,p2text,p2,p3text,p3

From v$session_wait

Where Sid between &1 and &2

and event not like '%sql% '

and event not like '%rdbms% ';

--Use P1, P2 information to find the relevant segments of the waiting event

Select Owner,segment_name,segment_type,tablespace_name

From Dba_extents

where file_id = &fileid_in

and &blockid_in between block_id and block_id + blocks-1;

--Get the SQL statements that manipulate the segment:

Select Sid, Getsqltxt (sql_hash_value,sql_address)

From V$session

where sid = &sid_in;

--Getsqltxt function

Create or replace

function Getsqltxt (hashaddr_in in V$sqltext.hash_value%type,

Addr_in in V$sqltext.address%type)

return VARCHAR2

Is

Temp_sqltxt varchar2 (32767);

Cursor Sqlpiece_cur

Is

Select Piece,sql_text

From V$sqltext

where Hash_value = hashaddr_in

and address = addr_in

Order by piece;

Begin

For Sqlpiece_rec in Sqlpiece_cur

Loop

Temp_sqltxt: = Temp_sqltxt | | Sqlpiece_rec.sql_text;

End Loop;

return temp_sqltxt;

End Getsqltxt;

--Now you have found objects and SQL statements that affect performance and can be targeted to optimize

Step4. To write a wait event into a trace file

Important: If you are having trouble tracking a wait event on your system for some reason, you can write these wait events into a trace file.

Method:

--for the current session:

Alter session set Timed_statistics=true;

Alter session set max_dump_file_size=unlimited;

Alter session SET Events ' 10046 Trace name Context forever, Level 12 ';

--Execute the application and locate the trace file in the directory indicated by User_dump_dest.

--View all lines in the file that begin with the word wait.

--For other sessions

--Determines the process ID (SPID) of the session. The following query identifies the session process ID of all users with the name starting with a:

Select S.username, p.spid from V$session S, v$process P

where s.paddr = p.addr and s.username like ' A% ';

--take sysdba into Sqlplus execution

Alter session set Timed_statistics=true;

Alter session set max_dump_file_size=unlimited;

Oradebug Setospid <SPID>

Oradebug Unlimit

Oradebug Event 10046 Trace name Context forever, Level X/* Where x = (1,4,8,12) */

--Track the session application at a time interval.

--Use the SPID to view trace files in the directory indicated by the User_dump_dest value

--View all lines in the file that begin with the word wait.

Step5. Identify current OS bottlenecks

(1) Monitoring on Windows NT

Using the Control Panel-〉 management tools-〉 Performance

(2) Monitoring on Unix

Use universal tools, including SAR, Iostat, Cpustat, Mpstat, netstat, top, osview, and more.

1) CPU Usage

Sar-u 5 1000

The values for%sys and%wio should be less than 百分之10到15

2) Use of equipment

Sar-d 5 1000

When%busy exceeds 60%, optimal device utilization starts to degrade, and on systems with sufficient disk caches, it is considered that the value of Avserv is 100 milliseconds high.

3) Virtual Memory usage

Vmstat-s 5 1000

The execution queue (R) should have a clear average of less than (2*CPU number)

Step6. Optimize the required ingredients (application, database, I/O, contention, OS, etc.)

Step7. Track and implement change control procedures

Step8. Measure and record current performance

Step9. Repeat steps 3 through 7 until the optimization target is met

Reprint a River-http://www.cnblogs.com/rootq/archive/2008/09/03/1282690.html

An overview of the basic methods of _oracle performance optimization in methodology