ANBOB™

高parse calls表示这些SQL不可以re-used 并且每次执行都要重新解析，理想的最佳状态是一次解析多次执行，你也知道频繁的解析会给library cache带来沉重的负担， 一个sql的执行必须先parse，解析过程包含sql 加载到shared pool, 语法检查， 表列权限等 字典的检查， 查询转换优化，扩展私有、共享sql内存区， 生成执行计划等等，这通常是hard parse， 相对较轻的是soft parse也只是无需加载sql 到shared pool, 还有一个解析当cursor 在SGA和session PGA的私有sql区里，cursor游标可以拿来直接使用，叫做softer soft parse，这里所说的游标 是library cache handle ,而不是pl/sql cursor, Softer soft parse通常与shared_pool_size和sesssion_cached_cursor有关 ，还有web app 使用了connect pool时session回收对 private sql area 中的cursor都有影响。

通常我们可以使用下面的sql 排查 parse calls大， 执行解析比例较小的sql

prompt 
prompt ********************************************************** 
prompt  SQL High parse calls 
prompt ********************************************************** 
prompt 
select 
   sql_text, 
   parse_calls, 
   executions 
from 
  v$sqlarea 
where parse_calls > 300 
and  executions < 2*parse_calls 
and executions > 1; 

在11.2.0.3中使用很方便，但后来升级到11.2.0.4后，偶尔使用上面的查询发现好多parse_calls 和executes比例接近1:1, 开始怀疑是11.2.0.4的bug, 也有去maclean的坛子讨论，现在把问题整理一下。

--####################### 11203

sys@ANBOB>alter system flush shared_pool;
System altered.

sys@ANBOB>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*weejar11203*/%';
no rows selected

sys@ANBOB>conn weejar/weejar
Connected.

weejar@ANBOB>select /*anbob11203*/ count(*) from test;
            COUNT(*)
--------------------
                   1
-- 执行同样sql 5次

weejar@ANBOB>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*anbob11203*/%';

     LOADED_VERSIONS        OPEN_VERSIONS           EXECUTIONS                LOADS          PARSE_CALLS
-------------------- -------------------- -------------------- -------------------- --------------------
                   1                    1                    5                    1                    3

weejar@ANBOB>show parameter session_cached

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     50
weejar@ANBOB>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

############  11204
sys@ORA11204>conn weejar;
Enter password:
Connected.
weejar@ORA11204>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*anbob11204*/%';
no rows selected

weejar@ORA11204>select /*anbob11204*/ count(*) from test1;
            COUNT(*)
--------------------
                   0
-- 同样执行5次


weejar@ORA11204>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*anbob11204*/%';

     LOADED_VERSIONS        OPEN_VERSIONS           EXECUTIONS                LOADS          PARSE_CALLS
-------------------- -------------------- -------------------- -------------------- --------------------
                   1                    1                    5                    1                    5

sys@ORA11204>show parameter session_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     50
sys@ORA11204>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

不对发现11.2.0.4 v$sql.parse_calls和11.2.0.3版本的显示不一样，后来在多个版本做了测试12.1.0.1, 10.2.0.4, 10.2.0.5 发现只有11.2.0.3 该字段更晚让我们理解。我也有对11.2.0.3 做10046 event, 显示 parse 数和executes 是1:1的，不过怀疑是10046的干扰，就像MACLEAN也怀疑是SQLPLUS 问题一样，我后来还写了一段java 用jdbc调 用

import java.sql.*;
public class parsetest{

public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@192.168.168.231:1521:ora11204";
    String username = "weejar";
    String password = "weejar";

    Class.forName(driver); 
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }

  public static int countRows(Connection conn, String tableName) throws SQLException {
    // select the number of rows in the table
    Statement stmt = null;
    ResultSet rs = null;
    int rowCount = -1;
    try {
      stmt = conn.createStatement();
          for (int i =0; i<10; i++){
            rs = stmt.executeQuery("SELECT /*ojdbc_parse*/ COUNT(*) FROM " + tableName);
          }
      
      // get the number of rows from the result set
      rs.next();
      rowCount = rs.getInt(1);
    } finally {
      rs.close();
      stmt.close();
    }

    return rowCount;
  }


 public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      String tableName = "test1";
      System.out.println("tableName=" + tableName);
      System.out.println("conn=" + conn);
          
          System.out.println("rowCount=" + countRows(conn, tableName));
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      // release database resources
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

用记事本编写手动编译执行

D:\>set CLASSPATH=E:\oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar;%CLASSPATH%
D:\>set CLASSPATH
CLASSPATH=E:\oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar;.;D:\jdk1_7\jre\lib\rt.jar;D:\jdk1_7\lib;D:\jdk1_7\lib\tools.jar

D:\>javac parsetest.java

D:\>java parsetest
tableName=test1
conn=oracle.jdbc.driver.T4CConnection@2f0e07
rowCount=0
-- 11204
weejar@ORA11204>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'SELECT /*ojdbc_parse*/%';

     LOADED_VERSIONS        OPEN_VERSIONS           EXECUTIONS                LOADS          PARSE_CALLS
-------------------- -------------------- -------------------- -------------------- --------------------
                   1                    0                   10                    1                   10
-- 11203
    sys@ANBOB>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'SELECT /*ojdbc_parse*/%';

         LOADED_VERSIONS        OPEN_VERSIONS           EXECUTIONS                LOADS          PARSE_CALLS
    -------------------- -------------------- -------------------- -------------------- --------------------
                       1                    0                   10                    1                    3

同样如果查询v$sesstat 的"parse count (total)"也是1：1的结果，后来终于找到了Steve Adams一种说法

If an SQL statement is parsed in a cursor and then executed repeatedly without closing the cursor or parsing another statement in it, then the V$SQLAREA statistics will show many more EXECUTIONS than PARSE_CALLS for that statement. If statements are never reused, then EXECUTIONS and PARSE_CALLS are normally identical. Note that the count of parse calls includes hits in the session cursor cache, which don't even touch the shared cursor. This is done by updating the statistics when the shared cursor is unpinned after execution.

也就是说v$sql v$sqlarea 中的parse_calls是包含softer soft parse的,但是11.2.0.3除外，这点通过v$sesstat 的"session cursor cache hits"是可以发现的，ML坛子有测试细节.

打赏

微信扫一扫，打赏作者吧～