| |
 |
|
Oracle
Metric Total Parse Count Per Sec
Oracle Tips by Burleson Consulting
|
The
Oracle
Documentation notes:
The
Total Parse Count Per Sec
Oracle
metric number reflects the total number of
parses per second, both hard and soft. A hard parse occurs when a SQL
statement has to be loaded into the shared pool. In this case, the
Oracle Server has to allocate memory in the shared pool and parse the
statement. A soft parse is recorded when the Oracle Server checks the
shared pool for a SQL statement and finds a version of the statement
that it can reuse.
Each time a particular SQL cursor is parsed, this
count will increase by one. There are certain operations which will
cause a SQL cursor to be parsed. Parsing a SQL statement breaks it
down into atomic steps which the optimizer will evaluate when
generating an execution plan for the cursor.
This test checks the number of parse calls per
second. If the value is greater than or equal to the threshold values
specified by the threshold arguments, and the number of occurrences
exceeds the value specified in the "Number of Occurrences" parameter,
then a warning or critical alert is generated.
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 E "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 E "physical reads" direct
(lob)',value,0)) +
sum(decode(c.name,'physical reads E "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 E "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 E "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 E "physical reads" ',
'physical
writes',
'physical
writes direct',
'physical
reads E "physical reads" direct',
'physical
writes direct (lob)',
'physical
reads E "physical reads" direct (lob)',
'db block
gets',
'db block
changes',
'consistent changes',
'consistent gets E "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
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.
|
