v$sqlarea之parse_calls及loads

---会话1

SQL> show user
USER is "TBL_BCK"

--会话1执行plsql代码
SQL> declare
  2  v_out pls_integer;
  3  begin
  4  for i in 1..1000 loop
  5  select count(a) into v_out from t_load;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

--会话2
SQL> select sql_text,loads,parse_calls from v$sqlarea where sql_id=(select nvl(sql_id,prev_s
ql_id) from v$session where sid=43);

SQL_TEXT
--------------------------------------------------------------------------------

     LOADS  parse_calls
----------  ---------
declare v_out pls_integer; begin for i in 1..1000 loop select count(a) into v_ou

t from t_load; end loop; end;
         1   1
        
--会话1重复执行上述的plsql代码        
SQL> declare
  2  v_out pls_integer;
  3  begin
  4  for i in 1..1000 loop
  5  select count(a) into v_out from t_load;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed. 

---会话2可以发现软解析loads还是1
SQL> /

SQL_TEXT
--------------------------------------------------------------------------------

     LOADS   parse_calls
----------   ----
declare v_out pls_integer; begin for i in 1..1000 loop select count(a) into v_ou

t from t_load; end loop; end;
         1   1

--会话3刷新共享池
SQL> alter system flush shared_pool;

System altered.      

--会话1重复执行刷新共享后的上述的plsql代码        
SQL> declare
  2  v_out pls_integer;
  3  begin
  4  for i in 1..1000 loop
  5  select count(a) into v_out from t_load;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed. 

--会话2可知load在原来的基础上累加，即++1
SQL> /

SQL_TEXT
--------------------------------------------------------------------------------

     LOADS    parse_calls
----------    ----
declare v_out pls_integer; begin for i in 1..1000 loop select count(a) into v_ou

t from t_load; end loop; end;
         2    1
        
小结:1,parse_calls仅会在硬解析增加值
    
     2,loads则会在刷新共享池后增加其值
    
             

 

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

转载于:http://blog.itpub.net/9240380/viewspace-762248/