SQL PROFILE 常见案例

纸上谈兵的操作咱不做，既然学习了sql profile，就来看个解决实际问题

的案例吧。

______________________________________________________________
1.创建测试环境

  CREATE TABLE user_money(user_id NUMBER PRIMARY KEY,money NUMBER,CONSTRAINT gt0_chk CHECK(money>0));

 

  INSERT INTO user_money VALUES(1,100000);
  INSERT INTO user_money VALUES(2,500000);
  COMMIT;

______________________________________________________________
2.强制启用系统绑定

  ALTER SESSION SET cursor_sharing=FORCE;
  ALTER SYSTEM FLUSH SHARED_POOL;

 

  UPDATE user_money SET money=20000 WHERE user_id=1;
  UPDATE user_money SET money=70000 WHERE user_id=2;
  COMMIT;
  --上面两条语句都使用了索引。

 

  SELECT s.sql_id, s.child_number, s.executions, s.sql_text
  FROM v$sql s
  WHERE lower(s.sql_text) LIKE lower('%user_money%"SYS_B%"%')
  AND lower(s.sql_text) NOT LIKE lower('%EXPLAIN%')
  AND lower(s.sql_text) NOT LIKE lower('%v$sql%');
  --ftcdcwgbfyt18 0 2 UPDATE user_money SET money=:"SYS_B_0" WHERE user_id=:"SYS_B_1"

 

    规律：一条语句中的字面值，由系统生成的绑定变量替换，这些
  绑定变量命名规则是：SYS_B_0,SYS_B_1...。

______________________________________________________________
3.修改原语句执行计划

 

  --生成计划相关outline
  DELETE FROM plan_table;

  EXPLAIN PLAN SET STATEMENT_ID='full_ts' FOR
  UPDATE /*+full(user_money)*/user_money SET money=70000 WHERE user_id=2;

 

  SELECT *
  FROM TABLE(dbms_xplan.display(NULL, 'full_ts', 'basic +outline', NULL));
  Outline Data
  -------------
  
    /*+
        BEGIN_OUTLINE_DATA
        FULL(@"UPD$1" ")
        OUTLINE_LEAF(@"UPD$1")
        ALL_ROWS
        DB_VERSION('11.2.0.1')
        OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
        IGNORE_OPTIM_EMBEDDED_HINTS
        END_OUTLINE_DATA
    */

  --将这些outline绑定到目标sql上，以生成sql profile，并将生成的profile装载到字典中

  BEGIN
    dbms_sqltune.drop_sql_profile(NAME => 'upd_user_money_prof1');
  END;
  /

 

  DECLARE
    v_sql_attr sys.sqlprof_attr; --数组
  BEGIN
    v_sql_attr := sys.sqlprof_attr('FULL(@"UPD$1" ,
                                   'OUTLINE_LEAF(@"UPD$1")',
                                   q'`DB_VERSION('11.2.0.1')`',
                                   q'`OPTIMIZER_FEATURES_ENABLE('11.2.0.1')`',
                                   'ALL_ROWS',
                                   'IGNORE_OPTIM_EMBEDDED_HINTS'); --填充数组内容
    dbms_sqltune.import_sql_profile(sql_text    => q'`UPDATE user_money SET money=:"SYS_B_0" WHERE user_id=:"SYS_B_1"`',
                                    profile     => v_sql_attr,
                                    NAME        => 'upd_user_money_prof1',  --注意命名规范化
                                    category    => 'jdbc_no_bind',          --命名规范化
                                    force_match => TRUE);
  END;
  /

______________________________________________________________
4.启用sql PROFILE

 

  ALTER SESSION SET sqltune_category=jdbc_no_bind;
  ALTER SESSION SET cursor_sharing=FORCE;

 

  set autotrace on
  UPDATE user_money SET money=70000 WHERE user_id=2;
  ---------------------------------------------------------------------------------
  | Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------
  |   0 | UPDATE STATEMENT   |            |     4 |   104 |     3   (0)| 00:00:01 |
  |   1 |  UPDATE            | USER_MONEY |       |       |            |          |
  |*  2 |   TABLE ACCESS FULL| USER_MONEY |     4 |   104 |     3   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------
  Note
  -----
     - SQL profile "upd_user_money_prof1" used for this STATEMENT
  
______________________________________________________________
5.总结

 

    对于一些没法修改sql源代码的封装程序（SAP是典范啊），我们只有
  通过创建sql概要来强制调整访问路径，调整的方法往往要引入登录触发
  器，以便设置cursor_sharing和sqltune_category参数，这也是上面强
  调了命名规范的重要性。