One short path to identifying performance problems in an Oracle
database includes the following steps:
This process has been made much easier in Oracle9i and Oracle10g,
especially with respect to identifying problem SQL that gets run in
a production database. The following sections work through these
four steps and see how several performance views can assist in this
process.
Even if there is no database monitor that offers a top sessions
view, it is easy to pinpoint the sessions that are giving the
database grief. 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.
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
SEE CODE DEPOT FOR FULL SCRIPTS
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
SEE CODE DEPOT FOR FULL SCRIPTS
The above query can also be modified to exclude Oracle background
processes, the SYS and SYSTEM user, etc. The end result should be a
current list of top offending sessions in the database as ranked by
various performance metrics, which is the normal way to rank problem
user accounts. Figure 15.15 shows a sample output of this query:
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.
