본문 바로가기
반응형

DBMS/SQL튜닝14

오라클 PARALLEL(병렬처리) PQ_DISTRIBUTE 힌트 오라클에서 병령처리(PARALLEL) 시 사용되는 조인 방식 제어 힌트 -- 힌트 문법 /*+ pq_distribute(inner_table, outer_distribution, inner_distribution) */ -- 두 테이블이 조인될 때 리딩하는 테이블이 Driving Table 또는 Outer Table 조인되어지는 테이블은 Inner Table -- 힌트 옵션별 작동 방식 PQ_DISTRIBUTE( Inner, none, none ) : Full-Partition Wise Join 유도할 때 사용한다 : 양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝 되어 있어야 함 PQ_DISTRIBUTE( Inner, partition, none ) : Partial-Partition Wis.. 2023. 9. 21.
윈도우함수 사용하여 누적 값 구하기 - 윈도우 함수 사용하여 누적 값 구하기 : sum() over(partition by) 사용하여 그룹별 row 누적 값을 구해보자- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  기본 값range between ...  : 값을 기준으로 같은 값이면 묶어서 계산하여 마지막 값을 보여줌(RANGE_TEST 컬럼 값 600 부분 참고) rows between ...  : row 기준으로 row 단위 순차 누적 값을 반환 (ROW_TEST 컬럼 값 참고)range / rows 일 때 결과 값 차이 테스트WITH tmp AS (SELECT 'a' c1, 1 c2, 100 c3 FROM dual UNION ALLSELECT 'a' c1, 2 c2, 100 c3 FR.. 2023. 8. 20.
PARALLEL SQL DBMS_XPLAN 실행통계 보기 DBMS : oracle 19.19 - 일반적인 DBMS_XPLAN 에서는 parallel 프로세스에 대한 통계확인이 안된다 - ALLSTATS ALL 옵션을 사용한 누적 통계 확인 시 parallel 프로세스 확인 가능 : parallel SQL 확인 시 ALLSTATS ALL 및 child_number(두번째 파라미터) 적절히 변경 - ALLSTATS ALL 은 누적치를 보여주므로 주석을 바꿔가면서 수행하여 sql_id를 변경하며 실행하는 방법으로 진행 : 모든 프로세스 통계의 합이라 실행시간은 실제 실행시간보다 많음 - child_number stats은 아래 뷰에서 확인 가능 : SELECT * FROM V$SQL_PLAN_STATISTICS_ALL WHERE SQL_ID = '0ktqj13jw7.. 2023. 8. 4.
INSERT..SELECT / INSERT..VALUES 성능 테스트 DBMS : oracle 19.3 - insert into values 로 한 건씩 처리하는 것과 insert into .. select 방식의 redo 와 buffer get 등 성능 차이 테스트 - insert into select 방식이 훨씬 좋은 성능을 보인다 100만건 insert 테스트 - insert into select 방식 SQL> alter SESSION SET STATISTICS_LEVEL = ALL ; SET LINESIZE 2000 SET PAGES 10000 Session altered. SQL> SQL> SQL> SQL> set timing on SQL> select b.name, a.value from v$mystat a, v$statname b where a.STATISTI.. 2023. 8. 2.
rollup, grouping sets, cube, grouping 함수 rollup grouping sets cube grouping 함수에 대해 알아본다 -- 샘플 테이블 및 데이터 생성 CREATE TABLE TAB10 ( ID VARCHAR2(5), MON VARCHAR2(10), COM VARCHAR2(10), VAL INTEGER ); INSERT INTO TAB10 VALUES ('P001', '2019.10', '삼성', 15000); INSERT INTO TAB10 VALUES ('P001', '2019.11', '삼성', 25000); INSERT INTO TAB10 VALUES ('P002', '2019.10', 'LG', 10000); INSERT INTO TAB10 VALUES ('P002', '2019.11', 'LG', 20000); INSERT I.. 2023. 7. 30.
oracle ABS 함수, 절대값을 구하는 함수 ABS 함수 - 절대값을 구하는 함수로 음수의 데이터를 양수로 표기하여 보여줌 - 컬럼 타입이 number(숫자타입)가 아닌 문자타입도 가능(단 숫자로 변환 가능한 값이어야 함) -- DUAL 사용 단순 테스트 SQL> SELECT ABS(10), ABS(-10), ABS(10.10), ABS(-10.10), ABS('7'), ABS('-7') FROM DUAL ; ABS(10) ABS(-10) ABS(10.10) ABS(-10.10) ABS('7') ABS('-7') ---------- ---------- ---------- ----------- ---------- ---------- 10 10 10.110.1 7 7 -- 테이블 생성하여 테스트 SQL> CREATE TABLE SK.ABS_TEST A.. 2023. 7. 18.
oracle hint 적용 안되는 경우 확인 및 해결 법 뷰 머징과 같은 쿼리변환(Query Transformation)으로 힌트가 적용되지 않는 경우 확인 및 해결 법 SQL 수행 -- 테스트 뷰 생성 CREATE VIEW HR.DP_V AS SELECT DP.* FROM(SELECT * FROM HR.DEPARTMENTS UNION ALL SELECT * FROM HR.DEPARTMENTS_TMP ) DP, EMPLOYEES E, LOCATIONS L WHERE DP.DEPARTMENT_ID = E.DEPARTMENT_ID AND DP.LOCATION_ID = L.LOCATION_ID ; -- SQL SELECT E.* FROM HR.EMPLOYEES E, HR.DP_V D WHERE 1=1 AND E.DEPARTMENT_ID = D.DEPARTMENT_I.. 2023. 7. 7.
oracle hint 위치별 적용여부 테스트 DBMS : oracle 19.3 힌트없음 SQL> SELECT * FROM sk.tab1 a, sk.tab2 b WHERE a.object_id = b.object_id AND a.owner = 'SK' ; --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2705 | 697K| 790 (1)| 00:00:01 | |* 1 | H.. 2023. 7. 4.
ORDER BY 컬럼명이 가리키는 컬럼은? ORDER BY 컬럼명이 가리키는 위치는 실제 컬럼명일까? alias 일까? - 먼저 SELECT 절 컬럼명(alias)을 찾아보고 없으면 실제 컬럼을 찾아간다 테이블 데이터 조회 SELECT * FROM sk.t1 ; C1 C2 ---------- ---------- 1 5 2 4 3 3 4 2 5 1 alias 명으로 정렬 alias (c1 -> c2, c2 ->b) 라고 선언 후 order by c2 실행 SELECT c1 c2 , c2 b FROM sk.t1 ORDER BY c2 ; C2 B ---------- ---------- 1 5 2 4 3 3 4 2 5 1 실제 컬럼명 정렬 alias (c1 -> a, c2 ->b) 라고 선언 후 order by c2 실행 SELECT c1 a, c2 b.. 2023. 7. 3.
펑션 위치별 실행 횟 수 - 스칼라서브쿼리와 달리 펑션은 실행 시점의 데이터를 가져와서 타세션에서 데이터 변경시 데이터가 달라질 수 있음 - 펑션 위치별 실행 횟수의 차이를 알아본다 select 절 펑션 : 메인 쿼리의 모든 결과만큼 수행 SELECT owner, object_name, sk.sel_d(object_id) FROM sk.tab10 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 4862 1.12 1.60 0 6185 0 72914 -.. 2023. 7. 2.
INLIST ITERATOR 성능개선(where 절 in 조건) INLIST ITERATOR 오퍼레이션은 인덱스 수직 탐색으로 인해 과도한 블록 I/O를 발생할 수 있다. 조회 범위가 넓지 않다면 RANGE SCAN 후 FILTER 처리로 I/O를 줄일 수 있다. 1. 테스트 테이블 생성 DROP TABLE T1 PURGE; CREATE TABLE T1 AS SELECT ROWNUM AS c1, LPAD ('X', 10, 'X') AS c2 FROM XMLTABLE ('1 to 1000000'); CREATE INDEX T1_IX01 ON t1 (c1, c2); 2. 아래 쿼리는 IN 절로 10개의 행을 조회했다. 실행 계획 2번 Starts에서 t1_x1 인덱스가 10번 액세스된 것을 확인할 수 있다. 이로 인해 14개의 블록 I/O가 발생했다. SELECT * F.. 2023. 6. 29.
스칼라서브쿼리 실행계획 순서 및 실행통계 스칼라 서브쿼리 실행계획 - 스칼라 서브 쿼리는 실행 계획 상단에 별도로 표시된다. - 스칼라 서브 쿼리는 최종 SELECT 목록 순서로 표시되며, 중첩 스칼라 서브 쿼리 안쪽부터 표시된다. 스칼라 서브쿼리의 buffers 와 수행시간 메인쿼리 실행통계에 포함 여부 - 일반적으로 스칼라 서브 쿼리의 수행 시간과 블록 I/O는 메인쿼리 실행 통계에 포함되지 않는다. - 인라인뷰와 같은 뷰머징이 발생하지 않을 경우 전체 실행통계에 포함되어 보인다. 메인쿼리 row 여러 개일 때 스칼라 서브쿼리 실행계획 - 일반상황 시 스칼라서브쿼리 실행통계는 메인쿼리와 별도로 보여짐 - 인라인뷰로 변경 시 메인쿼리 실행계획에 스칼라서브쿼리 시간 및 buffer 포함됨 아래 쿼리는 인라인 뷰(t2)와 메인 쿼리(t3)에 스칼.. 2023. 6. 28.
SELECT 절 펑션 수행 횟수 확인 단순 조회와 집계함수(SUM,MAX) 사용 시 SELECT 절 펑션 몇 번 실행 횟 수 차이 요약 SELECT LV, F_SQLP(LV) FROM SQLP GROUP BY LV ; -- 위 SQL은 아래와 같이 작동한다. SELECT LV, F_SQLP(LV) FROM ( SELECT LV FROM SQLP GROUP BY LV ) ; -- SUM과 같은 함수는 위 SQL처럼 동작할 수 없다 SELECT LV, SUM(F_SQLP(LV)) FROM SQLP GROUP BY LV; 테스트 함수 생성CREATE OR REPLACE FUNCTION F_SQLP(p_num NUMBER) RETURN NUMBER IS BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_num)||':리턴'); RE.. 2023. 6. 26.
스칼라 서브쿼리 캐싱 및 버퍼 pinning - 오라클은 스칼라 서브쿼리 수행 시 입력 값(메인쿼리에서 받은)과 출력 값을 내부 캐시(Query Execution Cache)에 저장 - _query_execution_cache_max_size 파라미터 사이즈 만큼 캐시 사용 - 스칼라 서브쿼리가 수행될 때 '입력 값'이 캐시에 있으면 저장된 '출력 값'을 리턴 - 캐시에서 찾지 못한 경우 쿼리 수행, 결과값은 캐시에 저장 - 스칼라 서브쿼리의 캐싱 효과는 입력 값의 종류가 적을 때 성능 개선 효과 테스트를 위해 아래와 같이 테이블을 생성하자.CREATE TABLE t1 AS SELECT ROWNUM AS c1, NVL (NULLIF (MOD (ROWNUM, 100), 0), 100) AS c2 FROM XMLTABLE ('1 to 10000'); .. 2022. 5. 24.
반응형

티스토리툴바