oracle 修改执行环境

1.1        
修改执行环境

当多个应用使用数据库时(例如，由于数据库服务器整合)或单个应用基于模块需要不同环境时(例如，白天的OLTP和夜晚的批处理),单独一个环境无法满足所有场景是很常见的。这种情况下，在会话级别甚至SQL语句级别修改执行环境是恰当的。

1.1.1      工作原理

在会话级别修改执行环境与在SQL语句级别修改完全不同。因此，我会分别介绍两种情况。此外，我会介绍一些显示数据库实例、单个会话或子游标相关环境的动态性能视图。

1.1.1.1   会话级别

大多数初始化参数都可以在会话级别使用ALTER SESSION语句进行修改。因此，如果你有用户或者模块需要特殊配置，可以简单地在会话级别更改默认值。例如，根据连接到数据库的用户来设置执行环境，可以使用配置表和数据库触发器，如下面的例子所示。可以在exec_env_trigger.sq1脚本中找到该SQL语句：

CREATE TABLE exec_env_conf (

 
username  VARCHAR2(30),

 
parameter VARCHAR2(80),

 
value     VARCHAR2(512)

);

INSERT INTO exec_env_conf VALUES
('OPS$CHA', 'optimizer_mode', 'first_rows_10');

INSERT INTO exec_env_conf VALUES
('OPS$CHA', 'optimizer_dynamic_sampling', '0');

COMMIT;

CREATE OR REPLACE TRIGGER
execution_environment AFTER LOGON ON DATABASE

BEGIN

  FOR
c IN (SELECT parameter, value

           
FROM exec_env_conf

           
WHERE username = sys_context('userenv','session_user'))

 
LOOP

   
EXECUTE IMMEDIATE 'ALTER SESSION SET ' || c.parameter || '=' || c.value;

  END
LOOP;

END;

/

接着针对需要某个特别配置的每个用户，应该为每个初始化参数在配置表里插入一行数据。例如，当名叫Alberto的用户登录时，下面两个INSERT语句会在会话级别更改和定义两个参数：

INSERT INTO exec_env_conf VALUES('ALBERTO','optimizer_mode','first_rows_10');

INSERT INTO exec_env_conf
VALUES('ALBERTO','optimizer_dynamic_sampling','0');

当然，也可以为单个模式定义触发器，或执行基于诸如userenv上下文的其他检查。

1.1.1.2   sql语句级别

SQL语句级别的执行环境通过初始化参数hint来更改。由于使用hint,因此之前介绍的hint的行为和性能都会生效。

并不是所有的初始化参数组成的查询优化器配置都可以在SQL语句级别上修改。表11-3总结了在SQL语句级别上哪些参数和值与初始化参数hint一样可以实现相同的配置。请注意对于某些初始化参数(比如，cursor_sharing)来说，并不是所有的值都可以使用hint来设置。

表11-3 SQL语句级别hint可修改的查询优化器配置

1.1.1.3   动态性能视图

有以下三个动态性能视图提供执行环境的信息。

Ø  v$sys_optimizer_env 提供实例级别的执行环境信息。例如，可以找出哪个初始化参数没有设置成默认值：

select name, value,default_value FROM
v$sys_optimizer_env WHERE isdefault='NO';

Ø  v$ses_optimizer_env提供每个会话的执行环境信息。由于没有列提供某个初始化参数是否在系统或会话级别被修改的信息，因此可以使用以下查询达到目的：

select name, value FROM v$ses_optimizer_env
WHERE sid =124 AND isdefault ='NO'

MINUS

select name, value FROM
v$sys_optimizer_env;

Ø  v$sql_optimizer_env 提供库缓存中存在的每个子游标的执行环境信息。比如，以下查询可以查明同一父游标的两个子游标是否使用不同的执行环境：

select e0.name, e0.value AS value_child_0,
e1.value AS value_child_1

from v$sql_optimizer_env e0,
v$sql_optimizer_env e1

where e0.sql_id = e1.sql_id

and e0.sql_id = 'a5ksgfhw2v9s1'

and e0.child_number = 0

and e1.child_number = 1

and e0.name = e1.name

and e0.value e1.value;

1.1.2      何时使用

每当默认配置无法满足应用的某一部分或部分用户时，就应该修改默认配置。尽管在会话级别初始化参数随时都可以修改，但hint只有在修改SQL语句级别时才有效。

1.1.3      陷阱和谬误

可以将设置集中在数据库或应用中时，在会话级别修改执行环境是非常简单的。如果使用的应用或模块共享的连接池需要不同的执行环境，你需要额外注意。实际上，会话参数与物理连接有关。由于其他应用或模块会使用物理连接，每次从连接池获取到连接都要设置一次执行环境(当然代价很高，因为需要额外反复连接数据库)。

如果有的应用或者模块需要不同的执行环境，为了避免这种开销，应该使用不同的连接池和不同的用户。这样，就可以针对每个连接池使用单独的配置，并且通过定义不同的用户连接到数据库，你也许能够将配置集中到一个简单的数据库触发器中。

在SQL语句级别修改执行环境也存在与hint一样的误区和谬误。