关于v$session_longops中记录的信息
一，版本操作系统版本：AIX 6001
数据库版本：11.2.0.3
二，问题
今天在做数据库巡检的时候，发现V$session_longops中有多条ELAPSED_SECONDS 达到十几万秒的操作SQL> select SID,SERIAL#,OPNAME,TARGET,TARGET_DESC,UNITS,START_TIME,LAST_UPDATE_TIME,TIMESTAMP,TIME_REMAINING,ELAPSED_SECONDS,CONTEXT,MESSAGE,USERNAME from v$session_longops where sql_id='gg016jx9ddbqk';
SID SERIAL# OPNAME TARGET TARGE UNITS START_TIME LAST_UPDATE_ TIMESTAMP TIME_REMAINING ELAPSED_SECONDS CONTEXT MESSAGE USERNAME
------ ---------- ---------- ---------- ----- -------- ------------ ------------ ------------ -------------- --------------- ---------- ---------- --------
260 10741 Table Scan FILEINTER. Blocks 15-SEP-14 15-SEP-14 0 206 0 Table Scan FILEINTE
V_CHANNEL_ : FILEINT R
PRODUCT ER.V_CHANN
EL_PRODUCT
: 17151 ou
t of 17151
Blocks do
ne
2461 28913 Table Scan FILEINTER. Blocks 27-AUG-14 29-AUG-14 0 177729 0 Table Scan FILEINTE
V_CHANNEL_ : FILEINT R
PRODUCT ER.V_CHANN
EL_PRODUCT
: 17151 ou
t of 17151
Blocks do
ne#如上查出的两条记录是对同一张表的 table scan操作，但是一个是200多秒，一个是十几万秒，这个是怎么回事呢？（表大小有130M左右，有50几万的记录数）
3,疑问
请问怎么同样的操作记录的时间会差距这么大呢？
我手动跑了一下同样的语句，只耗时几十秒而已
你可以基于AWR 具体诊断 其运行情况，相关脚本： SELECT *
FROM (SELECT '1.v$sql'||'实例号:'||GV$SQL.inst_id source,
SQL_ID,
plan_hash_value,
TO_CHAR (FIRST_LOAD_TIME) begin_time,
'在cursor cache中' end_time,
executions "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM Gv$SQL
WHERE sql_id = '&A'
UNION ALL
SELECT '2.sqltuning set' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT '3.dba_advisor_sqlstats' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT DISTINCT
'4.dba_hist_sqlstat' || '实例号:' || SQL.INSTANCE_NUMBER
source,
sql_id,
PLAN_HASH_VALUE,
TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,
TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
SQL.executions_delta,
SQL.buffer_gets_delta
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"LIO/exec",
(SQL.cpu_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"CPUTIM/exec",
(SQL.elapsed_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ETIME/exec",
SQL.DISK_READS_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"PIO/exec",
SQL.ROWS_PROCESSED_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ROWs/exec"
FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
WHERE SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
AND SQL.dbid = (SELECT dbid FROM v$database)
AND s.snap_id = SQL.snap_id
AND sql_id IN ('&A'))
ORDER BY source, begin_time DESC;
页:
[1]