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.
- The top SQL tab provides more detailed information than provided on the Overview tab, in terms of executing SQL statements and procedures. For more information, see Top SQL tab.
- The top Events tab displays information about wait events profiled by the execution process. For more information, see Top Events tab.
- The top Sessions tab displays information about sessions profiled by the execution process. For more information, see Top Sessions tab.
- The top Blockers tab displays information about blocking sessions. For more information, see Top Blockers tab.
- The top ObjectI/O tab displays information about the I/O profiled by the execution process. For more information, see Top Object I/O tab.
- The top Procedures tab displays information about procedures observed during profiling. For more information, see Top Procedures tab.
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.
Top SQL tab
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.
Statements
Statements can be grouped by type by right-clicking the view and selecting Organize > By Type.
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.
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:
Columns displayed on the top SQL tab differ depending on the data source platform.
| Statistic | Notes |
|---|---|
| Exeutions | 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) | The average amount of time that elapsed while executing the statement during the profiling period. |
| DB Activity (%) | A graphical representation of the distribution of execution and wait time for the statement or statement component. |
| SQL ID | The ID value of the SQL statement. |
| Child Number | The child number in the database. |
| Parsing User ID | The ID of the user who parsed the statement. |
| Plan Hash Value | The execution value of the statement. |
Top Events tab
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.
Top Sessions tab
The Sessions Tab provides information about individual sessions. This tab provides information about sessions that are very active or bottlenecked.
Top Blockers tab
The Blockers tab provides details on sessions holding blocking locks.
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. |
For more detailed information, see Viewing details on the Blockers tab.
Top Object I/O tab
The Object I/O tab displays information about Oracle I/O loads on the profiled data source.
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. |
Top Procedures tab
The Procedures tab displays information about Procedure loads on the profiled data source.
The following parameters are displayed on the Procedures tab:
| Value | Description |
|---|---|
| Procedure Name | The name of the procedure affecting the database performance. |
| Oracle | The owner of the schema in which the procedure resides. |
| Procedure ID | A unique ID created when the procedure is created. |