Chat now with support
Chat with Support

Spotlight on Oracle 10.7 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Spotlight on MySQL Troubleshooting: Connection Problems

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

  1. Select the Spotlight on Oracle connection in the Spotlight Browser.

  2. 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 DrilldownSQL 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.

Version ID is formed from

(Oracle 10g and later)

v$sql.sql_id || _ || v$sql.child_number

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.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating