可以在awr中看到绑定变量的具体值
select plan_table_output from table (dbms_xplan.display_awr(‘ckc0unuavzjrf’,null, null, ‘ADVANCED +PEEKED_BINDS’))
V_$SQL_BIND_DATA
SELECT dbms_sqltune.extract_bind(bind_data,).value_string1,
dbms_sqltune.extract_bind(bind_data,).value_string2,
dbms_sqltune.extract_bind(bind_data,).value_string3,
dbms_sqltune.extract_bind(bind_data,).value_string4,
dbms_sqltune.extract_bind(bind_data,).value_string5
FROM dba_hist_sqlstat
WHERE SQL_ID= and parsing_schema_name= and SNAP_ID= ;
if the values in columns 4 and 5 are null, it means that the values are not captured.
there is some parameters that control bind capture typically:
_cursor_bind_capture_area_size
在awr中查看sql
select
sql_text,
parsing_schema_name as parsed,
elapsed_time_delta/1000/1000 as elapsed_sec,
stat.snap_id,
to_char(snap.end_interval_time,’dd.mm hh24:mi:ss’) as snaptime,
txt.sql_id
from
dba_hist_sqlstat stat,
dba_hist_sqltext txt,
dba_hist_snapshot snap
where
stat.sql_id=txt.sql_id and
stat.snap_id=snap.snap_id and
snap.begin_interval_time>=sysdate-1 and
lower(sql_text) like ‘%&t%’ and
parsing_schema_name not in (‘SYS’,’SYSMAN’,’MDSYS’,’WKSYS’)
order by elapsed_time_delta asc;
调整awr记录sql的数量
SQL> begin
dbms_workload_repository.modify_snapshot_settings (
topnsql=>’MAXIMUM’);
end;
/
maximum记录所有的sql
