今天查看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