진실 or 거짓 : CBO에게는 DUAL이 1건이 아니라 4072건이다.

DUAL에 통계정보가 일반적으로 없는 8i와 9i에서 DUAL 테이블의 UNION ALL 혹은 UNION 의 실행계획에서 cardinality가 1이 아닌 것으로 계산되어 Cost가 높게 계산되는 CBO 버그에 대해서 테스트해봤다..

 

 

-- 다음과 같이 여러가지 경우의 DUAL UNION (ALL) DUAL에 대해 10053 trace를 수행함.
alter session set db_file_multiblock_read_count=32;
alter session set events '10053 trace name context forever,level 1';

 

-- 8.1.7.4는 UNION ALL과 UNION 이 동일하게 card=41임.

-- Block의 수는 1개로 인식됨.

 

Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production

DB_FILE_MULTIBLOCK_READ_COUNT = 32

QUERY
SELECT /*+ all_rows */ 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: DUAL   Alias: DUAL
  TOTAL ::  (NOT ANALYZED)    CDN: 41  NBLKS:  1  TABLE_SCAN_CST: 1  AVG_ROW_LEN:  100
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: DUAL     ORIG CDN: 41  CMPTD CDN: 41
  Access path: tsc  Resc:  1  Resp:  1
  BEST_CST: 1.00  PATH: 2  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: DUAL [DUAL]
Best so far: TABLE#: 0  CST:          1  CDN:         41  BYTES:          0
Final:
  CST: 1  CDN: 41  RSC: 1  RSP: 1  BYTES: 0

 

-- 9.2.0.7은 UNION ALL과 UNION, all_rows와 first_rows_1에 따라 다른 결과
-- first_rows_1/UNION ALL인 경우에만 card=1 이고 나머지 경우는 모두 4072임.

-- 4072건으로 인식되는 경우 블럭수가 100개로 인식되는 Case도 있음.
-- 특이사항) 본문에는 없지만 테스트결과
--           first_rows_10이면 card=10, first_rows_100이면 card=100, first_rows_1000이면 card=1000 임.

-- ** 9i에서 dynamic_sampling(2) 힌트를 사용하면 정상적으로 card=1 의 결과를 얻을 수 있다.

 

-- 1. all_rows/UNION ALL

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

DB_FILE_MULTIBLOCK_READ_COUNT = 32

QUERY
SELECT /*+ all_rows */ 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: DUAL   Alias: DUAL
  TOTAL ::  (NOT ANALYZED)    CDN: 4072  NBLKS:  100  AVG_ROW_LEN:  100
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: DUAL     ORIG CDN: 4072  ROUNDED CDN: 4072  CMPTD CDN: 4072
  Access path: tsc  Resc:  8  Resp:  8
  BEST_CST: 8.00  PATH: 2  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  DUAL[DUAL]#0
Best so far: TABLE#: 0  CST:          8  CDN:       4072  BYTES:          0
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 8  CDN: 4072  RSC: 8  RSP: 8  BYTES: 0
  IO-RSC: 8  IO-RSP: 8  CPU-RSC: 0  CPU-RSP: 0

 

-- 2. first_rows_1/UNION ALL

QUERY
SELECT /*+ first_rows(1) */ 4 C FROM DUAL UNION ALL SELECT /*+ first_rows(1) */ 6 C FROM DUAL

-- 2.1 case 1
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: DUAL   Alias: DUAL
  TOTAL ::  (NOT ANALYZED)    CDN: 4072  NBLKS:  100  AVG_ROW_LEN:  100
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: DUAL     ORIG CDN: 4072  ROUNDED CDN: 4072  CMPTD CDN: 4072
  Access path: tsc  Resc:  8  Resp:  8
  BEST_CST: 8.00  PATH: 2  Degree:  1

-- 2.2 case 2
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
  TABLE: DUAL     ORIG CDN: 1  ROUNDED CDN: 1  CMPTD CDN: 1
  Access path: tsc  Resc:  2  Resp:  2
  BEST_CST: 2.00  PATH: 2  Degree:  1
***********************

-- 2.3 final Decision
***********************
Join order[1]:  DUAL[DUAL]#0
Best so far: TABLE#: 0  CST:          2  CDN:          1  BYTES:          0
Final - First K Rows Plan:
  JOIN ORDER: 1
  CST: 2  CDN: 1  RSC: 2  RSP: 2  BYTES: 0
  IO-RSC: 2  IO-RSP: 2  CPU-RSC: 0  CPU-RSP: 0
  First K Rows Plan

--3. all_rows/UNION

QUERY
SELECT /*+ all_rows */ 6 C FROM DUAL UNION  SELECT 7 C FROM DUAL

***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  DUAL[DUAL]#0
Best so far: TABLE#: 0  CST:          8  CDN:       4072  BYTES:          0
    SORT resource      Sort statistics
      Sort width:            3 Area size:       43008 Max Area size:       43008   Degree: 1
      Blocks to Sort:       10 Row size:           10 Rows:       4072
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         14
      Total IO sort cost: 12
      Total CPU sort cost: 0
      Total Temp space used: 0
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 20  CDN: 4072  RSC: 20  RSP: 20  BYTES: 0
  IO-RSC: 20  IO-RSP: 20  CPU-RSC: 0  CPU-RSP: 0

--4. first_rows_1/UNION

-- UNION은 SORT OPERATION이 수행되므로 이 경우 CBO는 'All Rows Plan'만 고려되었음.

QUERY
SELECT /*+ first_rows(1) */ 8 C FROM DUAL UNION SELECT /*+ first_rows(1) */ 9 C FROM DUAL

***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  DUAL[DUAL]#0
Best so far: TABLE#: 0  CST:          8  CDN:       4072  BYTES:          0
    SORT resource      Sort statistics
      Sort width:            3 Area size:       43008 Max Area size:       43008   Degree: 1
      Blocks to Sort:       10 Row size:           10 Rows:       4072
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         14
      Total IO sort cost: 12
      Total CPU sort cost: 0
      Total Temp space used: 0
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 20  CDN: 4072  RSC: 20  RSP: 20  BYTES: 0
  IO-RSC: 20  IO-RSP: 20  CPU-RSC: 0  CPU-RSP: 0

 

 

-- 10.2.0.3 (FAST DUAL)

-- optimizer_mode, union all 혹은 union, _optimizer_cost_model=(io,cpu) 에 관계없이 모두 card=1
-- (dual의 통계정보 존재 여부와도 상관 없음)
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

****************
QUERY BLOCK TEXT
****************
SELECT 1 FROM DUAL
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$2 nbfros=1 flg=0
  fro(0): flg=0 objn=258 hint_alias="DUAL"@"SEL$2"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using WORKLOAD Stats
  CPUSPEED: 1023 millions instructions/sec
  SREADTIM: 1 milliseconds
  MREADTIM: 1 millisecons
  MBRC: 16.000000 blocks
  MAXTHR: 525863936 bytes/sec
  SLAVETHR: 3824640 bytes/sec
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: DUAL  Alias: DUAL
    #Rows: 1  #Blks:  1  AvgRowLen:  2.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: DUAL  Alias: DUAL    
    Card: Original: 1  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  2.01  Resp: 2.01  Degree: 0
      Cost_io: 2.00  Cost_cpu: 7271
      Resp_io: 2.00  Resp_cpu: 7271
  Best:: AccessPath: TableScan
         Cost: 2.01  Degree: 1  Resp: 2.01  Card: 1.00  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  DUAL[DUAL]#0
***********************
Best so far: Table#: 0  cost: 2.0065  card: 1.0000  bytes: 0
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 2.0065  Degree: 1  Card: 1.0000  Bytes: 0
  Resc: 2.0065  Resc_io: 2.0000  Resc_cpu: 7271
  Resp: 2.0065  Resp_io: 2.0000  Resc_cpu: 7271