首页 » ORACLE » about get bind_data column in v$sql(绑定变量值)

about get bind_data column in v$sql(绑定变量值)

今天查看AWR时,发现有个sql占用的cpu很高而且也并不复杂,top 5 event出现了cpu,read by other sessions,而且sql中再次看到的hint index,应用程序员说未找到sql的页面,而且使用了绑定变量,随后从生产环境中拿到绑定变量参数值进行本地调试,发现hint index 选择了错误的索引一致读为93万,去掉hint立即降低到了36,随后服务器压力将了下来

在下面演示如何取绑定变量的值,除了10046和用dbms_xplan查看变显窥探值

icmedb>select * from v$version where rownum<4;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production

--比如得到了sql_id
icmedb>select sql_text from v$sql  WHERE sql_id = '5nmps6phuxxvm';
--------------------------------------------------------------------
SELECT (SELECT MAX (t.score_type_id)
          FROM icme_score_type t
         WHERE t.org_id = :1 AND t.score_type_name = :2)
          AS score_type_id,
       (SELECT MAX (t.activity_id)
          FROM icme_project_activity t
         WHERE t.org_id = :3 AND t.activity_name = :4)
          AS activity_type_id,
       (SELECT MAX (t.subject_id)
          FROM ICME_PROJECT_SUBJECT t
         WHERE t.org_id = :5 AND t.subject_name = :6)
          AS subject_id,
       (SELECT /*+ index(t3 IDX_STUDENT_ORGID) */
              t3.ic_code
          FROM icme_org t1,
               icme_org t2,
               icme_student t3,
               icme_org_type t4
         WHERE     t1.org_id = :7
               AND t1.org_type_id = t4.org_type_id
               AND t4.org_levle >= 3
               AND t2.org_code LIKE t1.org_code || '%'
               AND t2.org_id = t3.org_id
               AND t3.tran_id > 0
               AND t3.is_valid < 3
               AND t3.ic_code = :8)
          AS ic_code
  FROM DUAL;

--最简单是使用DBMS_SQLTUNE package

icmedb>desc DBMS_SQLTUNE

...
FUNCTION EXTRACT_BIND RETURNS SQL_BIND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BIND_DATA                      RAW                     IN
 BIND_POS                       BINARY_INTEGER          IN
FUNCTION EXTRACT_BINDS RETURNS SQL_BIND_SET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BIND_DATA                      RAW                     IN
...

icmedb>select object_type,owner from dba_objects where object_name='SQL_BIND_SET';

OBJECT_TYPE         OWNER
------------------- ------------------------------
TYPE                SYS
SYNONYM             PUBLIC

icmedb>select dbms_metadata.get_ddl('TYPE','SQL_BIND_SET') from dual;

DBMS_METADATA.GET_DDL('TYPE','SQL_BIND_SET')
--------------------------------------------------------------------------------

  CREATE OR REPLACE TYPE "SYS"."SQL_BIND_SET"  AS TABLE OF sql_bind

icmedb>select dbms_metadata.get_ddl('TYPE','SQL_BIND') from dual;

DBMS_METADATA.GET_DDL('TYPE','SQL_BIND')
--------------------------------------------------------------------------------

  CREATE OR REPLACE TYPE "SYS"."SQL_BIND"
                                                                       AS object (
  name                VARCHAR2(30),                     /* bind variable name */
  position            NUMBER,            /* position of bind in sql statement */
  dup_position        NUMBER,    /* if any, position of primary bind variable */
  datatype            NUMBER,                    /* datatype id for this bind */
  datatype_string     VARCHAR2(15),/* string representation of above datatype */
  character_sid       NUMBER,              /* character set id if bind is NLS */
  precision           NUMBER,                               /* bind precision */
  scale               NUMBER,                                   /* bind scale */
  max_length          NUMBER,                          /* maximum bind length */
  last_captured       DATE,      /* DATE when this bind variable was captured */
  value_string        VARCHAR2(4000),     /* bind value (text representation) */
  value_anydata       ANYDATA)         /* bind value (anydata representation) */

icmedb>select position, datatype_string,last_captured,value_string 
  2  FROM TABLE (SELECT DBMS_SQLTUNE.extract_binds (bind_data)
  3                  FROM v$sql
  4                 WHERE sql_id = '5nmps6phuxxvm' AND child_number = 4);

  POSITION DATATYPE_STRING LAST_CAPTURED       VALUE_STRING
---------- --------------- ------------------- --------------------
         1 NUMBER          2012-12-20 13:18:01 30000000
         2 VARCHAR2(32)    2012-12-20 13:18:01 国家级
         3 NUMBER          2012-12-20 13:18:01 30000000
         4 VARCHAR2(32)    2012-12-20 13:18:01 培训班
         5 NUMBER          2012-12-20 13:18:01 30000000
         6 VARCHAR2(32)    2012-12-20 13:18:01 中医内科
         7 NUMBER          2012-12-20 13:18:01 30000015
         8 VARCHAR2(32)    2012-12-20 13:18:01 13000H05U

icmedb>SELECT dbms_sqltune.extract_bind(bind_data,2).value_string FROM V$SQL WHERE sql_id = '5nmps6phuxxvm' AND child_number =4;

DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,2).VALUE_STRING
------------------------------------------------------------------------------
国家级

icmedb>SELECT dbms_sqltune.extract_bind(bind_data,2) FROM V$SQL WHERE sql_id = '5nmps6phuxxvm' AND child_number =4;

DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,2)(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPT
-------------------------------------------------------------------------------------------
SQL_BIND(NULL, 2, NULL, 1, 'VARCHAR2(32)', 852, NULL, NULL, 32, '2012-12-20 13:18:01', '国家级', ANYDATA())

icmedb> select bind_data from v$sql WHERE sql_id = '5nmps6phuxxvm' AND child_number =4;

BIND_DATA
-----------------------------------------------------------------------------------------------------------
BEDA0A20040050D29F890008FFF0021602C41FF00120035406B9FABCD2BCB6F0021602C41FF00120035406C5E0D1B5B0E0F0021602C41FF00120035408D6D0D2BDC4DABFC6F0021605C41F010110F00120035409313330303048303555

有兴趣的可以直接计算bind_data,值是以raw string形式存在的。

part 1
[BEDA][0A200400][50D29F890][0008]
---------------
BEDA --bind data

0A200400 --10.2.0.4.0   --数据库版本
50D29F89-- 1355980681   --变量的绑定时间
0008 --8               --绑定变量的总数
--因为+8:00时区,是到1970-1-1 和秒数转16进制
sys@ANBOB>SELECT (TO_date('2012-12-20 13:18:01','yyyy-mm-dd hh24:mi:ss')-TO_date('1970-1-1 00:00:00','yyyy-mm-dd hh24:mi:ss')-8/24)*24*3600  secs FROM DUAL;
                SECS
--------------------
          1355980681
part 2

01 --数据类型
20 --最大长度
 --字符集
 --值RAW
F0[01][20]035409[313330303048303555]
[313330303048303555]
sys@ANBOB>select utl_raw.CAST_TO_VARCHAR2('313330303048303555') val from dual;

VAL
------------------------------------------
13000H05U


get bind variable value in 9i or 8i?


SELECT TO_CHAR(vbd.position) || ':’ || vbd.value , vsql.*
FROM v$sql_bind_data vbd,
sys.v_$sql vsql,
sys.v_$sql_cursor vsqlc
WHERE vsql.address = vsqlc.parent_handle
AND vsqlc.curno = vbd.cursor_num
and lower(vsql.sql_text) like '%pnl_vector%’

SELECT (’ Curno: '|| CUR.CURNO ||
'Position: '|| BIND.POSITION ||
'Datatype: '|| BIND.DATATYPE ||
'Flag: '|| CUR.FLAG ||
'Status: '|| CUR.STATUS ||
'Bind Vars: '|| CUR.BIND_VARS ||
'VALUE: '|| BIND.VALUE)
FROM v$sql_cursor CUR, v$sql_bind_data BIND
WHERE CUR.CURNO = BIND.CURSOR_NUM AND
CUR.STATUS <> 'CURNULL’
ORDER BY CURNO;

相关视图

v$sql_bind_capture,v$sql_bind_data,v$sql_bind_metadata

打赏

对不起,这篇文章暂时关闭评论。