SQL>show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.1.0
--> 10.2.0.4 Version이 설치되어 있다고 하더라도 그 기능을 사용할 수 없음
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
SQL> set autotrace traceonly explain
SQL> select * from big_table;
Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3891K| 7492M| 7168 (2)| 00:01:27 |
| 1 | TABLE ACCESS FULL| BIG_TABLE | 3891K| 7492M| 7168 (2)| 00:01:27 |
-------------------------------------------------------------------------------
SYS@BLOG8DB >ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=32;
Session altered.
SYS@BLOG8DB >set autotrace traceonly explain
SYS@BLOG8DB >select * from scott.big_table;
SQL> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=32;
Session altered.
SQL> select * from scott.big_table;
Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3891K| 7492M| 6327 (2)| 00:01:16 |
| 1 | TABLE ACCESS FULL| BIG_TABLE | 3891K| 7492M| 6327 (2)| 00:01:16 |
-------------------------------------------------------------------------------
설정 | 설명 |
---|---|
수준0 | 테이블로부터 견본을 추출하지 않는다. |
수준1 | 쿼리에 둘 이상의 테이블이 나타나는 경우에는 분석되지 않은 테이블로부터 견본을 추출한다. 해당 테이블은 분석되지 않았을 뿐만 아니라 인덱스가 설정되지 않았으면, OPTIMIZER는 쿼리 계획이 이 객체의 크기에 영향을 받는다고 생각한다.(9i Default) |
수준2 | 기본 견본 추출양(소규모 견본)을 이용하여 쿼리에 참조된 분석되지 않은 모든 테이블로부터 견본을 추출한다.(10g Default) |
수준3 | 수준 2와 동일하지만 추축을 통해 일부 술어를 선택하는테이블도 대상에 포함된다. 기본 견본 추출 양을 이용한다. |
수준4 | 수준 3과 동일하지만 두세 개의 열을 참조하는 단일 테이블 술어(Single-table predicates)를 가지는 테이블이 대상에 포함된다. 기본 견본 추출 양을 이용한다. |
수준5 | 수준 3과 동일하지만 기본 견본 추출 크기의 2배를 사용한다. |
수준6 | 수준 3과 동일하지만 기본 견본 추출 크기의 4배를 사용한다. |
수준7 | 수준 3과 동일하지만 기본 견본 추출 크기의 8배를 사용한다. |
수준8 | 수준 3과 동일하지만 기본 견본 추출 크기의 32배를 사용한다. |
수준9 | 수준 3과 동일하지만 기본 견본 추출 크기의 128배를 사용한다. |
수준6 | 수준 3과 동일하지만 테이블 내의 모든 블록을 사용한다. |
OPTIMIZER_MODE : all_rows,first_rows,choose(통계치정보가있어야함) QUERY_REWRITE_ENABLED : {true(default)|false|force} - true - Cost-based Rewrite - false - Rewrite없음 - force - 강제 Rewrite QUERY_REWRITE_INTEGRITY - stale_tolerated - 데이터 일관성이 보장되지 않아도 query rewrite수행. - trusted - 데이터 일관성이 보장된다고 가정하고 query rewrite수행. - enforced(default) - 변경전 sql과 변경후 sql의 결과값이 같다는 것을 옵티마이져가 확신하면 query rewrite수행.
QUERY_REWRITE
select b.prod_name,c.CUST_FIRST_NAME||' '||c.CUST_LAST_NAME NAME,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 54M| | 1943 (5)| 00:00:24 | | |
|* 1 | HASH JOIN | | 918K| 54M| | 1943 (5)| 00:00:24 | | |
| 2 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | | 3 (0)| 00:00:01 | | |
|* 3 | HASH JOIN | | 918K| 28M| 1736K| 1924 (5)| 00:00:24 | | |
| 4 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1083K| | 331 (2)| 00:00:04 | | |
| 5 | PARTITION RANGE ALL| | 918K| 10M| | 431 (10)| 00:00:06 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 10M| | 431 (10)| 00:00:06 | 1 | 28 - 6|
----------------------------------------------------------------------------------------------------------
Create materialized view m_sale_list
Build immediate
Refresh
Complete
On demand
Enable query rewrite
As
select b.prod_name,c.CUST_FIRST_NAME||' '||c.CUST_LAST_NAME NAME,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
select b.prod_name,c.CUST_FIRST_NAME||' '||c.CUST_LAST_NAME NAME,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 805K| 55M| 1405 (3)| 00:00:17 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| M_SALE_LIST | 805K| 55M| 1405 (3)| 00:00:17 |
단, 옵티마이져가 판단시, query rewrite를 수행한 실행계획보다 옵티마이져가 세운 실행계획의 cost가 더 낮다면, 기본적으로 query rewrite를 수행하지않는다.
Select b.prod_name,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 41M| 750 (48)| 00:00:09 | | |
| 1 | NESTED LOOPS | | 918K| 41M| 750 (48)| 00:00:09 | | |
|* 2 | HASH JOIN | | 918K| 36M| 450 (13)| 00:00:06 | | |
| 3 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL| | 918K| 10M| 431 (10)| 00:00:06 | 1 | 28 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 10M| 431 (10)| 00:00:06 | 1 | 28 |
|* 6 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 5 | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------
Query rewrite 힌트를 준경우
select/*+rewrite*/ b.prod_name,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 35M| 1409 (3)| 00:00:17 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| M_SALE_LIST | 918K| 35M| 1409 (3)| 00:00:17 |
Custermers의 fist_name,last_name 컬럼을 빼고 수행한경우는 query rewrite를 이용하지 않은 경우가 cost가 더 낮으므로 옵티마이져가 query rewrite를 이용하지 않았다.이 경우는 QUERY_REWRITE_INTEGRITY 옵션을 바꾸어도 동일하게 적용되었다.
c.gender컬럼이 추가된 경우에는 모드,옵션에 상관없이 query_rewrite를 이용하지 않았다.
Select b.prod_name,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
query_rewrite_enable모드가 force이므로 cost가 높더라도 query_rewrite를 이용한다.
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 934K| 25M| 1409 (3)| 00:00:17 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| M_SALE_LIST | 934K| 25M| 1409 (3)| 00:00:17 |
--------------------------------------------------------------------------------------------
- 강좌 URL : http://www.gurubee.net/lecture/3550
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.