The following is the second 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.
Identify the resource-intensive code
After identifying the top resource-hogging sessions in a database, you can then turn your attention to the code they (and others) are executing that is likely causing system bottlenecks. As with Top Session monitors, many decent database monitors have a "Top SQL" feature that can help you ferret out bad SQL code. If you don't have access to such tools, a script like the one shown in Exhibit 2 can be used.
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 firstname.lastname@example.org
SELECT SQL_TEXT , USERNAME , DISK_READS_PER_EXEC, BUFFER_GETS , DISK_READS, PARSE_CALLS , SORTS , EXECUTIONS , ROWS_PROCESSED , HIT_RATIO, FIRST_LOAD_TIME , SHARABLE_MEM , PERSISTENT_MEM , RUNTIME_MEM, CPU_TIME, ELAPSED_TIME, ADDRESS, HASH_VALUE FROM (SELECT SQL_TEXT , B.USERNAME , ROUND((A.DISK_READS/DECODE(A.EXECUTIONS,0,1, A.EXECUTIONS)),2) DISK_READS_PER_EXEC, A.DISK_READS , A.BUFFER_GETS , A.PARSE_CALLS , A.SORTS , A.EXECUTIONS , A.ROWS_PROCESSED , 100 - ROUND(100 * A.DISK_READS/GREATEST(A.BUFFER_GETS,1),2) HIT_RATIO, A.FIRST_LOAD_TIME , SHARABLE_MEM , PERSISTENT_MEM , RUNTIME_MEM, CPU_TIME, ELAPSED_TIME, ADDRESS, HASH_VALUE FROM SYS.V_$SQLAREA A, SYS.ALL_USERS B WHERE A.PARSING_USER_ID=B.USER_ID AND B.USERNAME NOT IN ('SYS','SYSTEM') ORDER BY 3 DESC) WHERE ROWNUM < 21
Exhibit 2. Identifying the resource-intensive code
The code in Exhibit 2 will pull the top 20 SQL statements as ranked by disk reads per execution. You can change the ROWNUM filter at the end to show more or all SQL that has executed in a database. You can also add WHERE predicates that just show the SQL for one or more of the top sessions that you previously identified. Note that in 9i, Oracle has added the CPU_TIME and ELAPSED_TIME columns, which provide more data that can be used to determine the overall efficiency of an SQL statement.
The new 9i v$sql_plan view can also help with identification of problem SQL. For example, a DBA may want to know how many total SQL statements are causing Cartesian joins on a system. The following query can answer that:
select count(distinct hash_value) carteisan_ statements, count(*) total_cartesian_joins from sys.v_$sql_plan where options = 'CARTESIAN' and operation like '%JOIN%'
Output from this query might resemble the following (note that it is possible for a single SQL statement to contain more than one Cartesian join):
CARTESIAN_STATEMENTS TOTAL_CARTESIAN_JOINS ----------------------- --------------------- 3 3
A DBA can then view the actual SQL statements containing the Cartesian joins, along with their performance metrics by using a query like the following:
select * from sys.v_$sql where hash_value in (select hash_value from sys.v_$sql_plan where options = 'CARTESIAN' AND operation LIKE '%JOIN%' ) order by hash_value
Another area of interest for DBAs is table scan activity. Most DBAs don't worry about small table scans because Oracle can many times access small tables more efficiency through a full scan than through index access. Large table scans, however, are another matter. Most DBAs prefer to avoid those where possible through smart index placement or intelligent partitioning. Using the v$sql_plan view, a DBA can quickly identify any SQL statement that contains one or more large table scans. The following query shows any SQL statement containing a large table scan (defined as a table over 1 MB), along with a count of how many large scans it causes for each execution, the total number of times the statement has been executed, and then the sum total of all scans it has caused on the system:
SELECT sql_text, total_large_scans, executions, executions * total_large_scans sum_large_scans FROM (SELECT sql_text, count(*) total_large_scans, executions 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 c.hash_value = a.hash_value AND b.bytes / 1024 > 1024 group by sql_text, executions) order by 4 desc
This query produces very interesting output. As a DBA, should you worry more about an SQL statement that causes only one large table scan, but has been executed 1,000 times, or should you care more about an SQL statement that has ten large scans in it but has only been executed a handful of times? Each DBA will likely have an opinion on this, but regardless, you can see how such a query can assist in identifying SQL statements that have the potential to cause system slowdowns.
Oracle 9.2 has introduced another new performance view -- v$sql_plan_statistics -- that can be used to get even more statistical data regarding the execution of SQL statements. This view can tell you how many buffer gets, disk reads, etc., each step in an execution plan caused, and even goes so far as to list the cumulative and last executed counts of all metrics. DBAs can reference this view to get a great perspective of which step in an SQL execution plan is really responsible for most of the resource consumption. Note that to enable the collection of data for this view, you must set the Oracle configuration parameter statistics_level to ALL.
About the authors
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.