Bind variable peeking question
- From: Chen Zhou <oracle.unknowns@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 31 Jan 2014 12:41:22 -0800
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
Other related posts:
