Basic methods for Oracle performance optimization

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

1. Set reasonable performance optimization Objectives : 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.

2. Measurement and recording of current performance Focus: (1) The need to obtain a snapshot of the current system capability at peak activity Time (2) The key is to collect information in the time period during which the performance problem occurs (3) must be collected at a reasonable time period, typically taking a few 15-minute snapshots during the peak period Method: Perform Statspack -build performance snapshot tablespace sqlplus sys as SYSDBA create tablespace perfstat datafile '/u02/oradata/dbnms/pe  RFSTAT.DBF ' size 500M extent management local; --Install statspack @ $ORACLE _home/rdbms/admin/spcreate.sql; --Get performance data that can generate multiple snapshots sqlplus perfstat execute statspack.snap;  --Generate a performance snapshot of the report sqlplus perfstat Select min (snap_id) snapid_min, Max (snap_id) Snapid_max from Stats$snapshot; @ $ORACLE _home/rdbms/admin/spreport; --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.

3. Identify current Oracle performance bottlenecksFocus: Get wait events from Oracle wait Interfaces V$system_event, V$session_event, and v$session_wait to identify the objects and SQL statement methods that affect performance:-First, using v$system_ The event view executes 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 Scatt Ered 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 are 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 as ' sql*net% ' and s.username is not null order by Sw.wait_time des C --Query the details of the session wait eventSelect Sid,event,p1text,p1,p2text,p2,p3text,p3 from v$session_wait where Sid between &1 and &2 and event not L Ike '%sql% ' and event not like '%rdbms% '; --Use P1, P2 information to find the relevant segments of the waiting eventSelect 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 functionCreate or Replace function Getsqltxt (hashaddr_in in V$sqltext.hash_value%type, addr_in in V$sqltext.address%ty  PE) 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

4. Note the wait events into the trace file Focus: 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--determine 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.usern Ame like ' A% '; --Take sysdba into Sqlplus to execute 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) * /-tracks the session application at a certain time interval. --Use the SPID to view the trace file in the directory indicated by the value of user_dump_dest--view all rows in the file that begin with the word wait.

5. Determine current OS bottleneck (1) Monitoring on Windows NT using Control Panel-〉 management tools-〉 Performance

(2) UNIX monitoring using universal tools, including SAR, Iostat, Cpustat, Mpstat, netstat, top, osview and so on. 1) CPU Usage sar-u 5%sys and%wio should be less than 百分之10到15 2) device usage sar-d 5 1000 when%busy exceeds 60%, optimal device utilization starts to decrease; On a system with sufficient disk cache, it is considered that the Avser The value of V for 100 milliseconds is very high. 3) Virtual Memory usage vmstat-s 5 1000 execution Queue (R) should be clearly defined on average less than (2*CPU number)

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

7. Track and implement change control procedures.

8. Measure and record current performance

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