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.
-- *************************************************
-- 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 * FROM
(SELECT b.SID SID,
decode (b.USERNAME,NULL,e.name,b.username) USER_NAME,
d.spid OS_ID,
b.machine MACHINE_NAME,
TO_CHAR(logon_time,'DD-MON-YY HH:MI:SS PM') LOGON_TIME,
(sum(DECODE(c.NAME,'physical reads',VALUE,0)) +
sum(DECODE(c.NAME,'physical writes',VALUE,0)) +
sum(DECODE(c.NAME,'physical writes direct',VALUE,0)) +
sum(DECODE(c.NAME,'physical writes direct (lob)',VALUE,0))+
sum(DECODE(c.NAME,'physical reads direct (lob)',VALUE,0)) +
sum(DECODE(c.NAME,'physical reads direct',VALUE,0)))
total_physical_io,
(sum(DECODE(c.NAME,'db block gets',VALUE,0)) +
sum(DECODE(c.NAME,'db block changes',VALUE,0)) +
sum(DECODE(c.NAME,'consistent changes',VALUE,0)) +
sum(DECODE(c.NAME,'consistent gets',VALUE,0)) )
total_logical_io,
(sum(DECODE(c.NAME,'session pga memory',VALUE,0))+
sum(DECODE(c.NAME,'session uga memory',VALUE,0)) )
total_memory_usage,
sum(DECODE(c.NAME,'parse count (total)',VALUE,0)) parses,
sum(DECODE(c.NAME,'CPU used by this session',VALUE,0))
total_cpu,
sum(DECODE(c.NAME,'parse time cpu',VALUE,0)) parse_cpu,
sum(DECODE(c.NAME,'recursive cpu usage',VALUE,0))
recursive_cpu,
sum(DECODE(c.NAME,'CPU used by this session',VALUE,0)) -
sum(DECODE(c.NAME,'parse time cpu',VALUE,0)) -
sum(DECODE(c.NAME,'recursive cpu usage',VALUE,0))
other_cpu,
sum(DECODE(c.NAME,'sorts (disk)',VALUE,0)) disk_sorts,
sum(DECODE(c.NAME,'sorts (memory)',VALUE,0)) memory_sorts,
sum(DECODE(c.NAME,'sorts (rows)',VALUE,0)) rows_sorted,
sum(DECODE(c.NAME,'user commits',VALUE,0)) commits,
sum(DECODE(c.NAME,'user rollbacks',VALUE,0)) rollbacks,
sum(DECODE(c.NAME,'execute count',VALUE,0)) executions
FROM sys.V_$SESSTAT a,
sys.V_$SESSION b,
sys.V_$STATNAME c,
sys.v_$process d,
sys.v_$bgprocess e
WHERE a.STATISTIC#=c.STATISTIC# and
b.SID=a.SID AND
d.addr = b.paddr and
e.paddr (+) = b.paddr and
c.NAME in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)',
'db block gets',
'db block changes',
'consistent changes',
'consistent gets',
'session pga memory',
'session uga memory',
'parse count (total)',
'CPU used by this session',
'parse time cpu',
'recursive cpu usage',
'sorts (disk)',
'sorts (memory)',
'sorts (rows)',
'user commits',
'user rollbacks',
'execute count'
)
GROUP BY b.SID,
d.spid,
decode (b.USERNAME,NULL,e.name,b.username),
b.machine,
TO_CHAR(logon_time,'DD-MON-YY HH:MI:SS PM')
order by 6 desc)
WHERE ROWNUM < 21
Exhibit 1. Finding the problem 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:
SELECT c.username username,
count(a.hash_value) scan_count
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.dba_users c,
sys.v_$sql 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 c.user_id = d.parsing_user_id
AND d.hash_value = a.hash_value
AND b.bytes / 1024 > 1024
group by c.username
order by 2 desc
Output from the above query might look something like the following:
USERNAME SCAN_COUNT
---------- ----------
SYSTEM 14
SYS 11
ERADMIN 6
ORA_MONITOR 3
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:
SELECT username,
COUNT(DISTINCT c.hash_value) NBR_STMTS
FROM sys.v_$sql a,
sys.dba_users b,
sys.v_$sql_plan c
WHERE a.parsing_user_id = b.user_id
AND options = 'CARTESIAN'
AND operation LIKE '%JOIN%'
AND a.hash_value = c.hash_value
GROUP BY username
ORDER BY 2 DESC
A result set from this query could look similar to the following:
USERNAME NBR_STMTS
--------- ---------
SYS 2
SYSMAN 2
ORA_MONITOR 1
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.
