Users View (Oracle Performance Analyst)

From DBArtisan
Jump to: navigation, search

Go Up to Oracle Performance Analyst Statistics

In addition to a Home page, the Users category of Oracle Performance Analyst includes the following tabbed pages:

Home

The Users home page includes the following sections:

Key User Analysis Pane

User database activity can be examined using both ratio-based and wait/bottleneck-based analysis. Ratio-based analysis involves examining a number of key database ratios and statistical readings that can be used to indicate how efficiently users are accessing the database. Performance ratios serve as roll-up mechanisms for busy DBAs to use for at-a-glance performance analysis.

When using ratio-based analysis, there are some standards to adhere to. To start with, many of the formulas that make up ratio-based analysis must be derived from delta measurements instead of cumulative statistics. Many of the global ratios that you examine come from the v$sysstat performance view. The performance view maintains a count of all the occurrences (in the VALUE column) of a particular database incident (in the NAME column) since the database was brought up. For databases that are kept up for long periods of time, these values can grow quite large and impacts how a particular ratio is interpreted. However, if delta statistics are used (taking, for a specified sampling period, the before and after counts of each statistic that make up a ratio), then an accurate and current portrayal of the various ratios can be had.

A final thing to remember about using ratio-based analysis is that, while there are a number of rules of thumb that can be used as starting points to begin the evaluation of database performance, DBAs must determine each database’s individual ‘personality’ with respect to the key performance ratios. Some hard and fast rules simply do not apply to every database. The danger in using blanket ratio standards is that they can lead you to haphazardly take action, which can at times contribute nothing to the situation, and sometimes even degrade performance.

The following ratios are used on the Performance Analyst Users home page to succinctly communicate the general session-based performance levels of the monitored database:

The following statistics, available on this pane are duplicates of statistics available on the Key Ratio Analysis Pane of the Home View (Oracle Performance Analyst) page:

Active Sessions

The Active Sessions statistic is the total number of active and open threads currently reported in the database as well as the number of processes actively performing work.

Note: For related information, see Active User Processes.

For more information, see Inactive Sessions.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Key User Analysis Pane

CPU/Parse Ratio

CPU resources are obviously required to accomplish parse activities. The CPU/Parse Ratio determines the percentage of CPU spent on user-initiated parse tasks.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Key User Analysis Pane

Metrics

Low numbers (0-5%) are desired with the CPU/parse ratio. Larger values can indicate a problem with excessive parse activity.

Correction

If you suspect an excessive parsing problem on your database, you can look into increasing the shared pool size (in hopes that SQL statements are not aged out and therefore reused) or investigate the usage of bind variable in SQL statements. If an undersized shared pool is suspected: Increase the size of the shared_pool_size parameter through use of the ALTER SYSTEM SET shared_pool_size command value (take caution to not over-allocate; ensure enough free memory exists on server before increasing value). Monitor the new value to see if performance improves. If using an SPFILE, save the new configuration values so Oracle reuses them each time the database is stopped and re-started. If you determine that SQL literals are causing SQL to not be reused, you can: Change the cursor_sharing parameter to FORCE by using the ALTER SYSTEM SET cursor_sharing=FORCE command. Monitor database to see if parse activity is reduced and library cache reloads shrink If using an SPFILE, save the new configuration values so Oracle reuses them each time the database is stopped and re-started. If using an Init.ora file, add the cursor_sharing=FORCE parameter to the file.

Inactive Sessions

The Inactive Sessions statistic is the total number of threads logged on to the database that are currently idle.

Note: For related information, see Inactive User Processes.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Key User Analysis Pane

Metrics

A large number of inactive users could indicate user sessions that have mistakenly been left logged on. Because each user thread consumes a portion of memory on the Oracle server, to reduce resource usage, you should sever any session that does not need a connection.

Correction

Double-click this statistic to open the Top Sessions Tab of the Users Detail page. On this tab you can check sessions that have many seconds idle and/or that have been logged on for very long periods of time as indicated by the logon time column. After verifying that the session is no longer necessary, you can KILL the session. For more information, see Active Sessions.

Open Cursors

Open Cursors is the total number of all SQL open cursors that exist on the system. In some cases, Oracle cached cursors that have been open by PL/SQL procedures can be kept open for certain lengths of time, even though the actual activity has ceased.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Key User Analysis Pane Oracle Performance Analyst Statistics > Session Details View (Oracle Performance Analyst) > Current Work Tab

Metrics

You should monitor sessions to make sure that they do not approach the Open Cursor limit (specified in the Init.ora file). The parameter, open_cursors, limits how many open cursors (context areas) a session can have open at one time.

Correction

If the total number of open cursors approaches the open_cursors limit then: Change the open_cursors parameter to FORCE by using the ALTER SYSTEM SET open_cursors=< new value > command.

Bottleneck Analysis Pane

User database activity can be examined using both ratio-based and wait/bottleneck-based analysis. When connected to an Oracle database, every process is either busy doing work or waiting to perform work. A process that is waiting may mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. You can use the Bottleneck Analysis section to determine if perceived bottlenecks in a database are contributing to a performance problem.

Bottleneck analysis is a valid method of measuring performance because it helps you track where a database has been spending its time. If user latch contention or heavy table scan activity has been dragging a database’s performance down, you can use bottleneck analysis to confirm the actual root cause. Once one or more wait events or other bottlenecks have been pinpointed as possible performance vampires, you can oftentimes discover a fair amount of detail about which sessions and objects are causing the problem.

For wait event analysis to be properly carried out, it is imperative that the timed_statistics initialization parameter be set to TRUE. By default this parameter is set to FALSE, which disallows the collection of wait times for each wait event defined to the Oracle engine. For one to really understand the impact of wait events on database performance, you need to not only discover what the database is or has been waiting on, but the durations of the waits. Having both allows a complete picture to be formed regarding the magnitude of wait-initiated performance degradations. Almost all Oracle experts now agree that allowing the collection of timing statistics adds little if anything to database overhead, so setting timed_statistics to TRUE should not be a worry. The Performance Analyst Users home page identifies the current top session waits as well as the top session-related bottlenecks that have seen the most waits on the system.

When using bottleneck analysis, you cannot rely only on the information contained in the wait event views that Oracle provides. Other user-related bottlenecks include current disk sort activity. Performance Analyst works to identify bottlenecks in your database that fall outside of pure wait events so you can get a total picture of all stoppages in your system.

The following bottleneck indicators are used on the Performance Analyst Users home page to succinctly communicate the general overall performance level of the monitored database:

Sessions Blocked

A single blocking user has the potential to stop work for nearly all other processes on a small system, and can cause major headaches even on large systems. Although Oracle supports unlimited row-level locking, blocking lock situations do crop up. User processes holding exclusive locks and not releasing them via a proper COMMIT generally cause most blocks.

Note: This statistic is also called Current Object Blocks on the Home View (Oracle Performance Analyst) page and Session Blocks on the Objects View (Oracle Performance Analyst) Bottleneck Analysis Pane.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

You should immediately investigate any indicator above zero for a blocking lock statistic before the situation has a chance to grow out of control.

Correction

Once discovered, a blocking lock situation can normally be quickly remedied. You can issue a KILL against the offending process, which eliminates the user's stranglehold on the objects they were accessing. Other user processes then nearly almost always complete in an instant. IDERA Performance Analyst makes it easier to discover the blocked lock situation, but the trick is to prevent the blocking lock situation in the first place. You can look at the Users View (Oracle Performance Analyst) detail and view all current blocking locks to see exactly which sessions are holding the currently restrictive locks. The culprit of blocking lock scenarios is usually the application design, or the SQL being used within the application itself. Properly coding an application to reference database objects in an efficient order, and then using the right SQL to get the job done, is an art. Most DBAs who have had to face Oracle Forms applications have suffered through the dreaded SELECT … FOR UPDATE statements that place unnecessary restrictive locks on nearly every read operation, and know all too well that good coding practice is important. The key to avoiding lock contention is to process user transactions in the quickest and most efficient manner possible - something not always easy to do.

Sessions in Disk Sorts

Oracle has the option of running automatic PGA memory management. Oracle has introduced a new Oracle parameter called pga_aggregate_target. When this parameter is set and you are using dedicated Oracle connections, Oracle ignores all of the PGA parameters in the Oracle file, including sort_area_size, hash_area_size and sort_area_retained_size. Oracle recommends that the value of pga_aggregate_target be set to the amount of remaining memory (less a 10% overhead for other server tasks) on a server after the instance has been started.

The sessions in disk sorts statistic displays a count of all sessions currently performing a disk sort.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Consistently seeing non-zero numbers for this statistic (as well as low values observed for the memory sort ratio) can indicate excessive disk sort activity. Investigate the use of pga_aggregate_target. Once the pga_aggregate_target has been set, Oracle automatically manages PGA memory allocation, based upon the individual needs of each Oracle connection. Oracle9i or later allows the pga_aggregate_target parameter to be modified at the instance level with the alter system command, thereby lets you dynamically adjust the total RAM region available to Oracle9i. Oracle also has a parameter called workarea_size_policy. When this parameter is set to automatic, all Oracle connections benefit from the shared PGA memory. When workarea_size_policy is set to manual, connections allocate memory according to the values for the sort_area_size parameter. Under the automatic mode, Oracle tries to maximize the number of work areas that are using optimal memory and uses one-pass memory for the others.

Correction

If you find a problem, do the following: Edit the Init.ora or SPFILE file for the database. Increase the amount of sort_area_size to a higher value (take care not to not over-allocate; ensure enough free memory exists on server before increasing value). Realize that EVERY user receives this amount for sorting). Cycle the Oracle server when possible to allow the new value to take effect. Monitor new value to see if performance improves. In addition to increasing the amount of memory devoted to sorting, you should also locate inefficient SQL that causes needless sorts. For example, in an SQL query (to eliminate duplicate rows) UNION ALL does not cause a sort whereas UNION does. People frequently code DISTINCT inappropriately (especially people transferring from Microsoft Access, which uses DISTINCT for most SELECT queries). There are times you simply cannot stop sort activity. When this happens, you should try to keep it in memory whenever possible. However, large data warehousing systems frequently exhaust RAM sort allotments, so if disk sorts must occur, ensure three things: Your user's TEMPORARY TABLESPACE assignment is not the SYSTEM tablespace, which is the default assignment. In Oracle9i or later, you can specify a default tablespace other than SYSTEM for every user account that is created. The TEMPORARY TABLESPACE assigned to your users is placed on a fast disk. The TEMPORARY TABLESPACE has the tablespace parameter TEMPORARY assigned to it, which allows sort activity to be performed in a more efficient manner.

Sessions with Enqueue Waits

An enqueue is an advanced locking device that allows multiple database processes to share certain resources. Enqueue waits typically occur when sessions wait to be granted a requested lock. Sometimes these locks are internal Oracle locks while other times they could be locks for rows of data. Note that enqueues are issued implicitly by Oracle.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Any enqueue waits that read consistently above 1 or more (delta statistics) should be investigated.

Correction

Removing contention for enqueues is almost always an application design issue. If many enqueue waits are seen, this normally indicates either contention for certain rows in the database, or the result of database-initiated lock escalation. You should examine the use of indexes to make sure all referencing foreign keys are indexes and that SQL is tuned to not tarry over rows in the database during modification operations. Enqueue waits can also be the result of space management tasks (such as objects extending) and disk sorts (mainly in tablespaces that do not make use of the TEMPORARY tablespace parameter).

Sessions Waiting

User connections that are waiting on a system generally occur for two reasons:

  1. A process waits because a requested resource is not available.
  2. A process waits for Oracle to perform a prerequisite task for its given operation.

Idle waits (processes waiting because they have no work) are not usually a concern. However the two wait causes mentioned above are the ones worth your time and investigation. The sessions waiting statistic is a count of all sessions that are currently waiting for one reason or another.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

To determine the actual wait causes currently experienced by user connections, you should drill down from the global count of users waiting, into the actual system and user wait details of a database.

Correction

If you find a problem, drill down into wait details to determine whether the waits are resource-related.

Top Bottleneck Events

When viewing wait statistics, there are many levels of detail. The first level is the system view, which provides a global, cumulative snapshot of all the waits that have occurred on a system. The second level is the session view, which shows details on which events connected sessions have experienced. Viewing these numbers can help you determine which session-related wait events have caused the most commotion in a database thus far. The Top Bottleneck Events section identifies the top waits that have occurred on the Oracle database based on the amount of time waited per event.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Correction

Appendix A in the Oracle Reference manual contains a listing and description of every current wait event defined in Oracle. DBAs unfamiliar with what each event represents should keep this listing close by as they examine wait-based event metrics. For example, a ‘db file scattered read’ event is typically indicative of table scan operations. If many of these events are observed, you should begin to see if large table scans are occurring in the database. Like the ‘db file scattered read’ event, each wait event has its own meaning and individual end-resolution diagSQL Analysis - Users

Much of a database's overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured database down in terms of end user response times. SQL statements that use much memory can also cause a problem in a database.

Before you can identify problem SQL in your database, you have to ask the question, “What is bad SQL?” What criteria do you use when you begin the hunt for problem SQL in your critical systems? Understand that even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every Oracle professional's satisfaction. The SQL Analysis for Users shows what SQL statements have consumed the largest percentages of executions, parse calls, CPU time, and elapsed time. The table below lists some general criteria you can use when evaluating the output from various database monitors or personal diagnostic scripts:

Criteria Description

Overall Response (Elapsed) Time

The amount of time the query took to parse, execute, and fetch the data needed to satisfy the query. It should not include the network time needed to make the round trip from the requesting client workstation to the database server. This statistic is available in Oracle9i or later.

CPU Time

The amount of CPU time the query took to parse, execute, and fetch the data needed to satisfy the query.

Physical I/O

This is often used as the major statistic in terms of identifying good vs. bad SQL, this is a measure of how many disk reads the query caused to satisfy the user's request. While you certainly want to control disk I/O where possible, it is important that you not focus solely on physical I/O as the single benchmark of inefficient SQL. Make no mistake, disk access is slower than memory access and also consumes processing time making the physical to logical transition, but you need to look at the entire I/O picture of a SQL statement, which includes looking at a statements' logical I/O as well.

Logical I/O

The number of memory reads the query took to satisfy the user's request. The goal of tuning I/O for a query should be to examine both logical and physical I/O, and use appropriate mechanisms to keep both to a minimum.

Repetition

The number of times the query has been executed. A problem in this area is not as easy to spot as the others unless you know your application well. A query that takes a fraction of a second to execute may still be causing a headache on your system if it has executed erroneously (for example, a query that executes in a runaway PL/SQL loop) over and over again.

There are other criteria that you can examine like sort activity or access plan statistics (that show items like Cartesian joins and the like), but more often than not, these measures are reflected in the criteria listed above.

Fortunately, Oracle records all the above measures (some only in 9i), which makes tracking the SQL that has been submitted against an Oracle database much easier.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

When you begin to look for inefficient SQL in a database, there are two primary questions you want answered: What HAS been the worst SQL that has historically run in my database? What IS the worst SQL that is running right now in my database?

Correction

When troubleshooting a slow system, you should be on the lookout for any query that shows an execution count that is significantly larger than any other query on the system. It may be that the query is in an inefficient PL/SQL loop, or other problematic programming construct. Only by bringing the query to the attention of the application developers will you know if the query is being mishandled from a programming standpoint. There is the possibility that the SQL statement just is not tuned well. To determine that, you can go further into Performance Analyst's Top SQL view and, if you have IDERA SQL Tuner installed, you can port the SQL over to SQL Tuner to better optimize the statement.

Transaction Analysis Pane

On a transaction-oriented system, user transactions perform COMMITs to make permanent any data additions, changes, or deletions to the database. Or they ROLLBACK any modifications to undo any work that has encountered an error or other transaction-halting situation. The transaction analysis breakdown shows current commits and rollbacks and the percentage that each activity accounts for on the database.

Metrics

Large volumes of rollbacks can indicate a transaction breakdown on the system, that may need investigation at the application level.

Workload Analysis Pane

When the database population as a whole experiences a system slowdown, it is not uncommon to find one or two users who are responsible for bringing the system to its knees. In the best of worlds, users have a pretty evenly divided amount of memory usage, disk I/O, CPU utilization, and parse activity. However, users submit large batch jobs during peak OLTP activity, or when sessions that are fire off untuned queries on a critical system.

If you are seeing a slowdown in your database, and cannot seem to find a root cause, examine the resource consumption of the leading sessions on a system. A handful of users can overwhelm the I/O capabilities of Oracle (through untuned queries or runaway batch jobs) or hammer the CPU or memory structures of the database and server.

Performance Analyst makes it easy to pinpoint the top sessions by showing the leading processes at the physical I/O, logical I/O, memory, disk sort, table scan, and CPU usage levels.

Metrics

If any one session uses more than 50% of a total resource (CPU, memory, etc.) go into the session to find out what they are currently executing.

Top Sessions Tab

The Top Sessions tab includes the following sections:

Top I/O Process

This section identifies the Oracle process that currently has caused the most I/O usage on the database.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Top Sessions Tab

Metrics

To obtain more details on the top I/O process, locate the SID in the Top Sessions grid and drill down to obtain more granular information.

Top Memory Process

The Top Memory Process section identifies the Oracle process that currently is using the highest percentage of memory in the database.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Top Sessions Tab

Metrics

To obtain more details on the top memory process, locate the SID in the Top Sessions grid and drill down to obtain more granular information.

Top CPU Process

The Top CPU Process section identifies the Oracle process that currently has the highest percentage of CPU usage in the database.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Top Sessions Tab

Metrics

To obtain more details on the top CPU process, locate the SID in the Top Sessions grid and drill down to obtain more granular information.

Top Sessions

When a system experiences heavy activity, sometimes you will find that all the user connections are contributing somewhat equally to the overall load. More often than not, however, one or two user connections are causing most of the activity. It may be that a large batch load or other typical process is running that is perfectly okay for your system. Or it may be a runaway process or other rogue connection that needs to be tracked down and possibly eliminated.

The Top Sessions section displays information regarding all key metrics for all current sessions on a database. The table below describes the information available in this section:

Column Description

SID

The system ID of the process.

User Name

The username of the process.

O/S ID

The operating system ID of the process.

Machine Name

The workstation where the process originated.

Logon Time

The date/time the process logged on to Oracle.

Tot Physical I/O

A summation of all physical reads and physical writes for the process (includes direct).

Tot Logical I/O

A summation of all logical I/O (buffer gets, consistent gets, etc.) for the process.

Hit Ratio

Shows how often object definitions are found in memory vs. having to read them in from disk.

Total Memory Usage

The total amount of memory used.

Parses

The total number of parses the process has caused.

Total CPU

The total CPU usage for the process.

Parse CPU

The amount of process CPU caused by parse activity.

Recursive CPU

The amount of process CPU caused by recursive calls.

Other CPU

Total CPU usage minus parse and recursive activity. This can be a negative number if the Oracle kernel is not properly reporting total CPU usage.

Disk Sorts

The total number of disk sorts caused by the process.

Memory Sorts

The total number of memory sorts caused by the process.

Rows Sorted

The total number of rows sorted by the process.

Commits

The total number of commits executed by the process.

Rollbacks

The total number of rollbacks executed by the process.

Executions

The total number of executions.

Physical Reads

The total number of physical reads produced by the process.

DB Block Gets

The total number of block gets produced by the process.

Consistent Gets

The total number of consistent gets produced by the process.

Consistent Changes

The total number of consistent changes produced by the process.

Last Wait

Event Defines the last wait event experienced by the session

Wait Class

The wait class of the last wait event

Wait time

The sessions last wait time

P1text

Text that describes the first wait parameter

P2text

Text that describes the second wait parameter

P2 r

The second wait parameter

P2Raw

The data for the second parameter

P3text

Text that describes the third wait parameter

P3

The third wait parameter

P3Raw

The data for the third parameter


Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Top Sessions Tab

Metrics

To obtain more details on any process, drill down to obtain more granular information.

System Waits Tab

The System Waits tab includes the following sections:

System Waits

Waits on a system generally occur for three reasons:

  1. A process waits because it has no work to do.
  2. A process waits because a requested resource is not available.
  3. A process waits for Oracle to perform a prerequisite task for its given operation.

Idle waits (processes waiting because they have no work) are not normally a problem, however the other two wait causes are the ones worth your time and investigation. From a global database level, there are many different types of waits and sources of contention. The system Waits section presents all the various system waits that have occurred on the system since startup. The table below lists the information available in this section:

Column Description

Event

The name of the wait event.

Class

Wait classes contain a collection of related wait events (like User I/O, System I/O, etc.)

Total Waits

The total number of waits for the event.

Percent of Total

The percentage of all waits that this event makes up.

Time Waited (Secs)

The total amount of time waited for the event, in seconds.

Percent Time Waited

The percentage of time that this event makes up.

Total Timeouts

The total number of timeouts for the event.

Percent Total Timeouts

The percentage of timeouts that this event makes up.

Average Waits (Secs)

The average amount of time waited for the event, in seconds.


Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > System Waits Tab

Metrics

Which waits should be a cause for concern and which waits can be ignored (for the most part)? Common wait events that can generally be ignored include SQL*Net more data from client, SQL*Net message from client, client message, rdbms ipc message, pipe get, pmon timer, smon timer, and Null event.

Correction

The table below describes wait events that deserve attention:

Wait Event Description

Enqueue waits

If many enqueue waits are seen, this normally indicates either contention for certain rows in the database, or the result of database-initiated lock escalation. You should examine the use of indexes to make sure all referencing foreign keys are indexes and that SQL is tuned to not tarry over rows in the database during modification operations. Enqueue waits can also be the result of space management tasks (such as objects extending) and disk sorts (mainly in tablespaces that do not make use of the TEMPORARY tablespace parameter).

Buffer busy waits

Buffer busy waits normally center around contention for rollback segments, too small an INITRANS setting for tables, or insufficient free lists for tables. The remedy for each situation would be increasing the number of rollback segments, or altering tables to have larger settings for INITRANS to allow for more transactions per data block, and more free lists.

db file scattered read waits

An interesting wait event is the db file scattered read event. This event is indicative of table scans occurring on the system. Large numbers of them may indicate heavy scan activity and the need to revisit your indexing/physical design.

Latch free waits

Latch free waits indicate contention for latches.

The SQL*Net message to client can help a DBA diagnose wait activity outside the database.

The key statistics for waits is not the number of waits for each event, but rather the wait time and average wait time. These indicate if the event is truly causing a significant bottleneck in the database. To view the wait times for sessions and overall system wait events, you must set the timed_statistics parameter to TRUE for your Oracle databases. You can either set this parameter in your Init.ora file or alter the system dynamically with the ALTER SYSTEM SET TIMED_STATISTICS=TRUE command.

Wait Percent by Total Waits

Waits on a system generally occur for three reasons:

  • A process waits because it has no work to do.
  • A process waits because a requested resource is not available.
  • A process waits for Oracle to perform a prerequisite task for its given operation.

Idle waits (processes waiting because they have no work) are not normally a problem, however the other two wait causes are the ones worth your time and investigation. From a global database level, there are many different types of waits and sources of contention. The Wait Percent by Total Waits section displays the percentage of waits for all top non-idle wait events.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > System Waits Tab

Metrics

Which waits should be a cause for concern and which waits can be ignored (for the most part)? Common wait events that can generally be ignored include SQL*Net more data from client, SQL*Net message from client, client message, rdbms ipc message, pipe get, pmon timer, smon timer, and Null event.

Correction

The table below describes wait events that deserve attention:

Wait Event Description

Enqueue waits

If many enqueue waits are seen, this normally indicates either contention for certain rows in the database, or the result of database-initiated lock escalation. You should examine the use of indexes to make sure all referencing foreign keys are indexes and that SQL is tuned to not tarry over rows in the database during modification operations. Enqueue waits can also be the result of space management tasks (such as objects extending) and disk sorts (mainly in tablespaces that do not make use of the TEMPORARY tablespace parameter).

Buffer busy waits

Buffer busy waits normally center around contention for rollback segments, too small an INITRANS setting for tables, or insufficient free lists for tables. The remedy for each situation would be increasing the number of rollback segments, or altering tables to have larger settings for INITRANS to allow for more transactions per data block, and more free lists.

db file scattered read waits

An interesting wait event is the db file scattered read event. This event is indicative of table scans occurring on the system. Large numbers of them may indicate heavy scan activity and the need to revisit your indexing/physical design.

Latch free waits

Latch free waits indicate contention for latches.

The SQL*Net message to client can help a DBA diagnose wait activity outside the database.

Wait Percent by Time Waited

Waits on a system generally occur for three reasons:

  1. A process waits because it has no work to do.
  2. A process waits because a requested resource is not available.
  3. A process waits for Oracle to perform a prerequisite task for its given operation.

Idle waits (processes waiting because they have no work) are not normally a problem, however the other two wait causes are the ones worth your time and investigation. From a global database level, there are many different types of waits and sources of contention. The Wait Percent by Time Waited section displays the percentage of waits for all top non-idle wait events.

Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > System Waits Tab

Metrics

The key statistics for waits is not the number of waits for each event, but rather the wait time and average wait time. These indicate if the event is truly causing a significant bottleneck in the database. Which waits should be a cause for concern and which waits can be ignored (for the most part)? Common wait events that can generally be ignored include SQL*Net more data from client, SQL*Net message from client, client message, rdbms ipc message, pipe get, pmon timer, smon timer, and Null event.

Correction

The table below describes wait events that deserve attention:

Wait Event Description

Enqueue waits

If many enqueue waits are seen, this normally indicates either contention for certain rows in the database, or the result of database-initiated lock escalation. You should examine the use of indexes to make sure all referencing foreign keys are indexes and that SQL is tuned to not tarry over rows in the database during modification operations. Enqueue waits can also be the result of space management tasks (such as objects extending) and disk sorts (mainly in tablespaces that do not make use of the TEMPORARY tablespace parameter).

Buffer busy waits

Buffer busy waits normally center around contention for rollback segments, too small an INITRANS setting for tables, or insufficient free lists for tables. The remedy for each situation would be increasing the number of rollback segments, or altering tables to have larger settings for INITRANS to allow for more transactions per data block, and more free lists.

db file scattered read waits

An interesting wait event is the db file scattered read event. This event is indicative of table scans occurring on the system. Large numbers of them may indicate heavy scan activity and the need to revisit your indexing/physical design.

Latch free waits

Latch free waits indicate contention for latches.

The SQL*Net message to client can help a DBA diagnose wait activity outside the database.

To view the wait times for sessions and overall system wait events, you must set the timed_statistics parameter to TRUE for your Oracle databases. You can either set this parameter in your Init.ora file or alter the system dynamically with the ALTER SYSTEM SET TIMED_STATISTICS=TRUE command.

Session Waits Tab

The Session Waits tab includes the following sections:

Historical Waits

Waits on a system generally occur for three reasons:

  1. A process waits because it has no work to do.
  2. A process waits because a requested resource is not available.
  3. A process waits for Oracle to perform a prerequisite task for its given operation.

Idle waits (processes waiting because they have no work) are not normally a problem, however the other two wait causes are the ones worth your time and investigation. From a global database level, there are many different types of waits and sources of contention. The Historical Waits section presents all the various waits that have occurred for all currently connected oracle sessions. The table below lists the information available in this section:

Column Description

Event

The name of the wait event.

Wait Class

Wait classes contain a collection of related wait events (like User I/O, System I/O, etc.)

Total Waits

The number of waits for the event.

Percent of Total Waits

The percentage of all waits that this event makes up.

Time Waited (Secs)

The total amount of time waited for the event, in seconds.

Average Wait (Secs)

The average amount of time waited for the event, in seconds.

Max Wait (Secs)

The maximum amount of time waited for the event, in seconds.

Total Timeouts

The number of timeouts for the event.


Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Session Waits Tab Oracle Performance Analyst Statistics > Session Details View (Oracle Performance Analyst) > Waits Tab

Metrics

Which waits should be a cause for concern and which waits can be ignored (for the most part)? Common wait events that can generally be ignored include SQL*Net more data from client, SQL*Net message from client, client message, rdbms ipc message, pipe get, pmon timer, smon timer, and Null event.

Correction

The table below describes wait events that deserve attention:

Wait Event Description

Enqueue waits

If many enqueue waits are seen, this normally indicates either contention for certain rows in the database, or the result of database-initiated lock escalation. You should examine the use of indexes to make sure all referencing foreign keys are indexes and that SQL is tuned to not tarry over rows in the database during modification operations. Enqueue waits can also be the result of space management tasks (such as objects extending) and disk sorts (mainly in tablespaces that do not make use of the TEMPORARY tablespace parameter).

Buffer busy waits

Buffer busy waits normally center around contention for rollback segments, too small an INITRANS setting for tables, or insufficient free lists for tables. The remedy for each situation would be increasing the number of rollback segments, or altering tables to have larger settings for INITRANS to allow for more transactions per data block, and more free lists.

db file scattered read waits

An interesting wait event is the db file scattered read event. This event is indicative of table scans occurring on the system. Large numbers of them may indicate heavy scan activity and the need to revisit your indexing/physical design.

Latch free waits

Latch free waits indicate contention for latches.

The SQL*Net message to client can help a DBA diagnose wait activity outside the database.

The key statistics for waits is not the number of waits for each event, but rather the wait time and average wait time. These indicate if the event is truly causing a significant bottleneck in the database. To view the wait times for sessions and overall system wait events, you must set the timed_statistics parameter to TRUE for your Oracle databases. You can either set this parameter in your Init.ora file or alter the system dynamically with the ALTER SYSTEM SET TIMED_STATISTICS=TRUE command.

Current Waits

Waits on a system generally occur for three reasons:

  • A process waits because it has no work to do.
  • A process waits because a requested resource is not available.
  • A process waits for Oracle to perform a prerequisite task for its given operation.

Session contention is merely a subset of contention that is viewable at the global database level. Oftentimes, it takes analysis at the session level to pinpoint the exact source of contention that is occurring globally. So you need to become accustomed to viewing contention statistics and waits at the user process level.

When monitoring waits with respect to user sessions, there are two areas of interest:

  1. What HAS the user session been waiting on?
  2. What IS the user session waiting on?

Oracle records both sets of wait statistics for you. In reviewing previous waits for a session, you can see which types of things have caused the session to be bottlenecked. The Current Waits section displays information to help you troubleshoot session contention. The table below lists the information available in this section:

Column Description

Event

The name of the wait event.

Wait (Secs)

The number of seconds in the current wait.

Last Wait Time

A non-zero value is the session's last wait time. A zero value means the session is currently waiting. A -2 value indicates the platform cannot support a fast timing mechanism or TIMED_STATISTICS is not set for the system.

State

Indicates the possible values. They includes the following: WAITING - the session is waiting. WAITED UNKNOWN TIME - duration of last wait is not known. WAITED SHORT TIME - last wait was less than 1/100th of a second. WAITED KNOWN TIME - the wait is equal to the time of the last wait.

P1 - P3

Any additional wait parameters.

P1TEXT - P3TEXT

Any descriptions of additional parameter.

P1RAW - P3RAW

Any additional wait parameters.


Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Session Waits Tab Oracle Performance Analyst Statistics > Session Details View (Oracle Performance Analyst) > Waits Tab

Metrics

Which waits should be a cause for concern and which waits can be ignored (for the most part)? Common wait events that can generally be ignored include SQL*Net more data from client, SQL*Net message from client, client message, rdbms ipc message, pipe get, pmon timer, smon timer, and Null event.

Correction

The table below describes wait events that deserve attention:

Wait Event Description

Enqueue waits

If many enqueue waits are seen, this normally indicates either contention for certain rows in the database, or the result of database-initiated lock escalation. You should examine the use of indexes to make sure all referencing foreign keys are indexes and that SQL is tuned to not tarry over rows in the database during modification operations. Enqueue waits can also be the result of space management tasks (such as objects extending) and disk sorts (mainly in tablespaces that do not make use of the TEMPORARY tablespace parameter).

Buffer busy waits

Buffer busy waits normally center around contention for rollback segments, too small an INITRANS setting for tables, or insufficient free lists for tables. The remedy for each situation would be increasing the number of rollback segments, or altering tables to have larger settings for INITRANS to allow for more transactions per data block, and more free lists.

db file scattered read waits

An interesting wait event is the db file scattered read event. This event is indicative of table scans occurring on the system. Large numbers of them may indicate heavy scan activity and the need to revisit your indexing/physical design.

Latch free waits

Latch free waits indicate contention for latches.

The SQL*Net message to client can help a DBA diagnose wait activity outside the database.

The key statistics for waits is not the number of waits for each event, but rather the wait time and average wait time. These indicate if the event is truly causing a significant bottleneck in the database. To view the wait times for sessions and overall system wait events, you must set the timed_statistics parameter to TRUE for your Oracle databases. You can either set this parameter in your Init.ora file or alter the system dynamically with the ALTER SYSTEM SET TIMED_STATISTICS=TRUE command.

Locks/Blocking Locks Tab

The Locks/Blocking Locks tab includes the following sections:

All Locks

To modify database information or structures, a user session must obtain a lock on the object to perform its task. Ibesides user locks, Oracle itself will issue lock requests to carry out its internal duties. The All Locks section displays information about all locks currently on a system. The table below lists the information available in this section:

Column Description

Machine

The workstation of the Oracle process.

Serial #

The serial number of the session holding the lock.

Request

The lock requests that Oracle issues to carry out its internal duties.

Table

The table name of the locked table.

Mode

The lock mode (EXCLUSIVE, SHARE, etc.).

Blocking?

Indicates if the lock is a blocking lock.


Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Locks/Blocking Locks Tab Oracle Performance Analyst Statistics > Session Details View (Oracle Performance Analyst) > Locks Tab

Metrics

Locks that are held for unusually long periods of time may be candidates for further investigation. The application logic may be inefficient or perhaps the program is not issuing frequent enough COMMITs.

Blocking Locks

Without a doubt, blocking lock situations can give the appearance of a frozen database almost more than anything else (except, perhaps, for a stuck archiver process). A single blocking user has the potential to stop work for nearly all other processes on a small system, and can cause major headaches even on large systems. Although Oracle supports unlimited row-level locking, blocking lock situations do crop up - sometimes frequently.

The Blocking Locks section contains information relating to user accounts that are currently blocked and the sessions that are blocking them. The table below describes the information available in this section:

Column Description

Blocked User

The user account of the session waiting for the lock.

Blocking User

The user account of the session holding the offending lock.

Waiting SID

The session identifier of the session waiting for the lock.

Holding SID

The session identifier of the session holding the offending lock.

Type

The type of lock (TRANSACTION, DML, etc.).

Mode

The lock mode (EXCLUSIVE, SHARE, etc.).

Request

The type of lock being requested by the session.

Row

The rowid of the row being held.

ID 1

Lock identifier #1 (depends on type).

ID 2

Lock identifier #2 (depends on type).

Time Waited (Secs)

The amount of time, in seconds, the blocked user has been waiting.

Locked Object

The name of the object being locked.


Location

Oracle Performance Analyst Statistics > Users View (Oracle Performance Analyst) > Locks/Blocking Locks Tab Oracle Performance Analyst Statistics > Session Details View (Oracle Performance Analyst) > Locks Tab

Metrics

Once discovered, a blocking lock situation can normally be quickly remedied - the DBA issues a KILL against the offending process, which eliminates the user's stranglehold on the objects they were accessing. Other user processes then nearly almost always complete in an instant. Discovering the blocked lock situation is made easier by using tools like Performance Analyst, but preventing the blocking lock situation in the first place is where it gets tricky. The culprit of blocking lock scenarios is usually the application design, or the SQL being used within the application itself. Properly coding an application to reference database objects in an efficient order, and then using the right SQL to get the job done, is an art. Most DBAs who have had to face Oracle Forms applications have suffered through the dreaded SELECT … FOR UPDATE statements that place unnecessary restrictive locks on nearly every read operation, and know all too well that good coding practice is important. The key to avoiding lock contention is to process user transactions in the quickest and most efficient manner possible - something not always easy to do. Data warehouses (whose data is mostly read) can benefit from tablespaces set in READ ONLY mode. READ ONLY signals to the other database that exclusive locks need not be used for the data contained within the tablespace. This is especially helpful in Oracle Parallel Server environments and drastically reduces ping activity.

Retrieved from "https://docwiki.embarcadero.com/DBArtisan/171/e/index.php?title=Users_View_(Oracle_Performance_Analyst)&oldid=6041"