SQLP 50회 실기 1번 2번 문제 복기 및 정리

탈락의 아픔을 딛고.. 다음 회차가 이번주로 다가와서 지난회차때 시험쳤었던 실기문제를 정리해보도록 하겠습니다.

지난회차는 이전과는 다른 스타일의 실기문제였습니다. 지난번의 문제들이 긴 설명과 쿼리와 실행계획등을 보여주고 튜닝하시오.필요하다면 인덱스도 생성하시오. 실행계획이 정확히 나오게 힌트도 서술하시오 느낌의 스타일이었다면, 50회의 느낌은 실기안에 단답형이 여러개 있는 스타일이었습니다. 각 실기문제의 컨셉은 동일하고 단답형 5개 3개가 있는 느낌이었습니다. 그래서 개념을 알고 있다면 바로 맞추지만 모른다면 감을 잡기 힘든 문제(특히 1번)이지 않았을까 생각해보게 되네요.

 

1번

5문제가 안에 있었고 실행계획을 보고 문제점과 개선점을 각각 적으시오 스타일의 문제

실행계획은 기본적으로는 실제 쿼리를 돌려서 뽑았지만, 문제를 복기하면서 좀 숫자들을 수정한거라 잘 안맞을 수도 있습니다. 문제가 요구하는 것은 수정되어있으니 참고하세요.

 

1)

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   100k 
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   100k 
--------------------------------------------------

full 인덱스시 bytes가 높다.
EMP의 인덱스를 생성해주어 index scan이 발생되게 하자

2)

-----------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     100 |   532
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |     100 |   532
|*  2 |   INDEX RANGE SCAN                  | EMP_X01 |    1000 |      
-----------------------------------------------------------------------

인덱스가 있지만 효율이 안좋음. (1000건 탐색 후 결국엔 테이블에서 100건만 읽음)
조건절 컬럼을 인덱스에 추가를 하여 인덱스효율 높이기

3)

-----------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     2k |  1010 
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |     2k |  1010 
|*  2 |   INDEX RANGE SCAN                  | EMP_X03 |     2k |  1000 
-----------------------------------------------------------------------

 

인덱스는 효율이 좋음 (2k 건 중에 2k건 다 읽음)
but bytes수가 높음. 컬럼 순서 조정

4)

--------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes    |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     2k |    1000 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |     2k |    1000 |
|*  2 |   INDEX RANGE SCAN                  | EMP_X03 |     2k |    10   |
--------------------------------------------------------------------------

인덱스는 효율 좋음. 컬럼순서도 좋음. but 램덤 액세스로 테이블을 찾아가는데 990bytes 소요됨.
테이블 블록의 클러스터링 팩터가 좋지않음을 알 수있으며(이부분을 도출해내고 생각해내는게 개인적으로 까다로웠음)
테이블 리오그를 통한 재구축

5)

EMP_PART_X2는 local partition index

--------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name        | Rows  | Bytes | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |             |     1 |    87 |       |       |
|   1 |  PARTITION RANGE ALL                       |             |     1 |    87 |     1 |    10 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| EMP_PART    |     1 |    87 |     1 |    10 |
|*  3 |    INDEX RANGE SCAN                        | EMP_PART_X2 |     1 |       |     1 |    10 |
--------------------------------------------------------------------------------------------------

local partition index를 사용하였으나 효율이 좋지 못함. (partition range all)

 

위의 실행계획만 보았을때는

1) 조건절에 파티션키 컬럼 없이 로컬파티션 인덱스를 사용하였을수도 있고,

2) 조건절에 파티션키 컬럼이 있으나 조건이 single이나 iterator를 사용할 수 없게 되어 있을수도 있습니다.

 

무엇이든 간에 로컬파티션인덱스의 효율이 현재 쿼리에서는 좋지않은것으로 보이고 쿼리수정으로 풀이하는 문제가 아니기 때문에 해당 인덱스 drop 후 비파티션 인덱스를 사용하기가 적절한것으로 보입니다.

 

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |             |     1 |    87 |     0   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| EMP_PART    |     1 |    87 |     0   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                         | EMP_PART_X4 |     1 |       |     0   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------

 

2번

3문제가 있었고 각각 로깅을 최소하하게 튜닝하라는 스타일

 

1)

insert into t1 select * from t2

 

insert 시에는 nologging + append 사용하여 로깅을 최소화하고 direct path insert 할 수 있음.

alter table t1 nologging;
insert /*+ append */ t1 select * From t2;

2)

c1,c2 는 둘다 not null
update set t1 c2 = (case when c1 < trunc(add_months(sysdate,2)) then 'Y' else c2 end);

 

update set t1
c2 = 'Y'
where c1  < trunc(add_months(sysdate,2))
and c2 != 'Y';

위의 case 절은 이미 Y인 로우들까지 모두 탐색후에 update를 수행한다.
이미 C2='Y'인 로우는 제거하고 수행하자.

3)

 

t1의 95%를 삭제하는 쿼리.
delete from t1 partition (202402) where c2='Y';

특정 파티션의 95%를 삭제하는데 일반 DML로 삭제하여 로깅이 많이 발생되고 있음.

파티션의 특성을 활용하여 5%데이터만 tmp테이블이 넘기고 truncate 후 다시 insert하여 로깅을 줄이기.

 

tmp 테이블 nologging으로 생성 -> insert tmp c2 !='Y' nologging +append -> t1 truncate -> insert t1 c2 != 'Y' nologgin + append

create table tmp nologging
as select * from t1 where 1=2;


insert /*+ append */ into tmp 
select * from t1 partition (202402) where c2 != 'Y';
commit;

alter table t1 truncate partition(202402); 

※실제 문제에서 이부분에 대한 명령어가 헷갈려 잘 못적었던 기억이 있네요..

alter table t1 nologging;
insert /*+ append */ into t1
as select * from tmp;
commit;

alter table t1 logging;
drop table tmp;

 

 

아래 네이버카페글을 참고하여 많은 도움이 되었습니다.

https://cafe.naver.com/sqlpd/71143?tc=shared_link

SQLP 50회 실기 1,2번 완벽복원