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.
Note: The Top SQL grid, when opened from the I/O drilldown, shows the top 100 SQL statements ordered by I/O. The page is filtered to show the details of I/O-related SQL statements only.
To open the SQL page
-
Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click I/O | SQL.
Why is the page empty the first time I open it? For more information, see SQL Criteria Wizard.
Actions on the Top SQL Grid
| Action | Description |
|---|---|
 |
Click Refresh to update the list of top SQL statements. Note: The data retrieved is NOT updated automatically. |
| Filtered view |
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 Drilldown | SQL Page is filtered to show the Top 100 I/O-related SQL statements. |
| Click (Highlight) a SQL Statement |
Sub Pages: |
| Double click a SQL Statement |
Sub Pages: |
| Right click in the Top SQL grid 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 |
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. | ||||
|
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. | ||||
|
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 |
(Oracle 11g Release 1 and earlier) This is the number of blocks read from the physical disk by the SQL statement. (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. | ||||
|
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. | ||||
| Flash cache reads | |||||
|
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. |