Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




What Constitutes a Significant SQL Statement?
Oracle Tips by Burleson

There is some debate about which characteristic of a SQL statement makes it the best candidate for tuning. Some people always tune statements according to their values for executions, while others always tune SQL in  order of disk_reads, tuning the statements with the highest disk_reads first. As a review, we can extract SQL statements from the library cache or the stats$sql_summary table, based on the following criteria:

  • Executions  The more frequently executed SQL statements should be tuned first, since they will have the greatest impact on overall performance.

  • disk_reads  High disk reads indicates a query that is causing lots of disk I/O. While most SQL with high disk_reads consists of reports, online summaries and aggregations may have high disk_reads and should be carefully examined.

  • rows_processed  Queries that process a large number of rows will have high I/O and may also have an impact on the TEMP tablespace if sorting occurs. Queries with high values for rows_processed may be reports or decision support queries that are not online transactions and may benefit from using the all_rows optimizer goal.

  • buffer_gets  High buffer gets may indicate a resource-intensive query.

  • memory_kb  This metric shows the memory used for each SQL statement. High memory_kb values are most often associated with large sorting operations.

  • sorts  Sorts can be a huge slowdown, especially if the sorts are being done on disk in the TEMP tablespace.

As you now know, you can get lists of your SQL statements from the v$sqlarea view or stats$sql_summary table in descending order of any of these variables. You can also use third-party tools such as the Q Diagnostic Center by Precise Software to quickly view SQL statements according to these characteristics.

Figure 2: Displaying SQL by rows_processed in the Q Diagnostic Center

Of course, you do not need to buy expensive tools to perform this task. Let’s take a quick look at some common methods for manually identifying high-impact SQL statements.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter