The following is the fourth part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization. Each tip is excerpted from the not-yet-released Rampant TechPress book, "Oracle SQL and index internals," by Kimberly Floss.
Tune the code/database for better performance
Once you have your problem queries in hand, you can then start the tuning process. The two broad steps involved are:
- Look for SQL rewrite possibilities
- Look for object-based solutions
For problem systems, step 1 above can consume a lot of a database professional's time. A chapter of this nature cannot possibly go into this vast subject as there are a plethora of techniques and SQL hints that can be used to turn a query that initially runs like molasses in winter into one that sprints as fast as a scalded dog.
Object-based solutions are another option for tuning specialists. These involve things like intelligent index creation, partitioning, and more. But to do this, you have to first find the objects that will benefit from such modification that, in turn, will enhance the overall runtime performance. Once again, the new 9i views can help. Continuing with the theme of finding large table scans on a system, the query listed in Exhibit 3 will identify the actual objects that are the target of such scans. It displays the table owner, table name, table type (standard, partitioned), table size in kilobytes (KB), number of SQL statements that cause a scan to be performed, number of total scans for the table each time the statement is executed, number of SQL executions to date, and then the total number of scans that the table has experienced (total single scans executions).
Copyright © 2003 by Rampant TechPress
This script is free for non-commercial purposes with no warranties. Use at your own risk.
To license this script for a commercial purpose, contact email@example.com.
SELECT table_owner, table_name, table_type, size_kb, statement_count, reference_count, executions, executions * reference_count total_scans FROM (SELECT a.object_owner table_owner, a.object_name table_name, b.segment_type table_type, b.bytes / 1024 size_kb, SUM(c.executions ) executions, COUNT( DISTINCT a.hash_value ) statement_count, COUNT( * ) reference_count FROM sys.v_$sql_plan a, sys.dba_segments b, sys.v_$sql c WHERE a.object_owner (+) = b.owner AND a.object_name (+) = b.segment_name AND b.segment_type IN ('TABLE', 'TABLE PARTITION') AND a.operation LIKE '%TABLE%' AND a.options = 'FULL' AND a.hash_value = c.hash_value AND b.bytes / 1024 > 1024 GROUP BY a.object_owner, a.object_name, a.operation, b.bytes / 1024, b.segment_type ORDER BY 4 DESC, 1, 2 )
Exhibit 3: Finding Large Table Scans
The query in Exhibit 3 will help determine what tables might benefit from better indexing or partitioning. However, a question that might come up is: Do these tables contain indexes and, if so, why don't the queries that are scanning the tables make use of them? While only examining the actual SQL statements can answer the second part of that question, the first part can be answered through the following query:
SELECT DISTINCT a.object_owner table_owner, a.object_name table_name, b.segment_type table_type, b.bytes / 1024 size_kb, d.index_name FROM sys.v_$sql_plan a, sys.dba_segments b, sys.dba_indexes d WHERE a.object_owner (+) = b.owner AND a.object_name (+) = b.segment_name AND b.segment_type IN ('TABLE', 'TABLE PARTITION') AND a.operation LIKE '%TABLE%' AND a.options = 'FULL' AND b.bytes / 1024 > 1024 AND b.segment_name = d.table_name AND b.owner = d.table_owner ORDER BY 1, 2
Such a query can create a mini "unused indexes" report that you can use to ensure that any large tables that are being scanned on your system have the proper indexing scheme.
One final thing that a DBA might want to know is how much physical I/O, etc., a large table scan causes on a system. A new 9.2 performance view -- v$segment_statistics -- can come to the rescue here. It displays I/O and some wait metrics that can give a DBA more insight into what Oracle is doing behind the scenes to access the object. A query such as the following can be used to uncover this information:
SELECT DISTINCT a.object_owner table_owner, a.object_name table_name, b.segment_type table_type, b.bytes / 1024 size_kb, c.tablespace_name, c.statistic_name, c.value FROM sys.v_$sql_plan a, sys.dba_segments b, sys.v_$segment_statistics c WHERE a.object_owner (+) = b.owner AND a.object_name (+) = b.segment_name AND b.segment_type IN ('TABLE', 'TABLE PARTITION') AND a.operation LIKE '%TABLE%' AND a.options = 'FULL' AND b.bytes / 1024 > 1024 AND b.owner = c.owner AND b.segment_name = c.object_name ORDER BY 1, 2
Following the path of finding the problem sessions on a database and then identifying and tuning their SQL can be one quick way to turn around a poorly performing database. And with Oracle9i, the process of performing in-depth SQL analysis has been made much easier, thanks to the introduction of new V$ performance views. DBAs who become familiar with these new views can greatly increase their SQL troubleshooting abilities and accelerate the process of making things right in their database.
About the author
Kimberly Floss is one of the most-respected Oracle database administrators in the U.S., and is president of the International Oracle Users Group (IOUG). With more than a decade of experience, Kimberly specializes in Oracle performance tuning and is a respected expert in SQL tuning techniques. She is an active member of the Chicago Oracle Users Group, and the Midwest Oracle Users Group, in addition to the IOUG. Kimberly Floss has over 15 years of experience in the information technology industry, with specific focus on relational database technology, including Oracle, DB2, Microsoft SQL Server and Sybase. She holds a bachelor's of science degree in computer information systems from Purdue University, specializing in systems analysis and design, and has an MBA with emphasis in management information systems from Loyola University.
This was first published in June 2004