oracle sql 对比,oracle 对比sql语句执行环境

由于执行环境，

子游标无法共享

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;