Page History
...
The Max CPU is a yardstick for performance on the database. The number of CPUs or Engine on the data source is information SQL Query Tuner obtains during the profiling process. However, sometimes the number of CPUs or Engines is not reported. In these cases, it might be desirable to change the default number of CPUs/Engines from one to a number more closely matching the actual system running the data source. You might also want to change the Max CPU/Engine line to reflect the performance impact of adding or removing a CPU or Engine from the system.
To change the Max CPU or Max Engine count in the Load Graph
...
- Time is displayed on the X axis. You can zoom in and zoom out on the graph via the icons in the upper right hand corner of the graph, once a profiling session is stopped.
- The Y axis shows the average number of sessions waiting or executing. Each supported platform has a specific set of wait event times.
| DBMS | Wait Event Category |
|---|---|
| IBM DB2 | Fetch, Cursor, Execution, Operation, Transaction, Connectivity, Lock, Other |
| Oracle | On CPU, System I/O, User I/O, Cluster, Application, Configuration, Commit, Other |
| SQL Server | CPU, Lock, Memory, Buffer, I/O, Other |
| Sybase | CPU, Lock, Memory, I/O, Network, Other |
* 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 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
Statements can be grouped by type by right-clicking the view and selecting Organize > By Type.
TIP:
| 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: GroupDescription
OTHERIncludes
| 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.
| 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. |
...
| 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 | ||||
|---|---|---|---|---|
|
...
Top Execution Activity Tab (DB2
...
Specific)
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.
| Anchor | ||||
|---|---|---|---|---|
|
...
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.
NOTE:
| 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. |
| Anchor | ||||
|---|---|---|---|---|
|
...
Top Sessions Tab
The Sessions Tab provides information about individual sessions. This tab provides information about sessions that are very active or bottlenecked.
| Anchor | ||||
|---|---|---|---|---|
|
...
Top Blockers Tab (Oracle Specific)
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. |
| Info |
|---|
For more detailed information, |
...
| Anchor | ||||
|---|---|---|---|---|
|
...
Top Object I/O
...
Tab (Oracle Specific)
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:
| 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 | ||||
|---|---|---|---|---|
|
...
Top Procedures Tab (Oracle, SQL
...
Server, and Sybase Specific)
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:
...
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
| Anchor | ||||
|---|---|---|---|---|
|
...
Session Details
The Session Details tab provides information on the server connection, client, and application associated with the blocking session.
SQL
The SQL tab displays information about the statements associated with the blocking session.
SQL statements are listed by the following parameters:
Value Notes
StatementThe name of the statement.
ExecutionsThe number of times the statement was executed during the session. Activity (%)A graphical representation of the distribution of execution and wait time
for the statement or statement component. SQL IDThe SQL ID value of the statement.
Child NumberThe child number in the database.
Events
The Events tab provides details about the events that the blocking session is associated with.
Events are listed by the following values:
Value Notes
EventThe name of the event.
DB Activity (%)A graphical representation of the distribution of execution and wait time for the statement or statement component.
| Anchor | ||||
|---|---|---|---|---|
|
In the Top Activities Section, selecting a statement entry on the Event tab displays information in the Profiling Details view. The graph portion and details on the event category tabs on the new editor pertain only to the selected statement. Additionally, new tabs become available.
Selecting an event type entry on an event category tab opens a new profiling editor page. The graph portion and details on the Events tab and event category tabs on the new editor page pertain only to the selected wait event and to SQL statements that waited in that event.
• SQL tab: Shows the statements involved in the selected event. For more information, see SQL.
• Sessions tab: Displays information about the sessions that the selected event is associated with. For more information, see Sessions.
• Procedures tab: Displays information about the procedures that ran during the selected event. For more information, see Procedures.
| Anchor | ||||
|---|---|---|---|---|
|
The SQL tab displays information about the SQL statements involved in the selected event.
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)
Value Notes
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.
| Anchor | ||||
|---|---|---|---|---|
|
The Sessions tab displays the sessions and related information regarding those that were associated with the selected event.
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.
| Anchor | ||||
|---|---|---|---|---|
|
The Procedures tab displays the procedures and related information regarding those that were associated with the selected event.
The following parameters are displayed on the Procedures tab:
Value Notes
Procedure NameThe name of the procedure that ran during the event. Database NameThe name of the database where the procedure resides. Procedure IDThe unique ID of the procedure.
ExecutionsThe number of times the procedure ran during the event.
DB Activity (%)A graphical representation of the distribution of execution and wait time for the procedure.
| Anchor | ||||
|---|---|---|---|---|
|
...