Nigel Noble's Oracle Blog

28/03/2013

11.2.0.3 v$sqlstats.last_active_time stops changing and breaks AWR

Filed under: 11gR2, Monitoring — Nigel Noble @ 3:18 pm

My site uses a 3rd party SQL monitoring tool which collects data based on the Oracle view v$sqlstats.  The tool collects data for all sql statements which have been executed since the previous collection using the last_active_time column. A few months ago we noticed (after an upgrade to 11g) we would occasionally be missing some sql statements from the monitor graphs. We investigated the problem, re-produced a test case, raised a bug with Oracle and Support has just released an 11.2 patch.

Before I explain the issue and demonstrate the issue, I will explain what prompted me to post this blog item. Until now I always thought this just effected v$sqlstats (and my 3rd party monitoring tool) and not AWR. If you ever trace (10046) an AWR collection you will not find any references to v$sqlstats. If the data is missing from the 3rd Party tools “I can always rely on AWR?”…. I thought!

Well, I recently discovered that any time a sql statement drops off the 3rd party monitoring tool…… IT STOPS BEING MONITORED IN AWR’s DBA_HIST_SQLSTAT table as well. We have our AWR collection threshold set to collect as many sql statements as possible. This problem has nothing to do with the cost of the SQL statements and you could well find your most expensive sql statement just disappear from AWR for a period of time.

Let’s start at the very beginning….

v$sqlstats.last_active_time can stop changing with multiple child cursors

Note: I have only tested this issue with 11.2.0.3 but suspect it exists in other versions of 11g.

We took a SQL statement which had stopped appearing on our monitor and looked at v$sqlstats and v$sql in more detail.  We found that the v$sqlstats.last_active_time had stopped changing a number of days before. v$sql contained a number of child cursors and in that case at least one of the v$sql.last_active_time child cursors was still changing along with the execution count.  It appeared that the v$sqlstats last_active_time had got stuck at the first child cursors last_active_time.

I wrote the following script to compare v$sqlstats to v$sql.


set linesize 140
set pagesize 10000

column min_date format a20
column max_date format a20

select x.sql_id,x.command_type,
       to_char(x.min_date,'dd-mon-yyyy hh24:mi:ss') min_date,
       to_char(x.max_date,'dd-mon-yyyy hh24:mi:ss') max_date,
       x.cnt,
       to_char(a.last_active_time,'dd-mon-yyyy hh24:mi:ss') v_sqlstat_last_active,
       (x.max_date - a.last_active_time) * 60 * 60 * 24 diff_sec ,
       a.executions stat_exe,
       round(a.cpu_time / 1000000,0) cpu_secs
from (
           select s.sql_id,
                  s.command_type,
                  min(s.last_active_time) min_date,
                  max(s.last_active_time) max_date,
                  count(*) cnt,
                  sum(s.executions) sql_exe
           from v$sql s
           group by sql_id,command_type
           --having count(*) > 1 -- remove this because still possible for 1 child cursor row (child_number>0] to have mismatch on last_active_time in v$sqlstats
      ) x,
      v$sqlstats a
where x.sql_id = a.sql_id
and   to_char(x.max_date,'dd-mon-yyyy hh24:mi:ss') != to_char(a.last_active_time,'dd-mon-yyyy hh24:mi:ss')
and   (x.max_date - a.last_active_time) * 60 * 60 * 24 > 20 -- Filter out any where v$sqlstat last active is less than 20 seconds
order by 7 desc
/

NOTE: you might well find some last_active_time dates in the range of 1969 or 1970. This is a separate bug and patch is available. BUG 14262042. We tested with that patch applied but this is a separate issue to our issue. In the case of the “1970” issue, any sql statement which no longer exists within v$sql but still holds statistics within v$sqlstats, will result in a 1-jan-1970 date within v$sqlstats. I think the patch for BUG 14262042 will just return a null instead.

Output looks like this:


columns are:
sql_id
sql command type [eg 47 being plsql package call]
min and max last_active_times from v$sql for each child cursor
The number of child cursors found within v$sql
the current last_active_time from v$SQLSTATS
diff_sec is the difference in seconds between the v$sqlstats.last_active_time and max from v$sql
I also added total executions and CPU (seconds) to help find the more exspensive statements... missing from your monitoring.

SQL_ID        COMMAND_TYPE MIN_DATE             MAX_DATE                    CNT V_SQLSTAT_LAST_ACTIVE        DIFF_SEC   STAT_EXE   CPU_SECS
------------- ------------ -------------------- -------------------- ---------- -------------------------- ---------- ---------- ----------
sd65trqz6400z            2 11-mar-2013 17:20:37 11-mar-2013 17:20:37          1 01-jan-1970 00:00:00       1363022437    3263207       2396
5cnpabmck2nj3            2 11-mar-2013 17:20:00 11-mar-2013 17:20:00          1 01-jan-1970 00:00:00       1363022400       3354          1
b4wtbx3wqrtm4            2 20-feb-2013 06:20:00 11-mar-2013 17:15:01          3 20-feb-2013 06:20:00          1680901     123749         25
xcqqf08p0xt5k           47 27-feb-2013 12:42:40 11-mar-2013 17:00:08          3 27-feb-2013 12:42:40          1052248        101          0
ymx3gkyjwbnbw           47 28-feb-2013 03:53:49 11-mar-2013 17:18:06          2 28-feb-2013 03:53:49           998657       4369         14
esy9sxkb47qmc            6 03-mar-2013 11:24:10 11-mar-2013 17:16:45          6 03-mar-2013 11:24:10           712355      24801         17
brw327jvnsb92            2 03-mar-2013 10:34:07 11-mar-2013 16:07:46          4 03-mar-2013 10:34:07           711219       2259          1
ahx4v4j14ckhx            2 03-mar-2013 09:38:03 11-mar-2013 15:11:24          4 03-mar-2013 09:38:03           711201         20          0
5sb5vwwjp8a7h            2 03-mar-2013 10:46:43 11-mar-2013 14:52:06          3 03-mar-2013 10:46:43           705923         26          0
g4y6nw3tts7cc           47 04-mar-2013 07:40:44 11-mar-2013 17:20:01          2 04-mar-2013 07:40:44           639557      76230         10
9zshuv60xux34            2 04-mar-2013 19:03:47 11-mar-2013 07:03:47          2 04-mar-2013 19:03:47           561600          2          0
cs2toja8hgcsg           47 03-mar-2013 10:40:11 09-mar-2013 18:00:49          2 03-mar-2013 10:40:11           544838          2          0
9ymqamzcpr39m            7 02-mar-2013 14:43:50 08-mar-2013 21:54:43          2 02-mar-2013 14:43:50           544253          6          0
8b8podnzxzjgm           47 28-feb-2013 13:07:14 05-mar-2013 16:38:16          2 28-feb-2013 13:07:14           444662          2          0
4d94o1xyf2cpx           47 07-mar-2013 12:03:39 11-mar-2013 10:42:10          2 07-mar-2013 12:03:39           340711          2          0
693pa6n8ynz0r           47 06-mar-2013 16:12:40 08-mar-2013 11:19:29          2 06-mar-2013 16:12:40           155209          4          0
xcu12hwy0fjtn            2 10-mar-2013 00:43:50 11-mar-2013 17:20:00          2 10-mar-2013 00:43:50           146170      18520         10
uxnspqc0yrnbx            2 03-mar-2013 05:20:43 11-mar-2013 17:15:42          3 10-mar-2013 00:55:42           145200       4461          1
1ru5uts17fv4v            2 03-mar-2013 03:05:43 11-mar-2013 17:15:42          3 10-mar-2013 01:05:43           144599      78297          3
5n8c8qdr0yu6v            2 03-mar-2013 03:44:57 11-mar-2013 17:20:35          3 10-mar-2013 01:12:50           144465      41533          7
7tt01pvmm1t10            2 03-mar-2013 04:46:02 11-mar-2013 17:19:23          9 10-mar-2013 05:38:27           128456     491627        270
8h21t09fukcpc            2 03-mar-2013 02:21:07 11-mar-2013 17:20:19          9 10-mar-2013 05:53:52           127587     154670         39
29ut0ax2ftmk3            2 10-mar-2013 06:05:45 11-mar-2013 17:20:00          2 10-mar-2013 06:05:45           126855      18481          2
1ahw8d7tfd1k3            2 10-mar-2013 06:59:14 11-mar-2013 17:15:00          2 10-mar-2013 06:59:14           123346       4904          1
fdvfr5y72us7v            2 10-mar-2013 06:59:14 11-mar-2013 17:15:00          2 10-mar-2013 06:59:14           123346        613          0
bm6dwc8w9620q            2 10-mar-2013 09:00:00 11-mar-2013 15:00:00          2 10-mar-2013 09:00:00           108000         59          0
86f2uvygkca0v           47 07-mar-2013 11:36:49 08-mar-2013 17:06:01          2 07-mar-2013 11:36:49           106152          2          0
ajszyytf8k22g           47 07-mar-2013 13:43:28 08-mar-2013 15:51:05          2 07-mar-2013 13:43:28            94057          3          0
5u9hnrwn9692u           47 09-mar-2013 14:50:52 10-mar-2013 13:55:43          2 09-mar-2013 14:50:52            83091          2          0
02tnxz90xbubx           47 09-mar-2013 15:23:49 10-mar-2013 13:16:49          2 09-mar-2013 15:23:49            78780         10          0
bj9yc5xcwshpx           47 06-mar-2013 15:04:11 07-mar-2013 12:47:57          2 06-mar-2013 15:04:11            78226          4          0
4f9h072zn4vf7           47 07-mar-2013 17:01:47 08-mar-2013 13:35:36          2 07-mar-2013 17:01:47            74029          2          0
b877xnz576pvs            2 06-mar-2013 10:34:39 07-mar-2013 06:47:02          2 06-mar-2013 10:34:39            72743       1734          0
9264czbs1b7ay            2 06-mar-2013 10:34:39 07-mar-2013 06:47:02          2 06-mar-2013 10:34:39            72743       1326          0
f5bjuqtqxkwqt           47 03-mar-2013 22:07:09 04-mar-2013 16:43:05          2 03-mar-2013 22:07:09            66956          6          0
bwgr1p81pt3vs           47 11-mar-2013 15:43:56 11-mar-2013 16:20:13          2 11-mar-2013 15:43:56             2177          3          0
9vn82pwvmnujf           47 03-mar-2013 09:12:22 03-mar-2013 09:43:44          2 03-mar-2013 09:12:22             1882         10          0
8ndg9fxv2fdtu           47 09-mar-2013 17:04:11 09-mar-2013 17:11:16          2 09-mar-2013 17:04:11              425          4          0
887gthe2j0fzw           47 09-mar-2013 02:42:32 09-mar-2013 02:43:56          2 09-mar-2013 02:42:32               84          5          0
6f3fx86f6v67g           47 04-mar-2013 19:26:18 04-mar-2013 19:27:36          2 04-mar-2013 19:26:18               78          3          0

I found many sql statements which had large difference between the v$sqlstats.last_active_time and the latest last_active_time within v$sql.

So it seems that there is some condition where multiple child cursors (for whatever reason) can cause the v$sqlstats.last_active_time to stall.

Test Case

I constructed a way to force Oracle to create two child cursors for the same sql statement. I created a PLSQL package which declares two bind variables at different lengths (See http:/jonathanlewis.wordpress.com/2007/01/05/bind-variables/  ) and then executes the same sql statement with each. Although this causes two child cursors it does not in itself break the last_active_time. I then found running the test twice and re-connecting to the database in between I could also break the v$sqlstats.last_active_time.

Once I had a test case I sent it to Oracle Support and they created the following:

Bug 16084340 – V$SQLSTATS LAST_ACTIVE_TIME STUCK AT FIRST CHILD CURSOR LAST_ACTIVE_TIME

I have since also updated the test case to also try and show that AWR is also effected by the same issue. The PLSQL package has also been made much more expensive with a long loop to ensure it would always be captured by AWR.

Test case


TEST:

Create following script in directory.

invalid_active_time.sql
=======================

set linesize 140

set pagesize 1000

select x.sql_id,x.command_type,
       to_char(x.min_date,'dd-mon-yyyy hh24:mi:ss') min_date,
       to_char(x.max_date,'dd-mon-yyyy hh24:mi:ss') max_date,
       x.cnt,
       to_char(a.last_active_time,'dd-mon-yyyy hh24:mi:ss') v_sqlstat_last_active,
       (x.max_date - a.last_active_time) * 60 * 60 * 24 diff_sec ,
       a.executions stat_exe
from (
           select s.sql_id,
                  s.command_type,
                  min(s.last_active_time) min_date,
                  max(s.last_active_time) max_date,
                  count(*) cnt,
                  sum(s.executions) sql_exe
           from v$sql s
           group by sql_id,command_type
           --having count(*) > 1
      ) x,
      v$sqlstats a
where x.sql_id = a.sql_id
and sql_text like '%&1%'
--and   to_char(x.max_date,'dd-mon-yyyy hh24:mi:ss') != to_char(a.last_active_time,'dd-mon-yyyy hh24:mi:ss')
--and   (x.max_date - a.last_active_time) * 60 * 60 * 24 > 20 -- Filter out any where v$sqlstat last active is less than 20 seconds
order by 7 desc
/

Load the following stored procedure. [ my test uses SYS but should be possible to move to another user]

create or replace package test_active as
     procedure payload (id varchar2);
end;
/
show error

create or replace package body test_active as
     procedure payload (id varchar2) is
     tmp number;
     begin
        dbms_output.put_line(id);
        -- burn CPU to ensure it would be seen within AWR
        for i in 1..5000000
        loop
         tmp:=tmp +1;
        end loop;
     end payload;
end;
/

Create script test_active.sql

Before you run the script, each time replace all references of “SEARCHSTRING011″ to something unique so each run creates a new sql_text and sql_id.

The test does the following:

  • Takes a AWR “snap”
  • executes the first sql/page statement 100 times using the bind variable defined as varchar2(20).
  • Takes another AWR “snap”
  • executes the second statement 50 times using the longer defined variable (varchar2(100)). This is done only 50 times to try and make it clear in AWR which collections are missing.
  • Take another AWR “snap”
  • At this point, we should have 2 delta within AWR.
  • We should have two child cursors although the last_active_time is NOT yet broken.
  • NOW THE SCRIPT RE-CONNECTS and runs the above again
  • ** At this point the last_active_time seems to break**
  • AWR should now show us 4 collection delta’s for this sql statement.
  • When I run the test for the same SEARCHSTRINGnnn I no longer get any AWR snaps
set time on

declare
id_char_short varchar2(20);  --short variable
id_char_long varchar2(100);  -- longer string to force multiple child cursor
begin
-- collect AWR
DBMS_WORKLOAD_REPOSITORY.create_snapshot;
--
for i in 1 .. 100
loop
   id_char_short := i;
   execute immediate 'begin /*+SEARCHSTRING011*/ test_active.payload(id=>:1); end;' using id_char_short;
end loop;
--
-- collect AWR
DBMS_WORKLOAD_REPOSITORY.create_snapshot;
--
dbms_lock.sleep(30);
--
for i in 1 .. 50
loop
   id_char_long := i;
   execute immediate 'begin /*+SEARCHSTRING011*/ test_active.payload(id=>:1); end;' using id_char_long;
end loop;
--
-- collect AWR
DBMS_WORKLOAD_REPOSITORY.create_snapshot;
--
end;
/

@invalid_active_time SEARCHSTRING011

-- reconnect -- in this case we need to do this to cause the last_active_times to break

connect / as sysdba

declare
id_char_short varchar2(20);  --short variable
id_char_long varchar2(100);  -- longer string to force multiple child cursor
begin
-- collect AWR
DBMS_WORKLOAD_REPOSITORY.create_snapshot;
--
for i in 1 .. 100
loop
   id_char_short := i;
   execute immediate 'begin /*+SEARCHSTRING011*/ test_active.payload(id=>:1); end;' using id_char_short;
end loop;
--
-- collect AWR
DBMS_WORKLOAD_REPOSITORY.create_snapshot;
--
dbms_lock.sleep(30);
--
for i in 1 .. 50
loop
   id_char_long := i;
   execute immediate 'begin /*+SEARCHSTRING011*/ test_active.payload(id=>:1); end;' using id_char_long;
end loop;
--
-- collect AWR
DBMS_WORKLOAD_REPOSITORY.create_snapshot;
--
end;
/

column SQL_ID format a15
column MAX_DATE format a25
column V_SQLSTAT_LAST_ACTIVE format a25

@invalid_active_time SEARCHSTRING011

rem now dump the stats, show be able to account for 400 executions
select
 to_char(snap.begin_interval_time,'DD-MON-YYYY HH24:MI:SS') ,
 (ELAPSED_TIME_DELTA/EXECUTIONS_DELTA)/1000  avg_elapsed,
 EXECUTIONS_DELTA  execs,
 ROWS_PROCESSED_DELTA rws,
 CPU_TIME_DELTA       cpu,
 ELAPSED_TIME_DELTA   elapse
from dba_hist_sqlstat  sql,
     dba_hist_snapshot snap
where sql.sql_id='&broken_sql_id'
and sql.dbid=snap.dbid
and sql.snap_id=snap.snap_id
order by sql.snap_id
/

Now run the following within sqlplus….


SQL> @test_active.sql

PL/SQL procedure successfully completed.

old  21: and sql_text like '%&1%'
new  21: and sql_text like '%SEARCHSTRING011%'

SQL_ID          COMMAND_TYPE MIN_DATE                  MAX_DATE                         CNT V_SQLSTAT_LAST_ACTIVE       DIFF_SEC   STAT_EXE
--------------- ------------ ------------------------- ------------------------- ---------- ------------------------- ---------- ----------
anprjxnccc6jg             47 15-jan-2013 18:04:41      15-jan-2013 18:04:48               2 15-jan-2013 18:04:41               7        150

Connected.

PL/SQL procedure successfully completed.

old  21: and sql_text like '%&1%'
new  21: and sql_text like '%SEARCHSTRING011%'

SQL_ID          COMMAND_TYPE MIN_DATE                  MAX_DATE                         CNT V_SQLSTAT_LAST_ACTIVE       DIFF_SEC   STAT_EXE
--------------- ------------ ------------------------- ------------------------- ---------- ------------------------- ---------- ----------
anprjxnccc6jg             47 15-jan-2013 18:04:41      15-jan-2013 18:05:38               2 15-jan-2013 18:04:41              57        300

Enter value for broken_sql_id: anprjxnccc6jg
old  10: where sql.sql_id='&broken_sql_id'
new  10: where sql.sql_id='anprjxnccc6jg'

TO_CHAR(SNAP.BEGIN_INTERVAL_TIME,'DD-MON-YYYYHH24:MI:SS')    AVG_ELAPSED      EXECS        RWS        CPU     ELAPSE
------------------------------------------------------------ ----------- ---------- ---------- ---------- ----------
15-JAN-2013 18:03:58                                           130.19341        100        100   12981026   13019341
15-JAN-2013 18:04:11                                            126.0937         50         50    6304041    6304685

In the example the v$sqlstats.last_active_time appears to have stopped at 15-jan-2013 18:04:41 even though v$sql.last_active_time is 57 seconds later at 18:05:38.

The query on the dba_hist_sqlstats table also seems to be missing the two more snap points.

If I keep running the test now for the same Search string/sql_id the last_active_time on v$sqlstats has stopped at 15-jan-2013 18:04:41 and I nolonger see any AWR sql snaps.

If I go back a number of hours later and run the same SEARCHSTRING value you see v$sqlstats.last_active_date still stuck even though v$sql changes and the execution count also increase. AWR nolonger reports and new snaps (only showing the original 2 snap delta from the first run a numbers of hours earlier)


SQL> @test_active.sql

PL/SQL procedure successfully completed.

old  21: and sql_text like '%&1%'
new  21: and sql_text like '%SEARCHSTRING011%'

SQL_ID          COMMAND_TYPE MIN_DATE                  MAX_DATE                         CNT V_SQLSTAT_LAST_ACTIVE       DIFF_SEC   STAT_EXE
--------------- ------------ ------------------------- ------------------------- ---------- ------------------------- ---------- ----------
anprjxnccc6jg             47 15-jan-2013 18:04:41      15-jan-2013 21:19:17               2 15-jan-2013 18:04:41           11676        450

Connected.

PL/SQL procedure successfully completed.

old  21: and sql_text like '%&1%'
new  21: and sql_text like '%SEARCHSTRING011%'

SQL_ID          COMMAND_TYPE MIN_DATE                  MAX_DATE                         CNT V_SQLSTAT_LAST_ACTIVE       DIFF_SEC   STAT_EXE
--------------- ------------ ------------------------- ------------------------- ---------- ------------------------- ---------- ----------
anprjxnccc6jg             47 15-jan-2013 18:04:41      15-jan-2013 21:20:07               2 15-jan-2013 18:04:41           11726        600

Enter value for broken_sql_id: anprjxnccc6jg
old  10: where sql.sql_id='&broken_sql_id'
new  10: where sql.sql_id='anprjxnccc6jg'

TO_CHAR(SNAP.BEGIN_INTERVAL_TIME,'DD-MON-YYYYHH24:MI:SS')    AVG_ELAPSED      EXECS        RWS        CPU     ELAPSE
------------------------------------------------------------ ----------- ---------- ---------- ---------- ----------
15-JAN-2013 18:03:58                                           130.19341        100        100   12981026   13019341
15-JAN-2013 18:04:11                                            126.0937         50         50    6304041    6304685

Oracle has just published a fix

Oracle fixed this issue within 12.2 and just published a 11.2.0.3 backport fix. The updated bug note now gives some more detail to the problem.

Bug 16084340 - V$SQLSTATS LAST_ACTIVE_TIME STUCK AT FIRST CHILD CURSOR LAST_ACTIVE_TIME
Fixed in 12.2

RELEASE NOTES:
The LAST_ACTIVE_TIME column of V$SQLSTATS could have been incorrect for certain cursor types.
This has now been resolved.
REDISCOVERY INFORMATION:You may be encountering this bug if [G]V$SQLSTATS.LAST_ACTIVE_TIME looks incorrect
and the cursor involved has a zero plan hash value
WORKAROUND:None

So looking at the 700 or so statements I see in my current running instance, they all in fact do have a 0 plan_hash_value. They are all types of sql statements where you would not expect to have a SQL plan (eg, PLSQL Package calls, Simple “INSERT INTO table VALUES”, Selects over a database link where ALL the objects are remote, updates over a database link again where all the objects are remote.

Since the 11.2 backport has just become available I have run the test with the patch applied. The result of the above test case now looks like this:

Notice that the DIFF_SEC is always value of 0 seconds (difference between the max v$sql.last_active_time and that of the v$sqlstat.last_active_time.
Notice that there are 4 AWR collection samples.


PL/SQL procedure successfully completed.

old  21: and a.sql_text like '%&1%'
new  21: and a.sql_text like '%SEARCHSTRING022%'

SQL_ID          COMMAND_TYPE MIN_DATE                  MAX_DATE                         CNT V_SQLSTAT_LAST_ACTIVE       DIFF_SEC   STAT_EXE
--------------- ------------ ------------------------- ------------------------- ---------- ------------------------- ---------- ----------
9v6bvctwu7fja             47 28-mar-2013 16:41:29      28-mar-2013 16:41:36               2 28-mar-2013 16:41:36               0        150

Connected.

PL/SQL procedure successfully completed.

old  21: and a.sql_text like '%&1%'
new  21: and a.sql_text like '%SEARCHSTRING022%'

SQL_ID          COMMAND_TYPE MIN_DATE                  MAX_DATE                         CNT V_SQLSTAT_LAST_ACTIVE       DIFF_SEC   STAT_EXE
--------------- ------------ ------------------------- ------------------------- ---------- ------------------------- ---------- ----------
9v6bvctwu7fja             47 28-mar-2013 16:41:29      28-mar-2013 16:42:25               2 28-mar-2013 16:42:25               0        300

Enter value for broken_sql_id: 9v6bvctwu7fja
old  10: where sql.sql_id='&broken_sql_id'
new  10: where sql.sql_id='9v6bvctwu7fja'

TO_CHAR(SNAP.BEGIN_INTERVAL_TIME,'DD-MON-YYYYHH24:MI:SS')    AVG_ELAPSED      EXECS        RWS        CPU     ELAPSE
------------------------------------------------------------ ----------- ---------- ---------- ---------- ----------
28-MAR-2013 16:40:45                                           130.99545        100        100   13093009   13099545
28-MAR-2013 16:40:59                                           126.88286         50         50    6344036    6344143
28-MAR-2013 16:41:36                                           124.08531        100        100   12405113   12408531
28-MAR-2013 16:41:49                                           125.54124         50         50    6274045    6277062

About these ads

7 Comments »

  1. [...] from Nigel Noble, comes another explanation for why the AWR report might be hiding expensive SQL – a bug, which doesn’t get fixed until 12.2 (although there are backports in [...]

    Pingback by Missing SQL | Oracle Scratchpad — 29/03/2013 @ 10:35 am

  2. Nice Blog.

    Thanks
    Pankaj Gupta

    Comment by Pankaj Gupta — 29/03/2013 @ 7:37 pm

  3. Thanks. Found this from.Jonathan Lewis’s post.

    Comment by Hemant K Chitale — 01/04/2013 @ 1:30 am

  4. Hi,

    Good Post.
    I’m seeing also the oposite case in our production system:

    http://pnmazzei.wordpress.com/2013/05/16/wrong-awr-statistics-for-sql-with-obsolete-parent-cursors/

    Comment by Pablo — 17/05/2013 @ 8:45 am

    • Nice post!

      Will we ever get Oracle to add up correctly! :-)

      Nigel.

      Comment by Nigel Noble — 22/05/2013 @ 2:55 pm

  5. Nigel,

    excellent post and information.
    Just fyi: Both issues got fixed in 11.2.0.4 as well:

    14262042 LAST_ACTIVE_TIME from V$SQLSTATS may be wrong
    16084340 V$SQLSTATS LAST_ACTIVE_TIME wrong for certain cursor types

    Thanks, Mike

    Comment by Mike Dietrich — 16/12/2013 @ 10:21 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: