optimzier对于排序COST评估

                                                    OPTIMIZER对于排序COST评估

      在sql操作有排序的情况，比如group by ,order by的情况.oracle需要评估在排序中所使用的系统资源
取决于有多少数据需要排序,sort_area_size的大小，是否需要使用temp segment.对于数据是否能够在整个sort area
取决于每行数据的长度和需要排序的数据行数.如果需要temp segemnts,加上multi block的cost

  以下以查询为例

select object_name,count(object_id) from t2 group by object_name   --object_id上有索引

使用10053 跟踪

执行计划如下

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   710 |           |
| 1   |  HASH GROUP BY      |         |   40K | 1210K |   710 |  00:00:09 |
| 2   |   TABLE ACCESS FULL | T2      |   68K | 2034K |   287 |  00:00:04 |
--------------------------------------+-----------------------------------+

对于id 2是全表扫描，cost 为287,这个比较好理解，os使用表的块数来决定cost,可以在跟踪文件中发现

***********************
Table Stats::
  Table: T2  Alias: T2
    #Rows: 69438  #Blks:  1049  AvgRowLen:  101.00
Index Stats::
  Index: T2_ID  Col#: 4
    LVLS: 1  #LB: 154  #DK: 69438  LB/K: 1.00  DB/K: 1.00  CLUF: 1150.00
Access path analysis for T2
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T2[T2]
  Table: T2  Alias: T2
    Card: Original: 69438.000000  Rounded: 69438  Computed: 69438.00  Non Adjusted: 69438.00
  Access Path: TableScan
    Cost:  287.32  Resp: 287.32  Degree: 0
      Cost_io: 286.00  Cost_cpu: 22052371
      Resp_io: 286.00  Resp_cpu: 22052371
  Best:: AccessPath: TableScan
         Cost: 287.32  Degree: 1  Resp: 287.32  Card: 69438.00  Bytes: 0

  对于排序部分,

GROUP BY adjustment factor: 1.000000
GROUP BY cardinality:  41304.000000, TABLE cardinality:  69438.000000
    SORT ressource         Sort statistics
      Sort width:         418 Area size:      366592 Max Area size:    73400320
      Degree:               1
      Blocks to Sort: 366 Row size:     43 Total Rows:          69438
      Initial runs:   2 Merge passes:  1 IO Cost / pass:        200
      Total IO sort cost: 418      Total CPU sort cost: 74240611
      Total Temp space used: 2802000

 对于排序部分有主要的成本在 Total IO sort cost: 418  ?  之前已经有一次全表扫描了，照理说应该主要的cost已民经在id 2中已经计算了.而后面根据排序又计算一次?