分类目录归档：Oracle性能优化

method_opt
Accepts:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]…]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms.
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.

举例说明：
method_opt => ‘FOR COLUMNS size 254 object_id’ 收集objct_id列直方图
method_opt => ‘FOR COLUMNS size 1 object_id’ 删除object_id列直方图
method_opt => ‘for all columns size repeat’ 重新分析现有直方图
method_opt => ‘for all columns size auto’ oracle决定收集哪些列的直方图（需要设置table monitoring）
method_opt => ‘for all columns size skewonly’ oracle分析所有列的分布情况，生成直方图
method_opt => ‘FOR COLUMNS object_id size SKEWONLY’ 收集object_id列分布情况，生成直方图
method_opt => ‘FOR all INDEXED COLUMNS size SKEWONLY’ 收集index列分布情况，并生成直方图

granularity
Granularity of statistics to collect (only pertinent if the table is partitioned).
‘ALL’ – gathers all (subpartition, partition, and global) statistics
‘AUTO’- determines the granularity based on the partitioning type. This is the default value.
‘DEFAULT’ – gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the ‘GLOBAL AND PARTITION’ for this functionality. Note that the default value is now ‘AUTO’.
‘GLOBAL’ – gathers global statistics
‘GLOBAL AND PARTITION’ – gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
‘PARTITION ‘- gathers partition-level statistics
‘SUBPARTITION’ – gathers subpartition-level statistics.

options
Further specification of which objects to gather statistics for:
GATHER: Gathers statistics on all objects in the schema.
GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.
GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.
GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.
LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.
LIST STALE: Returns list of stale objects as determined by looking at the *_tab_modifications views.
LIST EMPTY: Returns list of objects which currently have no statistics.

在有些执行计划中，可以会看到 BITMAP CONVERSION FROM ROWIDS这样的东东，也许你会感觉奇怪，我没有使用位图索引怎么出现了bitmap。
我通过一个sql和大家分析下原因：
sql语句为：

SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (  SELECT module_no, MAX (id) AS id
                    FROM vasoa.tab_moa_historypage t
                   WHERE t.company_id = :1 AND t.user_id = :2 AND t.TYPE = :3
                GROUP BY module_no
                ORDER BY id DESC) a
         WHERE ROWNUM <= :4) b
WHERE b.rn >= :5

执行计划为：

表结构为：

目前索引情况：

通过观察执行计划的图片可以发现，使用了 BITMAP CONVERSION FROM ROWIDS，其目的是：.oracle将btree索引中获得的rowid信息通过BITMAP CONVERSION FROM ROWIDS的步骤转换成bitmap进行匹配,然后匹配完成后通过BITMAP CONVERSION TO ROWIDS再转换出rowid获得数据或者回表获得数据。这种想象出现的原因为：oracle的cbo是根据cost来决定大小来选择合适的执行计划，当它计算获得通过bitmap的方式执行的时候cost会更小，它就会选择使用这样的执行计划。一般出现这样的情况，都是因为对表建立的不适当的index导致，特别是对表中的唯一度不高的列建立了index，然后oracle就有可能选择两个这样的列转为为bitmap来执行。根据oracle的执行计划，肯定是cost最小的，但是它很多时候忽略了一致性读等其他条件，导致这个执行计划并非像oracle想象的那样最优，因为把btree index转为为bitmap index执行，需要消耗更多的cpu，特别是在cpu比较紧张的系统中，所以这样的情况如果发生在oltp系统中，一般都需要解决。
本例的解决办法是删除唯一度低的index，建立组合index。分别删除COMPANY_ID与USER_ID上的index，建立COMPANY_ID和USER_ID的组合index，执行计划如下：

1、V$LOCK视图结构

2、其它相关视图说明

表3

表4