'Oracle DB 기본점검'에 해당되는 글 1건

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 &&timestamp&&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