ORA-00700: soft internal error, …

Errors in file/oracle_ebs/prod/db/tech_st/11.1.0/admin/PROD_ebs/diag/rdbms/prod/PROD/trace/PROD_j001_20847.trc (incident=2118696):
ORA-00700: soft internal error, arguments:[kesqsMakeSql-invstat:cpuTime], [], [], [], [], [], [], []
Incident details in:/oracle_ebs/prod/db/tech_st/11.1.0/admin/PROD_ebs/diag/rdbms/prod/PROD/incident/incdir_2118696/PROD_j001_20847_i2118696.trc
Tue Jun 12 13:01:09 2012
Trace dumping is performing id=[cdmp_20120612130109]
Tue Jun 12 13:01:11 2012
Sweep Incident[2118695]: completed
Tue Jun 12 13:01:23 2012
Errors in file/oracle_ebs/prod/db/tech_st/11.1.0/admin/PROD_ebs/diag/rdbms/prod/PROD/trace/PROD_j001_20847.trc (incident=2118697):
ORA-00700: soft internal error, arguments:[kesqsMakeSql-invstat:elpsTime], [], [], [], [], [], [], []
Incident details in:/oracle_ebs/prod/db/tech_st/11.1.0/admin/PROD_ebs/diag/rdbms/prod/PROD/incident/incdir_2118697/PROD_j001_20847_i2118697.trc
Errors in file/oracle_ebs/prod/db/tech_st/11.1.0/admin/PROD_ebs/diag/rdbms/prod/PROD/trace/PROD_j001_20847.trc (incident=2118698):
ORA-00700: soft internal error, arguments:[kesqsMakeSql-invstat:cpuTime], [], [], [], [], [], [], []
Incident details in:/oracle_ebs/prod/db/tech_st/11.1.0/admin/PROD_ebs/diag/rdbms/prod/PROD/incident/incdir_2118698/PROD_j001_20847_i2118698.trc
Tue Jun 12 13:02:07 2012
Incremental checkpoint up to RBA [0xb229.d1762.0], current log tailat RBA [0xb229.ea044.0]
Tue Jun 12 13:02:14 2012
Sweep Incident[2118698]: completed
Sweep Incident[2118697]: completed
Sweep Incident[2118696]: completed

 

ORA-700 [kesqsMakeSql-invstat:elpsTime] / ORA-700[kesqsMakeSql-invstat:cpuTime] [ID 811717.1]

---

 修改时间07-FEB-2011     类型PROBLEM     状态PUBLISHED 

In this Document
  Symptoms
  Cause
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 andlater   [Release:11.1 and later ]

Symptoms

The following errors are reported daily in the alert log whilerunning 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_dateTIMESTAMP WITH TIME ZONE := :mydate;  brokenBOOLEAN := 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 WITHTIME ZONE := :job_scheduled_start;  window_startTIMESTAMP 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 fromwrh$_sqlstat
                                       where elapsed_time_total > 1000000000000 or
                                                 elapsed_time_delta > 1000000000000or
                                                 cpu_time_total > 1000000000000or
                                                 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 convertedto 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 bug8224438

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

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

Bug 7643188: SQL VERSION COUNT REPORTED IN AWR DOES NOT MATCHSQLSTATS FOR 1 SQL ID
  -> RDBMS Ver: 11.1.0.7
  -> Details: Suspicious SQLstatistics 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 datavalues.
  -> Fixed: PSU 11.1.0.7.2,11.2

Bug 9253645: PATCH OF BASE BUG 7643188 STILL REPORTING ORA-00700ON RAC 11G ENV.
   -> RDBMS Ver:11.1.0.7
   -> Marked asduplicate 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

    Availableat Metalink:

          Patches & Updates

             Simple Search

                - Unix machines:

                  Patch Number: 7643188
                  Platform: <Unix platform>

                - Windows (32-bit) machines:

                  Patch Number: 8416539  -> patchset11.1.0.7 patch 10
                  Platform: Micrsoft Windows (32-bit)

                - Windows (64-bit) machines

                  Patch Number: 8416540  -> patchset11.1.0.7 patch 10
                  Platform: Microsoft Windows x64 (64-bit)

-OR-

2. Apply 11.1.0.7 PSU patch >= 11.1.0.7.2

    Latestavailable PSU patch is currently 11.1.0.7.6 (patch10248531)

 

Remarks:

1. The fix will prevent for new invalid/corrupt data - it will notfix the already invalid/corrupt data !
    Means, afterapplying the patch the ORA-700 can still occur for a while but infact doesn't harm.
    The ORA-700will go away overtime when corrupted data is flushed out.
2. To suppress the ORA-700 messages in the alert file, you may alsoapply patch 7974905 on top of
    patchset11.1.0.7. The ORA-700 is in fact a soft-error since non-fatal andshould not be reported
    in the alertfile.
    Bug 7974905is fixed as from release 11.2. The fix for bug 7974905 suppressesORA-700 messages
    in the alertfile - it does not solve the corrupted data.

References

BUG:7757533- ORA-00700: SOFT INTERNAL ERROR, ARGUMENTS:[KESQSMAKESQL-INVSTAT:ELPSTIME]
NOTE:161549.1 - Oracle Database Server and Networking Patchesfor Microsoft Platforms
NOTE:560295.1 - 11.1.0.x Oracle Database and Networking Patchesfor Microsoft Platforms