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則會在重新整理共享池後增加其值