SQL> SELECT value
2 FROM v$parameter_valid_values
3 WHERE name = 'optimizer_features_enable';
VALUE
--------------------------------------------------------------------------------
8.0.0
8.0.3
8.0.4
8.0.5
...
9.0.0
9.0.1
9.2.0
...
10.1.0.4
10.1.0.5
...
11.2.0.1
11.2.0.2
11.2.0.3
...
12.2.0.1
12.2.0.1.1
35 행이 선택되었습니다.
Formula 9-1. I/O cost of multiblock read operations with workload statistics blocks mreadtim io_cost mbrc sreadtim
BEGIN dbms_output.put_line('dbfmbrc blocks seconds cpu');
FOR i IN 0..10
LOOP
l_dbfmbrc := power(2,i);
EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count = '||l_dbfmbrc;
EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH BUFFER_CACHE';
SELECT sum(decode(name, 'physical reads', value)),
sum(decode(name, 'CPU used by this session', value))
INTO l_starting_blocks, l_starting_cpu
FROM v$mystat ms JOIN v$statname USING (statistic#)
WHERE name IN ('physical reads','CPU used by this session');
l_starting_time := dbms_utility.get_time();
SELECT count(*) INTO l_count FROM t;
l_ending_time := dbms_utility.get_time();
SELECT sum(decode(name, 'physical reads', value)),
sum(decode(name, 'CPU used by this session', value))
INTO l_ending_blocks, l_ending_cpu
FROM v$mystat ms JOIN v$statname USING (statistic#)
WHERE name IN ('physical reads','CPU used by this session');
l_time := round((l_ending_time-l_starting_time)/100,1);
l_blocks := l_ending_blocks-l_starting_blocks;
l_cpu := l_ending_cpu-l_starting_cpu;
dbms_output.put_line(l_dbfmbrc||' '||l_blocks||' '||to_char(l_time)||' '||to_char(l_cpu)); END LOOP; END;
12.1 버전부터는 동적 샘플링이라는 용어 대신에 동적 통계라는 용어가 사용되지만 이 책에서는 옛날 이름인 동적 샘플링을 사용한다.
SQL> CREATE TABLE t_noidx (id, n1, n2, pad) AS
SELECT rownum, rownum,
cast(round(dbms_random.value(1,100)) AS VARCHAR2(100)),
cast(dbms_random.string('p',1000) AS VARCHAR2(1000))
FROM dual CONNECT BY level <= 1000 ;
테이블이 생성되었습니다.
SQL> CREATE TABLE t_idx (id CONSTRAINT t_idx_pk PRIMARY KEY, n1, n2, pad) AS SELECT * FROM t_noidx;
테이블이 생성되었습니다.
SELECT * FROM t_noidx t1, t_noidx t2 WHERE t1.id = t2.id AND t1.id < 19
SELECT * FROM t_idx t1, t_idx t2 WHERE t1.id = t2.id AND t1.id < 19
SELECT NVL(SUM(C1),0),
NVL(SUM(C2),0),
COUNT(DISTINCT C3),
NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0 END),0)
FROM (
SELECT 1 AS C1,
CASE WHEN "T1"."ID"<19 THEN 1 ELSE 0 END AS C2,
"T1"."ID" AS C3
FROM "CHRIS"."T_NOIDX" SAMPLE BLOCK (20 , 1) SEED (1) "T1"
) SAMPLESUB
SELECT NVL(SUM(C1),0),
NVL(SUM(C2),0),
NVL(SUM(C3),0)
FROM ( SELECT 1 AS C1,
1 AS C2,
1 AS C3
FROM "CHRIS"."T_IDX" "T1"
WHERE "T1"."ID"<19
AND ROWNUM <= 2500
) SAMPLESUB
BEGIN dbms_stats.gather_table_stats( ownname => user,
tabname => 't_noidx',
method_opt => 'for all columns size 1');
dbms_stats.gather_table_stats( ownname => user,
tabname => 't_idx',
method_opt => 'for all columns size 1',
cascade => true);
END;
SELECT * FROM t_idx WHERE id = 19
SELECT * FROM t_idx WHERE round(id) = 19
첫 번째의 경우 쿼리 옵티마이저는 컬럼 통계와 히스토그램을 기반으로 id=19 조건절의 선택도를 추정할 수 있다. 따라서 어떤 동적 샘플링도 필요로 하지 않는다. 그 대신 두 번째 쿼리에 대해 쿼리 옵티마이저는 round(id)=19 조건절의 선택도를 추론할 수 없다.
SELECT NVL(SUM(C1),0),
NVL(SUM(C2),0),
COUNT(DISTINCT C3)
FROM ( SELECT 1 AS C1,
CASE WHEN ROUND("T_IDX"."ID")=19 THEN 1 ELSE 0 END AS C2,
ROUND("T_IDX"."ID") AS C3
FROM "CHRIS"."T_IDX" SAMPLE BLOCK (20 , 1) SEED (1) "T_IDX"
) SAMPLESUB
SELECT * FROM t_idx WHERE id < 19 AND n1 < 19
SELECT NVL(SUM(C1),0),
NVL(SUM(C2),0)
FROM ( SELECT 1 AS C1,
CASE WHEN "T_IDX"."ID"<19 AND "T_IDX"."N1"<19 THEN 1 ELSE 0 END AS C2
FROM "CHRIS"."T_IDX" SAMPLE BLOCK (20 , 1) SEED (1) "T_IDX"
) SAMPLESUB
요약하면 일반적으로 레벌1과 2는 그다지 유용하지 않다는 사실을 알 수 있다.
1. 인덱스의 루트 블록을 엑세스 한다.
2. 브랜치 블록을 거쳐서 첫 번째 키가 포함된 리프 블록을 찾아간다.
3. 검색 기준을 충족하는 각각의 키에 대해 다음을 수행한다.
SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100;
SQL> SELECT * FROM t WHERE val1 = 11 AND val2 = 11;
-----------------------------------------------
| Id | Operation | Name | -----------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | T_VAL2_I |
-----------------------------------------------
1 - filter("VAL1"=11)
2 - access("VAL2"=11)
SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | T_VAL1_I |
-----------------------------------------------
1 - filter("VAL2"=11) 2 - access("VAL1"=11)
SQL> ALTER INDEX t_val1_i RENAME TO t_val3_i;
SQL> SELECT * FROM t WHERE val1 = 11 AND val2 = 11;
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | T_VAL2_I |
-----------------------------------------------
1 - filter("VAL1"=11) 2 - access("VAL2"=11)
CREATE TABLE t ( id NUMBER(10) PRIMARY KEY, class NUMBER(10), pad VARCHAR2(10) )
CREATE OR REPLACE VIEW v AS SELECT * FROM t WHERE f(class) = 1
CREATE OR REPLACE FUNCTION spy (id IN NUMBER, pad IN VARCHAR2) RETURN NUMBER AS
BEGIN dbms_output.put_line('id='||id||' pad='||pad);
RETURN 1;
END;
SQL> SELECT id, pad
2 FROM v
3 WHERE id BETWEEN 1 AND 5;
ID PAD
---------- ---------
1 DrMLTDXxxq 4 AszBGEUGEL
SQL> SELECT id, pad
2 FROM v
3 WHERE id BETWEEN 1 AND 5
4 AND spy(id, pad) = 1;
ID PAD
---------- ---------
1 DrMLTDXxxq
4 AszBGEUGEL
id=1 pad=DrMLTDXxxq
id=2 pad=XOZnqYRJwI
id=3 pad=nlGfGBTxNk
id=4 pad=AszBGEUGEL
id=5 pad=qTSRnFjRGb
SQL> SELECT id, pad
2 FROM v
3 WHERE id BETWEEN 1 AND 5
4 AND spy(id, pad) = 1;
ID PAD
---------- ---------
1 DrMLTDXxxq
4 AszBGEUGEL
id=1 pad=DrMLTDXxxq
id=4 pad=AszBGEUGEL
- 강좌 URL : http://www.gurubee.net/lecture/4388
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.