Top SQL View (Oracle Performance Analyst)
Go Up to Oracle Performance Analyst Statistics
One or two bad queries can cause a lot of trouble for the remaining sessions in a database, and so can one or two bad queries. It is important to find them before they get into a production system, but sometimes a few sneak through.
By applying custom filters and performance-related thresholds, the Top SQL view locates inefficient SQL. By applying filters to certain I/O and statistical counters, you hope to isolate queries that far exceed their nearest competitors in the same area (like disk reads). When you find them, you should reduce the number of sorts a query performs. Or, for a query that returns only a few records, you should try to minimize the number of rows a query processes.
When you begin to look for inefficient SQL in a database, there are two primary questions you need to answer:
- What has been the worst SQL that’s historically been run in my database?
- What is the worst SQL that’s running right now in my database?
When troubleshooting a slow system, you should be on the lookout for any query that shows an execution count that is significantly larger than any other query on the system. It may be that the query is in an inefficient Transact SQL loop, or other problematic programming construct. Only by bringing the query to the attention of the application developers will you know if the query is being mishandled from a programming standpoint.
The Top SQL view displays requested SQLOracle datasources. The following tabs are available on the Top SQL page:
Contents
Summary
The Summary tab of the Top SQL page includes the following sections:
Top SQL Statements
A lot of a database’s overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured database down in terms of end user response times.
Before you can identify problem SQL in your database, you have to ask the question of what ‘bad SQL’ is. What criteria do you use when you begin the hunt for problem SQL in your critical systems? Understand that even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every Oracle professional’s satisfaction. The table below describes some general criteria you can use when evaluating the output from various database monitors or personal diagnostic scripts:
| Criteria | Description |
|---|---|
|
Overall Response (Elapsed) Time |
This is how much time the query took to parse, execute, and fetch the data needed to satisfy the query. It should not include the network time needed to make the round trip from the requesting client workstation to the database server. This statistic is available in Oracle9i and later. |
|
CPU Time |
This is how much CPU time the query took to parse, execute, and fetch the data needed to satisfy the query. |
|
Physical I/O |
Often used as the major statistic in terms of identifying good vs. bad SQL, this is a measure of how many disk reads the query caused to satisfy the user’s request. While you certainly want to control disk I/O where possible, it is important that you not focus solely on physical I/O as the single benchmark of inefficient SQL. Make no mistake, disk access is slower than memory access and also consumes processing time making the physical to logical transition, but you need to look at the entire I/O picture of a SQL statement, which includes looking at a statements’ logical I/O as well. |
|
Logical I/O |
This number of memory reads the query took to satisfy the user’s request. The goal of tuning I/O for a query should be to examine both logical and physical I/O, and use appropriate mechanisms to keep both to a minimum. |
|
Repetition |
This is a measure of how often the query has been executed. A problem in this area is not as easy to spot as the others unless you know your application well. A query that takes a fraction of a second to execute may still be causing a headache on your system if it is executed erroneously (for example, a query that executes in a runaway PL/SQL loop) over and over. |
Top SQL Details
A lot of a database’s overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured database down in terms of end user response times.
Before you can identify problem SQL in your database, you have to ask the question of what ‘bad SQL’ is. What criteria do you use when you begin the hunt for problem SQL in your critical systems? Even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every Oracle professional’s satisfaction. The Top SQL Details section displays the top SQL statements across various performance categories. The table below describes the information available in this section:
| Information | Description |
|---|---|
|
Top For |
Indicates the performance category for the SQL statement. |
|
Usage |
Displays the percent usage for this SQL statement in this performance category. |
|
Hash value |
A unique identifier for the SQL statement assigned by Oracle. |
|
SQL text |
A snippet of the actual SQL statement. |
|
User name |
The username of the account that originally parsed the SQL. |
|
Disk reads |
The number of disk reads for the statement. |
|
Buffer gets |
The number of logical reads for the statement. |
|
Parse calls |
The number of parse calls for the statement. |
|
Sorts |
The number of sorts caused by the statement. |
|
Executions |
The number of times that statement has been executed. |
|
Rows processed |
The number of rows processed by the statement. |
|
First load time |
The date/time of when the statement was first loaded into the shared pool. |
|
Sharable memory |
The amount of sharable memory used by the statement. |
|
Persistent memory |
The amount of persistent memory used by the statement. |
|
Runtime memory |
The amount of runtime memory used by the statement. |
|
Address |
The address of the SQL statement. |
|
Location |
Oracle Performance Analyst Statistics > Top SQL View (DBArtisan - Oracle Performance Analyst) > Summary |
Criteria
The Top SQL view displays all SQL running or executed in the current datasource. You can use the lists and boxes to reorder the display according to your specific needs. The Criteria tab of the Top SQL page includes the following sections that you can mix and match to give you the information you want to find.
- Demographic Criteria
- Statistical Criteria
- Sort Criteria
SQL Details
The SQL Details tab displays the following sections:
Top SQL Results
A lot of a database’s overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured database down in terms of end user response times.
Before you can identify problem SQL in your database, you have to ask the question of what ‘bad SQL’ is. What criteria do you use when you begin the hunt for problem SQL in your critical systems? Understand that even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every Oracle professional’s satisfaction. The Top SQL results grid displays the top SQL statements across various performance categories. The table below describes the information available in this section
| Information | Description |
|---|---|
|
SQL Text |
A snippet of the full SQL statement. Clicking the statement will cause the full statement to be presented in the Selected SQL Text grid. |
|
User name |
The username of the account that originally parsed the SQL. |
|
Disk reads per execution |
The average number of disk reads caused by the statement each time it is executed. |
|
Buffer gets per execution |
The average number of logical reads caused by the statement each time it is executed. |
|
Buffer gets |
The number of logical reads for the statement. |
|
Disk reads |
The number of disk reads for the statement. |
|
Parse calls |
The number of parse calls for the statement. |
|
Sorts |
The number of sorts caused by the statement. |
|
Executions |
The number of times that statement has been executed. |
|
Rows processed |
The number of rows processed by the statement. |
|
Hit Ratio |
The cache hit ratio for the statement. |
|
First load time |
The date/time of when the statement was first loaded into the shared pool. |
|
Sharable memory |
The amount of sharable memory used by the statement. |
|
Persistent memory |
The amount of persistent memory used by the statement. |
|
Runtime memory |
The amount of runtime memory used by the statement. |
|
Elapsed Time per Exec |
For Oracle9i and later, this is the average elapsed response time for the statement. |
|
CPU Time per Exe |
For Oracle9i and later, this is the average CPU time for the statement. |
|
CPU Time |
For Oracle9i and later, this is the total CPU time for the statement. |
|
Elapsed Time |
For Oracle9i and later, this is the total elapsed time for the statement over all executions. |
|
Hash value |
A unique identifier for the SQL statement assigned by Oracle. |
|
Address |
The address of the SQL statement. |
|
Location |
Oracle Performance Analyst Statistics > Top SQL View (DBArtisan - Oracle Performance Analyst) > SQL Details |
Selected SQL Text
The Selected SQL Text window allows you to select any of the Top SQL result rows and get the complete SQL text. From here you can open an Explain Plan or Tune the SQL.
|
Location |
Oracle Performance Analyst Statistics > Top SQL View (DBArtisan - Oracle Performance Analyst) > SQL Details |
Recent History (Oracle 10g only)
The Recent History tab of the Top SQL view displays:
SQL Activity (last 24 hours)
The SQL Activity graph shows you the resource usage of SQL statements over the last 24-hour period. You can see cumulative CPU, elapsed time, and wait time for all SQL statements that were executed during the last 24 hours.
You can also select a specific time period in the graph by drawing a box around part of the graph with your mouse. The graph will change to match your selection.
|
Location |
Oracle Performance Analyst Statistics > Top SQL View (DBArtisan - Oracle Performance Analyst) > Recent History (Oracle 10g only) |
SQL Details
The SQL Details section of the Recent History view displays the SQL statements that correspond to the time period shown in the SQL Activity graph. The following columns are displayed:
- SQL ID: The unique identifier for the SQL statement
- CPU Time: The total number of CPU seconds used by the statement during the specified time period
- CPU %: The percentage of CPU used by the statement during the specified time period
- Elapsed Time: The total elapsed time for the statement (in seconds) during the specified time period
- Elapsed %: The percentage of elapsed time for the statement during the specified time period
- Wait Time: The total wait time for the statement (in seconds) during the specified time period
- Wait %: The percentage of wait time for the statement during the specified time period
- SQL Time: The total SQL execution time for the statement (in seconds) during the specified time period
- PL/SQL Time: The total PL/SQL execution for the statement (in seconds) during the specified time period
- Java Time: The total java execution time for the statement (in seconds) during the specified time period
- Executions: The total number of executions for the statement (in seconds) during the specified time period
- Disk Reads: The total number of disk reads for the statement (in seconds) during the specified time period
- Buffer Gets: The total number of buffer gets for the statement (in seconds) during the specified time period
- Sorts: The total number of sorts for the statement (in seconds) during the specified time period
- Parse Calls: The total number of parse calls for the statement (in seconds) during the specified time period
|
Location |
Oracle Performance Analyst Statistics > Top SQL View (DBArtisan - Oracle Performance Analyst) > Recent History (Oracle 10g only) |
|
Metrics |
Potential SQL tuning targets will be those statements that have unusually large amounts of CPU time, elapsed time, wait time, or number of executions. |