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.
New performance views to identify problem SQL
One short path to identifying performance problems in an Oracle database is the following:
- Find the sessions responsible for hogging the most resources (I/O, CPU, etc.)
- Identify the code these sessions are running.
- Peel away the bad code these sessions have executed from the good/acceptable code.
- Highlight the worst SQL and then work to tune it for better performance.
This process has been made much easier in Oracle9i, especially with respect to identifying problem SQL that gets run in a production database. Let's work our way through these four steps and see how several new performance views introduced in Oracle9i can really assist in the process.
Find the problem sessions
Even if you don't have a database monitor that offers a "top sessions" view, you can easily pinpoint the sessions that are giving your database grief (see Exhibit 1). Keep in mind that different database professionals have their own ideas about what constitutes a "top session." Some feel that the sum total of physical I/O alone tells the story, while others look at CPU, and still others use a combination of physical and logical I/O. Whatever your preference, you can use the script in Exhibit 1 to quickly bubble to your top-twenty sessions in an Oracle9i database. Note that the initial sort is on physical I/O but you can change that to be any other column you'd like.
Exhibit 1. Finding the pro...
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
blem sessions
You can also modify the above query to exclude Oracle background processes, the SYS and SYSTEM user, etc. The end result should be a current list of your top offending sessions in the database as ranked by various performance metrics, which is the normal way to rank problem user accounts.
Some DBAs feel that this method, while useful, lacks depth. Specifically, because DBAs know that a user's resource consumption is almost always tied to inefficient SQL, they would like to cut to the chase and find the problem sessions in a database that have, for example, caused most of the large table scans on the system or have submitted queries containing Cartesian joins. Such a thing was difficult to determine in earlier versions of Oracle but, fortunately, 9i provides a new performance view that can be used to derive such data. The V$SQL_PLAN view contains execution plan data for all submitted SQL statements. Such a view provides a wealth of information regarding the performance and efficiency of SQL statements and the sessions that submitted them.
For example, if a DBA wants to know what sessions have parsed SQL statements that caused large table scans (with "large" in our example being anything over 1 MB) on a system, along with the total number of large scans by session, he could submit the following query:
Output from the above query might look something like the following:
In like fashion, if a DBA wants to uncover what sessions have parsed SQL statements containing Cartesian joins, along with the number of SQL statements that contain such joins, he could run the following query:
A result set from this query could look similar to the following:
As you can see, the v$sql_plan view adds more meat to the process of identifying problem sessions in a database. When combined with the standard performance metrics query, DBAs can really begin to pinpoint the sessions that are wreaking havoc inside their critical systems.
Go to the main series page for previous and upcoming installments.
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.
