Oracle 10g wait interface Diagnosis

Use the wait interface in Oracle 10 Gb to diagnose performance problems
John is the database administrator of ACME bank, and the other end of the phone is the angry user bill. He complained that his database session was hung up. Most database administrators are not familiar with such complaints. How can John eliminate Bill's complaint?
　　
The database of ACME bank is Oracle Database 10 Gb, so John has many options. The automatic database diagnosis Manager (ADDM) is a new feature of Oracle Database 10 Gb. It can tell John about the overall status and performance of the database, therefore, John used ADDM to determine whether the bill session was going through a database-wide problem from the very beginning. The ADDM report confirms that there is no database-wide problem that could have this impact on Bill's sessions, so John continues to consider the next option.
　　
One way to diagnose session-level events (such as Bill problems) is to determine whether the session is waiting for an event, such as a read operation on a file block or a lock or plug-in on a table row. Since oracle7, Oracle has provided a variety of mechanisms to display the wait in the database. In recent years, this product has been continuously improved and more diagnostic information has been added. In the Oracle Database 10 Gb, it provides greatly improved wait event information, making it easier to diagnose the slow speed of a session. This article shows you how to use the wait event in the Oracle database for 10 Gb to determine the bottleneck.
　　
　　 Session waiting
How does database administrator John determine what caused Bill's session suspension? In fact, the session is not suspended; it is waiting for an event, and this is exactly what John wants to check.
　　
John can use Oracle Enterprise Manager or directly access the V $ view through command lines to continue his investigation. John has a set of scripts to diagnose these issues, so he uses the command line.
　　
John queries the V $ session view to see what Bill's session is waiting. (Note that John has filtered out all idle events .)
　　
Select Sid, username, event, blocking_session,
Seconds_in_wait, wait_time
From v $ session where state in ('waiting ')
And wait_class! = 'Idle ';
　　
　　 The output result is shown in vertical format below.
Sid: 270
Username: Bill
Event: Enq: TX-row lock
Contention
Blocking_session: 254
Seconds_in_wait: 83
Wait_time: 0
　　
John immediately concluded that Bill's Sid 270 session is waiting for a lock on a table, which is occupied by 254 SESSION (blocking_session.
　　
But John wants to know which SQL statement causes the lock to be occupied. He can easily find the answer by connecting the V $ session and V $ SQL view to execute the following query:
　　
Select Sid, SQL _text
From v $ session S, V $ SQL Q
Where sid in (254,270)
And (
Q. SQL _id = S. SQL _id or
Q. SQL _id = S. prev_ SQL _id );
　　
Code List 1 shows the query results. John sees (in Listing 1) Both sessions attempt to update the same row. Session 254 will remain waiting for this lock unless session 270 is committed or rolled back. He explained everything to Bill, and Bill is not so angry now. he thinks something in the application is wrong, so John is asked to end session 254 to release the lock.
　　
　　 Waiting class
After John finishes the session that blocks bill, the session can continue but is slow. John decided to check for other problems in the session. He checked again to see if there were any other waiting events, but this time he checked Bill's session.
　　
In Oracle Database 10 Gb, wait events are divided into different wait categories based on the event type. Grouping events enables you to focus on specific classes and eliminate unimportant events, such as idle events. John performs the following query on the V $ session_wait_class View:
　　
Select wait_class_id, wait_class,
Total_waits, time_waited
From v $ session_wait_class
Where sid = 270;
　　
Code List 2 shows the output result, which shows the waiting class and the number of sessions waiting for events in each class. It tells John about the waiting related to the application after the instance is started. For example, the waiting caused by row-level locks occurs for 17760 times, the total time spent is 281654 seconds (1% seconds, CS ). John thinks that the value of time_waited is too high for this session. He decided to find the cause of these waits in the application wait class. You can obtain the number of occurrences of each wait in the V $ system_event view. Run the following query to determine each type of wait in the application wait class (class ID 4217450380:
　　
Select event, total_waits, time_waited
From v $ system_event E, V $ event_name n
Where n. event_id = E. event_id
And wait_class_id = 4217450380;
　　
Code listing 3 lists the output results of the query. The results show that the lock contention (identified by event Enq: TX-row lock contention) accounts for most of the waiting time of the Application wait class. This is related to John. Is it possible that poorly written applications directly enter the product database, which leads to contention for these locks?
　　
However, as an experienced dBA, John did not come to this conclusion immediately. The data in code list 3 only indicates that the user has gone through 2275 lock contention-related waits, which takes 280856 seconds in total. It is possible that most of the waits are only 1 to 2 S, and all waits may be caused by a long wait, in which case the application is okay. A single long wait may be an abnormal phenomenon. It distorted data and does not represent the real workload of the system. How does John determine if a single Wait event distort the data?
　　
Oracle 10 Gb provides a new view, V $ event_histogram, which shows the waiting time period and the frequency of session waiting for a specific time period. Jone executes the following query statement for V $ event_histogram:
　　
Select wait_time_milli bucket, wait_count
From v $ event_histogram
Where event =
'Enq: TX-row lock contention ';
　　
The output result is as follows:
　　
Bucket wait_count
---------------------
1 252
2 0
4 0
8 0
16 1
32 0
64 4
128 52
256 706
512 392
1024 18
2048 7
4096 843
　　
The V $ event_histogram view displays the waiting period and the number of sessions waiting for a specific event during this period-in this example, row-Level Lock contention. For example, a session waits for 252 events in less than 1 Millisecond (MS), one event in less than 1 millisecond, and so on. The value of the wait_count column is 2275, which is the same as the value shown in the event Enq: TX-row lock contention listed in listing 3. The V $ event_histogram view shows that most events wait for 256 milliseconds, 512 milliseconds, and 4096 milliseconds, which fully proves that the application is experiencing lock contention issues, the contention of this lock is the cause of the slow session speed of bill. If the view shows that the wait takes place within 1 millisecond, John cannot think so, because such a short wait seems normal.
　　
　　 Time Model
After explaining his initial findings to Bill, Lora came in with a similar complaint: Her Sid 355 session was very slow. John again looks for the session waiting event by executing the following query on the V $ session View:
　　
Select event, seconds_in_wait,
Wait_time
From v $ session
Where sid = 355;
　　
The output in Listing 4 shows that Lora sessions have various waiting events, including latch contention, which indicates that an application may be designed incorrectly. However, before providing Lora with a method to modify an application, John must use facts to support his theory, that is, poor design of the application causes Lora's session performance to be low. To test his theory, he decided to determine whether Lora sessions are extremely high in resource utilization and whether sessions other than this session are slow.
　　
On the 10 Gb time model interface of the Oracle database, John can easily view detailed information about the time used by sessions in various activities. He runs the following query statement on the V $ sess_time_model View:
　　
Select stat_name, Value
From v $ sess_time_model
Where sid = 355;
　　
The output in listing 5 shows the time (in microseconds) that the session takes in various aspects ). John learned from this result that it takes 878088366 microseconds to execute all SQL queries, of which 503996336 microseconds is used for parsing (the time spent in parsing), that is, 57% of the SQL Execution time, this indicates that the reason for the slow speed is that there are too many parsing operations. John told Lora that she had adopted the recommendations of the application design team.
　　
　　 OS statistics
John also needs to eliminate the possibility that the host system is a bottleneck when carefully checking the user's performance problems. Before using Oracle for 10 Gbit/s, he can use operating system (OS) tools such as SAR and vmstat and infer some metric indicators that determine contention issues. In Oracle 10 Gb, OS-level metrics are automatically collected in the database. To view potential host contention problems, John performs the following query on the V $ osstat View:
　　
Select * from V $ osstat;
　　
The output result in code listing 6 shows various metric elements at the OS level collected. All time elements are in the unit of per second. John learned from the results shown in code listing 6 that a CPU in the system is idle for 51025805 seconds (idle_ticks) and busy for 2389857 seconds (busy_ticks ), this indicates that the CPU is about 4% busy. He concluded that CPU is not a bottleneck in the host. Note that if the host system has more than one CPU, the header contains the AVG _ prefix column. For example, avg_idle_ticks displays the average value of these metrics for all CPUs.
　　
　　 Active session history
So far, whenever a problem occurs, the user asks John so that he can view the performance in real time. It wasn't long before Janice found John again and complained about recent performance problems. When John queries the V $ session view, the session is idle and there are no waiting events. How does John check what events are waiting for when a problem occurs in Janice sessions?
　　
Oracle 10 Gb collects information about an active session every second in the memory buffer. This buffer zone is called Active session history (Ash) and can be viewed in the dynamic performance view of V $ active_session_history, the data is retained for 30 minutes before being periodically overwritten by new data. John gets the SID and serial # Of the Janice session, and then