Re: Tools/monitoring question
From: Stéphane Faroult <sfaroult_at_roughsea.com>
Date: Wed, 17 Jul 2013 19:01:25 +0200
Message-ID: <51E6CDE5.2070104_at_roughsea.com>
Chris,
Do you mean something like this? (this is the "all public", Statspack-based version, it's easy to derive a version that uses ASH if you are licensed for it). I wrote this several years ago and haven't used it much of late, but I assume that it must still be OK.
Date: Wed, 17 Jul 2013 19:01:25 +0200
Message-ID: <51E6CDE5.2070104_at_roughsea.com>
Chris,
Do you mean something like this? (this is the "all public", Statspack-based version, it's easy to derive a version that uses ASH if you are licensed for it). I wrote this several years ago and haven't used it much of late, but I assume that it must still be OK.
-
--
-- STATSPACK version
--
--
===========================================================================
store set myenv replace
set termout off
set pagesize 0
set feedback off
set recsep off
set linesize 1000
set trimspool on
col fname noprint new_value spool_name
select d.name ||
case i.instance_name
when d.name then '_'
else '_' || i.instance_name || '_'
end ||
to_char(sysdate, 'YYYYMMDDHH24MI') || '.htm' fname
from v$database d,
(select instance_name
from v$instance
where instance_number = sys_context('USERENV', 'INSTANCE')) i
/
spool &spool_name
clear col
select '<html>' || chr(10) ||
'<head>' || chr(10) ||
'<title>' || db_name || '</title>' || chr(10) ||
'<style type="text/css">' || chr(10) ||
'<!--' || chr(10) ||
'.slice {color: black;' ||
'font-family: courier;' ||
'font-size: 60%;' ||
'align: left}' || chr(10) ||
'.idle {color: silver;' ||
'background-color: silver;' ||
'align: left}' || chr(10) ||
'.cpu {color: firebrick;' ||
'background-color: firebrick;' ||
'align: left}' || chr(10) ||
'.oracpu {color: red;' ||
'background-color: red;' ||
'align: left}' || chr(10) ||
'.logons {color: lightslategray;' ||
'background-color: lightslategray;' ||
'align: left}' || chr(10) ||
'.queries {color: darkgoldenrod;' ||
'background-color: darkgoldenrod;' ||
'align: left}' || chr(10) ||
'.activity {color: sienna;' ||
'background-color: sienna;' ||
'align: left}' || chr(10) ||
'.redo {color: peru;' ||
'background-color: peru;' ||
'align: left}' || chr(10) ||
'.inflow {color: limegreen;' ||
'background-color: limegreen;' ||
'align: left}' || chr(10) ||
'.outflow {color: green;' ||
'background-color: green;' ||
'align: left}' || chr(10) ||
'.regular {background-color: BlanchedAlmond}' || chr(10) ||
'.startup {background-color: lightsteelblue}' || chr(10) ||
'table {border-collapse: collapse;' || chr(10) ||
'border-spacing: 0px;' || chr(10) ||
'line-height: 60%}' || chr(10) ||
'th {text-align: center;' ||
'color: white;' ||
'background-color: navy;' ||
'font-size: 60%;' ||
'font-weight: bold}' || chr(10) ||
'body {background-color: BlanchedAlmond}' || chr(10) ||
'-->' || chr(10) ||
'</style>' || chr(10) ||
'</head>' || chr(10) ||
'<body>' || chr(10) ||
'<center>' || chr(10) ||
'<h1>' || "TITLE" || '</h1>' || chr(10) ||
'<table>'
from (select a.db_name ||
case a.instances
when 1 then ''
else '/' || a.instance_name
end || ' (Oracle ' || to_char(c.oraver) || ')'
|| ' on ' || a.host_name ||
' (' || b.osname || ' - ' || d.cpus || ')' "TITLE",
a.db_name
from (select distinct
db_name,
instance_name,
instance_number,
host_name,
count(distinct instance_name)
over (partition by db_name,
host_name) instances
from perfstat.stats$database_instance
where db_name = (select name
from v$database)
and host_name = (select machine
from v$session
where sid = (select min(sid)
from v$session
where type = 'BACKGROUND'))) a,
(select replace(substr(banner, 1,
instr(banner, ':') - 1), 'TNS for ', '') osname
from v$version
where banner like 'TNS%') b,
(select to_number(substr(v, 1, instr(v, '.', 1, 2) - 1),
'999.999',
'NLS_NUMERIC_CHARACTERS='',.''') oraver
from (select ltrim(substr(banner, instr(upper(banner),
'RELEASE') + 7)) v
from v$version
where upper(substr(banner, 1, 6)) = 'ORACLE')) c,
(select 'CPU count: ' || value cpus
from v$parameter
where name = 'cpu_count') d
where a.instance_number = sys_context('USERENV', 'INSTANCE'))
/
select case rownum
when 1 then
'<tr>' || chr(10) ||
'<th>Time</th>' || chr(10) ||
'<th>CPU - Oracle</th>' || chr(10) ||
'<th>Logons<br/>max:' || chr(38) || 'nbsp;' ||
trim(to_char(max_logons)) || '</th>' ||
chr(10) ||
'<th>Executions/s<br/>max:' || chr(38) || 'nbsp;' ||
trim(to_char(max_executions)) || '</th>'
|| chr(10) ||
'<th>Parsing to executions</th>' || chr(10) ||
'<th>User transactions/s<br/>max:' || chr(38) || 'nbsp;' ||
trim(to_char(max_user_tx)) || '</th>' ||
chr(10) ||
'<th>Logical I/Os/s<br/>max:' || chr(38) || 'nbsp;' ||
trim(to_char(max_lio)) || '</th>' ||
chr(10) ||
'<th>Physical reads/s<br/>max:' || chr(38) || 'nbsp;' ||
trim(to_char(max_pio)) || '</th>' ||
chr(10) ||
'<th>Redo blocs written/s<br/>max:' || chr(38) || 'nbsp;' ||
trim(to_char(max_redo_blocks)) ||
'</th>' || chr(10) ||
'<th>MB input/s<br/>max:' || chr(38) || 'nbsp;' ||
trim(to_char(max_in_Mb)) || '</th>' ||
chr(10) ||
'<th>MB output/s<br/>max:' || chr(38) || 'nbsp;' ||
trim(to_char(max_out_Mb)) || '</th>' ||
chr(10) ||
'</tr>' || chr(10)
else ''
end ||
'<tr class="' || case rn
when 1 then 'startup'
else 'regular'
end || '">' || chr(10) ||
'<td class="slice">' || "DATE" || '</td>' || chr(10) ||
'<td><table width="100%"><tr><td class="oracpu" width="' ||
to_char("ORACLE")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "ORACLE") || '%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'<td><table width="100%"><tr><td class="logons" width="' ||
to_char("LOGONS")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "LOGONS") || '%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'<td><table width="100%"><tr><td class="queries" width="' ||
to_char("EXECUTIONS")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "EXECUTIONS") ||
'%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'<td><table width="100%"><tr><td class="queries" width="' ||
to_char("PARSING")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "PARSING") || '%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'<td><table width="100%"><tr><td class="queries" width="' ||
to_char("USER_TX")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "USER_TX") || '%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'<td><table width="100%"><tr><td class="activity" width="' ||
to_char("LIO")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "LIO") || '%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'<td><table width="100%"><tr><td class="activity" width="' ||
to_char("PIO")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "PIO") || '%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'<td><table width="100%"><tr><td class="redo" width="' ||
to_char("REDO_BLOCKS")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "REDO_BLOCKS") ||
'%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'<td><table width="100%"><tr><td class="inflow" width="' ||
to_char("IN_MB")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "IN_MB") || '%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'<td><table width="100%"><tr><td class="outflow" width="' ||
to_char("OUT_MB")
|| '%">' || chr(38) || 'nbsp;</td>'
|| '<td width="' || to_char(100 - "OUT_MB") || '%">'
|| chr(38) || 'nbsp;</td></tr></table></td>' ||
chr(10) ||
'</tr>'
from (select replace(replace("DATE", ' ', chr(38) || 'nbsp;'),
'-', chr(38) || 'nbsp;') "DATE",
"ORACLE",
round(100 * logons / max_logons) "LOGONS",
round(max_logons) max_logons,
round(case max_executions
when 0 then 0
else 100 * executions / max_executions
end) "EXECUTIONS",
round(max_executions) max_executions,
round(case max_user_tx
when 0 then 0
else 100 * user_tx / max_user_tx
end) "USER_TX",
round(max_user_tx) max_user_tx,
round(case max_in_Mb
when 0 then 0
else 100 * in_Mb / max_in_Mb
end) "IN_MB",
round(max_in_Mb) max_in_Mb,
round(case max_out_Mb
when 0 then 0
else 100 * out_Mb / max_out_Mb
end) "OUT_MB",
round(max_out_Mb) max_out_Mb,
round(case max_redo_blocks
when 0 then 0
else 100 * redo_blocks / max_redo_blocks
end) "REDO_BLOCKS",
round(max_redo_blocks) max_redo_blocks,
round(case max_LIO
when 0 then 0
else 100 * LIO / max_LIO
end) "LIO",
round(max_lio) max_lio,
round(case max_PIO
when 0 then 0
else 100 * PIO / max_PIO
end) "PIO",
round(max_pio) max_pio,
round(case executions
when 0 then 0
else 100 * hard_parses / executions
end, 0) "PARSING",
rn
from (select to_char(os.b, 'DD-MON-YYYY HH24:MI') "DATE",
round(100 * ora.session_cpu / os.tot) "ORACLE",
ora.logons,
max(ora.logons) over () max_logons,
ora.executions / os.sec executions,
max(ora.executions / os.sec) over () max_executions,
ora.user_tx / os.sec user_tx,
max(ora.user_tx / os.sec) over () max_user_tx,
ora.in_Mb / os.sec in_Mb,
max(ora.in_Mb / os.sec) over () max_in_Mb,
ora.out_Mb / os.sec out_Mb,
max(ora.out_Mb / os.sec) over () max_out_Mb,
ora.redo_blocks / os.sec redo_blocks,
max(ora.redo_blocks / os.sec) over () max_redo_blocks,
ora.LIO / os.sec LIO,
max(ora.LIO / os.sec) over () max_LIO,
ora.PIO / os.sec PIO,
max(ora.PIO / os.sec) over () max_PIO,
ora.hard_parses / os.sec hard_parses,
os.dbid,
os.instance_number,
os.snap_id,
ora.rn
from (select s.snap_id,
s.dbid,
s.instance_number,
s.b,
s.sec,
s.sec * c.cpus * 1000000 tot
from (select snap_id,
dbid,
instance_number,
nvl(b, startup_time) b,
(e - nvl(b, startup_time)) * 86400 sec
from (select snap_id,
dbid,
instance_number,
lag(snap_time, 1)
over (partition by dbid,
instance_number,
startup_time
order by snap_time) b,
startup_time,
snap_time e
from perfstat.stats$snapshot
where dbid = (select dbid from v$database)
and instance_number =
sys_context('USERENV', 'INSTANCE'))) s,
(select to_number(value) cpus
from v$parameter
where name = 'cpu_count') c) os
inner join (select snap_id,
dbid,
instance_number,
logons,
session_cpu
- nvl(lag(session_cpu, 1)
over (partition by dbid,
instance_number,
startup_time
order by endint),
session_cpu) session_cpu,
executions
- nvl(lag(executions, 1)
over (partition by dbid,
instance_number,
startup_time
order by endint),
executions) executions,
user_tx
- nvl(lag(user_tx, 1)
over (partition by dbid,
instance_number,
startup_time
order by endint),
user_tx) user_tx,
in_Mb
- nvl(lag(in_Mb, 1)
over (partition by dbid,
instance_number,
startup_time
order by endint),
in_Mb) in_Mb,
out_Mb
- nvl(lag(out_Mb, 1)
over (partition by dbid,
instance_number,
startup_time
order by endint),
out_Mb) out_Mb,
redo_blocks
- nvl(lag(redo_blocks, 1)
over (partition by dbid,
instance_number,
startup_time
order by endint),
redo_blocks) redo_blocks,
LIO
- nvl(lag(LIO, 1)
over (partition by dbid,
instance_number,
startup_time
order by endint),
LIO) LIO,
PIO
- nvl(lag(PIO, 1)
over (partition by dbid,
instance_number,
startup_time
order by endint),
PIO) PIO,
hard_parses
- nvl(lag(hard_parses, 1)
over (partition by dbid,
instance_number,
startup_time
order by endint),
hard_parses) hard_parses,
row_number() over (partition by dbid,
instance_number,
startup_time
order by endint) rn
from (select a.snap_id,
a.dbid,
a.instance_number,
b.startup_time,
b.snap_time endint,
sum(case sn.name
when 'CPU used by this
session'
then a.value
else 0
end) session_cpu,
sum(case sn.name
when 'execute count'
then a.value
else 0
end) executions,
sum(case sn.name
when 'user commits'
then a.value
when 'user rollbacks'
then a.value
else 0
end) user_tx,
round(sum(case
when sn.name like
'bytes received%' then a.value
else 0
end)/ 1024/1024) in_Mb,
round(sum(case
when sn.name like
'bytes sent%' then a.value
else 0
end)/ 1024/1024)
out_Mb,
sum(case sn.name
when 'redo blocks written'
then a.value
else 0
end) redo_blocks,
sum(case sn.name
when 'session logical
reads'
then a.value
else 0
end) LIO,
sum(case sn.name
when 'logons current'
then a.value
else 0
end) logons,
sum(case sn.name
when 'physical reads'
then a.value
else 0
end) PIO,
sum(case sn.name
when 'parse count (hard)'
then a.value
else 0
end) hard_parses
from perfstat.stats$sysstat a
inner join v$statname sn
on sn.statistic# =
a.statistic#
inner join
perfstat.stats$snapshot b
on b.snap_id = a.snap_id
and b.dbid = a.dbid
and b.instance_number =
a.instance_number
where sn.name in
('CPU used by this session',
'bytes received via SQL*Net
from dblink',
'bytes sent via SQL*Net to
client',
'user commits',
'session logical reads',
'logons current',
'bytes sent via SQL*Net to
dblink',
'physical reads',
'parse count (hard)',
'user rollbacks',
'redo blocks written',
'execute count')
and a.dbid = (select dbid from
v$database)
and a.instance_number =
sys_context('USERENV', 'INSTANCE')
group by a.snap_id,
a.dbid,
a.instance_number,
b.startup_time,
b.snap_time)) ora
on ora.dbid = os.dbid
and ora.instance_number = os.instance_number
and ora.snap_id = os.snap_id
where os.b is not null)
order by dbid, instance_number, snap_id)
/
select '</table>' || chr(10) ||
'</center>' || chr(10) ||
'</body>' || chr(10) ||
'</html>'
from dual
/
spool off
_at__at_myenv
On 07/17/2013 05:55 PM, Christopher.Taylor2_at_Parallon.com wrote:
> I use snapper but its biggest usefulness seems to be when examining specific sessions.
> I thinking more of an overview/system level type set of scripts (reporting maybe?). Thinking thru this, I'm probably going to have to write a set of reports that will give me varying degrees of performance data (top waits, top sessions) by intervals. I know AWR does this but it generates a LOT of information that takes some time to generate where my aim is to get a quick snapshot on demand. Maybe snapper will give me what I want - I need to go back and investigate what it will give me and how presentable it is versus what I'm looking for.
> Thanks!
> Chris
>
>
> From: Don Seiler [mailto:don_at_seiler.us]
> Sent: Wednesday, July 17, 2013 10:48 AM
> To: usn_at_usn-it.de
> Cc: Taylor Christopher - Nashville; ORACLE-L
> Subject: Re: Tools/monitoring question
>
> I'd highly recommend getting familiar with at least a couple of the basic options for Tanel Poder's snapper.sql.
>
> http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper
>
> On Wed, Jul 17, 2013 at 10:22 AM, Martin Klier <usn_at_usn-it.de<mailto:usn_at_usn-it.de>> wrote:
> Impacting users without Enterprise Manager and Spotlight is not that easy.
>
> But generating AWR (or Statspack) reports is always a good idea to find
> out what's going on - by means of how resources are wasted, and which
> SQL is responsible for the worst.
>
> What I do a lot is:
> http://www.usn-it.de/index.php/2012/09/10/effecting-oracle-miracles-with-standard-edition-and-statspack-without-awr/
>
> But with Diagnostics Pack enabled, using ASH is great too.
>
> Regards
> Martin Klier
>
> Christopher.Taylor2_at_Parallon.com<mailto:Christopher.Taylor2_at_Parallon.com> schrieb:
>> I have come to the realization I've become a little too dependent upon Grid Control for giving me a "quick overview" of system performance. Those colorful graphs are like DBA crack ;)
>> So let's say Grid is down or I'm in an environment where Grid is not available. I'm pretty good with SQL but I still like that overview that Grid gives me where I can quickly identify "blips" related to performance.
>>
>> I'm curious what tools/scripts you guys have in your toolboxes to give you a quick overview when your favorite tool is unavailable. I have several tools in my toolbox as well, but nothing quite as good as Grid when it comes to giving me that "big picture" view to quickly see issues that are impacting users.
>>
>> I do have Spotlight available but we haven't configured it and I'm not entirely sure I want to go that route as it adds objects to the database. I have Toad as well but I'm really thinking along the lines of SQL scripts would be handy.
>>
>> Thoughts?
>>
>> Chris D. Taylor
>> Oracle DBA
>> Parallon ITS
>> 6640 Carothers Parkway
>> Franklin, TN 37067
>> P: 615.344-8419
>> christopher.taylor2_at_parallon.com<mailto:christopher.taylor2_at_parallon.com>
>> www.parallon.com<http://www.parallon.com><http://www.parallon.com>
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
> --
> Usn's IT Blog for Oracle and Linux
> http://www.usn-it.de
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> Don Seiler
> http://www.seiler.us
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
--
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 17 2013 - 19:01:25 CEST