ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 对比sql语句执行环境

oracle 对比sql语句执行环境

原创 Linux操作系统 作者:fufuh2o 时间:2010-06-12 23:49:55 0 删除 编辑

由于执行环境,
子游标无法共享

SQL> select distinct sid from v$mystat;

       SID
----------
       132

SQL> select distinct sid from v$mystat;

       SID
----------
       130
SQL> alter session set optimizer_mode=first_rows_10;(130)

Session altered.

 


SQL> select count(*) from t1;(sid 132)

  COUNT(*)
----------
      1999


select b.inst_id,b.sql_id,b.child_number.b.plaN_hash_value from gv$sql b where b.sql_text='select count(*) from t1'

#
set linesize 1000
 select a.inst_id,b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from gv$session a ,gv$sql b where a.sid=&sid and b.hash_value in (a.prev_hash_value)
and a.inst_id=b.inst_id;

SQL> show user
USER is "SYS"
SQL>
SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

SQL_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
PLAN_HASH_VALUE
---------------
5bc0v4my7dvr5 select count(*) from t1                  4235652837            0
     3724264953


SQL> select count(*) from t1;(130)

  COUNT(*)
----------
      1999
SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value);
Enter value for sid: 130
old   1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value)
new   1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=130 and b.hash_value in (a.sql_hash_value,a.prev_hash_value)

SQL_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
PLAN_HASH_VALUE
---------------
5bc0v4my7dvr5 select count(*) from t1                  4235652837            0
     3724264953

5bc0v4my7dvr5 select count(*) from t1                  4235652837            1
     3724264953

执行计划一样,plan_hash_value可以看出来,但确产生了 不同的子游标

 

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

#查环境变量 哪个不是默认的,针对当前session修改过
 SELECT name, value
 FROM v$ses_optimizer_env
 WHERE sid = 161 AND isdefault = 'NO'
 MINUS
 SELECT name, value
 FROM v$sys_optimizer_env;


#查看2个不同session环境变量
SQL>  SELECT e0.name, e0.value AS value_child_0, e1.value AS value_child_1
  2   FROM v$sql_optimizer_env e0, v$sql_optimizer_env e1
  3   WHERE e0.sql_id = e1.sql_id
  4   AND e0.sql_id = '&sql_id'
  5   AND e0.child_number = 0
  6   AND e1.child_number = 1
  7   AND e0.name = e1.name
  8   AND e0.value <> e1.value;

NAME                                     VALUE_CHILD_0
---------------------------------------- -------------------------
VALUE_CHILD_1
-------------------------
optimizer_mode                           all_rows
first_rows_10

 

  SELECT e0.inst_id,e0.name, e0.value AS value_child_0, e1.inst_id,e1.value AS value_child_1
   FROM gv$sql_optimizer_env e0, gv$sql_optimizer_env e1
    WHERE e0.sql_id = e1.sql_id
     AND e0.sql_id = '&sql_id'
    AND e0.child_number = 0
    AND e1.child_number = 1
    AND e0.name = e1.name
    AND e0.value <> e1.value;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-665125/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    427807