Re: Bind variable peeking question
Date: Fri, 31 Jan 2014 15:52:43 -0500
Message-Id: <C853DE69-269D-4430-86CE-111B69BCBC2C_at_gmail.com>
Peek binds are the ones used by the CBO at parsing. Captured are as the name implies obtained during execution time at regular intervals.
If you can use SQLT so you can see the history of both. MOS 215187.1
Sent from my iPhone
> On Jan 31, 2014, at 3:41 PM, Chen Zhou <oracle.unknowns_at_gmail.com> wrote:
>
> 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-lReceived on Fri Jan 31 2014 - 21:52:43 CET