SQL*Plus: Release 10.2.0.1.0 - Production on 일 5월 1 10:31:17

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> create table t

2  as

3  select * from all_objects;

테이블이 생성되었습니다.

SQL> create index t_owner_idx on t(owner);

인덱스가 생성되었습니다.

SQL> select count(*) from all_objects;

COUNT(*)

----------

50449

SQL> desc all_objects;

OWNER                                     NOT NULL VARCHAR2(30)

OBJECT_NAME                               NOT NULL VARCHAR2(30)

SUBOBJECT_NAME                                     VARCHAR2(30)

OBJECT_ID                                 NOT NULL NUMBER

DATA_OBJECT_ID                                     NUMBER

OBJECT_TYPE                                        VARCHAR2(19)

CREATED                                   NOT NULL DATE

LAST_DDL_TIME                             NOT NULL DATE

TIMESTAMP                                          VARCHAR2(19)

STATUS                                             VARCHAR2(7)

TEMPORARY                                          VARCHAR2(1)

GENERATED                                          VARCHAR2(1)

SECONDARY                                          VARCHAR2(1)

SQL> begin

2    dbms_stats.gather_table_stats(user, 'T'

3      , method_opt=>'for all columns size 1');

4  end;

5  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> alter session set "_optimizer_cost_model" = io;

세션이 변경되었습니다.

SQL> set autotrace traceonly exp;

SQL>

SQL> select /*+ index(t) */ * from t where owner = 'SYS';

Execution Plan

----------------------------------------------------------

---------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost  |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |  2102 |   190K|    62 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |  2102 |   190K|    62 |

|*  2 |   INDEX RANGE SCAN          | T_OWNER_IDX |  2102 |       |     5 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='SYS')

Note

-----

- 'PLAN_TABLE' is old version

- cpu costing is off (consider enabling it)

SQL> set autotrace off;

SQL> column index_name format a12

SQL> column clustering_factor format 999999 heading "CLUSTERING|_FACTOR   "

SQL> column "인덱스 스캔 비용" format 9999.99999 heading "인덱스  |스캔비용"

SQL> column "총 테이블 액세스 비용" format 9999.99999 heading "총 테이블  |액세스 비용"

SQL> select i.blevel, i.leaf_blocks, c.num_distinct, i.clustering_factor

2       , 1 + (i.leaf_blocks * 1/c.num_distinct) "인덱스 스캔 비용"

3       , 1 + (i.leaf_blocks * 1/c.num_distinct)

4           + (i.clustering_factor * 1/c.num_distinct) "총 테이블 액세스 비용"

5  from   user_indexes i, user_tab_col_statistics c

6  where  i.index_name = 'T_OWNER_IDX'

7  and    c.table_name = i.table_name

8  and    c.column_name = 'OWNER';

CLUSTERING    인덱스   총 테이블

BLEVEL LEAF_BLOCKS NUM_DISTINCT _FACTOR       스캔비용 액세스 비용

---------- ----------- ------------ ---------- ----------- -----------

1         117           24       1353     5.87500    62.25000

SQL> -- Full Scan에 의한 테이블 액세스 비용

SQL> select blocks from user_tables where table_name = 'T';

BLOCKS

----------

713

SQL>

SQL> set autotrace traceonly exp;

SQL>

SQL> alter session set db_file_multiblock_read_count = 2;

세션이 변경되었습니다.

SQL>

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

----------------------------------------------------------

----------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost  |

----------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  2102 |   190K|   271 |

|*  1 |  TABLE ACCESS FULL| T    |  2102 |   190K|   271 |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

Note

-----

- 'PLAN_TABLE' is old version

- cpu costing is off (consider enabling it)

SQL>

SQL> alter session set db_file_multiblock_read_count = 4;

세션이 변경되었습니다.

SQL>

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

----------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost  |

----------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  2102 |   190K|   173 |

|*  1 |  TABLE ACCESS FULL| T    |  2102 |   190K|   173 |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

Note

-----

- 'PLAN_TABLE' is old version

- cpu costing is off (consider enabling it)

SQL>

SQL> alter session set db_file_multiblock_read_count = 8;

세션이 변경되었습니다.

SQL>

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

----------------------------------------------------------

----------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost  |

----------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  2102 |   190K|   110 |

|*  1 |  TABLE ACCESS FULL| T    |  2102 |   190K|   110 |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

Note

-----

- 'PLAN_TABLE' is old version

- cpu costing is off (consider enabling it)

SQL>

SQL>

SQL> alter session set db_file_multiblock_read_count = 16;

세션이 변경되었습니다.

SQL>

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

----------------------------------------------------------

----------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost  |

----------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  2102 |   190K|    70 |

|*  1 |  TABLE ACCESS FULL| T    |  2102 |   190K|    70 |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

Note

-----

- 'PLAN_TABLE' is old version

- cpu costing is off (consider enabling it)

SQL>

SQL>

SQL> alter session set db_file_multiblock_read_count = 32;

세션이 변경되었습니다.

SQL>

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

----------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost  |

----------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  2102 |   190K|    45 |

|*  1 |  TABLE ACCESS FULL| T    |  2102 |   190K|    45 |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

Note

-----

- 'PLAN_TABLE' is old version

- cpu costing is off (consider enabling it)

SQL>

SQL>

SQL> alter session set db_file_multiblock_read_count = 64;

세션이 변경되었습니다.

SQL>

SQL>

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

----------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost  |

----------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  2102 |   190K|    29 |

|*  1 |  TABLE ACCESS FULL| T    |  2102 |   190K|    29 |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

Note

-----

- 'PLAN_TABLE' is old version

- cpu costing is off (consider enabling it)

SQL>

SQL> alter session set db_file_multiblock_read_count = 128;

세션이 변경되었습니다.

SQL>

SQL>

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

----------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost  |

----------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  2102 |   190K|    19 |

|*  1 |  TABLE ACCESS FULL| T    |  2102 |   190K|    19 |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

Note

-----

- 'PLAN_TABLE' is old version

- cpu costing is off (consider enabling it)