Problem solve Get help with specific problems with your technologies, process and projects.

Oracle SQL and index internals: Tune the code/database for better performance

This tip, excerpted from "Oracle SQL and index internals" by Kimberly Floss, identifies options for tuning specialists.

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. Check back to the main series page for previous and upcoming installments.


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 info@rampant.cc.
*************************************************

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 

Conclusion

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 last published in June 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close