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: 

GroupDescription
OTHERIncludes 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 ComponentDescription
Subject

The DML statement type (and FROM clause, as appropriate).

Predicate

The WHERE clause.

RemainderAny 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. 

StatisticNotes
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:

ValueDescription
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 InfoThe 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:

ValueDescription
ObjectThe name of the data source object affecting the Oracle I/O.
TypeThe 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.
TablespaceThe 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:

ValueDescription
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.



IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal