ptimizer_dynamic_sampling設定為4的作用。

SQL> create table test (lang varchar2(20),city varchar2(20));

表已建立。

已用時間:  00: 00: 00.06
SQL> begin
  2  for i in 1..10000 loop
  3  insert into test values('china','china');
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL 過程已成功完成。

已用時間:  00: 00: 01.20
SQL>  begin
  2   for i in 1..10000 loop
  3  insert into test values('english','english');
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL 過程已成功完成。

已用時間:  00: 00: 01.22
SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => 'NCSI',
  3                                  TABNAME          => 'TEST',
  4                                  ESTIMATE_PERCENT => 100,
  5                                  METHOD_OPT       => 'FOR ALL COLUMNS SIZE AUTO',
  6                                  CASCADE          => TRUE);
  7  END;
  8  /

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.32
SQL> select count(*) from test;

  COUNT(*)
----------
     20000

已用時間:  00: 00: 00.01
SQL> alter session set optimizer_dynamic_sampling=2;

會話已更改。

已用時間:  00: 00: 00.00
SQL> select * from test where lang='china' and city='english';

未選定行

已用時間:  00: 00: 00.01
SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic note'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
--------
EXPLAINED SQL STATEMENT:
------------------------
select * from test where lang='china' and city='english'

Plan hash value: 217508114

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| TEST |
----------------------------------

已選擇13行。

已用時間:  00: 00: 00.06
SQL> select * from test where lang='china' and city='english';

未選定行

已用時間:  00: 00: 00.00
SQL> select * from table(dbms_xplan.display_cursor(null,null,'all note'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
--------
SQL_ID  bbp7t90twf7p6, child number 0
-------------------------------------
select * from test where lang='china' and city='english'

Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    14 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |  5000 | 70000 |    14   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 /

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("LANG"='china' AND "CITY"='english'))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "LANG"[VARCHAR2,20], "CITY"[VARCHAR2,20]

已選擇28行。

已用時間:  00: 00: 00.05
SQL> alter session set optimizer_dynamic_sampling=4;

會話已更改。

已用時間:  00: 00: 00.00
SQL> select * from test where lang='china' and city='english';

未選定行

已用時間:  00: 00: 00.02
SQL> select * from table(dbms_xplan.display_cursor(null,null,'all note'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
--------
SQL_ID  bbp7t90twf7p6, child number 1
-------------------------------------
select * from test where lang='china' and city='english'

Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    14 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    14 |    14   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 /

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("LANG"='china' AND "CITY"='english'))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "LANG"[VARCHAR2,20], "CITY"[VARCHAR2,20]

Note
-----
   - dynamic sampling used for this statement

已選擇32行。

已用時間:  00: 00: 00.05