[20120425]PLAN_HASH_VALUE與 V$SQLAREA.txt

lfree發表於2012-04-26
SQL
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章