ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何获取绑定变量值

如何获取绑定变量值

原创 Linux操作系统 作者:wei-xh 时间:2011-03-08 16:50:26 0 删除 编辑

Bind Capture History
As of 10g, Oracle captures the bind value and stores it into the repository(AWR), but with following restrictions.

  • Captured periodically(_cursor_bind_capture_interval), not at real time.
  • Captured under maximum size(_cursor_bind_capture_area_size)
  • Only bind variables in WHERE clause are captured

Ouch! Too many restrictions, aren’t they?

Simple demonstration:

UKJA@ukja102> var bid1 number;
UKJA@ukja102> var eid1 number;
UKJA@ukja102>
UKJA@ukja102> exec :bid1 := dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

UKJA@ukja102>
UKJA@ukja102> var b1 number;
UKJA@ukja102> var b2 number;
UKJA@ukja102> var b3 varchar2(1);
UKJA@ukja102> begin
  2    :b1 := 1;
  3    :b2 := 2000;
  4    :b3 := 'y';
  5  end;
  6  /

PL/SQL procedure successfully completed.

UKJA@ukja102>
UKJA@ukja102> update t1 set c3 = :b3 where c1 between :b1 and :b2;

2000 rows updated.

UKJA@ukja102> commit;

Commit complete.

UKJA@ukja102>
UKJA@ukja102> exec :eid1 := dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

UKJA@ukja102>
UKJA@ukja102> var sql_id varchar2(100);
UKJA@ukja102> begin
  2    select sql_id into :sql_id
  3    from dba_hist_sqltext
  4    where sql_text like 'update t1 set c3 = :b3 where c1 between :b1 and :b2';
  5  end;
  6  /

PL/SQL procedure successfully completed.

UKJA@ukja102>
UKJA@ukja102> print sql_id

SQL_ID
--------------------------------------------------------------------------------
bvy5d4xupjkku                                                                   

UKJA@ukja102>
UKJA@ukja102> col name format a10
UKJA@ukja102> col position format 99
UKJA@ukja102> col value_string format a20
UKJA@ukja102> select snap_id, name, position, value_string, last_captured -
> from dba_hist_sqlbind
  2  where sql_id = :sql_id
  3  order by snap_id desc;

   SNAP_ID NAME       POSITION VALUE_STRING         LAST_CAPTURED
---------- ---------- -------- -------------------- -------------------
      6701 :B1               2 1                    2009/05/06 11:10:13
      6701 :B2               3 2000                 2009/05/06 11:10:13
      6701 :B3               1
      6700 :B2               3 1000                 2009/05/06 11:10:03
      6700 :B3               1
      6700 :B1               2 1                    2009/05/06 11:10:03
      6699 :B1               2 1                    2009/05/06 11:05:50
      6699 :B3               1
      6699 :B2               3 2000                 2009/05/06 11:05:50
      6697 :B2               3 2000                 2009/05/06 10:47:51
      6697 :B3               1
      6697 :B1               2 1                    2009/05/06 10:47:51         

12 rows selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-688706/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2315177