【调优篇基本原理】优化器相关参数配置

最新推荐文章于 2021-11-17 23:59:32 发布

cixlv48826

最新推荐文章于 2021-11-17 23:59:32 发布

阅读量558

点赞数

文章标签：运维数据库

看看影响优化器的一些参数(本例的数据库为dedicated server)：

SQL> show parameter optimizer_

NAME TYPE VALUE

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

optimizer_dynamic_sampling integer 2

optimizer_features_enable string 10.2.0.4

optimizer_index_caching integer 0

optimizer_index_cost_adj integer 100

optimizer_mode string ALL_ROWS

optimizer_secure_view_merging boolean TRUE

optimizer_mode是优化器模式，10g默认是all_rows，也可以设置为其他参数：

SQL> alter session set optimizer_mode = 'asd';

ERROR:

ORA-00096: invalid value asd for parameter optimizer_mode, must be from among

first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows,

all_rows, choose, rule

从这里的提示可以看出优化模式的选择值。一般来讲，需要获取所有记录更重要，应该吧参数设置为all_rows，很多应用都是这么设置的，包括olap和oltp，一些对相应时间的要求非常高的系统，可能会修改这个值，使用first_rows_n这里的n根据具体应用来选择。

optimizer_dynamic_sampling

这个值的设置范围是0-10，如果optimizer_features_enable设置为10.0.0或者以上，默认为2，9.2.0则为1，9.0.1或者以下则为0，可以在系统级别和会话级别来修改这个参数。

optimizer_index_caching

这个参数影响嵌套循环连接的探测索引的代价，0-100表示在使用嵌套循环或这in-list迭代时将索引缓存在buffer cache的百分比。例如，设置为100，则优化器认为100%能在内存中找到索引数据，会按照这个设定来计算cost和选择执行计划。

optimizer_index_cost_adj

和optimizer_index_caching一样，这个参数也是cbo用来计算cost的，这个参数可以用来调整使用索引的代价，默认值是100，范围是1-10000，它表示索引扫描和全表扫描的比值。例如设置为10，意味着使用通过索引路径访问是正常通过索引路径访问的10%（oracle 10g performace tuning guide），也即可以设置索引参与计算代价的不同值。

optimizer_secure_view_merging

这个参数控制视图合并，默认值是true，在不影响安全问题的情况下允许视图合并，如果设置为false，则在任何情况下允许视图合并。

SQL> show parameter cursor_sharing

NAME TYPE VALUE

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

cursor_sharing string EXACT

SQL>

cursor_sharing

这个参数用来设置sql如何来使用绑定变量，有三个值：SIMILAR, EXACT, FORCE，默认是EXACT，一般来说不会去更改这个设置，即使shared pool hit miss较大，也应该首先去思考是否需要优化应用。这个参数的测试见：

http://space.itpub.net/16179598/viewspace-627268

SQL> show parameters pga_aggregate_target;

NAME TYPE VALUE

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

pga_aggregate_target big integer 187M

SQL>

10g的pga_aggregate_target默认设置为sga_target 的1/3，这个参数指定了pga能达到的期望值，我们知道pga主要用来为连接的用户储存会话的相关内容，其中比较重要的是pga用来做排序操作，例如一些基于排序的操作：order by ,group by ,rollup和开窗函数(window functions)，hash-join，bitmap merge，bitmap create，还有一些使用bulk或者load来写buffer的操作等。

有两个视图来帮助调整这个参数：v$pga_target_advice和v$pga_target_advice_histogram

通过查看视图v$pgastat可以看到pga的一些属性或者状态值。

Oracle推荐的pga_aggregate_target：

Oltp：（系统物理内存大小*80）*20%

Dss：（系统物理内存大小*80）*50%

下面来看看有关pga的一些专用区域大小的具体设置：

SQL> show parameter area_size

NAME TYPE VALUE

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

bitmap_merge_area_size integer 1048576

create_bitmap_area_size integer 8388608

hash_area_size integer 131072

sort_area_size integer 65536

workarea_size_policy string AUTO

SQL>

workarea_size_policy

对于10g来说，这个参数默认为AUTO，则必须要设置pga_aggregate_target的值，且必须至少大于10M，如果设置为0，即让数据自动调整的话，会出现下列错误：

SQL> alter system set pga_aggregate_target = 0 scope=both;

alter system set pga_aggregate_target = 0 scope=both

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

sort_area_size

排序区大小，设定oracle一次排序使用的最大内存数量，这个参数设置对排序性能比较重要。

SQL> select name,value

2 from v$sysstat a where a.NAME

3 like '%sort%';

NAME VALUE

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

sorts (memory) 1019434

sorts (disk) 2

sorts (rows) 161842945

可以查看磁盘排序和内存排序的比率来考虑是否设置了较为合理的排序区大小。

hash_area_size

这个参数设置单个会话的hash内存空间大小，可以在实例和会话级修改，默认值是sort_area_size的两倍，上面的数值也可以看出来。对于10g来说dedicated server的hash area是从pga中分配的，而multi-threaded server的hash area是从uga中分配的，如果设置了PGA_AGGRATE_TARGET参数，则将自动管理hsah area大小。