V$SQLAREA相当于V$SQL的聚集，而各个子光标的执行计划的PLAN_HASH_VALUE可能不同，而v$sqlarea如何记录的呢？

测试环境：

select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

2.测试脚本:

drop table t purge ;

create table t as select rownum id,'test' name from dual connect by level<=999;

insert into t select 1000,'test' from dual connect by level<=1001;

commit ;

create index  i_t_id on t(id);

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t',cascade=>true,estimate_percent=>100,method_opt=>'for columns id size 254')

--执行脚本

spool aa.txt

alter system flush shared_pool;

variable a NUMBER

exec :a :=41;

select /*+ bind_aware */ * from t where id = :a;

col sql_id new_value sql_id

select sql_id, address from v$sqlarea where sql_text = 'select /*+ bind_aware */ * from t where id = :a';

select  plan_hash_value from   v$sqlarea where   sql_id='&&sql_id';

exec :a :=1000;

select /*+ bind_aware */ * from t where id = :a;

select  plan_hash_value from   v$sqlarea where   sql_id='&&sql_id';

exec :a :=41;

select /*+ bind_aware */ * from t where id = :a;

select  plan_hash_value from   v$sqlarea where   sql_id='&&sql_id';

exec :a :=1000;

select /*+ bind_aware */ * from t where id = :a;

select  plan_hash_value from   v$sqlarea where   sql_id='&&sql_id';

3.执行结果如下：

$ grep -A 2 PLAN_HASH_VALUE aa.txt

PLAN_HASH_VALUE

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

     4153437776

--

PLAN_HASH_VALUE

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

     4153437776

--

PLAN_HASH_VALUE

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

     4153437776

--

PLAN_HASH_VALUE

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

     4153437776

--可以发现V$SQLAREA中PLAN_HASH_VALUE并没有变化：

4.每次执行完成后休息一定的秒数：

spool aa.txt

alter system flush shared_pool;

variable a NUMBER

exec :a :=41;

select /*+ bind_aware */ * from t where id = :a;

exec dbms_lock.sleep(&1);

col sql_id new_value sql_id

select sql_id, address from v$sqlarea where sql_text = 'select /*+ bind_aware */ * from t where id = :a';

select  plan_hash_value from   v$sqlarea where   sql_id='&&sql_id';

exec :a :=1000;

select /*+ bind_aware */ * from t where id = :a;

exec dbms_lock.sleep(&1);

select  plan_hash_value from   v$sqlarea where   sql_id='&&sql_id';

exec :a :=41;

select /*+ bind_aware */ * from t where id = :a;

exec dbms_lock.sleep(&1);

select  plan_hash_value from   v$sqlarea where   sql_id='&&sql_id';

exec :a :=1000;

select /*+ bind_aware */ * from t where id = :a;

exec dbms_lock.sleep(&1);

select  plan_hash_value from   v$sqlarea where   sql_id='&&sql_id';

--sleep 1秒
SQL> @b3.sql 1

$ grep -A 2 PLAN_HASH_VALUE aa.txt

PLAN_HASH_VALUE

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

     4153437776

--

PLAN_HASH_VALUE

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

     4153437776

--

PLAN_HASH_VALUE

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

     4153437776

--

PLAN_HASH_VALUE

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

     1601196873

--可以发现V$SQLAREA中PLAN_HASH_VALUE最后一次发生了变化：
--仅仅在中间插入一行exec dbms_lock.sleep(1);看来理论讲多数情况下V$SQLAREA中记录的PLAN_HASH_VALUE是最后执行一次的计划hash值。

5.注解脚本刷新共享池的语句在执行：
@b3.sql 1

$ grep -A 2 PLAN_HASH_VALUE aa.txt

PLAN_HASH_VALUE

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

     4153437776

--

PLAN_HASH_VALUE

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

     1601196873

--

PLAN_HASH_VALUE

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

     4153437776

--

PLAN_HASH_VALUE

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

     1601196873

--可以发现V$SQLAREA中PLAN_HASH_VALUE每次都发生变化。

总结：
oracle内部的一些机制不是很清楚，不过多数情况下V$SQLAREA中记录的PLAN_HASH_VALUE是最后执行一次的计划hash值。
如果是不同的用户访问自己的表情况如何呢？估计应该也是一样。

1.建立用户：

CREATE USER TEST IDENTIFIED BY test123

  DEFAULT TABLESPACE USERS

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

  -- 2 System Privileges for TEST 

  GRANT CREATE TABLE TO TEST;

  GRANT CREATE SESSION TO TEST;

ALTER USER TEST QUOTA UNLIMITED ON USERS;

create table test.emp as select * from scott.emp ;

2.在scott用户下执行：

SQL> variable a number ;

SQL> exec :a := 7369;

PL/SQL procedure successfully completed.

SQL> select * from emp where empno=:a;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

SQL> col sql_id new_value sql_id

SQL> select sql_id, address from v$sqlarea where sql_text = 'select * from emp where empno=:a';

SQL_ID        ADDRESS

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

0m472wx7184s6 00000000995373D0

SQL> SELECT parsing_schema_id, parsing_schema_name, parsing_user_id, plan_hash_value FROM v$sqlarea WHERE sql_id = '&&sql_id';

old   1: SELECT parsing_schema_id, parsing_schema_name, parsing_user_id, plan_hash_value FROM v$sqlarea WHERE sql_id = '&&sql_id'

new   1: SELECT parsing_schema_id, parsing_schema_name, parsing_user_id, plan_hash_value FROM v$sqlarea WHERE sql_id = '0m472wx7184s6'

PARSING_SCHEMA_ID PARSING_SCHEMA_NAME            PARSING_USER_ID PLAN_HASH_VALUE

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

               84 SCOTT                                       84      2949544139

3.在test用户下执行，由于我没有建立索引,执行计划会不同。

SQL> variable a number ;

SQL> exec :a := 7369;

PL/SQL procedure successfully completed.

SQL> select * from emp where empno=:a;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

--回到scott用户查询：

SQL> SELECT parsing_schema_id, parsing_schema_name, parsing_user_id, plan_hash_value FROM v$sqlarea WHERE sql_id = '&&sql_id';

old   1: SELECT parsing_schema_id, parsing_schema_name, parsing_user_id, plan_hash_value FROM v$sqlarea WHERE sql_id = '&&sql_id'

new   1: SELECT parsing_schema_id, parsing_schema_name, parsing_user_id, plan_hash_value FROM v$sqlarea WHERE sql_id = '0m472wx7184s6'

PARSING_SCHEMA_ID PARSING_SCHEMA_NAME            PARSING_USER_ID PLAN_HASH_VALUE

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

               96 TEST                                        96      3956160932

--可以发现PLAN_HASH_VALUE发生了变化，并且PARSING_SCHEMA_NAME也跟者变化。
--不过这样在toad中SGA trace下看执行计划有可能看错！