Page History
...
The Text view, in the Activity tab, displays the full SQL text.
About information on the cabinet or folder related to a statement
The Workshop view displays details on the cabinet or folder where the statement is saved, and on how it will be explained.
The following table describes how the information in the Workshop view is structured.
Table 5-9 Structure 9 Structure of information in the Workshop viewItem Description
| Item | Description |
|---|---|
| Parsing |
...
| User | Oracle user name used to explain and parse the statement. |
| Cabinet |
...
| Name of the cabinet where the statement is stored. |
| Folder |
...
| Name of the folder where the statement is stored. |
| Origin |
...
Source of the statement. The Collector captures most statements automatically when they execute. The following values are possible: |
...
|
...
|
...
|
...
|
...
|
...
|
...
|
| Comment |
...
| User-defined comment associated with the statement. |
See “Editing the properties of a statement” on page 31. See “About the SQL tab” on page 131.
About the Execution Plan entity
The Oracle server generates execution plans at run-time. Precise for Oracle displays information on these plans so that you can analyze them and determine if any enhancements can be made to improve their execution.
You can drill down from an execution plan to the SQL statements that are using it. You can also associate execution plans with the following entities: Instances, Databases, Programs, Statements, PL/SQLs, other Execution Plans, Users, Modules, Actions, Machines (or their ERP equivalents), Host Users, Oracle Files, Objects, Recent Sessions and Extended Collections.
The following views are available for an execution plan entity:■ Overview
- Overview
...
- In Oracle
...
- Overall Activity
...
- Scalability
About an overview of an execution plan
The Overview, in the Activity tab, displays general information about the selected entity, a table that lists the In Oracle sub-states for this entity, and the complete SQL text of the SQL statement.
See “In Oracle” on page 36.
The following table describes the information displayed for an execution plan.
Table 5-10 General 10 General over of an execution plan
| Item | Description |
|---|---|
Item Description
In Oracle (Sum.) Total time spent in Oracle by statements using the selected plan.
Execution Counts Number of executions performed by the statements.
In Oracle (Avg.) Displays the average time spent by the execution plan in the Oracle database.
Duration (Avg.) Displays the average activity time for the execution plan.
Buffer Gets (Avg.) Average number of buffer gets for the execution plan.
Rows Processed (Avg.) Average number of rows processed by the execution plan.
The following table describes the In Oracle (Sum.) table for an execution plan.
Table 5-11 In Oracle (Sum.) for an execution plan
Column Description
Sub-State In Oracle sub-state.
Bar graph Graphical representation of the Time column.
Time Amount of time the entity was in this state.
% Percentage of time the entity was in this sub-state.
Additional information on In Oracle sub-states is available in the stacked bar graph of the In Oracle view and on the In
Oracle tab of the table in the Association area.
About In Oracle data for an execution plan
The In Oracle view, in the Activity tab, displays a stacked bar graph on the In Oracle resource consumption for the execution plan, over time, broken down into the In Oracle wait states.
See “In Oracle” on page 36.
About the overall activity view of an execution plan
The Overall Activity view, in the Activity tab, displays a stacked bar graph describing the overall activity resource consumption of the execution plan over time, broken down into overall activity wait states.
See “Overall activity” on page 35.
About the scalability view of an execution plan
The Scalability view, in the Activity tab, displays a stacked bar graph showing how the average In Oracle response time varies with the number of statement executions. This graph indicates whether your application scales as the number of statement executions increase.
When you drill down from an instance or Tier, the In Oracle (Avg.) vs. Statement Executions graph is shown. Otherwise, no graph is displayed.
About the Object entity
Whereas statements and PL/SQL are the fundamental building blocks of an application, the objects are the fundamental building blocks of a database. This view gives a DBA an opportunity to tune the objects in the database and is a far more powerful and less intrusive approach than looking at, for example, the V$SEGMENT_STATISTICS table. It is particularly useful if the application code cannot be changed, which is often the case when application packages have been purchased.
The Activity tab shows resource consumption information for objects that are accessed by SQL statements. These are the objects that can be seen in explain plans, namely tables, indexes, and clusters, plus some special objects representing Control file I/O, Log File I/O, Temporary I/O, and Undo I/O. Wait time on other types of objects is not displayed. For example, the Oracle Optimizer will expand a synonym to its underlying table name during the parse phase before the explain plan is created.
You can also view how many partitions an object has in the Association area.
Note: To look at the selected object in detail, or to understand its performance over time, you can launch the Objects tab by clicking the Object tab button for an object in the Main area or the launch icon for an object in the Association area.
An Object can associate with Instances, other Objects, Oracle Files, Statements, PL/SQLs, Programs, Users, Modules, Actions, Machines (or their ERP equivalents), and Host Users.
For an Object, the following views are available:
■ Overview
■ In Oracle
■ Dictionary
About the overview of an object
The Overview, in the Activity tab, displays a table that lists the I/O Wait sub-states for this object. Precise for Oracle records the following sub-states:
■ Sequential I/O Wait
■ Scattered I/O Wait
■ Direct I/O Wait
■ Other I/O Wait
■ Buffer Wait
■ Table Lock Wait
■ Row Lock Wait
Additional information on In Oracle sub-states is available in the stacked bar graph of the In Oracle view and on the In
Oracle tab of the table in the Association area. See “I/O wait” on page 38.
About In Oracle data for an object
The In Oracle view, in the Activity tab, displays a stacked bar graph on the In Oracle resource consumption over time, for the selected time frame, broken down into the In Oracle wait states. Particularly important for an object is the amount of time spent in I/O wait, Internal Lock wait, and Application Lock wait, that is how well cached the object is.
See “In Oracle” on page 36.
About the dictionary view of an object
The Dictionary view, in the Activity tab, describes the properties of the object. The information displayed is equivalent to information that can be found in the DBA_OBJECTS table.
About the Oracle File entity
Precise for Oracle shows resource consumption information for those Oracle files where the application spends time waiting directly on I/O. These are most likely to be data and temporary files. I/O wait time on other types of files such as redo log files are less likely because Oracle writes to these asynchronously using a background job such as LGWR. This information can be found in the Statistics tab.
Note: To look at the selected Oracle file in detail, or understand its performance over time, you can launch the Objects tab by clicking the Objects tab button for an Oracle file in the Main area or the launch icon for an Oracle file in the Association area.
An Oracle File can associate with Objects, Oracle Files, Statements, PL/SQLs, and Storage Devices (only if Precise for Storage is installed).
For an Oracle File, the following views are available in the Main Area:
■ Overview
■ In Oracle
■ Dictionary
About an overview of an Oracle file
The Overview, in the Activity tab, displays a table listing the I/O Wait sub-states for this Oracle file. Additional information on In Oracle sub-states is available in the stacked bar graph of the In Oracle view and on the In Oracle tab of the table in the Association area.
See “I/O wait” on page 38.
About the In Oracle view of an Oracle file
The In Oracle view, in the Activity tab, displays a stacked overtime bar graph on the I/O waits of the Oracle file.
About the dictionary view of an Oracle file
The Dictionary view, in the Activity tab, describes the properties of the Oracle file. The information displayed is equivalent to information that can be found in the DBA_DATA_FILES table.
About the Extended Collection entity
You can define an Extended Collection when you need to carry out a more intensive investigation into a particular performance problem. It lets you capture trace-level information from Oracle, allowing you to see Oracle wait event information for sessions and statements. You can, for example, find a unique index contention or ITL contentions.
The wait event information is similar to that which you can get from Oracle's SQL_TRACE table or event 10046 (for example, alter session set events 10046 trace name context forever, level 8) but with much less overhead. The Precise for Oracle Collector automatically captures all the wait events when it is sampling, but it does not normally store the full details. Rather it stores a summary to keep down the storage requirements. When an Extended Collection is enabled, the full wait event data, including wait event parameters, is stored in a trace file on the monitored server. It is not loaded into the PMDB. Wait event parameters allow you to precisely identify the wait cause, such as the exact block in the Oracle file that has caused a db file sequential read wait event.
You can create an Extended Collection by using the Define Extended Collection dialog box.
When an Extended Collection is no longer required, it should be deleted using either the icon in the Association area, or the action control button when in the Main area. Similarly, pending and running Extended Collections can be stopped, and subsequently deleted.
Note: To see Extended Selections listed in the Association controls, you need to have an Instance selected. When you drill down to an Extended Collection, the time frame in the Time Frame list and all graphs change to the start and end time of the collection.
The information displayed is the same as you would see for the Instance over the same time period filtered by the same criteria. However, in addition to this, an Extended Collection is the container for the Sessions, Statements, and Oracle Events that are captured.
For an Extended Collection, the following views are available in the Main Area:
■ Overview
■ In Oracle
■ Overall Activity
See “Defining extended collections” on page 80. See “Filtering data” on page 29.
About the overview of an extended collection
The Overview, in the Activity tab, displays general information about the Extended Collection for the specified time frame and a table that lists the In Oracle sub-states for this entity.
See “In Oracle” on page 36.
The following table describes how the general information for an extended collection is structured.
Table 5-12 Structure of general information for an extended collection
Item Description
Status Status of the collection, which can be one of the following: Pending, Completed, Running, or Stopped.
Criteria List of programs and other criteria that are included or excluded in the collection.
Current Size Size of the collection. The size is limited when the collection is created. See “Defining extended collections” on page 80.
Start Collecting at Start date and time of the collection.
Stop Collecting at End date and time of the collection.
The following table describes the In Oracle (Sum.) table for an extended collection.
Table 5-13 In Oracle (Sum.) table for an extended collection
Column Description
Sub-State In Oracle sub-state.
Bar graph Graphical representation of the Time column.
Time Amount of time the entity was in this state.
% Percentage of time the entity was in this sub-state.
Additional information on In Oracle sub-states is available in the stacked bar graph of the In Oracle view and on the In
Oracle tab of the table in the Association area.
About In Oracle data for an extended collection
The In Oracle view, in the Activity tab, displays a stacked bar graph on the In Oracle resource consumption over time, for the selected time frame, broken down into the In Oracle wait states.
See “In Oracle” on page 36.
About the overall activity view of an extended collection
The Overall Activity view, in the Activity tab, displays a stacked bar graph on the Overall Activity resource consumption over time, for the selected time frame, broken down into the Overall Activity wait states.
See “Overall activity” on page 35.
About Session and Recent Session entities (in Table view only)
Precise for Oracle captures a detailed history of every session but does not load this information into the PMDB because it quickly becomes out of date and because of the storage requirements. Instead, this information is saved on the monitored server for only as long as is required. This is accomplished using two mechanisms: Extended Collections and Recent Sessions.
When an Extended Collection is defined, the session information is captured and saved in the Extended Collection files. You can then drill down from an Extended Collection to a Session.
In addition, Precise for Oracle keeps the last n hours of session information on the server and makes it available using Recent Sessions. By default, information for the last 4 hours is saved. You can specify and change the length of time using Precise Agent Installer (see the Precise Installation Guide for details), but increasing it will obviously require more storage.
Note: Because session information is only saved for a limited amount of time (by default, 4 hours), if you drill down to recent sessions, you will only see session information for that period, regardless of the time frame specified in the Time Frame list.
A Recent Session can associate with Statements.
For a Session (in an Extended Collection) or Recent Session, the following views are available in the Main Area:
■ Overview
■ In Oracle
■ Overall Activity
See “About the Extended Collection entity” on page 75.
About the overview of a session or recent session
The Overview, in the Activity tab, displays general information about the Session (in an Extended Collection) or Recent
Session for the specified time frame and a table listing the In Oracle sub-states for this entity (for details on these sub-states, see See “In Oracle” on page 36.).
The following table describes how the general information for a session or recent session is structured.
Table 5-14 Structure of general information for a session or recent session
Item Description
In Oracle Total time the session spent in Oracle
User Oracle user name
Host User Host user name.
Machine Name of the machine connected to Oracle
Oracle Process ID OS-assigned process ID of the Oracle server process
Client Process ID Process ID of the client or application process, if it is running on the same server as the instance;
otherwise it is zero.
Oracle Process Type Type of Oracle server process: Dedicated, MTS, or Parallel.
The following table describes the In Oracle (Sum.) table for an extended collection.
Table 5-15 In Oracle (Sum.) table for a session or recent session
Column Description
Sub-State In Oracle sub-state.
Bar graph Graphical representation of the Time column.
Time Amount of time the entity was in this state.
% Percentage of time the entity was in this sub-state.
Additional information on In Oracle sub-states is available in the stacked bar graph of the In Oracle view and on the In
Oracle tab of the table in the Association area.
About In Oracle data for a session or recent session
The In Oracle view, in the Activity tab, displays a stacked bar graph on the In Oracle resource consumption over time, for the selected time frame, broken down into the In Oracle wait states.
See “In Oracle” on page 36.
About the overall activity of a session or recent session
The Overall Activity view, in the Activity tab, displays a stacked bar graph on the Overall Activity resource consumption over time, for the selected time frame, broken down into the Overall Activity wait states.
See “Overall activity” on page 35.
About the Imported Statement entity
Statements can be imported from external source files into Precise for Oracle and stored in the cabinet and folder hierarchy. To display them in the Activity tab, use the More... option in the Association area and click Imported Statements.
Note: To tune a statement, you can launch the SQL tab by clicking the SQL tab button for a statement in the Main area or the Tune icon for a statement in the Association area.
You cannot drill down from an imported statement to other entities.
For an Imported Statement, the following views are available in the Main Area:
■ Text
■ Workshop
About the text view of an imported statement
The Text view, in the Activity tab, displays the full SQL text of the statement.
About information on the cabinet or folder related to an imported statement
The Workshop view, in the Activity tab, displays details on the cabinet or folder where the statement or PL/SQL is saved, and on how it will be explained.
The following table describes how the information in the Workshop view is structured.
Table 5-16 Structure of information
Item Description
Explained on The date and time of the last explain.
Parsing User Oracle user name used to explain and parse the statement.
Cabinet Name of the cabinet where the statement is stored.
Folder Name of the folder where the statement is stored.
Origin Source of the statement. In this case, it is always Imported from source file.
Cost The cost of the imported statement.
Saved on Information on when the imported statement was saved.
Statement The name of the imported statement.
Oracle Hash Value The Oracle Hash value of the imported statement.
Comment User-defined comment associated with the statement. This is blank until the statement has been saved.
See “Editing the properties of a statement” on page 31. See “About the SQL tab” on page 131.
About storage device entities
In the Activity tab, there are the following Storage device entities:
■ EMC storage device
■ HP storage device
Note: These entities are only available if you have installed Precise for Storage. For more information on the supported platforms, see the Precise Installation Guide.
The Activity tab offers a unique method for identifying I/O problems in EMC and HP storage devices. It reports I/O wait time instead of just I/O counters. This lets you easily identify the resource that is actually being waited for and tune it instead of guessing by rules of thumb. Furthermore, you can associate the storage device with the applications and statements that are waiting on I/O.
See “Precise for Storage, Oracle version - how resource consumption of storage devices is examined” on page 41.
Note: To look at a storage device in detail or to understand its performance over time, you can launch the Object tab by clicking the Object tab button for a storage device in the Main area or the launch icon for a storage device in the Association area.
When an HP Storage Device is selected, the Launch HP PA button appears to the right of the Define Extended Collection button. This option launches the Web-based user interface of the HP Performance Advisor in another Internet Explorer window.
Storage devices can associate with Objects, Oracle Files, Statements, and PL/SQLs. For a storage device, the following views are available in the Main Area:
■ Overview
■ I/O Wait
■ Dictionary
About the overview of a storage device
The Overview, in the Activity tab, displays a table listing the In Oracle sub-states for this storage device. See “In Oracle” on page 36.
The following table describes the In Oracle (Sum.) table for an extended collection.
Table 5-17 In Oracle (Sum.) table for a storage device
Column Description
Sub-State In Oracle sub-state.
Bar graph Graphical representation of the Time column.
Time Amount of time the entity was in this state.
% Percentage of time the entity was in this sub-state.
Additional information on In Oracle sub-states is available in the stacked bar graph of the In Oracle view and on the In
Oracle tab of the table in the Association area.
About the I/O wait view of a storage device
The I/O Wait view, in the Activity tab, displays a stacked overtime bar graph on the I/O waits of the storage device.
About the dictionary of a storage device
This view, in the Activity tab, describes the properties of the storage device. Note that despite the name of the view, these are properties not derived from the Oracle data dictionary.
About the Findings area in the Activity tab
Note: This is only relevant for the Tree view.
The Findings area in the Activity tab displays performance problems detected in the selected J2EE Caller Service or
Web Transactions. The findings list is displayed according to severity (highest to lowest).
To view the details of a specific finding, hover over the finding name. The area for the selected finding will expand, displaying information and recommended actions. Click on the relevant link to launch the appropriate tab in context, or Learn more to open the help for that finding.
...