kesqsMakeSql-invstat:elpsTime

ORA-700 [kesqsMakeSql-invstat:elpsTime] / ORA-700 [kesqsMakeSql-invstat:cpuTime] (Doc ID 811717.1)
Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.

SYMPTOMS

The following errors are reported daily in the alert log while running the SQL Tuning job:

  ORA-700 [kesqsMakeSql-invstat:elpsTime]
  ORA-700 [kesqsMakeSql-invstat:cpuTime]

ORA-00700: soft internal error, arguments: [kesqsMakeSql-invstat:elpsTime], [], [], [], [], [], [], []

----- Current SQL Statement for this session (sql_id=6jbrg916bjmqc) -----
DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :mydate;  broken BOOLEAN := FALSE;  job_name VARCHAR2(30) := :job_name;  job_subname VARCHAR2(30) := :job_subname;  job_owner VARCHAR2(30) := :job_owner;  job_start TIMESTAMP WITH TIME ZONE := :job_start;  job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE := :window_start;  window_end TIMESTAMP WITH TIME ZONE := :window_end;  BEGIN  DECLARE
         ename VARCHAR2(30);
       BEGIN
         ename := dbms_sqltune.execute_tuning_task(
                    'SYS_AUTO_SQL_TUNING_TASK');
       END;  :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0000000453F0BD30      7294  package body SYS.DBMS_SQLTUNE_INTERNAL
000000044F365B58         8  SYS.WRI$_ADV_SQLTUNE
000000044BD3CEB8       545  package body SYS.PRVT_ADVISOR
000000044BD3CEB8      2597  package body SYS.PRVT_ADVISOR
0000000457A528D0       241  package body SYS.DBMS_ADVISOR
0000000447EED340       702  package body SYS.DBMS_SQLTUNE
000000045B9717F0         4  anonymous block
----- Call Stack Trace -----
ksedst1 <- ksedst <- dbkedDefDump <- ksedmp <- PGOSF184_ksfdmp <- dbgexPhaseII <-
dbgexProcessError <- dbgeExecuteForError <- dbgePostErrorKGE <- dbkePostKGE_kgsf <-
kgeadse <- kgerinv_internal <- kgesoftnmierr <- kesqsMakeSql <- kesqsMakeSqlCb <-
kessiWorkloadFetch <- kesaiTuneSqlDrv <- spefcifa <- spefmccallstd <- pextproc <-
PGOSF493_peftrust <- PGOSF519_psdexsp <- rpiswu2 <- psdextp <- pefccal <- pefcal <-
pevm_FCAL <- pfrinstr_FCAL <- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <-
kkxexe <- opiexe <- kpoal8 <- opiodr <- kpoodr <- xupirtrc <- upirtrc <- kpurcsc <-
kpuexec <- OCIStmtExecute <- jslvec_execcb <- jslvswu <- jslve_execute0 <-
jslve_execute <- rpiswu2 <- kkjex1e <- kkjsexe <- kkjrdp <- opirip <- opidrv <-
sou2o <- opimai_real <- opimai

CAUSE

The SQL statistics within WRH$_SQLSTAT are wrong.

SQL> select dbid, instance_number, sql_id, plan_hash_value,
                    snap_id,
                    cpu_time_total, cpu_time_delta,
                    elapsed_time_total, elapsed_time_delta,
                    executions_total, executions_delta,
                    buffer_gets_total, buffer_gets_delta,
                    loads_total, loads_delta,
                    invalidations_total, invalidations_delta,
                    parse_calls_total, parse_calls_delta,
                    version_count, loaded_versions,
                    parsing_schema_name
            from wrh$_sqlstat
            where sql_id in (select sql_id from wrh$_sqlstat
                                        where elapsed_time_total > 1000000000000 or
                                                  elapsed_time_delta > 1000000000000 or
                                                  cpu_time_total > 1000000000000 or
                                                  cpu_time_delta > 1000000000000)
            order by dbid, instance_number, sql_id, plan_hash_value, snap_id;

The TOTALs are correct but the DELTAs and a few other results, like VERSION_COUNT, are not.

The large values for the DELTA columns are wrong. When converted to hex, it is actually text, seemingly from SQL statements (ref. Bug 7025700).

Bug 7025700: ORA-700: SOFT INTERNAL ERROR, ARGUMENTS: [KESQSMAKESQL-INVSTAT:CPUTIME]
  -> RDBMS Ver: 11.1.0.7.0
  -> Marked as duplicate of bug 8224438

Bug 7757533: ORA-700: SOFT INTERNAL ERROR, ARGUMENTS: [KESQSMAKESQL-INVSTAT:ELPSTIME]
  -> RDBMS Ver: 11.1.0.7.0
  -> Marked as duplicate of bug 8224438

Bug 8224438: STBH:ORA-700-[KESQSMAKESQL-INVSTAT:ELPSTIME], [],
  -> RDBMS Ver: 11.1.0.7
  -> Marked as duplicate of bug 7643188

Bug 7643188: SQL VERSION COUNT REPORTED IN AWR DOES NOT MATCH SQLSTATS FOR 1 SQL ID
  -> RDBMS Ver: 11.1.0.7
  -> Details: Suspicious SQL statistics flushed by AWR to WRH$_SQLSTAT; similar issues may
              exist for stats captured in a SQL Tuning Set.
              AWR SQL statistic columns may show invalid/corrupted data values.
  -> Fixed: PSU 11.1.0.7.2, 11.2

Bug 9253645: PATCH OF BASE BUG 7643188 STILL REPORTING ORA-00700 ON RAC 11G ENV.
   -> RDBMS Ver: 11.1.0.7
   -> Marked as duplicate of bug 7974905

Bug 7974905: INVALID AWR DATA CAUSES ORA-700 [KESQSMAKESQL-INVSTAT:CPUTIME]
   -> RDBMS Ver: 11.1.0.7
   -> Fixed: 11.2

SOLUTION

1. Apply patch 7643188 on top of patchset 11.1.0.7

    Available at Metalink:

           Patches & Updates

              Simple Search

                 - Unix machines:

                   Patch Number: 7643188
                   Platform: <Unix platform>

                 - Windows (32-bit) machines:

                   Patch Number: 8416539  -> patchset 11.1.0.7 patch 10
                   Platform: Micrsoft Windows (32-bit)

                 - Windows (64-bit) machines

                   Patch Number: 8416540  -> patchset 11.1.0.7 patch 10
                   Platform: Microsoft Windows x64 (64-bit)

-OR-

2. Apply 11.1.0.7 PSU 2 or any newer PSU

Remarks:

1. The fix will prevent for new invalid/corrupt data - it will not fix the already invalid/corrupt data !
    Means, after applying the patch the ORA-700 can still occur for a while but in fact doesn't harm.
    The ORA-700 will go away overtime when corrupted data is flushed out.
2. To suppress the ORA-700 messages in the alert file, you may also apply patch 7974905 on top of
    patchset 11.1.0.7. The ORA-700 is in fact a soft-error since non-fatal and should not be reported
    in the alert file.
    Bug 7974905 is fixed as from release 11.2. The fix for bug 7974905 suppresses ORA-700 messages
    in the alert file - it does not solve the corrupted data.