Data Base/SQL 튜닝

정적(static) 파티션 pruning, 동적(dynamic) 파티션 pruning

잇꼬 2024. 2. 27. 18:24
728x90
반응형
SMALL

# 정적(static) 파티션 pruning 
- 파티션 키 컬럼을 '상수값 조건'으로 조회
- 액세스할 파티션을 쿼리 최적화 시점에 결정(실행 계획을 생성할 때) 

#) 확인해보기

select * from hr.sal_emp where salary between 5000 and 8000;

=> 125000 rows selected.

#) 실행계획 확인: partition 추가로 확인

select * from table(dbms_xplan.display_cursor(null,null,'allstats last partition'));
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |       |       |    125K|00:00:00.02 |    1463 |   1336 |
|   1 |  PARTITION RANGE SINGLE|         |      1 |    120K|     2 |     2 |    125K|00:00:00.02 |    1463 |   1336 |
|*  2 |   TABLE ACCESS FULL    | SAL_EMP |      1 |    120K|     2 |     2 |    125K|00:00:00.01 |    1463 |   1336 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SALARY"<=8000)


# 파티션 테이블 scan시 정보
Pstart: 시작 파티션 번호
Pstop: 종료 파티션 번호

# partition pruning 
SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상을 제외시키는 기능

#) arraysize 확인

show arraysize
----------------
arraysize 1000


#) trace 설정

set autotrace trace stat


#) 쿼리문 실행

select * from hr.sal_emp s where salary between 5000 and 8000;
125000 rows selected.

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1463  consistent gets
       1336  physical reads
          0  redo size
    4928014  bytes sent via SQL*Net to client
       1783  bytes received via SQL*Net from client
        126  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     125000  rows processed

 


# 동적(dynamic) 파티션 pruning
- 파티션 키 컬럼을 '바인드 변수'로 조회
- 쿼리의 최적화 시점에 액세스할 파티션을 미리 결정할 수 없다. 
   실행 시점에 결정된다.(마냥, 성능이 나쁜 것은 아니다.)

#) 바인드 변수 처리
<hr sess>

var b_start number
var b_stop number
execute :b_start :=5000
execute :b_stop :=8000

=> PL/SQL procedure successfully completed.

#) 바인드 변수로 확인

select * from hr.sal_emp where salary between :b_start and :b_stop;

=> 125000 rows selected.

#) 실행 계획 확인 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last partition'));
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |      1 |        |       |       |    125K|00:00:00.03 |    1463 |   1336 |
|*  1 |  FILTER                   |         |      1 |        |       |       |    125K|00:00:00.03 |    1463 |   1336 |
|   2 |   PARTITION RANGE ITERATOR|         |      1 |    120K|   KEY |   KEY |    125K|00:00:00.02 |    1463 |   1336 |
|*  3 |    TABLE ACCESS FULL      | SAL_EMP |      1 |    120K|   KEY |   KEY |    125K|00:00:00.01 |    1463 |   1336 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:B_STOP>=:B_START)
   3 - filter(("SALARY"<=:B_STOP AND "SALARY">=:B_START))

=> Pstart | Pstop 에 KEY 로 확인되면 바인드 변수 처리된 것으로 알면 된다. 

 

#) 테이블 생성 

create table hr.emp_non
as 
select rownum 
as employee_id, last_name, first_name, hire_date, job_id, salary, manager_id, department_id 
from hr.employees e, (select level as id from dual connect by level <=1000);

=> Table created.

#) 통계수집

exec dbms_stats.gather_table_stats('hr', 'emp_non');

=> PL/SQL procedure successfully completed.

#) 생성된 table 확인

select num_rows, blocks, avg_row_len, last_analyzed
from user_tables
where table_name = 'EMP_NON';
  NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANAL
---------- ---------- ----------- ---------
    107000        760          45 27-FEB-24


#) 1000번 사원 확인

select * from hr.emp_non where employee_id = 1000;

 


#) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |      1 |00:00:00.01 |     745 |
|*  1 |  TABLE ACCESS FULL| EMP_NON |      1 |      1 |      1 |00:00:00.01 |     745 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPLOYEE_ID"=1000)

=> table full scan 으로 확인.

#) 범위 스캔 확인

select count(*) from hr.emp_non where employee_id between 1000 and 25000;
  COUNT(*)
----------
     24001


#) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     744 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     744 |
|*  2 |   TABLE ACCESS FULL| EMP_NON |      1 |  24002 |  24001 |00:00:00.01 |     744 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("EMPLOYEE_ID"<=25000 AND "EMPLOYEE_ID">=1000))

=> table access full scan 으로 확인 → i/o 발생 ↑


#) index 생성 

create unique index hr.emp_non_idx on hr.emp_non(employee_id);

=> Index created

#) 통계수집 확인
-> 보이더라도 한번 체크 해주기!

select index_name, num_rows, blevel, leaf_blocks, clustering_factor, last_analyzed 
from user_indexes
where table_name = 'EMP_NON';
INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR LAST_ANAL
------------------------------ ---------- ---------- ----------- ----------------- ---------
EMP_NON_IDX                        107000          1         223               740 27-FEB-24


#) 100번 사원 확인

select * from hr.emp_non where employee_id = 100;


#) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |      1 |00:00:00.01 |       3 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_NON     |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_NON_IDX |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=100)


#) 범위 스캔 확인

select count(*) from hr.emp_non where employee_id between 1000 and 25000;
  COUNT(*)
----------
     24001


#) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |      1 |00:00:00.01 |      51 |     50 |
|   1 |  SORT AGGREGATE   |             |      1 |      1 |      1 |00:00:00.01 |      51 |     50 |
|*  2 |   INDEX RANGE SCAN| EMP_NON_IDX |      1 |  24002 |  24001 |00:00:00.01 |      51 |     50 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID">=1000 AND "EMPLOYEE_ID"<=25000)

=> CLUSTERING FACTOR 가 좋아서 index range scan의 성능이 좋음. → buffer pinning 발생 → i/o 발생률 낮음

#) 힌트사용, 범위 스캔 확인

select /*+ index_rs(e emp_non_idx) */ count(*) 
from hr.emp_non e 
where employee_id between 1000 and 25000;
  COUNT(*)
----------
     24001


#) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |      1 |00:00:00.01 |      51 |
|   1 |  SORT AGGREGATE   |             |      1 |      1 |      1 |00:00:00.01 |      51 |
|*  2 |   INDEX RANGE SCAN| EMP_NON_IDX |      1 |  24002 |  24001 |00:00:00.01 |      51 | 
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID">=1000 AND "EMPLOYEE_ID"<=25000)

=> buffer pinning 발생 → i/o 발생률 낮음

#) 힌트 사용, 범위 스캔 확인

select /*+ index_ffs(e emp_non_idx) */ count(*) 
from hr.emp_non e 
where employee_id between 1000 and 25000;
  COUNT(*)
----------
     24001


#) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |      1 |        |      1 |00:00:00.01 |     230 |    172 |
|   1 |  SORT AGGREGATE       |             |      1 |      1 |      1 |00:00:00.01 |     230 |    172 |
|*  2 |   INDEX FAST FULL SCAN| EMP_NON_IDX |      1 |  24002 |  24001 |00:00:00.01 |     230 |    172 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("EMPLOYEE_ID"<=25000 AND "EMPLOYEE_ID">=1000))

=> index fast full scan: index 전부 가져와서 i/o 발생 높아짐.
=> 멀티 블록 i/o 발생

728x90
반응형
LIST
저작자표시

'Data Base > SQL 튜닝' 카테고리의 다른 글

Parallel, 병렬 처리  (0) 2024.02.28
local partition index, global partition index, rebuild  (0) 2024.02.27
부분 범위 처리, 운반 단위, arraysize 조절  (1) 2024.02.27
Data Pump_expdp, impdp  (1) 2024.02.26
PARTITION 옵션_나누기, 추가, 수정, 삭제, 통계 수집  (0) 2024.02.26

'Data Base/SQL 튜닝'의 다른글

  • 현재글정적(static) 파티션 pruning, 동적(dynamic) 파티션 pruning

관련글

티스토리툴바