Monday, July 28, 2014

Oracle v$sql_monitor showing ORA-10173 .. coincidentally the same time my server crashed.

Reason for the v$sql_monitor status "DONE (ERROR)" is because the server has an power outage? First time looking into this .....


SYS> select distinct status from v$sql_monitor;

STATUS
-------------------
DONE (ERROR)
DONE (ALL ROWS)
DONE

SYS> select * from v$sql_monitor where status='DONE (ERROR)';

       KEY STATUS                   USER# USERNAME                       MODULE                                           ACTION                           SERVICE_NAME                             CLIENT_IDENTIFIER                                                 CLIENT_INFO                                                      PROGRAM                                          PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID FIRST_REF
---------- ------------------- ---------- ------------------------------ ------------------------------------------------ -------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------------------------------------------ --------------------- ------------------------- --------------- ------------------- ---------
LAST_REFR REFRESH_COUNT        SID PROCE SQL_ID
--------- ------------- ---------- ----- -------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I SQL_EXEC_ SQL_EXEC_ID SQL_PLAN_HASH_VALUE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE SQL_CHILD_ADDRES SESSION_SERIAL# P  PX_MAXDOP PX_MAXDOP_INSTANCES PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED PX_SERVER# PX_SERVER_GROUP PX_SERVER_SET PX_QCINST_ID   PX_QCSID ERROR_NUMBER                      ERRO
- --------- ----------- ------------------- ------------------------ ------------------------ ---------------- --------------- - ---------- ------------------- -------------------- -------------------- ---------- --------------- ------------- ------------ ---------- ---------------------------------------- ----
ERROR_MESSAGE                                                                                                                                                                                    BINDS_XML                                                                         OTHER_XML                                                                        ELAPSED_TIME QUEUING_TIME   CPU_TIME    FETCHES BUFFER_GETS DISK_READS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------ ------------ ---------- ---------- ----------- ----------
DIRECT_WRITES IO_INTERCONNECT_BYTES PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES PHYSICAL_WRITE_REQUESTS PHYSICAL_WRITE_BYTES APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME
------------- --------------------- ---------------------- ------------------- ----------------------- -------------------- --------------------- --------------------- ----------------- ----------------- --------------- --------------
6.6572E+11 DONE (ERROR)                 0 SYS                            DBMS_SCHEDULER                                   ORA$AT_SQ_SQL_SW_2771            SYS$USERS                                  oracle@privCloud (J000)                                                                                                                  25-JUL-14
25-JUL-14            18        302 j000  1u7skbm9tvwvk
SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "RB#3")  */ 1 AS C1 FROM "SYS"."X$KTFBUE" "U#1", "SYS"."RECYCLEBIN$" "RB#3" WHERE ("U#1"."KTFBUESEGTSN"="RB#3"."TS#") AND ("U#1"."KTFBUESEGFNO"="RB#3"."FILE#") AND ("U#1"."KTFBUESEGBNO"="RB#3"."BLOCK#")) innerQuery
Y 25-JUL-14    16777216          2108298085               9.6089E+18               9.7014E+18 00000001DB549520             164 N                                                                   10173                             ORA
ORA-10173: Dynamic Sampling time-out error                                                                                                                                                             33995036     0    2270654          1       40324       9145
            0              74915840                   9145            74915840                       0                    0                     0                     0                 0          33262831                0              0

The last shown that the server was rebooted by SOMEONE.


oracle   pts/1        10.16.185.21     Tue Jul 29 10:56 - 15:41  (04:44)
oracle   pts/1        10.16.185.21     Mon Jul 28 10:27 - 13:16  (02:48)
oracle   pts/1        10.113.231.204   Fri Jul 25 15:36 - 15:50  (00:13)
reboot   system boot  2.6.18-238.el5   Fri Jul 25 15:35         (9+23:01)
oracle   pts/1        10.113.231.204   Fri Jul 25 08:54 - 11:08  (02:13)
oracle   pts/2        10.113.224.30    Thu Jul 24 16:21 - 20:03  (03:42)
oracle   pts/1        10.113.224.30    Thu Jul 24 14:52 - 18:13  (03:21)

No comments:

Post a Comment