[오라클] 오라클 쿼리 튜닝 예시 (Oracle Query Tuning Example)

[오라클] 쿼리 튜닝 예시 (Oracle Query Tuning Example)

#나 자신을 위한 메모

 

오라클 쿼리 튜닝은 데이터베이스 성능을 향상시키기 위해 쿼리 실행 계획을 최적화하는 과정입니다.

 

다음은 오라클 쿼리 튜닝을 위한 몇 가지 방법입니다

 

---

0. 예시 쿼리

SELECT *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2022-01-01'
AND oi.product_id = 100

 

 

1. 인덱스 사용

인덱스는 데이터베이스에서 데이터를 검색하는 데 사용되는 구조입니다. 쿼리의 WHERE 절에 자주 사용되는 열에 인덱스를 생성하여 검색 속도를 향상시킬 수 있습니다.

#인덱스 생성
CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

SELECT *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2022-01-01'
AND oi.product_id = 100;

 

2.조인 최적화

 조인은 두 개 이상의 테이블을 연결하는 작업입니다. 조인에 사용되는 조인 조건과 테이블의 순서를 최적화하여 조인 성능을 향상시킬 수 있습니다. 

SELECT *
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.order_date >= '2022-01-01'
AND oi.product_id = 100;

 

 

3. 서브쿼리 최적화

 서브쿼리는 다른 쿼리 내에서 사용되는 쿼리입니다. 서브쿼리를 최적화하여 실행 계획을 개선할 수 있습니다. 예를 들어, EXISTS나 IN 연산자 대신에 JOIN을 사용하는 등의 최적화 기법을 적용할 수 있습니다.

SELECT *
FROM orders o
WHERE o.order_date >= '2022-01-01'
AND EXISTS (
  SELECT 1
  FROM order_items oi
  WHERE oi.order_id = o.order_id
  AND oi.product_id = 100
);

4. 통계 정보 유지

 오라클은 테이블과 인덱스의 통계 정보를 사용하여 실행 계획을 결정합니다. 통계 정보를 정확하게 유지하고 업데이트하는 것이 중요합니다. 통계 정보를 유지하기 위해 수동으로 수집하거나 자동 통계 수집 기능을 사용할 수 있습니다.

#통계정보 수집
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'ORDERS');
  DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'ORDER_ITEMS');
END;



SELECT *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2022-01-01'
AND oi.product_id = 100;

5. 쿼리 재작성

 쿼리를 재작성하여 더 효율적인 실행 계획을 얻을 수 있습니다. 예를 들어, 서브쿼리를 조인으로 변경하거나 UNION 연산자를 사용하는 대신 UNION ALL을 사용하는 등의 재작성 기법을 적용할 수 있습니다.

SELECT *
FROM (
  SELECT *
  FROM orders
  WHERE order_date >= '2022-01-01'
) o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.product_id = 100;

 

 

6. 쿼리 힌트 사용:

 쿼리 힌트는 쿼리 실행 계획에 대한 힌트를 제공하는 방법입니다. 힌트를 사용하여 특정 인덱스를 강제로 사용하거나 조인 순서를 지정할 수 있습니다. 하지만 쿼리 힌트는 주의해서 사용해야 하며, 오라클 옵티마이저가 최적의 실행 계획을 선택할 수 있도록 하는 것이 좋습니다.

SELECT /*+ INDEX(o idx_orders_order_date) INDEX(oi idx_order_items_product_id) */
  *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2022-01-01'
AND oi.product_id = 100;

이러한 예시들은 각각의 상황에 따라 적용될 수 있으며, 실제로는 실행 계획을 확인하고 테스트해보아야 합니다. 또한, 쿼리 튜닝은 데이터베이스의 구조와 데이터의 특성에 따라 다를 수 있으므로, 상황에 맞는 최적화 방법을 선택해야 합니다.

 

 

 

[오라클] 오라클 실행 계획 Cost &CPU, 플랜 보는법 (Oracle Explain plan Cost & CPU)

[오라클] 오라클 실행 계획 Cost &CPU, 플랜 보는법 (Oracle Explain plan Cost & CPU)