ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120425]PLAN_HASH_VALUE与 V$SQLAREA.txt

[20120425]PLAN_HASH_VALUE与 V$SQLAREA.txt

原创 Linux操作系统 作者:lfree 时间:2012-04-26 10:09:26 0 删除 编辑
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下看执行计划有可能看错!




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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2284
  • 访问量
    6023279