bind variables

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;

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em PLSQL TUNING, VARIAVEL BIND e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s