ibatis Oracle绑定变量分析报告

今天测试了一下abatis和spring架构下，Oracle的绑定变量，得出结论，abatis是使用的软解析，相比硬解析是优秀了些，但对于存储过程的无解析，还是有差距的

abatis的代码很简单，如果：
nation.xml :


    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "">



    
    
   
        SELECT NATIONCODE,
               NATIONNAME
          FROM NATION
         WHERE NATIONNAME=#value#
     
     
        SELECT NATIONCODE,
               NATIONNAME
          FROM NATION
     


NationImp.java：

package com.teapot.data;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import java.util.List;
import org.apache.log4j.Logger;

public class NationImp extends SqlMapClientDaoSupport {
     
    static Logger logger = Logger.getLogger(NationImp.class);
   
    public List queryAllNation() {
        List l = getSqlMapClientTemplate().queryForList("CRM.getAllNation", null);
        logger.info("nation.getAllNation GetSize : " + l.size());
        return l;
    }
   
    public List queryNation() {
        List l = getSqlMapClientTemplate().queryForList("CRM.getNation", "1");
        logger.info("nation.queryNation GetSize : " + l.size());
        return l;
    }
}

调用：
<%
    WebApplicationContext wac = (WebApplicationContext) config.getServletContext().getAttribute(WebApplicationContext.ROOT_WEB_APPLICATION_CONTEXT_ATTRIBUTE);

    NationImp ni = null;
    List l = null;
   
    ni = (NationImp)wac.getBean("NationImp");
    for (int i = 0; i < 36600; i++)
    {
        l = ni.queryNation();
    }
%>

通过tkprof，得到的执行结果如下：
可能得知，ibatis是使用了软解析

SELECT NATIONCODE,             NATIONNAME       
FROM
 NATION       WHERE NATIONNAME=:1   


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    36600      0.68       0.61          0          0          0           0
Execute  36600      1.28       1.12          0          0          0           0
Fetch    36600      1.87       1.77          0     256200          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   109800      3.84       3.51          0     256200          0           0

从共享池中抓出SQL的执行情况：
SQL> SELECT sql_text, sorts, executions, users_executing,
  2         TO_DATE(first_load_time, 'rrrr-mm-dd/hh24:mi:ss'),
  3         invalidations, parse_calls, disk_reads,s.buffer_gets,rows_processed,
  4         optimizer_mode, optimizer_cost, RAWTOHEX(address) address ,
  5         hash_value, u.username parsing_username,
  6         s.buffer_gets*100/total.buffer_gets pct_total_gets,
  7         s.buffer_gets/DECODE(s.rows_processed,0,1,s.rows_processed) gets_per_row
  8    FROM v$sql s,
  9         all_users u,
 10         (SELECT SUM(buffer_gets) buffer_gets FROM v$sql) total
 11   WHERE u.user_id=s.parsing_user_id AND executions > 0
 12   and u.username like 'CRM'
 13   ORDER BY executions asc;
 
SQL_TEXT                                                                              SORTS EXECUTIONS USERS_EXECUTING TO_DATE(FIRST_LOAD_TIME,'RRRR- INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS ROWS_PROCESSED OPTIMIZER_MODE OPTIMIZER_COST ADDRESS  HASH_VALUE PARSING_USERNAME               PCT_TOTAL_GETS GETS_PER_ROW
-------------------------------------------------------------------------------- ---------- ---------- --------------- ------------------------------ ------------- ----------- ---------- ----------- -------------- -------------- -------------- -------- ---------- ------------------------------ -------------- ------------
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB          0          1               0 2009-05-12 12:28:17                        0           1          0           7              1 ALL_ROWS                    3 3EB3102C 4135079179 CRM                            0.005405572372            7
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB          0          1               0 2009-05-12 12:28:15                        0           1          5           7              1 ALL_ROWS                    3 3EB3F080 2209843099 CRM                            0.005405572372            7
SELECT NATIONCODE,             NATIONNAME        FROM NATION       WHERE NATIONN          0       1000               0 2009-05-12 12:28:17                        0        1000          0        7030              0 ALL_ROWS                    3 3EB316A8  918026084 CRM                            5.428739111632         7030
SELECT COUNT(*) FROM nation                                                               0       1002               0 2009-05-12 12:28:15                        0        1002         13        3050           1002 ALL_ROWS                    2 3EB3FD68  451664816 CRM                            2.355285105331 3.0439121756


####################################################################################
而通过Oracle的存储过程执行：
得到了最完美的过程，如下：

已写入 file afiedt.buf

  1  declare
  2    a varchar2(100);
  3    b varchar2(100);
  4  begin
  5    for i IN 1..10000 LOOP
  6     select NATIONCODE,NATIONNAME into a, b FROM NATION WHERE NATIONNAME = '2';
  7    end loop;
  8* end;
CRM@ORCL> /

执行过程如下：

SELECT NATIONCODE,NATIONNAME
FROM
 NATION WHERE NATIONNAME = '2'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute  10000      0.26       0.26          0          0          0           0
Fetch    10000      0.50       0.46          0      70000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      0.78       0.73          0      70001          0       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 30     (recursive depth: 1)




这里回顾一下，TOMKate的绑定变量：
硬解析：


not connected> conn crm/crm@peizf
已连接。
CRM@ORCL> alter session set sql_trace=true;

会话已更改。

CRM@ORCL> DECLARE
  2    TYPE rc IS REF CURSOR;
  3    l_rc rc;
  4    l_dummy all_objects.object_name%TYPE;
  5    l_start NUMBER DEFAULT dbms_utility.get_time;
  6  BEGIN
  7    FOR i IN 1..100 LOOP
  8      OPEN l_rc FOR
  9        'SELECT object_name from all_objects
 10          where object_id = ' || i;
 11      FETCH l_rc INTO l_dummy;
 12      CLOSE l_rc;
 13    END LOOP;
 14
 15    dbms_output.put_line(round((dbms_utility.get_time - l_start) / 100, 2) || ' seconds...');
 16  END;
 17  /

PL/SQL 过程已成功完成。


在tkprof的报告中，有许多解析过程，如下(这里取两个，还有好多)，OLTP系统中，SQL写法最不可取的方式

SELECT object_name from all_objects
        where object_id = 23

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         19          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.04          0         19          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 30     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  FILTER  (cr=19 pr=0 pw=0 time=1396 us)
      1   NESTED LOOPS  (cr=6 pr=0 pw=0 time=155 us)
      1    TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=77 us)
      1     INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=33 us)(object id 36)
      1    TABLE ACCESS CLUSTER USER$ (cr=3 pr=0 pw=0 time=50 us)
      1     INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=18 us)(object id 11)
      0   TABLE ACCESS BY INDEX ROWID IND$ (cr=0 pr=0 pw=0 time=0 us)
      0    INDEX UNIQUE SCAN I_IND1 (cr=0 pr=0 pw=0 time=0 us)(object id 39)
      0   HASH JOIN  (cr=2 pr=0 pw=0 time=255 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=23 us)(object id 103)
      0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=43 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=27 us)
      0   TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=0 pw=0 time=150 us)
      1    NESTED LOOPS  (cr=3 pr=0 pw=0 time=116 us)
      0     NESTED LOOPS  (cr=3 pr=0 pw=0 time=99 us)
      0      MERGE JOIN CARTESIAN (cr=3 pr=0 pw=0 time=87 us)
      0       TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=62 us)
      1        INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=24 us)(object id 37)
      0       BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
      0        FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0      INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 103)
      0     INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us)(object id 122)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=24 us)
      0   NESTED LOOPS  (cr=2 pr=0 pw=0 time=28 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=14 us)(object id 103)
      0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0   HASH JOIN  (cr=2 pr=0 pw=0 time=232 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=17 us)(object id 103)
      0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=35 us)
      0   NESTED LOOPS  (cr=3 pr=0 pw=0 time=163 us)
      0    MERGE JOIN CARTESIAN (cr=3 pr=0 pw=0 time=150 us)
      0     TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=0 pw=0 time=113 us)
      1      NESTED LOOPS  (cr=3 pr=0 pw=0 time=81 us)
      0       TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=64 us)
      1        INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=25 us)(object id 37)
      0       INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us)(object id 122)
      0     BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
      0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 103)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=24 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   NESTED LOOPS  (cr=1 pr=0 pw=0 time=52 us)
      0    NESTED LOOPS  (cr=1 pr=0 pw=0 time=39 us)
      0     TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1 pr=0 pw=0 time=27 us)
      0      INDEX UNIQUE SCAN I_TRIGGER2 (cr=1 pr=0 pw=0 time=14 us)(object id 126)
      0     INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 103)
      0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=24 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   VIEW  (cr=0 pr=0 pw=0 time=0 us)
      0    FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)

********************************************************************************

SELECT object_name from all_objects
        where object_id = 24

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         22          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.04          0         22          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 30     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  FILTER  (cr=22 pr=0 pw=0 time=1656 us)
      1   NESTED LOOPS  (cr=6 pr=0 pw=0 time=413 us)
      1    TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=308 us)
      1     INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=266 us)(object id 36)
      1    TABLE ACCESS CLUSTER USER$ (cr=3 pr=0 pw=0 time=77 us)
      1     INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=16 us)(object id 11)
      1   TABLE ACCESS BY INDEX ROWID IND$ (cr=3 pr=0 pw=0 time=45 us)
      1    INDEX UNIQUE SCAN I_IND1 (cr=2 pr=0 pw=0 time=18 us)(object id 39)
      0   HASH JOIN  (cr=2 pr=0 pw=0 time=223 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=19 us)(object id 103)
      0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=56 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=29 us)
      0   TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=0 pw=0 time=149 us)
      1    NESTED LOOPS  (cr=3 pr=0 pw=0 time=114 us)
      0     NESTED LOOPS  (cr=3 pr=0 pw=0 time=96 us)
      0      MERGE JOIN CARTESIAN (cr=3 pr=0 pw=0 time=83 us)
      0       TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=57 us)
      1        INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=21 us)(object id 37)
      0       BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
      0        FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0      INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 103)
      0     INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us)(object id 122)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=23 us)
      0   NESTED LOOPS  (cr=2 pr=0 pw=0 time=25 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=13 us)(object id 103)
      0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0   HASH JOIN  (cr=2 pr=0 pw=0 time=224 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=13 us)(object id 103)
      0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=30 us)
      0   NESTED LOOPS  (cr=3 pr=0 pw=0 time=157 us)
      0    MERGE JOIN CARTESIAN (cr=3 pr=0 pw=0 time=145 us)
      0     TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=0 pw=0 time=107 us)
      1      NESTED LOOPS  (cr=3 pr=0 pw=0 time=77 us)
      0       TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=60 us)
      1        INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=22 us)(object id 37)
      0       INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us)(object id 122)
      0     BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
      0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 103)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=23 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   NESTED LOOPS  (cr=1 pr=0 pw=0 time=49 us)
      0    NESTED LOOPS  (cr=1 pr=0 pw=0 time=37 us)
      0     TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1 pr=0 pw=0 time=25 us)
      0      INDEX UNIQUE SCAN I_TRIGGER2 (cr=1 pr=0 pw=0 time=12 us)(object id 126)
      0     INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 103)
      0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=24 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   VIEW  (cr=0 pr=0 pw=0 time=0 us)
      0    FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)

********************************************************************************