728x90
반응형
SMALL
#) 대용량 table
<hr sess>
drop table hr.sal_emp purge;=> Table dropped.
create table hr.sal_emp
nologging
as select rownum as employee_id, last_name, first_name, hire_date, job_id, salary, manager_id, department_id
from employees e, (select level as id from dual connect by level <= 5000);=> Table created.
select * from hr.sal_emp where salary between 5000 and 8000;=> 125000 rows selected.
#) 통계 수집
exec dbms_stats.gather_table_stats('hr', 'sal_emp')=> PL/SQL procedure successfully completed.
#) 용량 확인
select num_rows, blocks, avg_row_len from user_tables where table_name = 'SAL_EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
535000 3774 45
#) 대용량의 데이터 확인
select * from hr.sal_emp;=> 535000 rows selected.
# 부분 범위 처리
- 부분 범위 처리는 SQL Developer에서 주어진 조건을 만족하는 데이터를 전체 범위로 처리하지 않고, 일정한 운반 단위(arraysize)까지만 먼저 처리하여 그 결과를 유저 프로세스에 전달하고, 사용자가 요청할 때까지 잠정적으로 수행을 멈추는 처리 방식
ex) 10000건의 데이터를 스캔해야 할 때, 1000건만 읽어서 운반 단위를 채울 수 있다면 10000건을 한꺼번에 다 읽지 않고 1000개씩 10번 으로 나누어서 처리할 수 있다.
# 예외적인 경우
- 그룹 함수 사용
- order by 절 사용(정렬)
- Union, Minus, Intersect 사용
# 부분 범위 처리를 할 수 없는 경우 대처 방안: [해결방안]
- Order by 절을 사용된 컬럼에 인덱스를 생성하여 부분 범위 처리 가능
- Union 대신 Union All과 Not Exists
- Minus 대신 Not Exists
- Intersect 대신 Exists
# 부분 범위 처리결과집합을 전송
- 전체 데이터를 한꺼번에 연속적으로 처리하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것을 의미.
- 오라클은 데이터를 클라이언트에게 전송할 때 일정량씩 나누어 전송하며 이 설정은 arraysize 파라미터로 설정하여 운반단위를 조절할 수 있다. 오라클은 데이터를 클라이언트에게 전송할 때 일정량씩 나누어 전송하며, 이는 arraysize 파라미터로 설정하여 운반 단위를 조절할 수 있다. (arraysize 값에 따라 읽어들이는 i/o 수가 달라진다.)
- full table scan 이나 대용량의 index scan 할 때 유용하다.
- arraysize 를 무의미하게 크게 한다면? server process 에서 arraysize만큼 채워야 하니, 대기이벤트 발생할 수 있다.
# 대용량 데이터를 fetch 해야 할 때 arraysize를 크게 설정할 때의 이점
1) Fetch Call 횟수 감소로 인한 네트워크 부하 감소 및 쿼리 성능 향상.
2) Server Process가 읽어야 할 블록 갯수 감소 (Consistent Gets 갯수 확인).
#) arraysize 확인
- putty 창 → show arraysize(확인): 15개
- sql developer → 결과창(확인): 50개
show arraysizearraysize 15[해석] 15개의 row를 user에게 전달가능
#) trace 설정
set autotrace trace stat
#) 확인
select * from hr.sal_emp;535000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
39128 consistent gets --블록 i/o
0 physical reads -- disk 에서 불러온rkqt
0 redo size
31346975 bytes sent via SQL*Net to client
392745 bytes received via SQL*Net from client
35668 SQL*Net roundtrips to/from client ★
0 sorts (memory)
0 sorts (disk)
535000 rows processed#1) 535000 rows processed: 읽은 row의 수
#2) 39128 consistent gets: 읽은 블록의 수
#3) 35668 SQL*Net roundtrips to/from client: fetch count의 수
# 전체 row 읽은 수(읽은 row의 수) / fetch count의 수 = 535000 / 35668 = 14.999 => arraysize
#) arraysize 100 으로 변경
set arraysize 100
=> 100개씩 row를 user에게 전달
#) arraysize 변경 후 확인
select * from hr.sal_emp;535000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9026 consistent gets --줄어듦
0 physical reads
0 redo size
27405765 bytes sent via SQL*Net to client
59258 bytes received via SQL*Net from client --줄어듦
5351 SQL*Net roundtrips to/from client --줄어듦
0 sorts (memory)
0 sorts (disk)
535000 rows processed
#) arraysize 1000 으로 변경
set arraysize 1000=> 1000개씩 row를 user에게 전달
select * from hr.sal_emp;535000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4244 consistent gets
0 physical reads
0 redo size
26779815 bytes sent via SQL*Net to client
6293 bytes received via SQL*Net from client
536 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
535000 rows processed
#) 2000개로 변경.
set arraysize 2000
select * from hr.sal_emp;535000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3979 consistent gets
0 physical reads
0 redo size
26745105 bytes sent via SQL*Net to client
3356 bytes received via SQL*Net from client
269 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
535000 rows processed
#) 5000개로 변경.
set arraysize 5000
select * from hr.sal_emp;535000 rows selected.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3817 consistent gets
0 physical reads
0 redo size
26724175 bytes sent via SQL*Net to client
1585 bytes received via SQL*Net from client
108 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
535000 rows processed
<hr sess>
#) arraysize 15 변경
set arraysize 15
#) 설정 하기
alter session set statistics_level = all;=> Session altered.
#) 확인해보기
select * from hr.sal_emp where salary between 5000 and 8000;=> 125000 rows selected.
#) 실행계획 확인
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 | | 125K|00:00:00.07 | 12004 |
|* 1 | TABLE ACCESS FULL| SAL_EMP | 1 | 124K| 125K|00:00:00.07 | 12004 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SALARY">=5000 AND "SALARY"<=8000))=> arraysize 설정값이 작으면 i/o 발생률 높음
#) 확인
<sys sess>
show parameter db_file_multiblock_read_countNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 126#) arraysize 1000 변경
set arraysize 1000
#) 확인해보기
select * from hr.sal_emp where salary between 5000 and 8000;=> 125000 rows selected.
#) 실행계획 확인
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 | | 125K|00:00:00.03 | 3833 |
|* 1 | TABLE ACCESS FULL| SAL_EMP | 1 | 124K| 125K|00:00:00.03 | 3833 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SALARY">=5000 AND "SALARY"<=8000))
#) arraysize 2000 변경
set arraysize 2000
#) 확인해보기
select * from hr.sal_emp where salary between 5000 and 8000;=> 125000 rows selected.
#) 실행계획 확인
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 | | 125K|00:00:00.03 | 3773 |
|* 1 | TABLE ACCESS FULL| SAL_EMP | 1 | 124K| 125K|00:00:00.03 | 3773 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SALARY">=5000 AND "SALARY"<=8000))
#) arraysize 10000 변경
set arraysize 10000=> SP2-0267: arraysize option 10000 out of range (1 through 5000), 범위 정해져 있음
#) arraysize 5000 변경
set arraysize 5000
#) 확인해보기
select * from hr.sal_emp where salary between 5000 and 8000;=> 125000 rows selected.
#) 실행계획 확인
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 | | 125K|00:00:00.02 | 3736 |
|* 1 | TABLE ACCESS FULL| SAL_EMP | 1 | 124K| 125K|00:00:00.02 | 3736 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SALARY">=5000 AND "SALARY"<=8000))
#) arraysize 1000 변경
-> arraysize 1000 최적
set arraysize 1000
#) 확인해보기
select * from hr.sal_emp where salary between 5000 and 8000;=> 125000 rows selected.
#) 실행계획 확인
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 | | 125K|00:00:00.02 | 3833 |
|* 1 | TABLE ACCESS FULL| SAL_EMP | 1 | 124K| 125K|00:00:00.02 | 3833 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SALARY">=5000 AND "SALARY"<=8000))
#) index 설정
create index hr.sal_emp_idx on hr.sal_emp(salary);=> Index created.
#) 확인해보기
select /*+ index(s sal_emp_idx) */ * from hr.sal_emp s where salary between 5000 and 8000;=> 125000 rows selected.
#) 실행계획 확인
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 | | 125K|00:00:00.10 | 67140 | 246 |
| 1 | TABLE ACCESS BY INDEX ROWID| SAL_EMP | 1 | 124K| 125K|00:00:00.10 | 67140 | 246 |
|* 2 | INDEX RANGE SCAN | SAL_EMP_IDX | 1 | 124K| 125K|00:00:00.01 | 372 | 246 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SALARY">=5000 AND "SALARY"<=8000) => buffer pinning=> random i/o 발생
# 테이블 통계 수집, 관련된 인덱스도 통계 수집
exec dbms_stats.gather_table_stats('hr', 'sal_emp', cascade => true)=> PL/SQL procedure successfully completed.
# 인덱스 통계 수집
exec dbms_stats.gather_index_stats('hr', 'sal_emp_idx')=> PL/SQL procedure successfully completed.
select num_rows, blocks, avg_row_len, last_analyzed from user_tables where table_name = 'SAL_EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
---------- ---------- ----------- ---------
535000 3774 45 27-FEB-24
select index_name, num_rows, blevel, leaf_blocks, clustering_factor, last_analyzed
from user_indexes
where table_name = 'SAL_EMP';INDEX_NAME NUM_ROWS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR LAST_ANAL
------------------------------ ---------- ---------- ----------- ----------------- ---------
SAL_EMP_IDX 535000 2 1057 218640 27-FEB-24
#) 테이블 삭제
drop table sal_emp purge;=> Table dropped.
#) partition 생성
CREATE TABLE hr.sal_emp
PARTITION BY RANGE (salary)
(
PARTITION p1 VALUES LESS THAN (5000),
PARTITION p2 VALUES LESS THAN (10000),
PARTITION p3 VALUES LESS THAN (30000),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
)
NOLOGGING
AS SELECT rownum
AS employee_id, ast_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 <= 5000);=> Table created.
#) granularity 작업
exec dbms_stats.gather_table_stats('hr', 'sal_emp', granularity => 'auto')=> PL/SQL procedure successfully completed.
#) partition 확인
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';PARTITION_ HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ------------------------------ ------------------------------ ---------- ---------- -----------
P1 5000 USERS 245000 1758 46
P2 10000 USERS 195000 1370 45
P3 30000 USERS 95000 669 44
PMAX MAXVALUE USERS 0 0 0
#) partition_position 추가로 확인.
select partition_name, partition_position, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';PARTITION_ PARTITION_POSITION HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ------------------ ------------------------------ -------------------- ---------- ---------- -----------
P1 1 5000 USERS 245000 1758 46
P2 2 10000 USERS 195000 1370 45
P3 3 30000 USERS 95000 669 44
PMAX 4 MAXVALUE USERS 0 0 0- PARTITION_POSITION: Pstart | Pstop 위치 확인할 수 있다.
#) 확인해보기
select * from hr.sal_emp s where salary between 5000 and 8000;=> 125000 rows selected.
#) 실행계획 확인
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 | | 125K|00:00:00.05 | 1463 | 1336 |
| 1 | PARTITION RANGE SINGLE| | 1 | 120K| 125K|00:00:00.05 | 1463 | 1336 |
|* 2 | TABLE ACCESS FULL | SAL_EMP | 1 | 120K| 125K|00:00:00.04 | 1463 | 1336 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SALARY"<=8000)
728x90
반응형
LIST
'Data Base > SQL 튜닝' 카테고리의 다른 글
| local partition index, global partition index, rebuild (0) | 2024.02.27 |
|---|---|
| 정적(static) 파티션 pruning, 동적(dynamic) 파티션 pruning (0) | 2024.02.27 |
| Data Pump_expdp, impdp (1) | 2024.02.26 |
| PARTITION 옵션_나누기, 추가, 수정, 삭제, 통계 수집 (0) | 2024.02.26 |
| Range partition, Hash partition, List partition, Composite partition (0) | 2024.02.26 |