블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      
  • 31,488total
  • 0today
  • 16yesterday
2018.09.19 16:31 오라클

DBMS Call을 줄인다.

 (1) 절차적 Loop를 One SQL로 변경한다.

 (2) Array Processing 활용 Array Size를 크게 늘려서 DBMS Call을 줄인다.

 (3) Index 및 제약조건 해제를 통하여 대량 DML 성능 개선

posted by bedbmsguru
2018.09.13 17:33 Zabbix

system 임시 테이블 스페이스 제외하고 사용자 테이블스페이스만 모니터링 되도록 query.property 수정




select * from
(
select '- Tablespace ->', substr(a.tablespace_name,1,30) TABLESPACE_NAME,
'- TOTAL_SIZE_MB ->', round(sum(a.total1)/1024/1024,1) TOTAL_SIZE_MB ,
'- USED_SIZE_MB ->', round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) USED_SIZE_MB,
'- FREE_SIZE_MB ->', round(sum(a.sum1)/1024/1024,1) FREE_SIZE_MB,
'- USED_PERCENT ->', round(100 - (round(sum(a.sum1)/1024/1024,1) / round(sum(a.total1)/1024/1024,1) * 100), 1) USED_PERCENT
from
(select tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
from dba_free_space
WHERE tablespace_name NOT IN('SYSTEM', 'SYSAUX', 'UNDOTBS1')
group by tablespace_name
union
select tablespace_name,sum(bytes) total1,0,0,0
from dba_data_files
WHERE tablespace_name NOT IN('SYSTEM', 'SYSAUX', 'UNDOTBS1')
group by tablespace_name) a
group by a.tablespace_name
) where USED_PERCENT > 93 

posted by bedbmsguru
2018.08.13 16:52 오라클

--특정테이블 index Size 조회


SELECT 
T1.owner, T1.segment_name, T1.segment_type, T1.bytes/1024/1024 SIZE_MB, T2.PRIMARY_KEY, T2.UNIQUENESS, INDEX_POS1, INDEX_POS2, INDEX_POS3, INDEX_POS4, INDEX_POS5, INDEX_POS6, INDEX_POS7, INDEX_POS8, INDEX_POS9, INDEX_POS10
FROM DBA_SEGMENTS T1
INNER JOIN 
(
SELECT * FROM TABLE(PKG_DBA_REPOSITORY.GET_INDEX_INFO('테이블이름'))
) T2
ON T1.SEGMENT_NAME = T2.INDEX_NAME



--PKG_DBA_REPOSITORY.GET_INDEX_INFO 함수 생성

http://bedbmsguru.tistory.com/101?category=793983

posted by bedbmsguru
2018.07.05 08:31 Zabbix

트리거 Flapping 방지


({TRIGGER.VALUE}=0 and {Template OS Windows2:perf_counter["\Processor Information(_Total)\% Processor Time",60].avg(#3)}>85)
or
({TRIGGER.VALUE}=1 and {Template OS Windows2:perf_counter["\Processor Information(_Total)\% Processor Time",60].avg(#3)}>65)



출처

https://blog.zabbix.com/no-more-flapping-define-triggers-the-smart-way/1488/



posted by bedbmsguru

티스토리 툴바