Page History
...
Table 4-3 Session Overview
| View area | Description |
|---|---|
...
| Session | Displays information for the following items: |
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
See “In Oracle” on page 36. |
| Connection |
...
Displays information for the following items: |
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
See “Executions vs. opens and fetches” on page 39. |
| In Oracle (Last Minute) |
...
Displays information for the following items: |
...
|
...
|
...
|
...
|
About the text of a current or active session
The Text view displays the SQL text for the statement that the session is currently executing, if any.
About associating a session with other entities
You can associate a Session, in the Main area of the Current tab, to different entities in the Association area. The information displayed is divided into different sets that show different aspects of the entity. Each set is accessed by a tab.
The following table describes the entities that are available in the Association area.
Table 4-4 Association 4 Association area entitiesEntity Description
Statements Displays
| Entity | Description |
|---|---|
| Statements | Displays a list of statements run by the session during the last minute. |
| Sessions Involved in |
...
| Lock | Displays a list of other sessions locking or locked by this session during the last minute. Three of the tabs displayed here are the same as those displayed for a regular session. In addition, the Lock tab displays locking information that is equivalent to the information displayed in Oracle's V$SESSION_WAIT table (For more information, see the Oracle documentation). |
| Parallel Query Server |
...
| Sessions | Displays Parallel Query Coordinator or Parallel Query Slave sessions associated with this session during the last minute. The tabs displayed here are the same as those displayed for a regular session. |
...
See “About associating an Instance with other entities” on page 53. |
...
| Info |
|---|
If you drill down to a Parallel Query Server process, the Main area displays information on this session. You can then identify the Parallel Query Coordinator Session using the Association controls. |
The following table explains the information that is displayed on the tabs in the Association area, when the Statements entity is selected.
Table 4-5 Statements Association area table
Tab Columns
Statement Displays 5 Statements Association area table
| Tab | Columns |
|---|---|
| Statement | Displays the following table columns: |
...
|
...
|
...
|
...
|
...
See “Statement identifier vs. Oracle hash value” on page 39. See “In Oracle” on page 36. |
| In |
...
| Oracle | Displays the following table columns: |
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
See “Statement identifier vs. Oracle hash value” on page 39. See “In Oracle” on page 36. |
...
| Statistics | Displays the change in Oracle statistics for the statements in the last minute. See “Oracle statistics usage within Precise for Oracle” on page 40. |
The following table explains the information that is displayed on the Lock tab in the Association area, when the
Sessions Involved in Lock entity is selected.
Table 4-6 Lock 6 Lock tab informationColumn Description
Program Name
| Column | Description |
|---|---|
| Program | Name of the program connected to Oracle. |
| Session |
...
| ID | ID of the session. |
| In Oracle (Last Minute) |
...
| Bar chart showing the In Oracle breakdown for the session. | |
| Status |
...
| Indicates whether the session is waiting for or holding the lock. |
...
| type | Transaction type: TX, TM, and so on. This information is derived from the P1 parameter in the |
...
| V$SESSION_WAIT table. |
...
| Mode | Transaction mode: Exclusive, Shared, and so on. This information is derived from the P1 parameter in the V$SESSION_WAIT table. |
...
| ID1 | This information is equivalent to P2 parameter in the V$SESSION_WAIT table. |
...
| ID2 | This information is equivalent to P3 parameter in the V$SESSION_WAIT table. |
...
| Object | Locked object, if appropriate. |
| Text |
...
| Portion of SQL text for the session being locked. |
About the Statement entity
The Statement entity, in the Current tab, displays information on a single SQL statement that has been run by a session in the last minute or is still running. You cannot associate a statement with any other entity. This implies that if the Main area displays information on a statement, the Association area remains blank.
See “About Precise for Oracle tabs” on page 21. Getting an overview of current Statement entities
The Statement Overview displays general information on a single SQL statement. The following table describes the information displayed in a Statement Overview.
Table 4-7 Statement 7 Statement Overview
| View |
|---|
...
| area | Description |
|---|---|
| Statement | Displays information on the following items: |
...
|
...
|
...
|
...
|
| In Oracle (Last Minute) |
...
|
...
|
...
|
...
|
...
|
| Overall Activity (Last |
...
| Minute) |
...
| A pie chart illustrating the overall activity of the statement during the last minute. See “Overall activity” on page 35. |
...
| Text | The SQL text of the statement. |
About the text of a statement
The Text view displays the SQL text of the statement.
About statement-related statistics changes
The Statistics view displays a list of the changes that occurred in the Oracle statistics for the statement during the last minute.
See “Oracle statistics usage within Precise for Oracle” on page 40.
The following table describes the information displayed in the Statement Statistics view.
Table 4-8 Statement 8 Statement Statistics viewColumn Description
Statistic Oracle
| Column | Description |
|---|---|
| Statistic | Oracle statistic name |
...
| Value | Statistic value |
| Anchor | ||||
|---|---|---|---|---|
|
You can identify a performance problem by doing one or more of the following:■ Examining
- Examining resource consumption of an entire instance
...
- Examining a single connection
...
- Resolving a locking situation
Examining resource consumption of an entire instance
The Current tab displays near real-time information on all sessions that were connected to your Oracle database during the last minute. Usually you will use this tab to monitor the system's current activities and resource consumption, and to identify performance problems, such as sessions involved in locks as they occur.
The following example shows how to examine resource consumption of an instance. By examining the current activity at an instance level of display, you can see how it is currently behaving and determine if there is an overall problem that affects all sessions or if there is a problem with specific sessions. For this example, the Main area of the Current tab shows the top two sub-states (in the Sub-State column) for the instance's In Oracle time as Using CPU and Redo Log Buffer Wait.
To examine resource consumption of an entire instance
1 In the Instance list, choose the instance you want to analyze.
Do one of the following:
■ On the View controls in the Main area, click Overview. This will show you a breakdown of resource consumption for the last minute.
■ On the View controls, click Statistics to analyze the current statistics of the selected instance.
2 In the Main area, check for any unproductive wait states. In our example, the sessions on average (in the %
column) are spending roughly a third of their time (34.91%) waiting on the redo log buffer.
3 To further investigate, launch the Statistics tab. View the Statistics tab to look for system events that relate to the redo log buffer. Launch to the Statistics tab, in context, to view additional statistical information for any selected instance.
4 In the Association area, move the cursor over the In Oracle bar in the list of Active Sessions to display a ToolTip that shows which sessions are spending the majority of their time waiting on Redo Log Buffer Wait.
5 Check how much time is spent using CPU. If all other wait states have been eliminated, obviously this will be
100%; therefore, it is important to look at the actual time (in hh:mm:ss), in the Main area, as well as the percentage value. High CPU usage can indicate excessive logical I/O operations.
6 To further investigate, open the Activity tab. Try tuning any SQL with a high Using CPU time and high values for the statistics consistent gets and db block gets.
Examining a single connection
You can also monitor current connections to your Oracle instance, check the state of each connection, and identify the resources currently utilized by each session.
The following example shows how to examine a single connection. For this example, the Association area of the
Current tab displays the sub-state Using CPU in the Active Sessions list. To examine a single connection
1 In the Instance list, choose the instance you want to analyze.
2 In the Association area, drill down to the session whose connection you want to analyze. For an example, click the top session ID 10 (in the Session ID column), in the Active Sessions list, for the sub-state Using CPU. In this example, we see all the statements that the selected Session entity has executed in the last minute, or is possibly still executing. The top statement is mainly using CPU and waiting for I/O.
3 Continue your analysis by examining its resource breakdown. To further investigate, in the Association area, click the Tune icon in the row of the top statement. Clicking the Tune icon opens the SQL tab for tuning this statement.
Resolving a locking situation
You can view any sessions that are locked in the Current tab. Then you can fully investigate the situation to determine the type of lock and which session is holding the lock.
In the Main area, the following example shows three sessions (shown in the Sessions column) that have waited for a row lock over the last minute. The time waited (shown in the Time column) is 1 minute 57 seconds or 100% of the total In Oracle time for the instance.
In the Association area, three active sessions are in the state of a row lock wait. For our example, Session 117 (the top locked session shown in the Association area) is attempting to execute and UPDATE on the CUSTOMER table (shown in the Program column).
To resolve a locking situation
1 In the Instance list, choose the instance you want to analyze.
2 On the Association controls, in the Association area, you can associate Session 117 with the other Sessions involved in the lock. For an example, Session ID124 is holding the lock in Exclusive mode.
3 Using the Association controls, you can now drill down to see the statements being executed by the session that is holding the lock (Session ID 124).
For our example, in the Main area, you can see that the holding session (for Duration) has not run any statements in the last 15-minute time slice, meaning that the UPDATE to the CUSTOMER table (for Program) was executed earlier but that the session has not yet issued a COMMIT to release the row lock.
...