Oracle --获取绑定变量的值.

SELECT * FROM DBA_HIST_SQLBIND WHERE SNAP_ID>67073 AND SNAP_ID<=67079 AND SQL_ID=‘3DR3410F086P4‘;
SELECT * FROM v$sql_bind_capture where sql_id=‘‘

http://blog.itpub.net/22034023/viewspace-689802/

通过v$sql_bind_capture视图，可以查看绑定变量，但是这个视图不太给力，只能捕获最后一次记录的绑定变量值。

而且两次捕获的间隔有一个隐含参数控制。默认是900秒，才会重新开始捕获。在900内，绑定变量值的改变不会反应在这个视图中。

10G以后可以通过如下方法查看AWR报告里记录的SQL的绑定变量值。

select snap_id, name, position, value_string,last_captured,WAS_CAPTURED  from dba_hist_sqlbind  where sql_id = ‘576c1s91gua19‘ and snap_id=‘20433‘;

----------SNAP_ID就是AWR报告的快照ID。

----------name,绑定变量的名称

----------position，绑定值在SQL语句中的位置，以1，2，3进行标注  

---------value_string ，就是绑定变量值

---------,last_captured，最后捕获到的时间

--------- WAS_CAPTURED，是否绑定被捕获，where子句前面的绑定不进行捕获。

dba_hist_sqlbind视图强大的地方在于，它记录了每个AWR报告里的SQL的绑定变量值，当然这个绑定变量值也是AWR生成的时候从v$sql_bind_capture采样获得的。

通过这个视图，我们能够获得比较多的绑定变量值，对于我们排查问题，这些值一般足够了。

还有一个需要注意的地方是，这两个视图中记录的绑定变量只对where条件后面的绑定进行捕获，这点需要使用的时候注意。(备注 11G已经修复了这个问题.)

sys@CRMG>SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV (‘Instance‘)
  4     AND y.inst_id = USERENV (‘Instance‘)
  5     AND x.indx = y.indx
  6     AND x.ksppinm LIKE ‘%&par%‘
  7  /
Enter value for par: bind_ca
old   6:    AND x.ksppinm LIKE ‘%&par%‘
new   6:    AND x.ksppinm LIKE ‘%bind_ca%‘
NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_cursor_bind_capture_area_size 400                  maximum size of the cursor bind capture area
_cursor_bind_capture_interval  900                 interval (in seconds) between two bind capture for a cursor

below from http://xanpires.wordpress.com/2012/05/20/vsql_bind_capture-and-dba_hist_sqlbind-view/

Starting from Oracle 10g, bind variables are periodically captured in the v$sql_bind_capture and dba_hist_sqlbind views
(keep in mind a Diagnostics Pack license is required for accessing dba_hist_* views).

Bind variables are captured either when a query is hard parsed;
or if the last capture was _cursor_bind_capture_interval seconds ago (default 900).

alter system set “_cursor_bind_capture_interval”=86400 sid=’*‘;

select * from GV$ACTIVE_SESSION_HISTORY
where sql_id=’7k954ba91zsxb’
and inst_id=1
and SESSION_ID =1752

select * from dba_objects where object_id=5904

select * from dba_objects where object_name=’SPC1214P’

object_id=59155
79199

select * from dba_arguments
where object_id=59047
and SUBPROGRAM_ID =8

select * from dba_arguments
where object_id=4039
and SUBPROGRAM_ID =82

select p1.object_name ||’ ‘|| p1.procedure_name || ‘ ‘ ||
p2.object_name ||’ ‘|| p2.procedure_name “calling_code”,
s.sql_id,
substr(st.sql_text,1,500) sqltext
from GV$ACTIVE_SESSION_HISTORY s,
dba_procedures p1,
dba_procedures p2,
GV$sql st
where
s.plsql_entry_object_id = p1.object_id (+)
and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
and s.plsql_object_id = p2.object_id (+)
and s.plsql_subprogram_id = p2.subprogram_id (+)
and s.sql_id = st.sql_id(+)
AND s.sql_id = ‘7k954ba91zsxb’
and s.inst_id = 1
and s.SESSION_ID =1752

select * from DBA_HIST_SQLBIND where sql_id = ‘7k954ba91zsxb’
select * from GV$SQL_BIND_CAPTURE where sql_id = ‘7k954ba91zsxb’
select * from GV$SQL_BIND_DATA where sql_id = ‘7k954ba91zsxb’
select * from DBA_HIST_SQL_BIND_METADATA where sql_id = ‘7k954ba91zsxb’
select * from DBA_SQLSET_BINDS where sql_id = ‘7k954ba91zsxb’

select snap_id, name, position, value_string, last_captured
from dba_hist_sql bind
where sql_id = ‘7k954ba91zsxb’
order by snap_id desc;

————-
select * from dba_procedures where
object_id=4021
and SUBPROGRAM_ID =24

—————-
and plsql_subprogram_id=82

select * from all_procedures
where

select s.sid, s.username,
p1.object_name ||’ ‘|| p1.procedure_name || ‘ ‘ ||
p2.object_name ||’ ‘|| p2.procedure_name
“calling_code”,
s.sql_id,
substr(st.sql_text,1,40) sqltext
from v$session s,
dba_procedures p1,
dba_procedures p2,
v$sql st
where s.plsql_entry_object_id = p1.object_id (+)
and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
and s.plsql_object_id = p2.object_id (+)
and s.plsql_subprogram_id = p2.subprogram_id (+)
and s.sql_id = st.sql_id(+)
order by 1,2
/

and s.PLSQL_ENTRY_OBJECT_ID = dg.OBJECT_ID(+)
and s.PLSQL_ENTRY_SUBPROGRAM_ID = dg.SUBPROGRAM_ID(+)
and s.PLSQL_OBJECT_ID = dg2.OBJECT_ID(+)
and s.PLSQL_SUBPROGRAM_ID = dg2.SUBPROGRAM_ID(+))
– and s.sql_address = sa.address
– and s.sql_hash_value = sa.hash_value

select * from dba_constraints where owner = ‘SPCJAVA’ and r_constraint_name = ‘PK_CONSULTA_REALIZADA’

select * from dba_triggers where table_name= ‘CONSULTA_REALIZADA’ and owner = ‘SPCJAVA’

sekect TRIGGER_BODY from

select * from spcjava.CONSULTA_REALIZADA where id_consulta_origem=1

alter table spcjava.CONSULTA_REALIZADA disable constraint ‘FK_CONSREAL_ID_CONS_ORIGEM’

select * from dba_constraints where owner = ‘SPCJAVA’ and r_constraint_name = ‘PK_CONSULTA_REALIZADA’

select * from dba_constraints where owner = ‘SPCJAVA’ and index_name is not null

r_constraint_name = ‘PK_CONSULTA_REALIZADA’

select * from GV$SQL_BIND_DATA
where cursor_num

– *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
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;

select
sql.sql_text line,
sqlbd.cursor_num,
sqlbd.position,
sqlbd.datatype,
‘xx’ || sqlbd.value || ‘xx’
from
v$sql sql,
v$sql_cursor sqlc ,
v$sql_bind_data sqlbd
whereselec
sql.address=sqlc.parent_handle and
sqlbd.cursor_num = sqlc.curno
and sql.sql_id = ‘7k954ba91zsxb‘;

select
sql_text,
other_xml
from
$sql_plan
where
sql_id=’XXXX’
and
id=0;

AWR dba_hist_sqlstat Bind Variable Display

select
sql_text,
other_xml
from dba_hist_sql_plan
where
sql_id=’7k954ba91zsxb’
and id=0;

select count(*) from gv$sql_cursor

select * from dba_hist_sqlstat where sql_id = ‘7k954ba91zsxb‘;

select * from dba_hist_sql_plan where sql_id = ‘7k954ba91zsxb‘;

select * from table(dbms_xplan.display_awr(‘&SQL_ID’,NULL,NULL,’ADVANCED’))

DBA_HIST_SQLBIND

select * from DBA_HIST_SQLSTAT where sql_id = ‘8z8wfyk5s6d1n‘;
select * from DBA_HIST_SQLTEXT where sql_id = ‘8z8wfyk5s6d1n‘;
select * from dba_hist_sql_plan where sql_id = ‘8z8wfyk5s6d1n‘;
select * from DBA_HIST_SQLBIND where sql_id = ‘8z8wfyk5s6d1n‘;

rownum <20

See V$SQL_PLAN.OTHER_XML for those used at parse time. Also DBA_HIST_SQL_PLAN.OTHER_XML.

You could trace your session using event 10046 at level 12 and see the resulting trace file.

You could get them from real time sql monitoring via V$SQL_MONITOR.BINDS_XML.

They are captured periodically in V$SQL_BIND_CAPTURE and and stored in the repository DBA_HIST_SQLBIND.

And there are probably some other ways as well.

So, in terms of AWR, DBA_HIST_SQL_PLAN and DBA_HIST_SQLBIND should be of use.

——-
Bind values can be found together with other info in column OTHER_XML in V$SQL_PLAN or DBA_HIST_SQLPLAN in row with ID=0.
They can by displayed (if available) with the option ADVANCED from

V$SQL_PLAN:
select * from table(dbms_xplan.display_cursor(‘&SQL_ID‘,&child,‘ADVANCED‘));

AWR:
select * from table(dbms_xplan.display_awr(‘&SQL_ID‘,NULL,NULL,‘ADVANCED‘));

———————-
select sql_id,name, position, value_string
from (select sql_id,bind_data
from dba_hist_sqlstat
where bind_data is not null
and rownum <= 1) x
, table(dbms_sqltune.extract_binds(x.bind_data)) xx;

select p1.object_name ||‘ ‘|| p1.procedure_name || ‘ ‘ ||
p2.object_name ||‘ ‘|| p2.procedure_name "calling_code",
s.*,
substr(st.sql_text,1,500) sqltext
from DBA_HIST_ACTIVE_SESS_HISTORY s,
dba_procedures p1,
dba_procedures p2,
GV$sql st
where
s.plsql_entry_object_id = p1.object_id (+)
and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
and s.plsql_object_id = p2.object_id (+)
and s.plsql_subprogram_id = p2.subprogram_id (+)
and s.sql_id = st.sql_id(+)
and s.sample_time between to_date(‘20/07/2011 02:58‘, ‘dd/mm/yyyy hh24:mi‘) and to_date(‘20/07/2011 04:00‘, ‘dd/mm/yyyy hh24:mi‘)
and p1.object_id=59155

select * from dba_procedures where object_name= ‘SPC1214P‘

select distinct to_char(sample_time, ‘dd/mm/yyyy‘) from GV$ACTIVE_SESSION_HISTORY

select o.object_name, g.*
from GV$ACTIVE_SESSION_HISTORY g, dba_objects o
where g.current_obj# = o.OBJECT_ID(+)
and g.BLOCKING_SESSION is not null

– sessoes bloqueadoras
select o.object_name, g.*
from GV$ACTIVE_SESSION_HISTORY g, dba_objects o
where g.current_obj# = o.OBJECT_ID(+)
and (g.BLOCKING_SESSION, g.session_serial#) in
(select session_id,session_serial#
from GV$ACTIVE_SESSION_HISTORY
where BLOCKING_SESSION is not null)

———–
select u.username, a.sample_id,
a.sample_time,
a.session_id,
a.event,
a.session_state,
a.event,
a.sql_id,
a.blocking_session,
a.blocking_session_status
from v$active_session_history a,
dba_users u
where u.user_id = a.user_id
and u.username = ‘TESTUSER‘;

select * from GV$ACTIVE_SESSION_HISTORY

select distinct to_char(sample_time, ‘dd/mm/yyyy‘) from DBA_HIST_ACTIVE_SESS_HISTORY

select count (*) from V$ACTIVE_SESSION_HISTORY
select count (*) from DBA_HIST_ACTIVE_SESS_HISTORY

—–query para verificar qual uma sql lento ou travando o banco em um periodo- – – – – – -
SELECT C.SQL_TEXT,
B.NAME,
COUNT(*),
SUM(TIME_WAITED)
FROM v$ACTIVE_SESSION_HISTORY A,
v$EVENT_NAME B,
v$SQLAREA C
WHERE A.SAMPLE_TIME BETWEEN ‘10-JUL-04 09:57:00 PM‘ AND
‘10-JUL-04 09:59:00 PM‘ AND
A.EVENT# = B.EVENT# AND
A.SESSION_ID= 123 AND
A.SQL_ID = C.SQL_ID
GROUP BY C.SQL_TEXT, B.NAME

——

–*********** hot_files_ash.sql

SELECT
f.file_name "Data File",
COUNT(*) "Wait Number",
SUM(h.time_waited) "Total Time Waited"
FROM
v$active_session_history h,
dba_data_files f
WHERE
h.current_file# = f.file_id
GROUP BY f.file_name
ORDER BY 3 DESC

———****** events_waits_hr_ash.sql

SELECT
h.event "Wait Event",
SUM(h.wait_time + h.time_waited) "Total Wait Time"
FROM
v$active_session_history h,
v$event_name e
WHERE
h.sample_time BETWEEN sysdate – 1/24 AND sysdate
AND h.event_id = e.event_id
AND e.wait_class ‘Idle’
GROUP BY h.event
ORDER BY 2 DESC

SELECT * –sid, seq#, event, wait_time, p1, p2, p3
FROM v$session_wait_history
WHERE sid = 154
ORDER BY seq#;

SELECT a.session_id, a.blocking_session, u.username as bloqued , ub.username bloquer,
a.blocking_session_status status,
a.event –, seconds_in_wait siw
FROM v$active_session_history a,dba_users u, dba_users ub
WHERE blocking_session_status = ‘VALID’
and u.user_id = a.user_id
and ub.user_id = a.user_id ;

select * from v$active_session_history

SQL> SELECT sid, blocking_session, username,
2 event, seconds_in_wait siw
3 FROM v$session
4 WHERE blocking_session_status = ‘VALID‘;

where sample_time select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= ‘ON CPU’ and
sample_time > sysdate – interval ‘5’ minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state=’WAITING’ and
sample_time > sysdate – interval ‘5’ minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum select serial#,
username,
osuser,
machine,
program,
resource_consumer_group,
client_info
from v$session where sid=&sid;

————————-
–
— What did that SID do?
–
————————-

SQL> select distinct sql_id, session_serial# from v$active_session_history
where sample_time > sysdate – interval ‘5’ minute
and session_id=&sid;
———————————————-
–
— Retrieve the SQL from the Library Cache:
–
———————————————-
col sql_text for a80
SQL> select sql_text from v$sql where sql_id=’&sqlid‘;

– Display blocked session and their blocking session details.
SELECT sid, serial#, blocking_session_status, blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL;

no rows selected

– Display the resource or event the session is waiting for.
SELECT sid, serial#, event, (seconds_in_wait/1000000) seconds_in_wait
FROM v$session
ORDER BY 4 desc;

– Display session wait information by wait class.
SELECT *
FROM v$session_wait_class
WHERE sid = 134;