NAVEEN

Tuesday, December 29, 2009

Monitoring Script

set verify off
set feedback off

connect username/password


set linesize 120
set pages 500

prompt - Database status
prompt ------------------------------------------------------------------------------------------------------

Select instance_name, status from v$instance;

Select name,open_mode from v$database;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Tablespace Monitoring
prompt ------------------------------------------------------------------------------------------------------

SELECT ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free"
FROM (SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Datafiles Monitoring
prompt ------------------------------------------------------------------------------------------------------

col tablespace_name for a20
col file_name for a40

SELECT tablespace_name,file_name, bytes/1024/1024 MB, status,autoextensible FROM dba_data_files order by tablespace_name;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Database Buffer cache
prompt ------------------------------------------------------------------------------------------------------

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio" from v$sysstat;


prompt -

prompt ------------------------------------------------------------------------------------------------------
Library Buffer Cache
prompt ------------------------------------------------------------------------------------------------------

SELECT SUM(PINS-RELOADS)/SUM(PINS)*100 "Library Cache Hit Ratio",sum(reloads)/sum(pins) "RELOADS to PINS" FROM V$LIBRARYCACHE;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Dictionary Cache Hit Ratio
prompt ------------------------------------------------------------------------------------------------------

SELECT (SUM(GETS-GETMISSES))/SUM(GETS)*100 "Dictionary Cache Hit Ratio" FROM V$ROWCACHE;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Number of Users Connected
prompt ------------------------------------------------------------------------------------------------------

select count(*) "Number of users Connected" from v$session where type='USER';

prompt -

prompt ------------------------------------------------------------------------------------------------------
Active Users Count
prompt ------------------------------------------------------------------------------------------------------

SELECT count(*) "Number of Active Users" from v$session where type='USER' and status='ACTIVE';

prompt -

prompt ------------------------------------------------------------------------------------------------------
Monitoring User Session Connected
prompt ------------------------------------------------------------------------------------------------------



set linesize 750
column box format a20
column username format a7
column program format a20
column os_user format a20

select b.sid,b.serial#,a.spid, substr(b.machine,1,20) box,b.logon_time logon_date , to_char (b.logon_time, 'hh24:mi:ss') logon_time,
substr(b.username,1,7) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,20) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by os_user;

prompt ------------------------------------------------------------------------------------------------------
Active Transactions
prompt ------------------------------------------------------------------------------------------------------

select count(*) "Number of Active Transactions"
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address;

prompt-

prompt ------------------------------------------------------------------------------------------------------
Wait Time Ratio Monitoring
prompt ------------------------------------------------------------------------------------------------------

select METRIC_NAME,
VALUE
from SYS.V_$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio',
'Database Wait Time Ratio') AND
INTSIZE_CSEC =
(select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);

prompt ------------------------------------------------------------------------------------------------------
Performance of the Database
prompt ------------------------------------------------------------------------------------------------------




select CASE METRIC_NAME
WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)
WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
from SYS.V_$SYSMETRIC_SUMMARY
where METRIC_NAME in ('CPU Usage Per Sec',
'CPU Usage Per Txn',
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Executions Per Sec',
'Executions Per Txn',
'Response Time Per Txn',
'SQL Service Response Time',
'User Transaction Per Sec')
ORDER BY 1;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Performance of SQL Queries
prompt ------------------------------------------------------------------------------------------------------

select case db_stat_name
when 'parse time elapsed' then
'soft parse time'
else db_stat_name
end db_stat_name,
case db_stat_name
when 'sql execute elapsed time' then
time_secs - plsql_time
when 'parse time elapsed' then
time_secs - hard_parse_time
else time_secs
end time_secs,
case db_stat_name
when 'sql execute elapsed time' then
round(100 * (time_secs - plsql_time) / db_time,2)
when 'parse time elapsed' then
round(100 * (time_secs - hard_parse_time) / db_time,2)
else round(100 * time_secs / db_time,2)
end pct_time
from
(select stat_name db_stat_name,
round((value / 1000000),3) time_secs
from sys.v_$sys_time_model
where stat_name not in('DB time','background elapsed time',
'background cpu time','DB CPU')),
(select round((value / 1000000),3) db_time
from sys.v_$sys_time_model
where stat_name = 'DB time'),
(select round((value / 1000000),3) plsql_time
from sys.v_$sys_time_model
where stat_name = 'PL/SQL execution elapsed time'),
(select round((value / 1000000),3) hard_parse_time
from sys.v_$sys_time_model
where stat_name = 'hard parse elapsed time')
order by 2 desc;

prompt -



prompt ******************************************************************************************************
prompt ******************************************************************************************************
EXIT;



%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
How do I find the overall database size?
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;

------------------------------ EXTRA DBSIZE SCRIPTS -----------------------------

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:

select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:
-------------------------------------------

select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Putting it all together into a single query:(without controlfile )
------------------------------------------------
select (a.data_size+b.temp_size+c.redo_size)/1024/1024 "total_size(IN MB)"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;



select (a.data_size+b.temp_size+c.redo_size)/1024/1024/1024 "total_size(IN GB)"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;



ref: http://www.orafaq.com/wiki/Oracle_database_FAQ



%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

TOTAL DATA FILE SIZE -

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
clear breaks
clear computes
clear columns
set pagesize 50
set linesize 120
set heading on
column tablespace_name heading 'Tablespace' justify left format a20 truncated
column tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
column tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
column tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
column tbusedpct heading 'Used % ' justify left format a8
column tbfreepct heading 'Free % ' justify left format a8
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on report
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name ;


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

No comments:

Post a Comment