Database/Oracle Database
[오라클] 일일점검 항목 예시
Dong538
2021. 12. 25. 12:17
SQL> select * from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE CON_ID
------------------------- ------------------- --------------- ------------------------- ------------------------- ----------
processes 59 72 300 300 0
sessions 78 90 472 472 0
enqueue_locks 25 38 5580 5580 0
enqueue_resources 19 40 2296 UNLIMITED 0
ges_procs 0 0 0 0 0
ges_ress 0 0 0 UNLIMITED 0
ges_locks 0 0 0 UNLIMITED 0
ges_cache_ress 0 0 0 UNLIMITED 0
ges_reg_msgs 0 0 0 UNLIMITED 0
ges_big_msgs 0 0 0 UNLIMITED 0
ges_rsv_msgs 0 0 0 0 0
gcs_resources 0 0 0 UNLIMITED 0
gcs_shadows 0 0 0 UNLIMITED 0
smartio_overhead_memory 0 0 0 UNLIMITED 0
smartio_buffer_memory 0 0 0 UNLIMITED 0
smartio_metadata_memory 0 0 0 UNLIMITED 0
smartio_sessions 0 0 0 UNLIMITED 0
dml_locks 0 19 2076 UNLIMITED 0
temporary_table_locks 0 0 UNLIMITED UNLIMITED 0
transactions 0 1 519 UNLIMITED 0
branches 0 0 519 UNLIMITED 0
cmtcallbk 0 1 519 UNLIMITED 0
max_rollback_segments 11 11 519 65535 0
sort_segment_locks 1 3 UNLIMITED UNLIMITED 0
k2q_locks 0 0 944 UNLIMITED 0
max_shared_servers 1 2 UNLIMITED UNLIMITED 0
parallel_max_servers 2 2 20 32767 0
27 rows selected.SQL> select INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME, STATUS, ARCHIVER
2 from v$instance;
INSTANCE_NAME HOST_NAME VERSION STARTUP_ STATUS ARCHIVE
---------------- -------------------- ----------------- -------- ------------ -------
orcl enp0s3 19.0.0.0.0 21/12/20 OPEN STARTEDSQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
------- -------------------------------------------------- --- ---------- -------------- ----------
/u01/app/oracle/oradata/ORCL/control01.ctl NO 16384 666 0
/u01/app/oracle/oradata/ORCL/control02.ctl NO 16384 666 0SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------- ------------ -------- ----------
1 1 31 209715200 512 1 YES INACTIVE 1949069 21/12/17 2050245 21/12/20 0
2 1 32 209715200 512 1 NO CURRENT 2050245 21/12/20 1.8447E+19 0
3 1 30 209715200 512 1 YES INACTIVE 1819318 21/12/16 1949069 21/12/17 0SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME CON_ID
---------- ---------------- -------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- -------------------------------------------------- ----------
1 3955 21/11/18 3 1 ONLINE READ WRITE 39845888 4864 33554432 8192 /u01/app/oracle/oradata/ORCL/temp01.dbf 0SQL> select TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, STATUS, CONTENTS, LOGGING, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT, BIGFILE
2 from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN BIG
------------------------------ ---------- -------------- ----------- ----------- --------- --------------------- --------- ---------- --------- ------ ---
SYSTEM 8192 65536 2147483645 ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NO
SYSAUX 8192 65536 2147483645 ONLINE PERMANENT LOGGING LOCAL SYSTEM AUTO NO
UNDOTBS1 8192 65536 2147483645 ONLINE UNDO LOGGING LOCAL SYSTEM MANUAL NO
TEMP 8192 1048576 1048576 ONLINE TEMPORARY NOLOGGING LOCAL UNIFORM MANUAL NO
USERS 8192 65536 2147483645 ONLINE PERMANENT LOGGING LOCAL SYSTEM AUTO NO
TS_TEST_DAT 8192 1048576 1048576 2147483645 ONLINE PERMANENT LOGGING LOCAL UNIFORM AUTO NO
6 rows selected.
ratio 향상시키려면 shared pool size값 증가시켜야 한다.
SQL> select to_char(trunc(sum(reloads)/sum(pins)*100, 5),99.99999)||'% (LESS THAN 1%)' "LIBRARY CACHE MISS RATIO"
2 from v$librarycache;
LIBRARY CACHE MISS RATIO
-------------------------
1.77523% (LESS THAN 1%)SQL> select trunc(sum(getmisses)/sum(gets)*100, 5)||'% (LESS THAN 9.8%)' "DATA DICTIONARY MISS RATIO"
2 from v$rowcache;
DATA DICTIONARY MISS RATIO
----------------------------------------------------------
10.14215% (LESS THAN 9.8%)SQL>
select (a.value+b.value-c.value)/(a.value+b.value)*100 BufferHitRatio
from sys.v_$sysstat a, sys.v_$sysstat b, sys.v_$sysstat c
where a.name = 'db block gets'
and b.name = 'consistent gets'
and c.name = 'physical reads cache';
BUFFERHITRATIO
--------------
99.3326234SQL> select value "REDO LOG REQUEST"
2 from v$sysstat
3 where name = 'redo log space requests';
REDO LOG REQUEST
----------------
0
internal sort and external sort
SQL> select sum(decode(name, 'sorts (memory)', value, 0)) MEM,
2 sum(decode(name, 'sorts (disk)', value, 0)) DSK,
3 round((sum(decode(name, 'sorts (memory)', value, 0))
4 / (sum(decode(name, 'sorts (memory)', value, 0))
5 + sum(decode(name, 'sorts (disk)', value, 0))))*100, 2) "MemSortPCT(%)"
6 ,sum(decode(name, 'sorts (rows)', value, 0)) "Sort_Rows"
7 ,round(sum(decode(name, 'sorts (memory)', value, 0)) /
8 (sum(decode(name, 'sorts (memory)', value, 0))
9 + sum(decode(name, 'sorts (disk)', value, 0)))) "RowsPerSort"
10 from v$sysstat
11 where name in ('sorts (memory)', 'sorts (disk)', 'sorts (rows)');
MEM DSK MemSortPCT(%) Sort_Rows RowsPerSort
---------- ---------- ------------- ---------- -----------
71704 0 100 1963048 1SQL> select * from v$rollstat;
USN LATCH EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT CURBLK CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 0 7 450560 5712 0 315 0 450560 0 0 0 0 0 ONLINE 4 2 0
1 0 17 2088960 10301686 0 4836 0 12705792 3 29 19 4194304 788704 ONLINE 16 32 0
2 0 3 1171456 3646834 0 4231 0 2220032 3 13 2 1048576 349148 ONLINE 2 46 0
3 0 4 2220032 8485954 0 5239 0 5365760 4 22 8 1835008 637126 ONLINE 2 9 0
4 0 4 2220032 1880652 0 3678 0 3268608 2 9 2 1572864 286958 ONLINE 2 15 0
5 0 4 2220032 3639718 0 4183 0 3268608 1 11 2 2097152 345941 ONLINE 2 34 0
6 0 3 8511488 1078606 0 3328 0 9560064 1 2 0 1048576 11714 ONLINE 2 144 0
7 0 3 1171456 4960670 0 4311 0 4317184 2 12 4 2097152 716934 ONLINE 2 97 0
8 0 4 2220032 2741276 0 3930 0 2220032 2 8 2 1048576 186648 ONLINE 2 5 0
9 0 3 1171456 3897298 0 4276 0 3268608 3 13 2 1398101 357216 ONLINE 2 121 0
10 0 3 1171456 3052632 0 3933 0 2220032 2 10 1 1048576 272562 ONLINE 2 79 0SQL> select rs.usn, name, xacts, rssize/1024/1024 rssize, optsize, wraps, extents, shrinks,
2 aveshrink/1024/1024 aveshrink, ((gets-waits)*100)/gets hits, waits wait, rs.status
3 from v$rollstat rs, v$rollname rn
4 where rs.usn = rn.usn;
USN NAME XACTS RSSIZE OPTSIZE WRAPS EXTENTS SHRINKS AVESHRINK HITS WAIT STATUS
---------- ------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 SYSTEM 0 .4296875 0 7 0 0 100 0 ONLINE
1 _SYSSMU1_3036466962$ 0 1.9921875 29 17 3 4 100 0 ONLINE
2 _SYSSMU2_207549919$ 0 1.1171875 13 3 3 1 100 0 ONLINE
3 _SYSSMU3_3105461469$ 0 2.1171875 22 4 4 1.75 100 0 ONLINE
4 _SYSSMU4_2975556719$ 0 2.1171875 9 4 2 1.5 100 0 ONLINE
5 _SYSSMU5_2524574165$ 0 2.1171875 11 4 1 2 100 0 ONLINE
6 _SYSSMU6_1696192826$ 0 8.1171875 2 3 1 1 100 0 ONLINE
7 _SYSSMU7_2443272087$ 0 1.1171875 12 3 2 2 100 0 ONLINE
8 _SYSSMU8_255433850$ 0 2.1171875 8 4 2 1 100 0 ONLINE
9 _SYSSMU9_3527676257$ 0 1.1171875 13 3 3 1.33333302 100 0 ONLINE
10 _SYSSMU10_2767235476$ 0 1.1171875 10 3 2 1 100 0 ONLINE
11 rows selected.SQL> select c.name "latch name",
2 nvl(misses/decode(b.gets,0,1),0) "misses_gets ratio",
3 nvl(immediate_misses/decode(immediate_gets,0,1),0) "immediate misses_gets ratio"
4 from v$latch a, v$latchholder b, v$latchname c
5 where a.addr = b.laddr(+)
6 and a.latch# = c.latch#
7 and c.name in ('redo allocation', 'redo copy')
8 order by a.latch#;
latch name misses_gets ratio immediate misses_gets ratio
------------------ ----------------- ---------------------------
redo copy 0 0
redo allocation 0 0-- tables analyze info
select owner, to_char(last_analyzed, 'yyyy-mm-dd') analyze_date, partitioned, count(*)
from dba_tables
where owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'PERFSTAT')
and (trunc(sysdate) - trunc(last_analyzed) > 7 or last_analyzed is null)
and table_name not like 'DR$TX%'
and table_name not like 'TMP%'
and tablespace_name not like '%/_CLS' escape '/'
and tablespace_name is not null
group by owner, to_char(last_analyzed, 'yyyy-mm-dd'), partitioned
order by 1, 2, 3;
OWNER ANALYZE_DA PAR COUNT(*)
-------------------- ---------- --- ----------
APPQOSSYS 2021-11-18 NO 5
CTXSYS 2021-11-18 NO 37
DBSFWUSER 2021-11-18 NO 3
DVSYS 2021-11-18 NO 43
GSMADMIN_INTERNAL 2021-11-18 NO 34
GSMADMIN_INTERNAL NO 1
LBACSYS 2021-11-18 NO 22
MDSYS 2021-11-18 NO 128
OJVMSYS 2021-11-18 NO 6
OLAPSYS 2021-11-18 NO 2
ORDDATA 2021-11-18 NO 71
ORDSYS 2021-11-18 NO 4
WMSYS 2021-11-18 NO 32
WMSYS NO 1
XDB 2021-11-18 NO 34-- index analyze info
select owner, to_char(last_analyzed, 'yyyy-mm-dd') analyze_date, partitioned, count(*)
from dba_indexes
where owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'PERFSTAT')
and (trunc(sysdate) - trunc(last_analyzed) > 7 or last_analyzed is null)
and table_name not like 'DR$TX%'
and table_name not like 'TMP%'
and tablespace_name not like '%/_CLS' escape '/'
and tablespace_name is not null
group by owner, to_char(last_analyzed, 'yyyy-mm-dd'), partitioned
order by 1, 2, 3;
OWNER ANALYZE_DA PAR COUNT(*)
---------------------- ---------- --- ----------
CTXSYS 2021-11-18 NO 64
CTXSYS NO 4
DBSFWUSER 2021-11-18 NO 3
DVSYS 2021-11-18 NO 77
DVSYS NO 1
GSMADMIN_INTERNAL 2021-11-18 NO 42
GSMADMIN_INTERNAL NO 8
LBACSYS 2021-11-18 NO 30
MDSYS 2021-11-18 NO 119
MDSYS NO 76
OJVMSYS 2021-11-18 NO 6
OJVMSYS NO 1
OLAPSYS 2021-11-18 NO 2
ORDDATA 2021-11-18 NO 99
ORDDATA NO 6
ORDSYS 2021-11-18 NO 3
WMSYS 2021-11-18 NO 57
WMSYS NO 10
XDB 2021-11-18 NO 72
XDB NO 289
20 rows selected.SQL>
select table_owner, to_char(last_analyzed, 'yyyy-mm-dd') analyze_date, count(*)
from dba_tab_partitions
where table_owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'PERFSTAT')
and (trunc(sysdate) - trunc(last_analyzed) > 7 or last_analyzed is null)
and table_name not like 'DR$TX%'
and partition_name not like 'SYS_IL_P%'
group by table_owner, to_char(last_analyzed, 'yyyy-mm-dd')
order by 1, 2;
TABLE_OWNER ANALYZE_DA COUNT(*)
------------ ---------- ----------
AUDSYS 2021-11-18 1
AUDSYS 2
MDSYS 2021-11-18 1