Top SQL Page
Use Top SQL to view the SQL statements that are consuming the most system resources for the current connection. These statements can affect the overall performance of the database, and may need to be tuned to use system resources more efficiently.
To open the Top SQL page
-
Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click SQL & Application Workload | Top SQL.
Why is the page empty the first time I open it? SQL Criteria Wizard
Actions on the Top SQL Grid
| Action | Description |
|---|---|
| Select (Highlight) a SQL Statement |
View detailed information on the SQL Statement. See: |
| Double click a SQL Statement |
View alternative (full screen) detailed information on the SQL Statement. See: |
| Right click a SQL Statement and select Predictive Diagnostics |
Will the selected SQL statement scale adequately as data volumes and SQL execution rates increase? View Spotlight's Predictive Diagnostics analysis. SQL Performance |
Filtering the view of the Top SQL Grid
The Top SQL page populated via the SQL Criteria Wizard shows the SQL statements that are consuming the most system resources. These SQL statements can affect the overall performance of the database, and may need to be tuned to use system resources more efficiently.
If you open the Top SQL page by right click and select SQL Details from another page (from the Array Fetch Page, Parse Activity Page, Sort Activity Page) the Top SQL page is filtered to show the selected SQL statements.
Note: The I/O | SQL Page is filtered to show the Top 100 I/O-related SQL statements.
Top SQL (Grid)
Note: Not all columns are visible by default. Use the Column Organizer to view hidden columns. Show, Hide & Order Columns
| Column | Description | ||||
|---|---|---|---|---|---|
| ID |
The SQL identifier for the specified SQL statement in the library cache. The form that this ID takes depends on the version of the Oracle system under investigation.
The SQL ID allows you to compare SQL information from multiple sources. | ||||
|
Execs |
The number of times the SQL statement has been executed. | ||||
|
Rows |
The total number of rows retrieved by the SQL statement. | ||||
|
Buffer gets |
The number of logical reads (buffer gets) retrieved by this SQL statement. | ||||
|
% of total |
The number of logical reads recorded for this SQL statement as a percentage of logical reads recorded for all SQL statements. | ||||
|
Disk reads |
In Oracle 11g Release 1 and earlier, this is the number of blocks read from the physical disk by the SQL statement. In Oracle 11g Release 2 and later, this is the number of read requests issued to the physical disk by the SQL statement. | ||||
|
Direct writes |
The number of direct path writes performed by the SQL. | ||||
|
Elapsed time (ms) |
The total amount of elapsed time taken to execute the SQL statement. | ||||
|
CPU time (ms) |
Within the elapsed time, the amount of time spent actively executing the SQL statement. | ||||
|
Gets/exe |
The average number of logical reads per execution. | ||||
|
Disk/exe |
The average number of disk reads per execution. | ||||
|
Rows/exe |
The average number of rows retrieved per execution. | ||||
|
User |
The name of the user who first parsed this SQL statement. | ||||
|
IO time (ms) |
The time spent by the SQL statement waiting for events in the User I/O class. Waits in this class include all waits for I/O operations started by user-generated SQL statements, including single-block and multiblock reads from datafiles, direct I/O reads and writes, redo log sync operations, and file I/O. | ||||
|
Optimizer cost |
The cost of executing the SQL statement (via the cost-based optimizer). | ||||
|
SQL statement |
The text of the SQL statement. Some of the SQL statement may be hidden so that the important sections are visible. For example, Spotlight on Oracle may hide the list of columns in the SELECT list so that tables in the FROM clause are visible. If this is the case, use the scroll bars to display the rest of the statement. | ||||
|
App time (ms) |
The time spent by the SQL statement waiting for events in the Application class. Waits in this class include waits for user locks such as row level locks. | ||||
|
Buf gets/row |
The number of logical reads (buffer gets) per row. | ||||
|
Cluster time (ms) |
The time spent by the SQL statement waiting for events in the Cluster class. Waits in this class are those related to cache coherency waits for Oracle RAC clusters — for example, waits for blocks requested from the Global Cache Service). | ||||
|
Concurrency time (ms) |
The time spent by the SQL statement waiting for events in the Concurrency class. Waits in this class include waits for latches, pins, free buffers and Oracle internal locks. | ||||
|
First Parsed |
The date and time this statement was first parsed. | ||||
|
Java time (ms) |
The time spent by the SQL statement executing Java code. This may occur when the SQL statement includes calls to PL/SQL wrappers that in turn invoke Java stored procedures. | ||||
|
PLSQL time (ms) |
The time spent by the SQL statement executing PL/SQL code. This occurs when the SQL is in fact a PL/SQL block, or when the SQL statement invokes a PL/SQL function. |