Query Tuning for Developer, Beginner DBAs

If you’re a DBA, improving query performance is probably on your mind. Read this excerpt from Oracle Database 11g Release 2 Performance Tuning Tips & Techniques by Richard Niemiec for his take.

This chapter focuses on specific queries that you may encounter and some general information for tuning those specific queries, but it has also been updated to include some basic information on Oracle’s 11g Automatic SQL Tuning and some queries to access Oracle’s 11g Automatic Workload Repository (AWR). Examples of query tuning are spread throughout this book as well as instructions on making them more effective in terms of your system’s architecture. This chapter centers on some of the most common queries that can be tuned on most systems. A query can display several variations in behavior, depending on system architecture, the data distribution in the tables, what tool or application is accessing the database, the specific version of Oracle Database, and a variety of other exceptions to the rules. Your results will vary; use your own testing to come up with the most favorable performance. The goal in this chapter is to show you many of the issues to watch for and how to fix them. 

For more on Oracle performance tuning

Check out the Oracle Database performance tuning guide

Review 26 performance tuning  questions  and answers to them

What’s the first step in a performance tuning project?

This chapter uses strictly cost-based examples for timings (except where noted). No other queries were performed at the time of the tests performed for this chapter. Many hints are also used throughout this chapter. For a detailed look at hints and the syntax and structure of hints, please refer to Chapter 7. Multiple table and complex queries are the focus of the next chapter and are not covered here. 

Please note that this is not an all-inclusive chapter. Many other queries are covered throughout the book, which need to be investigated when trying to increase performance for a given query. Some of the most dramatic include using the parallel features of Oracle Database (Chapter 11), using partitioned tables and indexes (Chapter 2), and using PL/SQL to improve performance (Chapter 10). Note the benefits of using EXPLAIN and TRACE for queries (Chapter 6). Oracle Database 11g provides the Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM). The Enterprise Manager views of these new features are shown in Chapter 5. Tips covered in this chapter include the following:

  • What queries do I tune? Querying the V$SQLAREA and V$SQL views
  • Some useful new 11g views for locating resource-intensive sessions and queries
  • When should I use an index?
  • What if I forget the index?
  • Creating and checking an index
  • What if I create a bad index?
  • Exercising caution when dropping an index
  • Using invisible indexes
  • Function based indexes and virtual columns
  • Increasing performance by indexing the SELECT and WHERE columns
  • Using the Fast Full Scan feature to guarantee success
  • Making queries "magically" faster
  • Caching a table into memory
  • Using the new 11g Result Cache
  • Choosing between multiple indexes on a table (use the most selective)
  • Indexes that can get suppressed
  • Tuning OR Clauses
  • Using the EXISTS clause and the nested subquery
  • That table is a view!
  • SQL and the Grand Unified Theory
  • Automatic SQL Tuning and the SQL Tuning Advisor
  • Using the SQL Performance Analyzer (SPA)

What Queries Do I Tune? Querying V$SQLAREA and V$SQL Views

V$SQLAREA and V$SQL are great views that you can query to find the worst-performing SQL statements that need to be optimized. The value in the DISK_READS column signifies the volume of disk reads that are being performed on the system. 

This excerpt from Oracle Database 11g Release 2 Performance Tuning Tips & Techniques - Best Practices for Optimizing Database Performance by Richard Niemiec is reprinted here with permission from Oracle Press, copyright 2012. Download a PDF of the full chapter.

This, combined with the executions (DISK_READS/EXECUTIONS), return the SQL statements that have the most disk hits per statement execution. Any statement that makes the top of this list is most likely a problem query that needs to be tuned. The AWR Report or Statspack Report also lists the resource-intensive queries; see Chapter 14 for detailed information.

Selecting from the V$SQLAREA View to Find the Worst Queries

The following query can be used to find the worst queries in your database. This query alone is worth the price of this book if you’ve not heard of V$SQLAREA yet. 

To find the worst queries:

select b.username username, a.disk_reads reads,
a.executions exec, a.disk_reads /decode
(a.executions, 0, 1,a.executions) rds_exec_ratio,
a.sql_text Statement
from V$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;
-------- ------- ----- --------------- ---------------------
ADHOC1 7281934 1 7281934 select custno, ordno
from cust, orders

ADHOC5 4230044 4 1057511 select ordno
from orders where trunc(ordno) = 721305
ADHOC1 801716 2 400858 select custno,
ordno from cust where substr(custno,1,6) = '314159'

The DISK_READS column in the preceding statement can be replaced with the BUFFER_GETS column to provide information on SQL statements requiring the largest amount of memory.

Now consider the output in a second example where there is a count of a billion-row table (EMP3) and a count of what was originally a 130M row table (EMP2), where all of the rows in EMP2, except the first 15 rows inserted, were deleted. Note that Oracle counts all the way up to the high water mark (HWM) of EMP2 (it read over 800,000, 8K blocks even though all of the data was only in 1 block). This listing would have told you something is wrong with the query on EMP2 that needs to be addressed, given that it only has 15 rows in it (analyzing the table will not improve this).

-------- ------- ----- --------------- -------------------------
SCOTT 5875532 1 5875532 select count(*) from emp3
SCOTT 800065 1 800065 select count(*) from emp2

For this issue, if the EMP2 table was completely empty, you could simply truncate the table
to fix it. Since the table still has 15 rows, you have a few options; which option you choose
depends on your unique situation. I can

  • EXPORT/TRUNCATE/IMPORT; CREATE TABLE emp2b AS SELECT * FROM emp2 (CTAS) and then DROP and RENAME (I have to worry about indexes/related objects, etc.)
  • Do an “ALTER TABLE emp2 MOVE TABLESPACE new1” and rebuild the indexes.
  • If it has a primary key, use DBMS_REDEFINITION.CAN_REDEF_TABLE to verify that the table can be redefined online.

Please check the Oracle documentation for syntax/advantages/disadvantages and stipulations (not all are listed here) for each of these options, so you can apply the best option to your situation (each of these options have major downsides, including users not being able to access the table and related objects getting dropped depending on which you use, so be careful). Once I reorganize the table, the next count(*)only reads 1 block instead of 800,065 blocks (it was well worth fixing the problem). Note in the query, I change “emp2” to emP2” so I can find that cursor in the cache.

alter table emp2 move; -- You can specify a tablespace
select count(*)
from emP2;
select b.username username, a.disk_reads reads,
a.executions exec, a.disk_reads /decode
(a.executions, 0, 1,a.executions) rds_exec_ratio,
a.sql_text Statement

from V$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.sql_text like '%emP2%'
order by a.disk_reads desc;
-------- ------- ----- --------------- ---------------------
SCOTT 1 1 1 select count(*) from emP2

You can also shrink space in a table, index-organized table, index, partition, subpartition,
materialized view, or materialized view log. You do this using ALTER TABLE, ALTER INDEX,
ALTER MATERIALIZED VIEW, or ALTER MATERIALIZED VIEW LOG statement with the SHRINK SPACE clause. See the Oracle Administrators Guide for additional information. Lastly, if you want to use the “ALTER TABLE table MOVE TABLESPACE tablespace_name” command, consider using the same size tablespace (or smaller if appropriate) to move things “back and forth” so as not to waste space.


Query V$SQLAREA to find your problem queries that need to be tuned.



Richard Niemiec is chief executive officer of TUSC, an Inc. 500 full-service consulting and training company specializing in Oracle technology. Rich has been named by Oracle Corporation as an Oracle Certified Master--one of only six originally so recognized around the world. He is the former president of the Independent Oracle Users Group (IOUG) and president of the Midwest Oracle Users Group. Rich has been named the Top Speaker of the IOUG Conference five times and is a member of the Entrepreneurial Hall of Fame.

Dig Deeper on Oracle database performance problems and tuning