数据库性能优化（针对开发人员） 1 共享池争用问题

1.1 latch争用

1.1.1 绑定变量

案例：

 

xx核心库财务模块的一个function，具体语句如下。优化之前是使用拼sql的方式（红色部分），这种模式在调用次数一般或者较小时，性能没有明显的下降，且对其他业务不会造成影响。不过极端情况出现时（如月结关帐，年结），大批量的处理，使得共享池latch竞争加剧，导致批处理整体性能下降，甚至无法成功运行。

 

以下红色部分语句，oracle在每次调用时均会重新解析，当循环数量突增时，占用大量latch资源，导致系统性能严重下降。

 

FUNCTION F_GET_REF10_2(I_LIST_ID        IN NUMBER,

                       I_GL_TABLE       IN VARCHAR,

                       I_SOURCE_TABLE   IN VARCHAR,

                       I_BIZ_POSTING_ID IN NUMBER) RETURN VARCHAR2 IS

  V_SQL             VARCHAR2(2000);

  CURSOR_BUILD_DATA INTEGER;

  O_ROW_COUNT       INTEGER;

  M_FEE_TYPE        NUMBER(3);

  M_AC_DATE         DATE;

  M_PAY_MODE        T_PAY_MODE.MODE_ID%TYPE;

  V_REF_10          VARCHAR2(2000);

  V_LIST_ID         NUMBER;

  V_SOURCE_TABLE    VARCHAR2(30);

BEGIN

  begin

    V_LIST_ID      := I_LIST_ID;

    V_SOURCE_TABLE := I_SOURCE_TABLE;

    -- MODIFY BY KAIQIN.XU 2014-01-02,优化,使用绑定变量

    /*

    V_SQL := 'SELECT T.FEE_TYPE,T.ACCOUNT_GL_DATE, T.PAY_MODE ';

    V_SQL := V_SQL || ' FROM ' || I_GL_TABLE;

    V_SQL := V_SQL || ' T WHERE T.FEE_ID = ' || I_LIST_ID;

    V_SQL := V_SQL || ' AND T.SOURCE_TABLE = ''' || I_SOURCE_TABLE || '''';

    EXECUTE IMMEDIATE V_SQL

      INTO M_FEE_TYPE, M_AC_DATE, M_PAY_MODE;

    */

    V_SQL := 'SELECT T.FEE_TYPE,T.ACCOUNT_GL_DATE, T.PAY_MODE ';

    V_SQL := V_SQL || ' FROM ' || I_GL_TABLE;

    V_SQL := V_SQL || ' T WHERE T.FEE_ID = :2';

    V_SQL := V_SQL || ' AND T.SOURCE_TABLE = :3';

    EXECUTE IMMEDIATE V_SQL

      INTO M_FEE_TYPE, M_AC_DATE, M_PAY_MODE

      USING V_LIST_ID, V_SOURCE_TABLE;

 

    -- 确认日期+‘-’+费用类型名称

 

    V_REF_10 := TO_CHAR(M_AC_DATE, 'YYYY-MM-DD') || '-' || I_BIZ_POSTING_ID || '-' ||

                PKG_PUB_MULTI_LANG.F_GET_CODE_DESC('T_FEE_TYPE', M_FEE_TYPE);

 

  exception

    when others then

      V_REF_10 := 'ERROR WHILE GETTING REF-10 IN FUNCTION 2';

  end;

  return V_REF_10;

END F_GET_REF10_2;

 

修改后，使用

  EXECUTE IMMEDIATE V_SQL

      INTO M_FEE_TYPE, M_AC_DATE, M_PAY_MODE

      USING V_LIST_ID, V_SOURCE_TABLE;

将传入的参数使用绑定变量，降低硬解析，很好的解决了latch 竞争问题。

 

 

小结：

所以，不仅仅是java程序里面要使用绑定变量的方式来调用sql语句或者pkg等，存储过程的写法也必须遵循使用绑定变量的原则。

开发人员调整后，大数据量批处理时，性能提升数倍以上，且不会造成大量latch等待，不影响其他业务进行。