1 person found this helpful
>Where they are?
did you look in the bit locker in the /dev/null bin?
did you query V$SQL_BIND_CAPTURE
Thanks, that's right, v$sql_bind_capture shows all bind values, but I'm still confused about missing bind values in display_awr report...
perhaps these binds weren't peeked (e.g. they weren't in the WHERE clause).
for the sake of completness: Tanel Poder wrote in his blog - http://blog.tanelpoder.com/2010/10/18/read-currently-running-sql-statements-bind-variable-values/:
V$SQL_BIND_CAPTURE is not a reliable way for identifying the current bind variable values in use. Oracle’s bind capture mechanism does not capture every single bind variable into SGA (it would slow down apps which run lots of short statements with bind variables). The bind capture only selectively samples bind values, during the first execution of a new cursor and then every 15 minutes from there (controlled by _cursor_bind_capture_interval parameter), assuming that new executions of that same cursor are still being started (the capture happens only when execution starts, not later during the execution).
Regarding AWR Dion Cho mentioned some restrictions of the capture mechanism: http://dioncho.wordpress.com/2009/05/07/tracking-the-bind-value/
- Captured periodically(_cursor_bind_capture_interval), not at real time.
- Captured under maximum size(_cursor_bind_capture_area_size)
- Only bind variables in WHERE clause are captured (as Nikolay already mentioned)
Many thanks for help, guys !