Bind variable peeking question

From: Chen Zhou <oracle.unknowns_at_gmail.com>
Date: Fri, 31 Jan 2014 12:41:22 -0800
Message-ID: <CAJUY3dRTv=5+8mSPq52NaWXT+TMHvX7uTWit26GZt10FfNF-sg_at_mail.gmail.com>

Hi, Everyone,
I had a query that used up lots of CPU for a fairly long time yesterday. There are 2 variables that define the time period for this query. So the query searches for data from several tables during the time period between :5 and :6.
While the query was running, I ran this

(1) select * from table ( dbms_xplan.display_cursor ('&SQL_ID',0,
'ADVANCED')); and got these:

Peeked Binds (identified by position):

---

   1 - :1 (NUMBER): XXXXX    2 - :2 (VARCHAR2(30), CSID=31): 'XXXX'    3 - :3 (NUMBER): XXXX    4 - :4 (VARCHAR2(30), CSID=31): 'XXXX'    5 - :5 (VARCHAR2(30), CSID=31): '2014-01-29 06:53:07'    6 - :6 (VARCHAR2(30), CSID=31): '2014-01-30 14:53:07' So it seems that the query was searching for data during that 32 hours between :5 and :6.
I also checked dba_hist_sqlbind, ran

(2) select SNAP_ID, NAME, POSITION, DATATYPE_STRING, WAS_CAPTURED,
to_char(LAST_CAPTURED,'DD-MON-YYYY HH24:MI:SS') LAST_CAPTURED, VALUE_STRING from dba_hist_sqlbind
where SQL_ID = '&SQL_ID'
order by snap_id,LAST_CAPTURED, POSITION;

It gave out totally different bind values and the last_captured was Dec 24th of last year. I assumed the values were old.

This morning I ran query (2) again on dba_hist_sqlbind with a range of snap_id to limit the time period to when the troublesome query was running and expected that the peeked bind values I saw in dbms_xplan.display_cursor last night when the query was running would be in dba_hist_sqlbind by now.
so I ran this in the morning:

(3) select SNAP_ID, NAME, POSITION, DATATYPE_STRING, WAS_CAPTURED,
to_char(LAST_CAPTURED,'DD-MON-
YYYY HH24:MI:SS') LAST_CAPTURED, VALUE_STRING from dba_hist_sqlbind
where SQL_ID = '&SQL_ID'
and snap_id between 215884 and 215902
order by snap_id, POSITION;

But no, even though i used snap_id to define that few hours when the troublesome query was running, it still returned the values captured on Dec 24th, same as these I saw last night.

I also ran query (1) on dbms_xplan.display_cursor() this morning, the values are different now. I assumed that this query was run with different values in the database since last night. However, I can't seem to find any evidence that this SQL was actually run again in OEM.

What are good ways to capture the bind values while the SQL is running and afterwards?
Thank you,
Chen

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 31 2014 - 21:41:22 CET