|
column timecol new_value timestamp column
spool_extension new_value suffix
select to_char(sysdate,'yymmdd') timecol, '_mon.txt' spool_extension from sys.dual;
column
output new_value instancename
select '_'
|| value output from v$parameter where name = 'instance_name';
spool
&×tamp&&instancename&&suffix
alter
session set sort_area_size=50000000;
prompt
*********************************************************************************
prompt
*
*
prompt
*
BASE INFO *
prompt
*
*
prompt
*********************************************************************************
set head off
select '
HOST_NAME : '||HOST_NAME from
v$instance;
select '
Database Name : '||name from v$database;
select '
Instance Name : '||INSTANCE_NAME from v$instance;
select '
Global Name : '||value from v$parameter
where name='global_names';
select '
Version : '||VERSION from
v$instance;
select '
Startup Time : '||STARTUP_TIME from
v$instance;
select '
Archive : '||ARCHIVER from
v$instance;
col comp_id
for a10
col
comp_Name for a30
col version
for a15
set head on
SELECT
substr(comp_id,1,15) comp_id, status,
substr(version,1,10)
version,
substr(comp_name,1,30)
comp_name
FROM
dba_registry
ORDER BY 1;
set head off
PROMPT
PROMPT
*********************************************************************
PROMPT
** Buffer Cache Hit Ratio
Statistics **
PROMPT
**
**
PROMPT
*********************************************************************
SET LINESIZE
70
COL
"Get1" FORMAT 999,999,999,999,999 HEADING "Consistent Gets"
COL
"Get2" FORMAT 999,999,999,999,999 HEADING "DB Block Gets"
COL
"Get3" FORMAT 999,999,999,999,999 HEADING "Physical Reads"
COL
"Hit" FORMAT 999.99 HEADING "Hit|Ratio"
select
sum(decode(name, 'consistent gets', value, 0)) "Get1",
sum(decode(name, 'db block gets', value,
0)) "Get2",
sum(decode(name, 'physical reads',
value, 0)) "Get3",
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"
from
sys.v_$sysstat;
PROMPT
PROMPT
*********************************************************************
PROMPT
** Library Cache Hit Ratio
Statistics **
PROMPT
**
**
PROMPT
*********************************************************************
SET LINESIZE
70
COL
"Executions" FORMAT 999,999,999,999,999 HEADING
"Executions"
COL
"EH" FORMAT
999,999,999,999,999 HEADING "Executions Hits"
COL
"Hit1" FORMAT 999.99 HEADING "Hit|Ratio"
COL
"Misses" FORMAT
999,999,999 HEADING
"Misses"
COL
"Hit2" FORMAT 999.99 HEADING "Hit|Ratio"
select
sum(pins) "Executions", sum(pinhits) "EH",
round((sum(pinhits) / sum(pins)) * 100,
3) "Hit1",
sum(reloads) "Misses",
round((sum(pins) / (sum(pins) +
sum(reloads))) * 100, 3) "Hit2"
from sys.v_$librarycache;
PROMPT
PROMPT
*********************************************************************
PROMPT
** Data Dictionary Cache Hit Ratio
Statistics **
PROMPT
*********************************************************************
SET LINESIZE
45
COL
"Gets" FORMAT
999,999,999,999,999 HEADING "Gets"
COL
"Cache" FORMAT 999,999,999,999
HEADING "Cache Missess"
COL
"Hit" FORMAT 999.99 HEADING "Hit|Ratio"
select
sum(gets) "Gets", sum(getmisses) "Cache",
round((1 - (sum(getmisses) / sum(gets)))
* 100, 2) "Hit"
from sys.v_$rowcache;
PROMPT
PROMPT
*********************************************************************
PROMPT
** Sort Statistics
**
PROMPT
**
**
PROMPT
*********************************************************************
SET LINESIZE
80
COL
name FORMAT A30 HEADING "Sort Parameter"
COL value
FORMAT
A30
HEADING "Value"
select name,
value
from v$parameter
where name like '%sort%';
PROMPT
*********************************************************************
PROMPT
** Sort Ratio 가 < 1% 이하 **
PROMPT
*********************************************************************
SET LINESIZE
60
COL
Disk FORMAT 999,999,999,999,999
COL Mem FORMAT 999,999,999,999,999
COL Ratio
FORMAT 999.99 HEADING "Sort|Ratio"
select
disk.value Disk, mem.value Mem,
round((1 - (mem.value
)/(mem.value+disk.value))*100,2) Ratio
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';
PROMPT
PROMPT
PROMPT
*********************************************************************
PROMPT
** Tablespace Statistics **
PROMPT
**
**
PROMPT
*********************************************************************
SET LINESIZE
120
COL
tablespace_name FORMAT A20
HEADING "Tablespace Name"
COL
"Bytes" FORMAT
999,999,999.99 HEADING "TBS Size(MB)"
COL
"Free" FORMAT
999,999.99 HEADING "Free
Size(MB)"
COL
"Percent" FORMAT
999.99 HEADING
"Usage(%)"
COL
"Used" FORMAT
999,999,999.99 HEADING "Used(MB)"
COL
"FreeP" FORMAT
999.99 HEADING
"Free(%)"
select *
from (
select ddf.tablespace_name,
ddf.bytes/1024/1024 "Bytes",
dfs.bytes/1024/1024 "Free",
round(((ddf.bytes - dfs.bytes) /
ddf.bytes) * 100, 2) "Percent"
from ( select tablespace_name, sum(bytes) bytes
from
dba_data_files
group by tablespace_name) ddf,
( select tablespace_name, sum(bytes)
bytes
from
dba_free_space
group by tablespace_name) dfs
where ddf.tablespace_name = dfs.tablespace_name
order by
((ddf.bytes-dfs.bytes)/ddf.bytes) desc)
where rownum
< 6
;
PROMPT
PROMPT
PROMPT
*********************************************************************
PROMPT
** Datafile I/O Statistics **
PROMPT
**
**
PROMPT
*********************************************************************
PROMPT
PROMPT
set lines
120
col
File_name form a52 heading 'File name'
col
tablespace_name form a20 heading
'Tablespace'
col br form 99,999,999 heading 'BLK
Read'
col bw form 99,999,999 heading 'BLK
Write'
col
Weight form 999.99 heading 'Weight|(%)'
select *
from (
select
tablespace_name,file_name,
round((phyblkrd+phyblkwrt*1.2)/z.sum*100, 2) "Weight"
from dba_data_files df,
v$filestat fs,
(select sum(phyblkrd+phyblkwrt*1.2) as
sum from v$filestat) z
where df.file_id = fs.file# order by 3 desc)
where
rownum<6
;
PROMPT
PROMPT
*********************************************************************
PROMPT
** Object Extents > 20 **
PROMPT
*********************************************************************
col
Owner form a9
col
Object_name form a30
col
Type form a1 trunc
col
Tablespace form a12
col
Size(KB) form 999,999,999
col
Init(KB) form 999,999
col
Next(KB) form 999,999
col Ext form 999,999
col
Owner form a9
col
Object_name form a30
col
Type form a1 trunc
col
Tablespace form a15
col
Size(KB) form 999,999,999
col
Init(KB) form 999,999
col
Next(KB) form 999,999
col Ext form 999,999
select *
from (
select Owner,
segment_name Object_name,
segment_type Type,
tablespace_name Tablespace ,
Bytes/1024 "Size(KB)",
initial_extent/1024
"Init(KB)",
next_extent/1024 "Next(KB)",
extents Ext
from dba_segments
where owner != 'SYS' and owner != 'SYSTEM'
and extents > 20
order by 8 desc, 1,2,3,4)
where rownum
< 6;
PROMPT
PROMPT
PROMPT
*********************************************************************
PROMPT ** Tablespace SIZE **
PROMPT
**
**
PROMPT
*********************************************************************
PROMPT
PROMPT
set head on
set
trimspool on
column
totalsize heading "total size(M)"
column
usedsize heading "used size(M)"
column
freesize heading "free size(M)"
column
tablespace_name format a20
column
totalsize format 999,999.9
column
usedsize format 999,999.9
column
freesize format 999,999.9
column
used_percent format 999.9
select
a.tablespace_name, a.totalsize,
nvl(b.usedsize,0)
usedsize,nvl(round(((b.usedsize/a.totalsize)*100),1),0) Used_Percent,c.freesize
from ( select tablespace_name,
sum(bytes)/1024/1024 totalsize
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name,
sum(bytes)/1024/1024 usedsize
from dba_segments
group by tablespace_name ) b,
( select tablespace_name,
sum(bytes)/1024/1024 freesize
from dba_free_space
group by tablespace_name ) c
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name(+)
order by 1;
set head off
prompt
*******************************************************
prompt
*
*
prompt
* Cache Fusion For RAC
prompt
*
*
prompt
*******************************************************
prompt
set linesize
100
col name
format a16
col kind
format a10
col
partition_name format a10
column
partition_name for a15
select
inst_id,name, substr(kind,1,10) kind, partition_name,
sum(xnc) xnc,sum(FORCED_READS) reads,
sum(FORCED_writes) writes
from gv$cache_transfer
group by inst_id,name, kind,
partition_name
having sum(xnc) > 1
order by 1,4
;
prompt
prompt
*************************************************************
prompt *
RESOURCE
*
prompt
*************************************************************
prompt
set head on
col
RESOURCE_NAME for a10
col
CURRENT_UTILIZATION for 9999
col
MAX_UTILIZATION for 9999
col
INITIAL_ALLOCATION for a10
col
LIMIT_VALUE for a10
select *
from v$resource_limit where resource_name in ('processes', 'sessions');
set head off
Prompt
prompt 2. Data file total size
select '
TOTAL DATA FILE SIZE :
'||sum(bytes)/1024/1024||' MB' FROM v$datafile
union
SELECT '
USED SIZE :
'||sum(bytes)/1024/1024||' MB' FROM dba_segments
union
SELECT '
FREE SIZE :
'||sum(bytes)/1024/1024||' MB' FROM dba_free_space;
prompt
set head on
prompt
prompt 3.
Backup check
col time for
a80
select 'Hot
Backup : '||to_char(min(b.time),'yyyy-mm-dd hh24:mi:ss')||' ~
'||to_char(max(b.time),'yyyy-mm-dd hh24:mi:ss') Time
from
v$backup b, v$datafile d
where
b.file#=d.file#
and b.status
= 'NOT ACTIVE';
select *
from
v$backup b, v$datafile d
where
b.file#=d.file#
and b.status
!= 'NOT ACTIVE';
set head off
prompt
prompt
prompt
prompt
*************************************************************
prompt
* NEXT CHECK *
prompt
*************************************************************
prompt
prompt alert
log and trace file check
show
parameter dump
prompt
prompt
archive log list
archive log
list;
Prompt
prompt
prompt
prompt
prompt
*************************************************************
prompt
* NLS_CHARACTERSET *
prompt
*************************************************************
prompt
prompt
select * from NLS_DATABASE_PARAMETERS where parameter =
'NLS_CHARACTERSET';
Prompt
prompt
prompt
prompt
prompt
*************************************************************
prompt
* NLS_CHARACTERSET *
prompt
*************************************************************
prompt
prompt
Select
* from v$version;
Prompt
prompt
*************************************************************
prompt * *
prompt
*************************************************************
select name,
state, type, total_mb, free_mb from v$asm_diskgroup;
spool off |
