Page History
...
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:
| Group | Description |
|---|---|
| OTHER | Includes all recognized statements other than INSERT, SELECT, UPDATE, DELETE, and MERGE statements. |
| UNKNOWN | 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:
| Statement Component | Description |
|---|---|
| Subject | The DML statement type (and FROM clause, as appropriate). |
| Predicate | The WHERE clause. |
| Remainder | Any statement component following 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.
...
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. |
| Statistic | Shown for Platform | Notes |
|---|---|---|
| Exeutions | 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. |
| Avg. Elapsed (sec) | Oracle, DB2 | 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. |
| DB Activity (%) | SQL Server, Oracle, Sybase, DB2 | A graphical representation of the distribution of execution and wait time for the statement or statement component. |
| SQL ID | Oracle | The ID value of the SQL statement. This statistic only appears on Oracle data sources. |
| Child Number | Oracle | The child number in the database. This statistic only appears on Oracle data sources. |
| Parsing User ID | Oracle | The ID of the user who parsed the statement. This statist only appears on Oracle data sources. |
| Plan Hash Value | Oracle | The execution value of the statement. This statistic only appears on Oracle data sources. |
| Anchor | ||||
|---|---|---|---|---|
|
...
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 following parameters are displayed on the Blockers tab:
| Value | Description |
|---|---|
| User Name | The user name under which the session was run. |
| Program | The name of the executable under which the session was run. |
| SID | The SID value of the session. |
| Serial | The serial number of the machine from which the session executed. |
| Blocking (%) | A graphical representation of the percentage of total blocked sessions being blocked by a blocking session. |
| Machine | The machine name and network location of the machine from which the session executed. |
| Session Type | The type of session. |
| Client Info | The name/type of the client from which the session initiated. |
| Info |
|---|
For more detailed information, see Viewing Details on the Blockers Tab (Oracle). |
...
The following parameters are displayed on the I/O tab:
| Value | Description |
|---|---|
| Object | The name of the data source object affecting the Oracle I/O. |
| Type | The object type. For example, table, partition, or index. |
| DB Activity (%) | Use the color chart on the right-hand side of the I/O tab to view the I/O load on the data source during the profiling session. |
| Tablespace | The name of the tablespace where the object resides. |
| File ID | The unique ID value of the file from where specified object resides. |
| Anchor | ||||
|---|---|---|---|---|
|
...
The following parameters are displayed on the Procedures tab:
| Value | Description |
|---|---|
| Procedure Name | The name of the procedure affecting the database performance. |
| Database Name (SQL Server, Sybase only) | The name of the database where the procedure resides. |
| Oracle (Oracle only) | The owner of the schema in which the procedure resides. |
| Procedure ID | A unique ID created when the procedure is created. |
| Executions (SQL Server, Sybase only) | The number of times the procedure was executed during the session. |
| Anchor | ||||
|---|---|---|---|---|
|
...
Analyze Profiling Details
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.
Tab NameDescriptionSupported Platform
OracleSybaseDB2 SQL Server
SQL textDisplays the full code of the selected SQL statement.yesyesyesyes
SQL Details Provides details on statement, like execution statistics.yesyes
EventsProvides database activity details about events the statement is associated with.
...