Page History
The Load Chart is located on the top section of the Profile Session editor and provides a display of the overall load on the system. The bars represent individual aspects of the enterprise, and the view is used to discover bottlenecks.
The most important part of the previous screenshot is the Average Active Sessions (AAS) graph. AAS shows the performance of the database measured in the single powerful unified metric AAS. AAS easily and quickly shows any performance bottlenecks on the database when compared to the Max Engines (for Sybase) or Max CPU (for Oracle) line. The Max Engines line is a yardstick for performance on the database. When AAS is larger than the Max CPU line, there is a bottleneck on the database. Bottleneck identification is that easy.
...
* A chart legend displays a color and code scheme for executing and waiting session categories, in the upper right-hand corner of the view.
...
The Top Activity Section is located in the middle section of the editor and displays where the load originates. Specifically, the top SQL statements, top events that the database spends time in, as well as the top activity sessions.
The Top Activity Section is composed of a series of tabs that provide detailed statistics on individual SQL statements and sessions that were waiting or executing over the length of a profiling session.
...
.
...
When you select any item from the Top Activity tabs, details are displayed in the Profiling Details view. The tabs that appear in Profiling Details will be different depending on the database platform and whether you selected a statement, session, or an event. This is to accommodate the parameter specifics of the item you selected.
...
The Profile editor's SQL tab shows a representation of all SQL statements that are executing or waiting to execute over the length of the profiling session or within the currently selected graph bars.
| Info |
|---|
The image below depicts results achieved for a Sybase database. The columns displayed on this tab differ depending on the database platform. |
...
Statements can be grouped by type by right-clicking the view and selecting Organize > By Type.
| Info |
|---|
Statements are grouped when they differ only by their clause values. This enables the roll-up of SQL statements that only differ by a variable value. For example: select * from emp where empno=1; and select * from emp where empno=2. A '+' symbol appears beside rollup statements. You can click the symbol to expand and view the different statement predicates. |
Additionally, the SQL tab displays two other groupings of statements:
...
Statements that are not recognized by the application. SQL Query Tuner has been improved to query the caching more often and more intelligently so that UNKNOWN appears less frequently in the Top SQL tab. The system queries the data source for SQL text in 15 second intervals. Unknown may still appear infrequently as the SQL text may have been removed by the database.
All statements are displayed in a tree structure with the following statement components:
...
The DML statement type (and FROM clause, as appropriate).
...
The WHERE clause.
...
For example, all statements with common subjects are shown as a single entry with multiple children; one child for each unique predicate. Predicates are similarly broken down by remainders.
| Info |
|---|
Right-clicking the SQL tab and selecting Organize By lets you choose between Statement Type grouping and None. The None option disables grouping by statement. |
Statistics
Statistics are provided for statements and statement components. The statistics let you evaluate costs and spot wait event problems not just at the level of entire SQL statements, but also at the level of statement components. For each subject, predicate or remainder entry, the following statistics are provided:
| Info |
|---|
Columns displayed on the top SQL tab differ depending on the data source platform. |
...
SQL Server, Oracle, Sybase, DB2
...
The number of active executions for the statement or statement component over the length of the profiling session or the selected graph bars.
...
The average amount of time that elapsed while executing the statement during the profiling period. This column appears for only SQL Server, DB2 and Oracle data sources.
...
SQL Server, Oracle, Sybase, DB2
...
A graphical representation of the distribution of execution and wait time for the statement or statement component.
...
The ID value of the SQL statement. This statistic only appears on Oracle data sources.
...
The child number in the database. This statistic only appears on Oracle data sources.
...
The ID of the user who parsed the statement. This statist only appears on Oracle data sources.
...
The execution value of the statement. This statistic only appears on Oracle data sources.
...
In addition to the statistics displayed on the Top SQL tab, DB2 platforms have an additional tab in the Profile Session editor named Execution Activity, which contains the following statistical rows: Rows Read, Rows Written, Fetch Count, Statement Sorts, Sort Time, and Sort Overflows.
...
The Top Events tab displays information about wait events on the resources involved in the profiling process. This display is used to tune at the application or database configuration level. For example, if the top events are locks, then application logic needs to be examined. If top events are related to database configuration, then database setup should be investigated.
| Info |
|---|
The columns that display are data source-dependent. For example, the Wait Count and Avg. Per Wait (sec) columns display only for an Oracle data source. |
...
The Sessions Tab provides information about individual sessions. This tab provides information about sessions that are very active or bottlenecked.
...
The Blockers tab provides details on sessions holding blocking locks.
The following parameters are displayed on the Blockers tab:
...
The user name under which the session was run.
...
The name of the executable under which the session was run.
...
The SID value of the session.
...
The serial number of the machine from which the session executed.
...
A graphical representation of the percentage of total blocked sessions being blocked by a blocking session.
...
The machine name and network location of the machine from which the session executed.
...
The type of session.
...
| Info |
|---|
For more detailed information, see Viewing Details on the Blockers Tab (Oracle). |
...
The Object I/O Tab is specific to the Oracle data source platform, and displays information about Oracle I/O loads on the profiled data source.
The following parameters are displayed on the I/O tab:
...
The unique ID value of the file from where specified object resides.
...
The Procedures tab is specific to Oracle, SQL Server, and Sybase data source platforms. It displays information about Procedure loads on the profiled data source.
The following parameters are displayed on the Procedures tab:
...
The name of the procedure affecting the database performance.
...
The name of the database where the procedure resides.
...
The owner of the schema in which the procedure resides.
...
A unique ID created when the procedure is created.
...
The Profiling Details view displays detailed information on any item selected in the Top Section View, such as an SQL statement, an Event, a Session or a Procedure.
Depending on the data source platform you have specified, the tabs that appear in the view will be different, in order to accommodate the parameter specifics of the statement you have selected.
Depending on the top activity selected and the profiled platform types, some tabs may not be available.
Statement Selected
When a Statement is selected, the following Profile Detail tabs are available.
...
Shows which sessions held blocking locks against the session associated with this statement.
Double-clicking an entry on this tab opens that session in the Top Blockers tab, letting you find more information on the blocking session. For details, see Top Blockers Tab (Oracle Specific).
...
Lists all copies of the cursor or SQL query, if Oracle has cached multiple copies of the same statement.
...
Shows bind variable information for SQL captured during the Profiling session.
...
Event Selected
When an Event is selected, the following Profile Detail tabs are available.
...
Shows which sessions held blocking locks against the session associated with this event.
Double-clicking an entry on this tab opens that session in the Top Blockers tab, letting you find more information on the blocking session. For details, see Top Blockers Tab (Oracle Specific).
...
Shows which procedures contain the selected event.
...
Raw data that was sampled from the database, specifically the following:
- Sample time
- SID
- Serial #
- User name
- Program
- Sql ID
- P1
- P2
- P3
...
Displays for "buffer busy waits" and "cache buffer chains latch" waits. The analysis shows data and documentation to assist in solving these bottlenecks.
...
Session Selected
When a Session is selected, the following Profile Detail tabs are available.
...
Provides parameters regarding the session. For example, database server connection information, and data regarding the client tool and application.
...
Shows which sessions held blocking locks while this session was active.
double-clicking an entry on this tab opens that session in the Top Blockers tab, letting you find more information on the blocking session. For more details, see .
...
Provides identifier and V$SESSION session information on the sessions being locked by the blocking session.
Provides parameters regarding the session. For example, database server connection information, and data regarding the client tool and application.
...
yesyes
This section also addresses the following topics:
• Viewing Details on the SQL Tab
• Viewing Details on the Sessions Tab
• Viewing Details on the Events Tab
• Viewing Details on the Procedures Tab
...
The SQL Details tab provides information and the execution of the statement and other information related to how it is running. It is only applicable to Oracle data sources:
...
The Events tab provides details about the events that the statement is associated with.
...
The Sessions tab provides information about any sessions the statement is associated with:
Session details include information on different parameters, depending on the platform. For example, on Oracle platforms, the following parameters are displayed: User Name, Program, SID, Serial #, Activity (%), Network Machine Name, and Session Type.
...
The Procedures tab provides information about any procedures containing the selected statement.
The following parameters are displayed on the Procedures tab:
Value Description
Procedure NameThe name of the procedure that contains the selected statement. Database NameThe name of the database where the procedure resides.
Procedure IDThe unique ID value of the file where the specified procedure resides. ExecutionsThe number of times the procedure was executed.
DB Activity (%)Use the color chart on the right-hand side of the Procedures tab to view the procedures load on the data source during the profiling session.
...
In the Top Activities Section, selecting an entry on the Blocked Sessions tab displays information on sessions holding blocking locks in the Profiling Details view.
Blocked Sessions
The Blocked Sessions tab provides general information on blocked sessions and the details identifying the specific row locked.
This tab provides the following columns for each blocked session:
Value | Notes |
User Name | The user name under which the blocking session was run. |
SID | The SID value of the blocking session. |
ROW_WAIT_OBJ# | Object ID of the table containing the row specified in ROW_WAIT_ROW#. |
ROW_WAIT_FILE# | Identifier of the datafile containing the row specified in |
ROW_WAIT_BLOCK# | Identifier of the block containing the row specified in ROW_WAIT_ROW#. |
ROW_WAIT_ROW# | The current row being locked. |
...
In the Top Activities Section, selecting a procedure entry on the Procedure tab displays information in the Profiling Details view. The graph portion and details on the procedure category tabs on the new editor pertain only to the selected procedure. Additionally, new tabs become available.
Selecting a procedure type entry on a procedure category tab opens a new profiling editor page. The graph portion and details on the Procedure tab and procedure category tabs on the new editor page pertain only to the selected procedure and to SQL statements that waited in that procedure.
• The SQL Text tab shows the SQL of the procedure. For more information, see SQL Text.
• The SQL shows the statements involved in the procedure. For more information, see SQL.
• The Events displays the time and parameter information about the selected procedure. For more information, see Events.
• The Sessions displays information about the sessions that the selected procedure is associated with. For more information, see Sessions. Anchor
The SQL Text tab displays the full code of the procedure.
The SQL tab displays information about the SQL statements involved in the selected procedure.
The SQL tab displays the following parameters about the statement:
Value Notes
StatementThe name of the statement.
SQL IDThe ID value of the SQL statement. Child NumberThe child number in the database.
Parsing User IDThe ID of the user who parsed the statement. Plan Hash ValueThe execution value of the statement.
CPUCumulative CPU time for the process. (measured in "ticks", an arbitrary unit of time)
Physical IOCumulative disk reads and writes for the process. (total count)
Memory UsageNumber of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
ExecutionsThe number of times the statement was executed.
Activity (%)A graphical representation of the distribution of execution and wait time for the statement or statement component.
The Events tab provides details about the events that the session is associated with.
Events are listed by the following values:
Value Notes
Event NameThe name of the event.
ClassThe wait group the event in the selected procedure belongs to.
Activity (%)A graphical representation of the distribution of execution and wait time for the event.
The Sessions tab displays the sessions and related information regarding those that were associated with the selected procedure.
The following parameters are displayed on the Sessions tab:
Value Notes
User NameThe user name under which the session was run.
ProgramThe name of the executable under which the session was run. SIDThe SID value of the session.
Serial NumberThe serial number of the machine from which the session executed.
Activity (%)A graphical representation of the distribution of execution and wait time for the statement or statement component.
MachineThe machine name and network location of the machine from which the session executed.
Session TypeThe type of session.
For the Oracle Platform, you can kill a session by right-clicking the entry on the Sessions tab and choosing Kill Session. You can start a trace on a session by right-clicking the entry on the Sessions tab and choosing Trace. For more information, see Killing an Oracle Session and Tracing an Oracle Session.