하지만, 금융쪽은 장애 등을 고려하여 안정성이 높은 오라클을 어쩔수 없이 사용하였고, 그이후 단가가 낮은 DB를 도입하는 것을 업종 특성상 꺼려하고 있어, 금융쪽에서 업무를 한다면 무조건 오라클에 익숙해야 한다. 따라서 MSSQL, MySQL 등과 비교해 보면, 질의형 query라는 큰 틀에서는 차이를 느끼지 못하지만, 조인/Null값 체크, 서브쿼리, 페이징 기법 등을 비교해보면 다름을 알 수 있다. 그말은 DBMS를 변경하게 되면 쿼리 호환성을 반드시 검토해야 한다.
DBMS의 후발주자인 tibero, netezza는 전략적으로 공공과 금융시장을 타켓으로 하였기에 DBMS 변경에 따른 작업(결국 비용)을 최소화하여 시장을 공략하기 위해 오라클과 동일한 문법을 채택하였다. 다만, 오라클의 기본 문법을 따라할 수 있었으나, 오라클의 분석함수 등 일부 기능는 지원하지 않는다.
tibero를 먼저 살펴보면,
거의 모든 문법이 동일해서 전환이 용이하다. 사실 기존 구축된 시스템에 분석함수(partition by, window구문 등)을 사용하는 경우가 드물다. 설령 존재할지라도 대체 가능할 정도의 숫자이기에 재개발하면 된다. 게다가 만약 일부만 tibero를 도입한다면, DBLink로 이종DB간의 연결되 가능해서 중요시스템과 비중요시스템을 구분하여 도입할 수도 있다. 다만, 일부 제약사항이 있기에 도입전에 반드시 제약사항을 검토하자.
몇년 전만할지라도 티베로의 이중화(TAC)가 불안했으나, 지금은 많은 개선을 통해 안정화가 되었다고 한다. 가성비를 고려한다면 도입 검토를 하지 않을 이유가 없다. 게다가 국가에서 국산SW를 우선 이용할 것을 권고하고 있어서 공공/금융에서 많이 이용하다 보니, 많은 피드백을 거쳐 발전하고 있는 것 같다.
많은 비오라클 DBMS가 있음에도 "굳이", 오라클과 티베로를 비교하는 이유는 국가 정책 때문이다. 한국 소프트웨어 발전을 위해 공공은 국산 솔루션을 우선 검토하고 있어, 공공에서는 최근 Tibero 디비가 몇백억대 납품실적까지 생겼다. 사실 많이 사용하다보면, 문제점에 대해 개선해 가면서 버전업을 하고, 발주자는 다시 구매하여 개선된 제품을 이용하는 선순환 구조가 형성된다. 또한, 금융에서도 최근 홈페이지 등 일부 업무에서는 다양한 DBMS를 활용하고자 하는데 사실 오라클 비용이 부담되기 때문이다.
netezza(네티자)를 살펴보면,
네티자는 기본적으로 정보계DBMS이다. 즉, 거래 목적이 아닌 통계 목적이다. 게다가 PDA(Pure Data System)라는 IBM에서 어플라이언스로 판매하고 있어서, 하드웨어와 소프트웨어 일체형이다. 자료 규모에 따라 구매한다. 압축률은 10배(?) 정도라고 하지만, 사실 압축률은 자료의 타입에 따라 다르다.! 만약 자료에 한글이 포함되었다면, 1글자당 3byte를 차지하여 압축률이 낮고, 영문이나 숫자로만 되어 있다면 압축률이 높다. 즉, 무조건 압축률이 좋은게 아니라 자료에 따라 높을 수 있다.
쿼리는 거의 오라클과 대동소이해서 오라클 쿼리를 그대로 통계(netezza)에 이용할 수 있다. 다만, 일부 substring 등의 시작이 0이 아니라, 1로 시작하는 등 작은 차이는 존재한다.
통계DB 특성상 Column-wise 하기에 속도는 체감상 Sybase에 견줄만하다. 다만, 이것도 통계DB에 쿼리를 이렇게 저렇게(조인 방식) 하다보면, 어떻게 질의문을 작성해야 답이 잘 나오는지 본인이 습득해야 한다. 다만, 조인없는 단순 쿼리는 누구라도 빠른 답을 얻을 수 있다. 1억건 기준 1~3초 이내 답을 얻을 수 있다.
아마 여러분이 10년 넘은 금융회사의 직원이라면 이용하고 있는 정보계 DB는 Sybase(사이베이스)일 것이다. 사이베이스는 서버와 별도의 스토리지를 구매하여 사용하기에 자료량이 폭증할지라도 스토리지만 추가로 구매하여 할당하면 되서 부담이 적으나, PDA는 어플라이언스 장비라 서버 자체를 구매해야 하는데, 상위 버전으로 올라 갈때 마다 비용이 상당히 올라 간다(50%이상?). 하지만, Sybase 솔루션과 ETL(Extract, Tranform, Load) 솔루션의 비용이 상당히 높아 다른 제품과 비교를 해보는 것도 좋을 것 같다.
사실 정보계 솔루션은 20명(3명이내일지도 모름) 이내의 내부직원만 사용하는 솔루션인데, 비용은 5억~10억 이상 발생하는 아주 고가의 제품이다. 하지만, 회사가 전략적 판단을 하기 위해 적시(빠른)에 다양한 통계를 뽑기 위해서는 충분한 가치가 있는 제품이다. 그럼에도 비용이 걱정된다면 다른 제품도 비교해봄직하다.
# 오라클 기동 : crsctl start crs - (주의)startup 등 단일 인스턴스와는 명령어가 다름
클러스터 상태 확인 후, 오라클을 기동하고, 클러스터 자원 정보를 확인!!!! 상세한 내용은 아래를 참조하시기 바랍니다.
* 서버 재구동시, 오라클 역시 자동으로 reboot되게 하려며, crsctl disable crs * => rebooting 후 crs가 자동 시작되는 것을 disable crsctl enable crs* => rebooting 후 crs가 자동 시작되는 것을 enable crsctl config crs* => crs 설정 상태 확인
# 클러스터 상태 확인
crsctl check crs
vmgdb01n:/ORACLE> crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
vmgdb01n:/ORACLE> crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 97a1ccd5f7ca4f9ebf9ca9e23420e74a (/dev/raw/raw1) []
2. ONLINE 0ce6ebddc2764f97bfceaff24edc388e (/dev/raw/raw2) []
3. ONLINE ad28933487d24f09bf37169b8c67d786 (/dev/raw/raw3) []
Located 3 voting disk(s).
ocrcheck
vmgdb01n:/ORACLE> ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2884
Available space (kbytes) : 259236
ID : 560703319
Device/File Name : /dev/raw/raw4
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw5
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user
crsctl get css disktimeout
crsctl get css misscount
crsctl get css reboottime
vmgdb01n:/ORACLE> crsctl get css disktimeout
CRS-4678: Successful get disktimeout 200 for Cluster Synchronization Services.
vmgdb01n:/ORACLE> crsctl get css misscount
CRS-4678: Successful get misscount 30 for Cluster Synchronization Services.
vmgdb01n:/ORACLE> crsctl get css reboottime
CRS-4678: Successful get reboottime 3 for Cluster Synchronization Services.
### crsctl 명령어 도움말
crsctl -h
Usage: crsctl add - add a resource, type or other entity
crsctl check - check a service, resource or other entity
crsctl config - output autostart configuration
crsctl debug - obtain or modify debug state
crsctl delete - delete a resource, type or other entity
crsctl disable - disable autostart
crsctl discover - discover DHCP server
crsctl enable - enable autostart
crsctl get - get an entity value
crsctl getperm - get entity permissions
crsctl lsmodules - list debug modules
crsctl modify - modify a resource, type or other entity
crsctl query - query service state
crsctl pin - pin the nodes in the node list
crsctl relocate - relocate a resource, server or other entity
crsctl replace - replaces the location of voting files
crsctl release - release a DHCP lease
crsctl request - request a DHCP lease
crsctl setperm - set entity permissions
crsctl set - set an entity value
crsctl start - start a resource, server or other entity
crsctl status - get status of a resource or other entity
crsctl stop - stop a resource, server or other entity
crsctl unpin - unpin the nodes in the node list
crsctl unset - unset an entity value, restoring its default
아래의 쿼리를 실행하면, 쉽게 전체 테이블별 건수 및 용량을 쉽게 체크할 수 있다. (참고로 Block의 사이즈는 8k, 16k 등 테이블 생성시 별도로 정할 수 있어서 확인 필요)
실제 추출되는 정보는 통계정보를 수행한 (Analyze)한 결과이므로, 실제 select count(*) 결과와는 다르다. 통계정보는 analyze한 시점의 정보이기 때문이다. 하지만 전체량을 확인코자 할 때, 약간의 오차 보다는 큰 틀에서 정보를 확인하는 것이 효율적이다.
특히, 주기적으로 삭제하는 테이블의 block을 보면 특이점이 있다. 분명히 많은 정보를 지웠음에도 불구하고, block이 많다. 그리고 full scan을 할 경우 너무 느리다. 그것을 delete의 한계 때문이다. delete를 할지라도 실제 최대로 할당 되었던 block을 반환하고 재구성하지 않기 때문에 차지하는 공간은 여전히 넓다. 필요하다면 테이블 재구성(rebuild 또는 shrink)를 해야 하겠지만, 온라인 상태인 경우 테이블의 lock되는 등 서비스가 일시 정지될 우려가 있으니 충분히 고려하길 바란다.
추가로, 통계정보를 갱신하기 위해 analyze할 경우 반드시 서버에 접속하여 topas로 서버의 상태를 모니터링하고 수행하기 바란다.
테이블명 규칙이 테이블의 명령 규칙이 : T + 업무명(2자리) + 유니크한 이름 EX) TOK_DFSFSDFWER, TQA_DSFSKFSF 등
SELECT TA.*, TB.NUM_ROWS 레코드수
FROM
(
SELECT SUBSTR(SEGMENT_NAME, 2,2) AS UPMU, SEGMENT_NAME, C.COMMENTS, ROUND(SUM(A.BYTES)/8/1024) BLOCK수
FROM DBA_SEGMENTS A, DBA_TABLES B, ALL_TAB_COMMENTS C
WHERE A.SEGMENT_NAME = B.TABLE_NAME
AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
AND A.OWNER = '홍길동'
AND A.SEGMENT_NAME LIKE 'T%' -- 테이블의 명령 규칙이 T + 업무명(2자리) + 유니크한 이름 인 경우...
AND A.SEGMENT_NAME = C.TABLE_NAME
AND C.TABLE_TYPE IN ('TABLE','TABLE PARTITION')
GROUP BY SUBSTR(SEGMENT_NAME, 2,2), SEGMENT_NAME, C.COMMENTS
) TA,
DBA_TABLES TB
WHERE
TA.SEGMENT_NAME = TB.TABLE_NAME
ORDER BY 1,2
Shrink를 이용하기 전에, 검토해야 할것과 Shrink의 기능이 왜 생겨났는지를 알아야 한다.
우선, 여러분이 Delete 를 해서 Table에 수천만건이 있다가 1건으로 줄이더라도 실제 데이터를 검색하기 위해서는 이미 할당된 공간을 full scan하거나, High Warter Mark 까지 체크하는 것을 이해해야 한다.
그리고, Archive log에 변경이력이 쌓이는 걸 알아야 한다. Shrink는 데이터의 저장된 구조를 변경하는 것이다. (블록에 적재된 데이터의 량이 들쑥날쑥하고, row-chaining 등이 많다면, 동일한 데이터라도 table을 읽을 때 시간이 많이 걸린다).
그래서 triger로 인한 추가적인 DB변경은 발생하지 않지만, 실제 row를 delete/insert 작업을 통해 재정렬하는 것이기에 Archive log 파일이 대량으로 생성될 수 있다. 그래서 데이터가 많은 테이블을 Shrink할 경우에는 Archive log 의 디렉토리를 확인하여 백업이 된 log 파일은 삭제할 것을 권고한다.
제일 좋은 것은, 운영 중, 불필요한 데이터를 이관하거나 삭제하는 것이다. (개인정보보호법에 따라, 불필요한 정보는 즉시(5일이내) 삭제해야 하며, Log 테이블은 partition되어 있고, 매일 증가분에 대해 이관하였다면, drop partition을 통해 정리하는 것이 좋다.)
Shrink의 자세한 이론과 테스트는 아래의 내용을 참고하기 바란다.
어떤 세그먼트를 위해 공간이 크게 할당된 경우 High Water Mark 이후의 공간은 사용되지 않은 채로 남아 있게 될 수 있다. 또한 High Water Mark 이전의 영역에도 누적된 delete 연산의 결과로 빈 공간이 존재할 수 있다. 이 경우 다음과 같은 문제점이 있음을 알 수 있다:
데이터가 보다 많은 블록들에 걸쳐 흩어져 있으므로 스캔시 보다 많은 I/O가 필요하다.
내부 단편화로 인해 row-chaining/row migration이 일어날 가능성이 높다.
전체적인 공간 낭비가 발생한다.
Oracle9i에서 이러한 문제점들을 해결하는 방안은 해당 오브젝트를 이동하거나 재생성하는 것이었다. Oracle Database 10g에서는 이 문제를 위해 Segment Shrink 기능을 추가적으로 제공한다.
한편 Oracle Database 10g의 Advisory framework는 Segment의 공간 사용에 관하여 각종 통계 보고 및 Segment Shrink를 포함한 권고안 제시를 위해 Segment Advisor를 제공한다.
Segment Shrink의 원리
Segment Shrink는 다음의 두 단계에 걸쳐 이루어진다.
데이터의 compact
다만, 실제 데이터가 바뀌는 것은 아니기 때문에 DML 트리거가 정의되어 있다 하더라도 발생하지 않는다.
Segment Shrink가 row-chaining을 완전히 제거하는 것을 보장하는 것은 아니다.
보통의 DML과 같은 방식으로 이루어지므로 인덱스 dependency는 자동으로 처리된다. 다만 IOT에 대한 2차 인덱스는 shrink 직후 재생성하는 것을 권장한다.
이 단계는 HWM 아래 영역에 있는 hole들을 채우는 작업이다. 이는 내부적으로 INSERT/DELETE 연산에 의해 이루어진다: HWM에 가까이 있는 행을 안쪽의 빈 공간을 찾아 INSERT하고, 그것이 끝나면 해당 행을 DELETE함으로써 행을 옮기는 것이다.
HWM의 push down
1번 단계에 의해 데이터는 HWM에서 먼 쪽에 촘촘히 채워져 있을 것이고, 반대로 HWM에서 가까운 쪽의 공간은 비어 있는 상태가 된다. 이제 HWM를 내리고, 새롭게 설정된 HWM 이후의 모든 공간을 해당 테이블스페이스에 반납함으로써 segment shrink가 완료된다.
Segment Shrink는 online이자 in-place 연산이다. 1번 단계에서는 통상의 row-level lock이 필요할 뿐, 다른 세션의 DML을 불허하는 것은 아니기 때문에 오브젝트의 가용성은 제한되지 않는다. 또한 2번 단계에서 HWM를 내리는 데 필요한 exclusive 테이블 lock은 매우 짧은 시간 동안만 필요하다. 한편 Segment Shrink가 Oracle9i의 online redefinition과 다른 점은 별도의 임시 공간이 없이 바로 그 오브젝트에 대해서 (in-place) 수행될 수 있다는 점이다.
Segment Shrink의 조건
오직 Automatic Segment Space Management(ASSM)를 사용하는 테이블스페이스 내의 세그먼트만이 shrink될 수 있다. 데이터의 compaction 정보는 세그먼트 헤더의 bitmap block을 이용하기 때문이다. 다만 다음의 세그먼트들은 ASSM 테이블스페이스 내에 있더라도 shrink될 수 없는 제한이 있다:
임시 세그먼트 및 Undo 세그먼트
클러스터 내의 테이블
LONG 컬럼을 가진 테이블
ROWID 기반의 materialized view가 정의된 테이블
LOB 인덱스
IOT mapping 테이블
IOT overflow 세그먼트
공유된 LOB 세그먼트
2. 데이터 compaction 단계에서 rowid가 변경되므로 해당세그먼트에 대해 미리 ROW MOVEMENT가 enable되어 있어야 한다.
SHRINK SPACE 명령
테이블 세그먼트에 대한 segment shrink 명령은 다음과 같다:
ALTER table table_name SHRINK SPACE [COMPACT] [CASCADE];
물론 테이블 뿐만 아니라 segment shrink를 지원하는 모든 오브젝트들에 대해 위와 같은 명령을 사용할 수 있다.
COMPACT 옵션이 지정된 경우 segment shrink는 1단계인 데이터 compaction까지만 수행되게 된다.
CASCADE 옵션이 지정된 경우 segment shrink는 dependent한 오브젝트들에 대해서도 자동으로 수행되게 된다. 예를 들면 테이블을 shrink하면서, 그 테이블에 대해 정의된 인덱스들 또한 자동으로 동시에 shrink할 수 있다.
Segment Advisor
Segment Advisor는 Segment Shrink를 포함한 Segment 관리를 위해 EM에서 제공하는 GUI이다. 이는 EM의 테이블스페이스 페이지, 스키마 오브젝트 페이지, 그리고 Advisor Central 페이지 중 하나로부터 access할 수 있다.
Segment Advisor의 기능은 다음과 같으며 필요한 자료들은 AWR로부터 제공받는다.
Segment Shrink
Segment Advisor는 어떤 오브젝트가 shrink 연산을 위한 좋은 후보인지를 권고한다. 이러한 결정은 그 세그먼트에 할당되었으나 사용되지 않는 공간의 크기, 세그먼트의 공간 사용 경향 등에 근거한다.
Growth Trend Report
세그먼트의 공간 사용 경향을 보고함으로써 DBA의 용량 계획을 돕는다. 이는 나아가 Proactive Tablespace Monitoring 기능과 연동된다.
New Segment Resource Estimation
EM의 새로운 세그먼트 생성 페이지에는 "크기 예측" 버튼이 있어 이를 이용하면 필요한 디스크 크기를 예측할 수 있다. 이는 세그먼트의 구조, 예상되는 행의 수 등에 근거한다.
테스트
테스트 1: Segment Shrink
1. 테스트를 위해 테이블을 하나 생성하고 데이터를 입력해둔다.
SQL> conn scott/tiger
연결되었습니다.
SQL> create table employees as select * from hr.employees;
테이블이 생성되었습니다.
SQL> insert into employees select * from employees;
107 개의 행이 만들어졌습니다.
SQL> /
. . .
SQL> /
27392 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
2. 이제 employees 테이블이 얼마만큼의 공간을 차지하고 있는지를 확인해보자.
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from employees;
BLOCKS
-
556
SQL> select blocks, extents from user_segments where segment_name = ‘EMPLOYEES’;
BLOCKS EXTENTS
- -
640 20
첫번째 조회에서 얻는 blocks는 실제로 사용하고 있는 block의 개수를 나타낸다. 반면에 두번째 조회에서 얻은 blocks는 할당된 block의 개수, 즉 HWM를 나타낸다.
온라인 서비스는 멀티프로세스로 인해, 개별 거래의 속도가 느릴지라도 사용자가 느낄 만큼 속도가 느린 경우는 없다. 물론 조회시 where 조건들이 index에 등록되어 있지 않다면, 처음에는 속도가 느린 것을 느끼지 못할지라도 ... 자료가 쌓이면 속도가 느린 것을 느낄 것이다.
그럼 실행 쿼리의 실행시간 기준으로 TOP 10을 조회하여, 해당 where 조건에 항목들에 대해 인덱스를 생성해주면 된다.
인덱스 생성시에 모든 조건을 인덱스로 만들어서는 안된다. 데이터 1건인데, 인덱스가 10개면 실제 11개의 처리가 발생하므로, 속도저하가 발생하고 인덱스 실행 계획이 원하지 예상하지 못하는 방식으로 나와 문제가 될 수 있다.
인덱스는 무조건 5개 이하이다.
왠만하여 공통 인덱스는 모든 업무를 커버할 수 있도록 선정하고, 조회는 인덱스만 잘 설정되어 있다면 문제 없다.
만약, 배치업무를 수행하는데 느리다면, 다음 사항을 체크해 보자.
1. 조회시 인덱스를 이용하는지? 2. Array 작업을 수행하는지? 3. 중간중간 commit 을 수행할 수 있는지? 4. Counter 체크 5. 통계 정보 생성 6. 불필요한 과거 정보 삭제 7. 업무 프로세스 변경
1. 조회시 인덱스를 이용하는지? 또는 TABLE FULL SCAN 을 하는지? 만들었다고 생각하지 말고, 체크해보자. 그리고 인덱스는 순차적으로 차례대로 찾기 때문에 항목이 5개인 인덱스는 중간에 3번 항목이 없다면, 결국 1번과 2번 항목만 인덱스를 읽고 나머지는 range_scan을 하기 때문에 데이터량에 따라 항목 조절을 해야 한다.
2. Array 작업을 수행하는지? 대량의 작업은 기본적으로 다량 Fetch, 일괄 Insert(또는 Update)를 수행해야 속도가 빠르다. execute() * 10000 보다는 batch_Execute() * 5 가 몇십배 빠르다.
3. 중간중간 commit 을 수행할 수 있는지? 업무에 따라, 중간에 commit을 할수 있다면, 몇천건 또는 몇만건 단위로 commit을 수행한다면 재작업시 작업량을 줄일 수 있다. 검토 후 중간에 commit을 삽입하자.
4. Counter 체크 : 오라클의 sequence를 사용하지 않고, select max(seq) from tableA; 사용한다면, 당장 변경하라. * 혹시 개발자 중에서 SELECT TASEQ.nextval from BigTable where rownum =1; 로 개발했다면, 당장 변경하라. SELECT TASEQ.nextval from dual; 로 실제 수행속도는 테이블의 사이즈에따라 몇백배까지 차이 난다.
5. 통계 정보 생성 : 통계정보가 최신화 되지 않아 plan이 느릴 수 있으므로, Analyze 해보자.
6. 불필요한 과거 정보 삭제 :데이터의 사이즈에 따라, 성능이 저하될 수 도 있으므로 과거 정보 삭제나 parition 나누자.
7. 업무 프로세스 변경 : DB에서 index 타고, 기본적인 속도개선까지 했다면 프로세스 변경을 고려해 보자. 여기까지 한 후, 하드웨어 증설을 고려하자.
# 추가 설명. sql 구성요소들은 아래와 같은 순서로 실행 된다
1) FROM, WHERE 절을 처리 2) ROWNUM 조건 적용 3) SELECT COLUMN LIST 절을 적용 4) GROUP BY 절을 적용 5) HAVING 절을 적용 6) ORDER BY 절을 적용
그러므로, 4번의 예시처럼 의미없이 대량의 테이블에서 rownum <=1 을 하면, 대량의 자료를 fetch 한후 nextval을 가져오는 불필요한 작업이 발생하므로, dual을 적절히 사용하자.
추가 tip. SMS 제품에서는 Oracle에서 제공하 v$sql, DBA_HIST_SQLSTAT, DBA_HIST_SQLTEXT, DBA_HIST_SNAPSHOT 등의 테이블을 이용하여, TOP N Query(상위 N개의 쿼리)를 제공한다. 이것을 바탕으로 지연 서비스의 쿼리 실행계획 및 실행 단위의 소요시간까지 제공해준다. SMS제품이 제공해 주는 것외에 본인이 DBA 또는 고급 개발자로써, 서비스 품질을 위한다면, 아래의 내용을 참고하여 개선 대상을 찾아 고민해보자.
다만, 아래의 것은 여러분이 문제를 찾기 위한 Query일 뿐, 해결방법은 각양각색이므로 획일적으로 답할 순 없다.
반복하는 쿼리 중에서 총 수행시간이 긴 쿼리 찾기. (짧은 업무일지라도, 단일 프로세스로 처리되면 문제가 된다. 예를 들어, 30msec * 100만번이면, 300,00초= 6,000분 = 100시간이 된다.)
SELECT * FROM ( SELECT S.SQL_ID, ROUND(SUM(CPU_TIME_DELTA)/100000) CPUTIME, SUM(EXECUTIONS_DELTA) TOTAL_EXECUTED, DBMS_LOB.SUBSTR(SQL_TEXT,2000,1) SQLTEXT FROM DBA_HIST_SQLSTAT H, DBA_HIST_SQLTEXT S, DBA_HIST_SNAPSHOT T WHERE S.SQL_ID = H.SQL_ID AND H.SNAP_ID = T.SNAP_ID AND T.BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20190513 09:00:00','YYYYMMDD HH24:MI:SS') AND TO_DATE('20190514 15:00:00','YYYYMMDD HH24:MI:SS') GROUP BY S.SQL_ID, DBMS_LOB.SUBSTR(SQL_TEXT,2000,1) ORDER BY 3 DESC ) WHERE rownum < 21 and CPUTIME > 1000 ;
ORACLE서버에서 수행시간이 긴 쿼리 찾기 쿼리 SELECT ROWNUM NO, PARSING_SCHEMA_NAME, to_char(ELAPSED_TIME/(1000000 * decode(executions,null,1,0,1,executions)),999999.9999 ) 평균실행시간, executions 실행횟수, SQL_TEXT 쿼리 , SQL_FULLTEXT FROM V$SQL WHERE LAST_ACTIVE_TIME > SYSDATE-(1/24*2) -- AND LAST_ACTIVE_TIME BETWEEN to_Date('20111226163000','YYYYMMDDHH24MISS') AND to_Date('20111226170000','YYYYMMDDHH24MISS') -- AND ELAPSED_TIME >= 1 * 1000000 * decode(executions,null,1,0,1,executions) and PARSING_SCHEMA_NAME = 'ZIPCODE' ORDER BY 평균실행시간 DESC, 실행횟수 DESC;
SELECT TO_CHAR (SID) sid, serial# serialNumber, SUBSTR (TO_CHAR (last_call_et), 1, 6) executeSeconds, userName, machine, b.sql_text sqlText FROM v$session a, v$sqltext b WHERE username NOT IN ('SYSTEM', 'SYS') AND a.TYPE != 'BACKGROUND' AND a.status = 'ACTIVE' AND a.sql_address = b.address(+) AND a.sql_hash_value = b.hash_value(+) ORDER BY a.last_call_et DESC, a.SID, a.serial#, b.address, b.hash_value, b.piece
현재 실행되고 있는 쿼리 와 실행 시간
SELECT TO_CHAR (SID) sid, serial# serialNumber, SUBSTR (TO_CHAR (last_call_et), 1, 6) executeSeconds, userName, machine, b.sql_text sqlText FROM v$session a, v$sqltext b WHERE username NOT IN ('SYSTEM', 'SYS') AND a.TYPE != 'BACKGROUND' AND a.status = 'ACTIVE' AND a.sql_address = b.address(+) AND a.sql_hash_value = b.hash_value(+) ORDER BY a.last_call_et DESC, a.SID, a.serial#, b.address, b.hash_value, b.piece
