Category: Oracle

2025
0
DevelopOracle

Oracle - 파티셔닝 활용

Oracle 파티셔닝 활용 오라클 파티셔닝을 활용하여 대용량 테이블의 성능과 관리 효율성을 향상시키는 방법을 다룹니다. 파티셔닝은 대용량 테이블과 인덱스를 더 작고 관리하기 쉬운 조각으로 나누는 기법입니다. 쿼리 성능 향상, 데이터 관리 개선, 가용성 향상 등 다양한 이점을 제공합니다. Range 파티션날짜나 숫자 범위로 파티션을 나눕니다. -- Range 파티션 생성CREATE TABLE sales ( sale_id NUMBER, sale_date DATE, customer_id NUMBER, amount NUMBER)PARTITION BY RANGE (sale_date) ( PARTITION p_2024_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')), PARTITION p_2024_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')), PARTITION p_2024_q3 VALUES LESS THAN (TO_DATE('2024-10-01', 'YYYY-MM-DD')), PARTITION p_2024_q4 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')), PARTITION p_max VALUES LESS THAN (MAXVALUE));-- 월별 파티션CREATE TABLE orders ( order_id NUMBER, order_date DATE, customer_id NUMBER)PARTITION BY RANGE (order_date)INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))( PARTITION p_initial VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))); List 파티션특정 값 목록으로 파티션을 나눕니다. -- List 파티션CREATE TABLE employees_regional ( employee_id NUMBER, first_name VARCHAR2(50), last_name VARCHAR2(50), country_code VARCHAR2(2))PARTITION BY LIST (country_code) ( PARTITION p_asia VALUES ('KR', 'JP', 'CN', 'TH'), PARTITION p_europe VALUES ('UK', 'FR', 'DE', 'IT'), PARTITION p_america VALUES ('US', 'CA', 'MX', 'BR'), PARTITION p_other VALUES (DEFAULT));

0
DevelopOracle

Oracle - 힌트 사용 전략

Oracle 힌트 사용 전략 오라클 힌트를 효과적으로 사용하여 쿼리 실행 계획을 제어하는 방법을 다룹니다. 힌트는 옵티마이저에게 특정 실행 방법을 지시하는 강력한 도구입니다. 하지만 잘못 사용하면 오히려 성능을 저하시킬 수 있으므로 신중하게 사용해야 합니다. 자주 사용하는 힌트PARALLEL 힌트대용량 데이터 처리에 효과적입니다. -- PARALLEL 힌트 (대용량 처리)SELECT /*+ PARALLEL(e, 4) */ *FROM employees eWHERE hire_date >= DATE '2020-01-01';-- 테이블별 병렬도 지정SELECT /*+ PARALLEL(e, 4) PARALLEL(d, 2) */ e.employee_id, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id;-- AUTO 병렬도SELECT /*+ PARALLEL(e, AUTO) */ *FROM employees e;-- 주의사항:-- - CPU 리소스 집약적-- - 배치 작업에 적합-- - OLTP 환경에서는 신중하게 사용 APPEND 힌트Direct Path Insert로 성능 향상.

0
DevelopOracle

Oracle - 성능 모니터링

Oracle 성능 모니터링 오라클 데이터베이스의 성능을 모니터링하고 문제를 진단하는 방법을 다룹니다. 성능 모니터링은 데이터베이스의 현재 상태를 파악하고 병목 지점을 찾아 개선하는 데 필수적입니다. 다양한 모니터링 도구와 기법을 활용하여 시스템 성능을 지속적으로 관리할 수 있습니다. AWR (Automatic Workload Repository)AWR 스냅샷 관리-- 수동 스냅샷 생성EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;-- 스냅샷 목록 확인SELECT snap_id, begin_interval_time, end_interval_timeFROM dba_hist_snapshotORDER BY snap_id DESCFETCH FIRST 10 ROWS ONLY;-- AWR 리포트 생성 (SQL*Plus)@$ORACLE_HOME/rdbms/admin/awrrpt.sql-- AWR 리포트 생성 (PL/SQL)SELECT outputFROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT( l_dbid => (SELECT dbid FROM v$database), l_inst_num => 1, l_bid => 100, -- 시작 스냅샷 ID l_eid => 110 -- 종료 스냅샷 ID)); AWR 설정 변경-- AWR 수집 간격 및 보관 기간 변경EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, -- 30일 (분 단위) interval => 30 -- 30분 간격);-- 현재 설정 확인SELECT snap_interval, retentionFROM dba_hist_wr_control; 대기 이벤트 분석

0
DevelopOracle

Oracle - SQL 재작성 기법

Oracle SQL 재작성 기법 비효율적인 SQL을 효율적으로 재작성하여 성능을 개선하는 다양한 기법을 다룹니다. SQL 재작성은 동일한 결과를 얻으면서도 더 빠르게 실행되도록 쿼리를 변경하는 기법입니다. 옵티마이저가 최적화하기 어려운 경우 직접 SQL을 개선할 수 있습니다. UNION ALL vs UNION-- UNION ALL (중복 제거 불필요시)SELECT employee_id, last_name FROM employees WHERE department_id = 10UNION ALLSELECT employee_id, last_name FROM employees WHERE department_id = 20;-- UNION (중복 제거 필요시만)SELECT employee_id FROM employees WHERE department_id = 10UNIONSELECT employee_id FROM employees WHERE department_id = 20;-- 성능 차이:-- UNION ALL: 정렬 없음, 빠름-- UNION: 정렬 + 중복 제거, 느림 COUNT 최적화-- 나쁜 예: COUNT(*)에 불필요한 조건SELECT COUNT(*) FROM employees WHERE salary > 0;-- 문제: salary는 NOT NULL이므로 불필요한 조건-- 좋은 예: 필요한 조건만SELECT COUNT(*) FROM employees WHERE department_id = 10;-- 인덱스 활용SELECT COUNT(*) FROM employees;-- 인덱스가 있으면 테이블 스캔 없이 빠르게 계산-- 존재 여부만 확인할 때-- 나쁜 예SELECT COUNT(*) FROM employees WHERE department_id = 10;-- 좋은 예SELECT CASE WHEN EXISTS ( SELECT 1 FROM employees WHERE department_id = 10) THEN 1 ELSE 0 END FROM DUAL; DISTINCT 최적화-- 나쁜 예: 불필요한 DISTINCTSELECT DISTINCT e.employee_id, e.last_nameFROM employees e, departments dWHERE e.department_id = d.department_id;-- 문제: employee_id는 PK이므로 DISTINCT 불필요-- 좋은 예: DISTINCT 제거SELECT e.employee_id, e.last_nameFROM employees e, departments dWHERE e.department_id = d.department_id;-- EXISTS로 대체-- 나쁜 예: DISTINCT로 중복 제거SELECT DISTINCT e.employee_id, e.last_nameFROM employees e, job_history jhWHERE e.employee_id = jh.employee_id;-- 좋은 예: EXISTS 사용SELECT e.employee_id, e.last_nameFROM employees eWHERE EXISTS ( SELECT 1 FROM job_history jh WHERE jh.employee_id = e.employee_id);

0
DevelopOracle

Oracle - 통계 정보 관리

Oracle 통계 정보 관리 오라클 옵티마이저가 최적의 실행 계획을 수립할 수 있도록 통계 정보를 관리하는 방법을 다룹니다. 통계 정보는 옵티마이저가 실행 계획을 결정하는 데 사용하는 핵심 데이터입니다. 정확하고 최신의 통계 정보를 유지하는 것이 쿼리 성능 최적화의 기본입니다. 통계 수집테이블 통계 수집-- 테이블 통계 수집EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname => 'HR', tabname => 'EMPLOYEES', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);-- 파라미터 설명:-- ownname: 스키마 이름-- tabname: 테이블 이름-- estimate_percent: 샘플링 비율 (AUTO_SAMPLE_SIZE 권장)-- method_opt: 히스토그램 수집 방법-- cascade: 인덱스 통계도 함께 수집 스키마 전체 통계 수집-- 스키마 전체 통계 수집EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname => 'HR', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE, degree => 4 -- 병렬도);-- 데이터베이스 전체 통계 수집EXEC DBMS_STATS.GATHER_DATABASE_STATS( estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE); 시스템 통계 수집

0
DevelopOracle

Oracle - 서브쿼리 최적화

Oracle 서브쿼리 최적화 오라클에서 서브쿼리를 효율적으로 작성하고 최적화하는 방법을 다룹니다. 서브쿼리는 SQL의 강력한 기능이지만, 잘못 사용하면 성능 문제를 일으킬 수 있습니다. 적절한 서브쿼리 유형과 최적화 기법을 선택하는 것이 중요합니다. EXISTS vs INEXISTS 사용외부 쿼리가 큰 경우 효과적입니다. -- EXISTS 사용 (외부 쿼리가 큰 경우)SELECT * FROM employees eWHERE EXISTS ( SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location_id = 1700);-- 특징:-- - 첫 번째 일치하는 행을 찾으면 즉시 반환-- - NULL 값 처리에 안전-- - Correlated 서브쿼리로 실행 IN 사용서브쿼리 결과가 작은 경우 효과적입니다.

0
DevelopOracle

Oracle - 조인 최적화

Oracle 조인 최적화 오라클에서 효율적인 조인 방법을 선택하고 최적화하는 방법을 다룹니다. 조인은 여러 테이블의 데이터를 결합하는 중요한 연산입니다. 적절한 조인 방식과 순서를 선택하면 쿼리 성능을 크게 향상시킬 수 있습니다. 조인 방식 선택Nested Loop Join작은 테이블과 인덱스가 있을 때 효과적입니다. -- NESTED LOOP JOIN (작은 테이블 + 인덱스)SELECT /*+ USE_NL(e d) */ e.*, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id AND e.employee_id = 100;-- 특징:-- - 작은 결과 집합에 유리-- - 선행 테이블의 각 행마다 후행 테이블 인덱스 검색-- - OLTP 환경에 적합 Hash Join큰 테이블 간 조인에 효과적입니다.

0
DevelopOracle

Oracle - 인덱스 힌트 사용법

Oracle 인덱스 힌트 사용법 오라클 옵티마이저가 최적의 인덱스를 선택하지 못할 때, 힌트를 사용하여 특정 인덱스 사용을 유도하는 방법을 다룹니다. 인덱스 힌트는 옵티마이저에게 특정 인덱스를 사용하도록 지시하는 강력한 도구입니다. 하지만 남용하면 오히려 성능을 저하시킬 수 있으므로 신중하게 사용해야 합니다. INDEX 힌트 - 특정 인덱스 사용 강제-- 기본 INDEX 힌트 사용SELECT /*+ INDEX(e idx_emp_dept) */ *FROM employees eWHERE department_id = 10;-- 여러 테이블에 각각 인덱스 힌트 적용SELECT /*+ INDEX(e idx_emp_dept) INDEX(d idx_dept_loc) */ e.employee_id, e.last_name, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id AND d.location_id = 1700;-- 복합 인덱스 사용 강제SELECT /*+ INDEX(e idx_emp_dept_sal) */ *FROM employees eWHERE department_id = 10 AND salary > 5000; INDEX_DESC 힌트 - 인덱스 역순 스캔-- 인덱스를 역순으로 스캔 (내림차순 정렬 효과)SELECT /*+ INDEX_DESC(e idx_emp_salary) */ *FROM employees eWHERE salary > 5000ORDER BY salary DESC;-- 최근 데이터 조회 시 유용SELECT /*+ INDEX_DESC(o idx_order_date) */ *FROM orders oWHERE order_date >= TRUNC(SYSDATE) - 30ORDER BY order_date DESC; INDEX_FFS 힌트 - Fast Full Index Scan-- 테이블 액세스 없이 인덱스만으로 데이터 조회SELECT /*+ INDEX_FFS(e idx_emp_dept_sal) */ department_id, salaryFROM employees eWHERE department_id IS NOT NULL;-- COUNT 연산에 효과적SELECT /*+ INDEX_FFS(e idx_emp_dept) */ COUNT(*)FROM employees eWHERE department_id > 0;

0
DevelopOracle

Oracle - 인덱스 최적화

Oracle 인덱스 최적화 오라클 데이터베이스에서 인덱스를 효과적으로 생성하고 활용하는 방법을 다룹니다. 인덱스는 쿼리 성능을 크게 향상시킬 수 있는 중요한 데이터베이스 객체입니다. 적절한 인덱스 선택과 활용은 쿼리 튜닝의 핵심입니다. 적절한 인덱스 생성-- 단일 컬럼 인덱스CREATE INDEX idx_emp_dept ON employees(department_id);-- 복합 인덱스 (선택도가 높은 컬럼을 앞에)CREATE INDEX idx_emp_dept_sal ON employees(department_id, salary);-- 함수 기반 인덱스CREATE INDEX idx_upper_name ON employees(UPPER(last_name));-- 비트맵 인덱스 (Cardinality가 낮은 컬럼)CREATE BITMAP INDEX idx_gender ON employees(gender); 인덱스 사용 유도-- 나쁜 예: 인덱스 컬럼에 함수 사용SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';-- 좋은 예: 함수 기반 인덱스 사용 또는 검색값 변환SELECT * FROM employees WHERE last_name = UPPER('Smith');-- 나쁜 예: 인덱스 컬럼 변형SELECT * FROM employees WHERE salary * 12 > 100000;-- 좋은 예: 조건 값 변형SELECT * FROM employees WHERE salary > 100000 / 12; 인덱스 종류별 특징B-Tree 인덱스 (기본)

0
DevelopOracle

Oracle - 실행 계획 분석

Oracle 실행 계획 분석 오라클 쿼리의 실행 계획을 확인하고 분석하는 다양한 방법을 다룹니다. 실행 계획은 오라클 옵티마이저가 SQL 문을 실행하기 위해 선택한 작업 순서와 방법입니다. 효과적인 쿼리 튜닝을 위해서는 실행 계획을 정확히 읽고 분석할 수 있어야 합니다. EXPLAIN PLAN 사용-- 실행 계획 생성EXPLAIN PLAN FORSELECT * FROM employees WHERE department_id = 10;-- 실행 계획 확인SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);-- 상세 실행 계획 확인SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALLSTATS LAST')); AUTOTRACE 활용-- AUTOTRACE 활성화SET AUTOTRACE ON EXPLAIN;SET AUTOTRACE ON STATISTICS;SET AUTOTRACE TRACEONLY;-- 쿼리 실행SELECT * FROM employees WHERE salary > 10000;-- AUTOTRACE 비활성화SET AUTOTRACE OFF; SQL 모니터링-- SQL 모니터링 활성화 (11g 이상)SELECT /*+ MONITOR */ * FROM large_table WHERE condition = 'value';-- SQL 모니터 레포트 확인SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id => 'sql_id_here', type => 'TEXT') FROM DUAL;

0
DevelopOracle

Oracle - 쿼리 튜닝 가이드

Oracle 쿼리 튜닝 가이드 오라클 데이터베이스에서 쿼리 성능을 최적화하는 종합 가이드입니다. 쿼리 튜닝은 SQL 문의 실행 시간을 단축하고 시스템 리소스 사용을 최적화하는 프로세스입니다. 효과적인 튜닝을 위해서는 실행 계획 분석, 인덱스 활용, 통계 정보 관리 등 다양한 기법을 이해해야 합니다. 주제별 가이드1. 실행 계획 분석쿼리의 실행 계획을 확인하고 분석하는 방법을 다룹니다. EXPLAIN PLAN 사용법 AUTOTRACE 활용 SQL 모니터링 운영 환경에서 실행 계획 조회 SQL_ID를 이용한 추적 GATHER_PLAN_STATISTICS 힌트 AWR에서 과거 계획 조회 2. 인덱스 최적화인덱스를 효과적으로 생성하고 활용하는 방법을 다룹니다.

2022
0
DevelopOracle

Oracle 12c - Docker 로 시작하기

목차 Oracle - Docker 로 시작하기 Oracle 12c - Docker 로 시작하기 11g 제한사항Oracle 11g 의 경우 11G 제한이 걸려 있어서 부득이 하게 12c 로 변경 했다. 11g 가 11G 까지라서 11g 인건가….. SQL Error [12953] [72000]: ORA-12953: The request exceeds the maximum allowed database size of 11 GB 이미지 다운로드 Image : absolutapps/oracle-12c-ee docker pull absolutapps/oracle-12c-ee 이미지 실행하기

0
DevelopOracle

Oracle - Docker 로 시작하기

목차 Oracle - Docker 로 시작하기 Oracle 12c - Docker 로 시작하기 이미지 다운로드Oracle 의 경우 Mac 버전 데이터 베이스를 지원하지 않기 때문에 Docker 를 이용해 Oracle 를 이용해보려고 한다. Image : jaspeen/oracle-xe-11g docker pull jaspeen/oracle-xe-11g 이미지 실행하기docker run --name oracle11g -d -p 1521:1521 jaspeen/oracle-xe-11g# Docker Volumn 과 외부 Volumn 연결docker run --name oracle11g -d -p 1521:1521 -v ~/docker/oracle:/u01/app/oracle jaspeen/oracle-xe-11g Sqlplus 실행

0
DevelopOracle

Oracle - Clustering Factor

Oracle - Clustering Factor 데이터베이스에서는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다. 인덱스 클러스터링 팩터가 좋다. 고 하면 인덱스 정렬 순서 와 테이블 정렬 순서 가 서로 비슷하다는 것을 말한다. Index Scan의 Cost에 큰 영향을 준다. Index Lookup을 통해 Data를 읽는 일량을 결정한다. Clustering Factor 이해하기Clustering Factor(CF)는 인덱스 통계 정보 중 하나로, 인덱스를 통해 테이블 데이터를 액세스할 때 발생하는 물리적 I/O 양을 예측하는 데 사용된다. CF 값의 범위 최소값: 테이블 블록 수와 같음 (가장 좋은 경우) 최대값: 인덱스 레코드 수와 같음 (가장 나쁜 경우) -- Clustering Factor 확인SELECT index_name, clustering_factor, num_rows, leaf_blocksFROM user_indexesWHERE table_name = 'YOUR_TABLE';