DECLARE
CURSOR c1
IS
SELECT version FROM v$instance;
CURSOR c2
IS
SELECT host_name,
instance_name,
TO_CHAR (SYSDATE, 'HH24:MI:SS DD-MON-YY') currtime,
TO_CHAR (startup_time, 'HH24:MI:SS DD-MON-YY') starttime
FROM v$instance;
CURSOR c4
IS
SELECT *
FROM ( SELECT COUNT (*) cnt, SUBSTR (event, 1, 50) event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer',
'pipe get',
'wakeup time manager',
'pmon timer',
'rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC)
WHERE ROWNUM < 6;
CURSOR c5
IS
SELECT ROUND (SUM (VALUE) / 1048576) AS sgasize FROM v$sga;
CURSOR c6
IS
SELECT ROUND (SUM (bytes) / 1048576) AS dbsize FROM v$datafile;
CURSOR c7
IS
SELECT 'top physical i/o process' category,
sid,
username,
total_user_io
amt_used,
ROUND (100 * total_user_io / total_io, 2) pct_used
FROM ( SELECT b.sid sid,
NVL (b.username, p.name) username,
SUM (VALUE) total_user_io
FROM v$statname c,
v$sesstat a,
v$session b,
v$bgprocess
p
WHERE a.statistic# = c.statistic#
AND p.paddr(+) = b.paddr
AND b.sid = a.sid
AND c.name IN ('physical reads',
'physical writes',
'physical reads direct',
'physical reads direct (lob)',
'physical writes direct',
'physical writes direct (lob)')
AND b.username NOT IN ('SYS',
'SYSTEM',
'SYSMAN',
'DBSNMP')
GROUP BY b.sid, NVL (b.username, p.name)
ORDER BY 3 DESC),
(SELECT SUM (VALUE) total_io
FROM v$statname c, v$sesstat a
WHERE a.statistic# = c.statistic#
AND c.name IN ('physical reads',
'physical writes',
'physical reads direct',
'physical reads direct (lob)',
'physical writes direct',
'physical writes direct (lob)'))
WHERE ROWNUM < 2
UNION ALL
SELECT 'top logical i/o process',
sid,
username,
total_user_io
amt_used,
ROUND (100 * total_user_io / total_io, 2) pct_used
FROM ( SELECT b.sid sid,
NVL (b.username, p.name) username,
SUM (VALUE) total_user_io
FROM v$statname c,
v$sesstat a,
v$session b,
v$bgprocess
p
WHERE a.statistic# = c.statistic#
AND p.paddr(+) = b.paddr
AND b.sid = a.sid
AND c.name IN ('consistent gets', 'db block gets')
AND b.username NOT IN ('SYS',
'SYSTEM',
'SYSMAN',
'DBSNMP')
GROUP BY b.sid, NVL (b.username, p.name)
ORDER BY 3 DESC),
(SELECT SUM (VALUE) total_io
FROM v$statname c,
v$sesstat a,
v$session b,
v$bgprocess p
WHERE a.statistic# = c.statistic#
AND p.paddr(+) = b.paddr
AND b.sid = a.sid
AND c.name IN ('consistent gets', 'db block gets'))
WHERE ROWNUM < 2
UNION ALL
SELECT 'top memory process',
sid,
username,
total_user_mem,
ROUND (100 * total_user_mem / total_mem, 2)
FROM ( SELECT b.sid sid,
NVL (b.username, p.name) username,
SUM (VALUE) total_user_mem
FROM v$statname c,
v$sesstat a,
v$session b,
v$bgprocess
p
WHERE a.statistic# = c.statistic#
AND p.paddr(+) = b.paddr
AND b.sid = a.sid
AND c.name IN ('session pga memory',
'session uga memory')
AND b.username NOT IN ('SYS',
'SYSTEM',
'SYSMAN',
'DBSNMP')
GROUP BY b.sid, NVL (b.username, p.name)
ORDER BY 3 DESC),
(SELECT SUM (VALUE) total_mem
FROM v$statname c, v$sesstat a
WHERE a.statistic# = c.statistic#
AND c.name IN ('session pga memory',
'session uga memory'))
WHERE ROWNUM < 2
UNION ALL
SELECT 'top cpu process',
sid,
username,
total_user_cpu,
ROUND (100 * total_user_cpu / GREATEST (total_cpu, 1), 2)
FROM ( SELECT b.sid sid,
NVL (b.username, p.name) username,
SUM (VALUE) total_user_cpu
FROM v$statname c,
v$sesstat a,
v$session b,
v$bgprocess
p
WHERE a.statistic# = c.statistic#
AND p.paddr(+) = b.paddr
AND b.sid = a.sid
AND c.name = 'CPU used by this session'
AND b.username NOT IN ('SYS',
'SYSTEM',
'SYSMAN',
'DBSNMP')
GROUP BY b.sid, NVL (b.username, p.name)
ORDER BY 3 DESC),
(SELECT SUM (VALUE) total_cpu
FROM v$statname c,
v$sesstat a,
v$session b,
v$bgprocess p
WHERE a.statistic# = c.statistic#
AND p.paddr(+) = b.paddr
AND b.sid = a.sid
AND c.name = 'CPU used by this session')
WHERE ROWNUM < 2;
CURSOR c8
IS
SELECT username, SUM (VALUE / 100) cpu_usage_sec
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.statistic# = sn.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.sid
AND username IS NOT NULL
AND username NOT IN ('SYS',
'SYSTEM',
'SYSMAN',
'DBSNMP')
GROUP BY username
ORDER BY 2 DESC;
BEGIN
DBMS_OUTPUT.put_line ('Database Version');
DBMS_OUTPUT.put_line ('-----------------');
FOR rec IN c1
LOOP
DBMS_OUTPUT.put_line (rec.version);
END LOOP;
DBMS_OUTPUT.put_line (CHR (13));
DBMS_OUTPUT.put_line ('Hostname');
DBMS_OUTPUT.put_line ('----------');
FOR rec IN c2
LOOP
DBMS_OUTPUT.put_line (rec.host_name);
END LOOP;
DBMS_OUTPUT.put_line (CHR (13));
DBMS_OUTPUT.put_line ('SGA Size (MB)');
DBMS_OUTPUT.put_line ('-------------');
FOR rec IN c5
LOOP
DBMS_OUTPUT.put_line (rec.sgasize);
END LOOP;
DBMS_OUTPUT.put_line (CHR (13));
DBMS_OUTPUT.put_line ('Database Size (MB)');
DBMS_OUTPUT.put_line ('-----------------');
FOR rec IN c6
LOOP
DBMS_OUTPUT.put_line (rec.dbsize);
END LOOP;
DBMS_OUTPUT.put_line (CHR (13));
DBMS_OUTPUT.put_line ('Instance start-up time');
DBMS_OUTPUT.put_line ('-----------------------');
FOR rec IN c2
LOOP
DBMS_OUTPUT.put_line (rec.starttime);
END LOOP;
DBMS_OUTPUT.put_line (CHR (13));
FOR b IN (SELECT total,
active,
inactive,
SYSTEM,
killed
FROM (SELECT COUNT (*) total FROM v$session),
(SELECT COUNT (*) SYSTEM
FROM v$session
WHERE username IS NULL),
(SELECT COUNT (*) active
FROM v$session
WHERE status = 'ACTIVE' AND username IS NOT NULL),
(SELECT COUNT (*) inactive
FROM v$session
WHERE status = 'INACTIVE'),
(SELECT COUNT (*) killed
FROM v$session
WHERE status = 'KILLED'))
LOOP
DBMS_OUTPUT.put_line ('Active Sessions');
DBMS_OUTPUT.put_line ('---------------');
DBMS_OUTPUT.put_line (
b.total
|| ' sessions: '
|| b.inactive
|| ' inactive,'
|| b.active
|| ' active, '
|| b.SYSTEM
|| ' system, '
|| b.killed
|| ' killed ');
END LOOP;
DBMS_OUTPUT.put_line (CHR (13));
DBMS_OUTPUT.put_line ('Sessions Waiting');
DBMS_OUTPUT.put_line (CHR (13));
DBMS_OUTPUT.put_line ('Count Event Name');
DBMS_OUTPUT.put_line (
'-----
-----------------------------------------------------');
FOR rec IN c4
LOOP
DBMS_OUTPUT.put_line (rec.cnt || ' ' || rec.event);
END LOOP;
DBMS_OUTPUT.put_line (CHR (13));
DBMS_OUTPUT.put_line (
'-----
-----------------------------------------------------');
DBMS_OUTPUT.put_line (
'TOP Physical i/o, logical i/o, memory and CPU processes');
DBMS_OUTPUT.put_line ('---------------');
FOR rec IN c7
LOOP
DBMS_OUTPUT.put_line (
rec.category
|| ': SID '
|| rec.sid
|| ' User : '
|| rec.username
|| ': Amount used : '
|| rec.amt_used
|| ': Percent used: '
|| rec.pct_used);
END LOOP;
DBMS_OUTPUT.put_line ('------------------------------------------------------------------');
DBMS_OUTPUT.put_line ('TOP CPU users by usage');
DBMS_OUTPUT.put_line ('---------------');
FOR rec IN c8
LOOP
DBMS_OUTPUT.put_line (rec.username || '--' || rec.cpu_usage_sec);
DBMS_OUTPUT.put_line ('---------------');
END LOOP;
END;
No comments:
Post a Comment