用户在v$sql中发现elapsed_time 的时间有问题,我看了下,也觉得有点奇怪。请各位帮忙解释下为何会出现这种情况??
v$sql 一个sql 语句导出如下: 其中
EXECUTIONS =2293,
ELAPSED_TIME = 9500593398 ,单位是ms
单纯换算 ELAPSED_TIME 9500593398 /1000/60/60/24 = 109(天)
也就是这个sql 已经运行了这么长,可是数据库才装了不超过10天。
insert into v$sql (SQL_TEXT, SQL_FULLTEXT, SQL_ID, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, FETCHES, EXECUTIONS, PX_SERVERS_EXECUTIONS, END_OF_FETCH_COUNT, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS, DIRECT_WRITES, BUFFER_GETS, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, ROWS_PROCESSED, COMMAND_TYPE, OPTIMIZER_MODE, OPTIMIZER_COST, OPTIMIZER_ENV, OPTIMIZER_ENV_HASH_VALUE, PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME, KEPT_VERSIONS, ADDRESS, TYPE_CHK_HEAP, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, SERVICE, SERVICE_HASH, MODULE, MODULE_HASH, ACTION, ACTION_HASH, SERIALIZABLE_ABORTS, OUTLINE_CATEGORY, CPU_TIME, ELAPSED_TIME, OUTLINE_SID, CHILD_ADDRESS, SQLTYPE, REMOTE, OBJECT_STATUS, LITERAL_HASH_VALUE, LAST_LOAD_TIME, IS_OBSOLETE, CHILD_LATCH, SQL_PROFILE, PROGRAM_ID, PROGRAM_LINE#, EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE, LAST_ACTIVE_TIME, BIND_DATA, TYPECHECK_MEM)
values ('Select a.SID, a.SERIAL# serial, a.STATUS, a.Username, a.Machine, a.LOGON_TIME, ltrim(b.Sql_Text, '' '') SQL_TEXT, b.SHARABLE_MEM, b.BUFFER_GETS, b.EXECUTIONS, b.DISK_READS, b.SORTS, OSUSER, PROGRAM, SCHEMANAME, B.ELAPSED_TIME elapsed_time, case when b.EXECUTIONS = 0 then null else round(B.ELAPSED_TIME/b.EXECUTIONS) end avg_elsp From v$session a, v$sql b Where a.Sql_Address = b.Address(+) And a.Sql_Hash_Value = b.Hash_Value(+) and a.STATUS = ''ACTIVE'' order by b.ELAPSED_TIME desc nulls last', 'Select a.SID,
a.SERIAL# serial,
a.STATUS,
a.Username,
a.Machine,
a.LOGON_TIME,
ltrim(b.Sql_Text, '' '') SQL_TEXT,
b.SHARABLE_MEM,
b.BUFFER_GETS,
b.EXECUTIONS,
b.DISK_READS,
b.SORTS,
OSUSER,
PROGRAM,
SCHEMANAME,
B.ELAPSED_TIME elapsed_time,
case when b.EXECUTIONS = 0 then null else round(B.ELAPSED_TIME/b.EXECUTIONS) end avg_elsp
From v$session a, v$sql b
Where a.Sql_Address = b.Address(+)
And a.Sql_Hash_Value = b.Hash_Value(+)
and a.STATUS = ''ACTIVE'' order by b.ELAPSED_TIME desc nulls last', '1s575xr0w3fvv', 44671, 11280, 8920, 2293, 1, 0, 0, 6878, 2293, 0, 2293, 0, 1, '2013-06-09/15:39:03', 0, 2293, 4, 0, 268, 0, 3054, 0, 11429, 0, 0, 46684, 3, 'ALL_ROWS', 1, 'E289FB89A4E49800CE001000AEF9C3E2CFFA331056414555519521105555551545545558591555449665851D5511058555155515152552455580588055A1454A8E09508021000020000000000100001000000002002000017D000000C02F034026A3001010000080830F4000002093510000E09701404A8E09504646262040262320030020003020A000A5A000', 1463175048, 58, 58, 'IPNMS', 0, '000007FF0C87D698', '00', 3250699131, 2619311110, 1180516266, 0, 'SYS$USERS', 0, 'JDBC Thin Client', -1748072636, '', 0, 0, '', 7042866586, 9500593398, null, '000007FF0C87D470', 6, 'N', 'VALID', 0, '2013-06-14/22:04:03', 'N', 4, '', 0, 0, 5.56248367953929E18, 1.08383759330889E19, to_date('17-06-2013 14:44:04', 'dd-mm-yyyy hh24:mi:ss'), '', 0);