2.5_OBYE(Order_By_Elimination)
2011.09.18 06:25
2.2.5 OBYE - 불필요한 Order By를 삭제하라
-- Oracle 10g에서 추가.
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SELECT E.DEPARTMENT_ID, COUNT (*) CNT
FROM EMPLOYEE E,
(SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM DEPARTMENT D
ORDER BY D.DEPARTMENT_ID) D -- ①
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.JOB_ID = 'ST_CLERK'
GROUP BY E.DEPARTMENT_ID;
ALTER SESSION SET EVENTS '10053 trace name context off';
-- ① 인라인 뷰 D 에 쓸모 없는 Oder By절
-- Order By 절이 삭제되지 않고 SQL 실행시 SORT 부하에 의한 수행속도 저하와 불필요한 PGA 소모가 발생할것이다.
(참고 : 성능고도화 1권 기본아키텍처 - PGA 관련, 성능고도화 2권 소트머지조인, 소트수행원리, 비용기반 정렬과 머지 조인)
Registered qb: SEL$1 0x3abd20 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=5 objn=0 hint_alias="D"@"SEL$1"
fro(1): flg=4 objn=86728 hint_alias="E"@"SEL$1"
Registered qb: SEL$2 0x3ee028 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$2 nbfros=1 flg=0
fro(0): flg=4 objn=86726 hint_alias="D"@"SEL$2"
SPM: statement not found in SMB
-----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | HASH GROUP BY | | 10 | 200 | 3 | 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 200 | 3 | 00:00:01 |
| 3 | VIEW | VW_GBC_9 | 10 | 160 | 3 | 00:00:01 |
| 4 | HASH GROUP BY | | 10 | 120 | 3 | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 | 240 | 2 | 00:00:01 |
| 6 | INDEX RANGE SCAN | EMP_JOB_IX| 20 | | 1 | 00:00:01 |
| 7 | INDEX UNIQUE SCAN | DEPT_ID_PK| 1 | 4 | 0 | |
-----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
6 - access("E"."JOB_ID"='ST_CLERK')
7 - access("ITEM_1"="D"."DEPARTMENT_ID")
-- Sort Order By 가 존재 하지 않음
-- Outline 정보에 Order By 절을 삭제 하기 위하여 힌트를 사용한것을 알수있음
Content of other_xml column
===========================
db_version : 11.2.0.1
parse_schema : TLO
plan_hash : 4268263766
plan_hash_2 : 3412796541
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$EFEC17CA")
OUTLINE_LEAF(@"SEL$5602C880")
PLACE_GROUP_BY(@"SEL$51F12574" ( "E"@"SEL$1" ) 9)
OUTLINE(@"SEL$5CFEE4DD")
OUTLINE(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
NO_ACCESS(@"SEL$5602C880" "VW_GBC_9"@"SEL$5CFEE4DD")
INDEX(@"SEL$5602C880" "D"@"SEL$2" ("DEPARTMENT"."DEPARTMENT_ID"))
LEADING(@"SEL$5602C880" "VW_GBC_9"@"SEL$5CFEE4DD" "D"@"SEL$2")
USE_NL(@"SEL$5602C880" "D"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$5602C880")
INDEX_RS_ASC(@"SEL$EFEC17CA" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))
USE_HASH_AGGREGATION(@"SEL$EFEC17CA")
END_OUTLINE_DATA
7 - access("ITEM_1"="D"."DEPARTMENT_ID")
■ 10053 Trace에서 OBYE를 분석 해보자.
***************************
Order-by elimination (OBYE)
***************************
OBYE: Considering Order-by Elimination from view SEL$2 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: Removing order by from query block SEL$2 (#0) (order not used) -- OBYE가 성공적으로 수행
Registered qb: SEL$73523A42 0x3ee028 (ORDER BY REMOVED FROM QUERY BLOCK SEL$2; SEL$2)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$73523A42 nbfros=1 flg=0
-- OBYE 수행결과 새로운 쿼리블럭인 SEL$73523A42 생성(교재와 똑같이 쿼리 블럭아이디가 생성)
fro(0): flg=0 objn=86726 hint_alias="D"@"SEL$2"
OBYE: OBYE performed.
OBYE: OBYE bypassed: no order by to eliminate.
JE: Considering Join Elimination on query block SEL$1 (#0)
■ Control 파라미터를 이용한 제어
ALTER SESSION SET "_optimizer_order_by_elimination_enabled" = TRUE;
-----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | HASH GROUP BY | | 10 | 200 | 3 | 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 200 | 3 | 00:00:01 |
| 3 | VIEW | VW_GBC_9 | 10 | 160 | 3 | 00:00:01 |
| 4 | HASH GROUP BY | | 10 | 120 | 3 | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 | 240 | 2 | 00:00:01 |
| 6 | INDEX RANGE SCAN | EMP_JOB_IX| 20 | | 1 | 00:00:01 |
| 7 | INDEX UNIQUE SCAN | DEPT_ID_PK| 1 | 4 | 0 | |
-----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
6 - access("E"."JOB_ID"='ST_CLERK')
7 - access("ITEM_1"="D"."DEPARTMENT_ID")
ALTER SESSION SET "_optimizer_order_by_elimination_enabled" = FALSE;
-----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 5 | |
| 1 | SORT GROUP BY NOSORT | | 10 | 160 | 5 | 00:00:01 |
| 2 | MERGE JOIN | | 48 | 768 | 5 | 00:00:01 |
| 3 | VIEW | | 27 | 108 | 2 | 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK| 27 | | 1 | 00:00:01 |
| 5 | SORT JOIN | | 20 | 240 | 3 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 | 240 | 2 | 00:00:01 |
| 7 | INDEX RANGE SCAN | EMP_JOB_IX| 20 | | 1 | 00:00:01 |
-----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
5 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
7 - access("E"."JOB_ID"='ST_CLERK')
Content of other_xml column
===========================
db_version : 11.2.0.1
parse_schema : TLO
plan_hash : 3745689626
plan_hash_2 : 2404097522
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('_optimizer_order_by_elimination_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "D"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
USE_MERGE(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$2" "D"@"SEL$2" ("DEPARTMENT"."DEPARTMENT_ID"))
END_OUTLINE_DATA
*/
-- 결론 : 꼭 필요한 일만해라. 꺼꾸로 표현하자면 필요한 일만시켜라.
-- 참고로 TKPROF 명령어를 실행하지 않고 trace 파일을 원본그대로의 trace 파일을 이렇게 상세하게? 살펴보게될줄 몰랐고
볼 염두 조차 나지않았던게 사실이다. 하지만 분석해보고 나니 참으로 유용한 정보들이 많은거 같다.
다시한번 1장 1.7절 10053 Event Trace를 참고해보게 되었다 ㅋ Trace와 친해지길 바래~
-- 마지막으로 어떠한 SQL이 어라 이상한데? 냄새가나! 이상해~~! 이정도로 생각이 든다면
이렇게 trace 파일을 분석해보는것이 좋은 방법 중 하나라고 생각이 든다.
The Logical Optimizer (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 9월 17일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
