Oracle database performance tuning is an important part of facilitating and maintaining rapid data retrieval and application functionality. The query gives Date, Event_Name and Total_Waits. 2 mname varchar2 (200); 3 begin. In my database the only prominent issue I see is huge log-file-sync waits. Oracle Wait Events - db file sequential read - Toad The waits on the enq: TM - contention event for the sessions that are . It samples information from the [G]V$ views allowing you to see current and historical information about active sessions on the database. TO find out sql_id for the above sid: . Top 5 time-consuming SQL queries in Oracle - Stack Overflow Operating System tools show very high utilization or saturation of disks used for storage of database files. Not only can you see the waits inside the database, but you can examine the execution path too. Top 5 wait events from v$active_session_history Statistics Although wait events are great for helping with understanding the speed bumps the sessions experience, they do not show another important attribute of sessions: the use of resources such as CPU, I/O, and memory. (It is usually a good idea to also have quick look at high-load SQL). See the sample AWR report (fig-1) showing "cache buffer chain" wait event as top event. I have been looking in past Oracle-L posts, Google, Oracle FAQ, OTN, Metalink, Oracle manuals, etc. Wait for stopper event to be increased | Oracle Blog : Real Problems ... You can COUNT the occurrences on ON_CPU and WAITING (and group the WAITING by EVENT). Parallel query slaves also use direct I/O when scanning a table or index. It may happen that one database is consuming more server resources than others. Your disks may be too slow or you have an I/O bottleneck. The User process charges this wait time as a 'log file sync' event. An Oracle session logs the db file sequential read wait event when it has to wait for a single-block I/O read request to complete. The top wait events identify the next places to investigate. This DMV shows the names, counts, and wait times of wait events encountered by the SQL Server engine, or to be exact the SQLOS. . Drilling & Solution: The "cache buffer chain" latch wait is normal, but high values are associated with high simultaneous buffer access, similar to a free-list shortage on an index or table segment header. V$SYSTEM_EVENT displays the total number of times all the sessions have waited for the events in that view. This finds the top SQL statements that are currently stored in the SQL cache ordered by elapsed time. Oracle - A query to get the "Wait Events" part of the AWR report.