Page History
...
For more information, see:
- Opening an Existing Profiling Session
- Filtering Results
- Analyze the Load Chart
- Analyze the Top Activity Section
- Analyze Profiling Details
...
Saved profiling session data is stored in either an SQL Project or in a Profiling Repository on an Oracle data source. You can find profiling sessions saved as .oar files in the SQL Project Explorer. You can find profiling sessions saved to a profiling repository in the Data Source Explorer, either in the Profiling Repository node of the data source or in the Profiling Repositories.
To view a saved profiling session, locate it in either the SQL Project Explorer or in the Data Source Explorer and double-click the icon to open it in the Profiling Session window.
...
You can display filtered subsets of the original profiling results set for each section of profiling based on DBMS platform type:
- IBM DB/2 for Windows, UNIX, and Linux: Application, Creator ID, Cursor Name, Package Name, Statement Type, and User Name.
- Microsoft SQL Server: Application, Command, Database, Hostname, NT domain, Net Address, and User Name.
- Oracle: Processes (Background or User), Action, Application, Hostname, Module, Schema Name, and User Name. When profiling a RAC, there is also an instance filter that appears to let you limit the profiling results shown to a specific instance.
- Sybase: Application, Database, Host, IP Address, KPSID, SPID, and User Name.
You filter results using the filter controls in the upper, right-hand part of the profiling editor.
To filter profile editor results
- Use the Filter by menu to select a filter type. The second menu becomes active based on your selection in the first menu.
- Use the second menu to specify a value.
- Click Refresh [
] to update the profiling details.
The profiling editor is updated to show only results associated with your choice.
| Info |
|---|
Select -None- from the Filter by list to restore the unfiltered results. |
...
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.
AAS or the average number or sessions active, shows how many sessions are active on average (over a 5 second range in SQL Query Tuner) and what the breakdown of their activity was. If all the users were running on CPU then the AAS bar is all green. If some users were running on CPU and some were doing I/O, represented by blue, then the AAS bar will be partly green and partly blue.
The line Max Engines or Max CPU represents the number of CPU processors on the machine. If we have one CPU/Engine then only one user can be running on the CPU/Engine at a time. If we have two CPUs/Engines then only two users can be on CPU at any instant in time. Of course users can go on and off the CPU/Engine extremely rapidly. When we talk about sessions on the Engines we are talking about the average number of sessions on CPU/Engine. A load of one session on the Engine thus would be an average which could represent one uses who is consistently on the CPU/Engine or many users who are on the CPU for short time slices. When the number of Engines becomes a resource bottleneck on the database we will the average active sessions in CPU/Engine state go over the Max Engine/Max CPU line. The number of sessions above the Max Engine line is the average number of sessions waiting for CPU/Engine.
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
- From the Profile Session window, right-click anywhere on the AAS graph and select Edit Engine Count or Edit CPU Count.
- In the EngineCount dialog that appears select Useacustomvalue, enter a new value, and then click OK.
The AAS or Load Chart MaxCPU or MaxEngine line is updated immediately to reflect the change.
The Load Chart is designed as a high level entry point to profile session results. Subsequently, you can use the Top Activity and Profiling Details views to examine more detailed information on waiting and executing sessions over the length of the session. Alternatively, you can select one or more bars on the graph to populate the Top Activity section (and subsequently, the Details View) with information on a specific subset of the graph.
The Load Chart displays the distribution of waiting and executing sessions over the length of a profiling session.
- 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.
...
On CPU, System I/O, User I/O, Cluster, Application, Configuration, Commit, Other
...
CPU, Lock, Memory, Buffer, I/O, Other
...
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 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 ExecutionActivity (DB 2 Specific) tab provides details about the statements and procedures that ran. This is DB 2 specific. For more information, see Top Execution Activity Tab (DB2 Specific).
- 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 (Oracle) displays information about blocking sessions. For more information, see Top Blockers Tab (Oracle Specific).
- The top ObjectI/O tab (Oracle-Specific) tab does not appear in the Top Activity Section unless the data source being profiled is an Oracle platform. This tab displays information about the I/O profiled by the execution process. For more information, see Top Object I/O Tab (Oracle-Specific).
- The top Procedures tab (Oracle, SQL Server, and Sybase-specific) displays information about procedures observed during profiling. For more information, see Top Procedures Tab (Oracle, SQL Server, and Sybase Specific).
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. |
...
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. |
...
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, DB2The 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 IDOracleThe ID value of the SQL statement. This statistic only appears on Oracle data sources.
Child NumberOracleThe child number in the database. This statistic only appears on Oracle data sources.
Parsing User IDOracleThe ID of the user who parsed the statement. This statist only appears on Oracle data sources.
Plan Hash ValueOracleThe 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.
NOTE: 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:
...
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.
...
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
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 IDThe 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:
...
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.
...
Owner (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.
...
yesyesyesyes
SessionsShows which sessions executed this statement.yesyesyesyes
BlockersShows 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).
yes
Children
Details
Lists all copies of the cursor or SQL query, if Oracle has cached multiple copies of the same statement.
yes
Object I/OIf the SQL query has done physical I/O, then these are the objects, such as tables, and indexes that were read to satisfy the query. Temporary objects with not have values in Object and Type columns.
...
Shows bind variable information for SQL captured during the Profiling session.
...
yesyesyesyes yes
Procedures Shows which procedures contain the selected event.yesyes
Raw DataRaw data that was sampled from the database, specifically the following:
• Sample time
• SID
• Serial #
• User name
• Program
• Sql ID
• P1
• P2
• P3
AnalysisDisplays for "buffer busy waits" and "cache buffer chains latch" waits. The analysis shows data and documentation to assist in solving these bottlenecks.
...
| Wiki Markup |
|---|
yesyesyes
\\
\\
BlockersShows 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 details, see [Top Blockers Tab (Oracle Specific)|WORK WITH SESSION RESULTS#_TOP_BLOCKERS_TAB].
yes
\\
SQLShows which SQL statements this session ran.yesyesyesyes
\\
\\
EventsShows which events this session waited on.yesyesyesyes
\\
\\
Procedures Shows which procedures ran the selected session.yesyes
\\
\\
<span style="color: #444087">NOTE:</span> When right-clicking on a SQL statement in the Top Activity Section in Profiling, if the SQL statement is run by a different user than the user who is running DBO, than the User Mismatch dialog appears, with an example of the following message: "This query was executed by \[SOE\] and you are currently connected as \[system\]. We recommend you reconnect as \[SOE\] to tune the SQL. Would you like to continue anyway?" This message indicates that the statement is being tuned by a user other than the user who originally ran the query, and tables may be missing based on the different schemas. Click OK to run the query, or click Cancel and run tuning under the original user.
\\
\\
*Blocking* *Session* *Selected*
When a *Blocking* *Session* is selected, the following Profiling Detail tabs are available.
\\
Tab NameDescriptionSupported Platform
\\
Oracle SybaseDB2 SQL Server
\\
Blocked
Sessions
\\
Session
Details |
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.
...
| Children Display |
|---|
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. |
...