RAC+DATAGUARD，凌晨1点左右，执行计划改变，查找不出来原因
11.2.0.3的RAC系统，做了单机dataguard，系统中每个小时有个跑批的语句，循环100多次。平时白天交易量大的时候，速度都没问题，但是只要是凌晨1点左右开始，速度就特别慢，在dataguard只读模式下查询的，同时在dataguard下生成了ash报告，可以看到这个语句变成了3种执行计划方式267u084sjc4rc 581374162 15 87.95 INDEX - RANGE SCAN 25.00 CPU + Wait for CPU 25.00 select d.TRAN_SNO DTRAN_SNO, t...
267u084sjc4rc 581374162 15 87.95180722891566265060240963855421686747 TABLE ACCESS - BY GLOBAL INDEX ROWID 24.60 CPU + Wait for CPU 24.60
267u084sjc4rc 581374162 15 87.95180722891566265060240963855421686747 TABLE ACCESS - BY GLOBAL INDEX ROWID 23.59 CPU + Wait for CPU 23.59
加了附件图片和ASH报告，同时通过脚本查询，但是历史执行计划查不到结果。
执行的脚本如下：
set lines 150 pages 150
col BEGIN_INTERVAL_TIME for a23
col PLAN_HASH_VALUE for 9999999999
col date_time for a30
col snap_id heading 'SnapId'
col executions_delta heading "No. of exec"
col sql_profile heading "SQL|Profile" for a7
col date_time heading 'Date time'
col avg_lio heading 'LIO/exec' for 99999999999.99
col avg_cputime heading 'CPUTIM/exec' for 9999999.99
col avg_etime heading 'ETIME/exec' for 9999999.99
col avg_pio heading 'PIO/exec' for 9999999.99
col avg_row heading 'ROWs/exec' for 9999999.99
SELECT distinct
s.snap_id ,
PLAN_HASH_VALUE,
to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
SQL.executions_delta,
SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,
--SQL.ccwait_delta,
(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,
(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,
SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,
SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row
--,SQL.sql_profile
FROM
dba_hist_sqlstat SQL,
dba_hist_snapshot s
WHERE
SQL.instance_number =(select instance_number from v$instance)
and SQL.dbid =(select dbid from v$database)
and s.snap_id = SQL.snap_id
AND sql_id in
('&SQLID') order by s.snap_id
/
这个没有显示结果。
然后执行了另外一个脚本
select a.INSTANCE_NUMBER,a.snap_id,a.sql_id,a.plan_hash_value,b.begin_interval_time
from dba_hist_sqlstat a, dba_hist_snapshot b
where sql_id ='267u084sjc4rc'
and a.snap_id = b.snap_id
order by instance_number, snap_id;
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='267u084sjc4rc' order by TIMESTAMP;
都未发现改变的执行计划，但是ash报告明显可以看到从INDEX - RANGE SCAN 到TABLE ACCESS - BY GLOBAL INDEX ROWID
请问下这个要从什么方向重新查找。
这几个脚本，分别在dataguard只读模式下查询，RAC的一个节点中查询。ash报告是在dataguard只读模式下生成的。 给出 针对该SQL_ID 的脚本运行，使用代码模式贴出来select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED +PEEKED_BINDS'));
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;
SQL> SELECT *
2 FROM (SELECT '1.v$sql'||'实例号:'||GV$SQL.inst_id source,
3 SQL_ID,
4 plan_hash_value,
5 TO_CHAR (FIRST_LOAD_TIME) begin_time,
6 '在cursor cache中' end_time,
7 executions "No. of exec",
8 (buffer_gets / executions) "LIO/exec",
9 (cpu_time / executions / 1000000) "CPUTIM/exec",
10 (elapsed_time / executions / 1000000) "ETIME/exec",
11 (disk_reads / executions) "PIO/exec",
12 (ROWS_PROCESSED / executions) "ROWs/exec"
13 FROM Gv$SQL
14 WHERE sql_id = '&A'
15 UNION ALL
16 SELECT '2.sqltuning set' source,
17 sql_id,
18 plan_hash_value,
19 'JUST SQLSET NO DATE' begin_time,
20 'JUST SQLSET NO DATE' end_time,
21 EXECUTIONS "No. of exec",
22 (buffer_gets / executions) "LIO/exec",
23 (cpu_time / executions / 1000000) "CPUTIM/exec",
24 (elapsed_time / executions / 1000000) "ETIME/exec",
25 (disk_reads / executions) "PIO/exec",
26 (ROWS_PROCESSED / executions) "ROWs/exec"
27 FROM dba_sqlset_statements
28 WHERE SQL_ID = '&A'
29 UNION ALL
30 SELECT '3.dba_advisor_sqlstats' source,
31 sql_id,
32 plan_hash_value,
33 'JUST SQLSET NO DATE' begin_time,
34 'JUST SQLSET NO DATE' end_time,
35 EXECUTIONS "No. of exec",
36 (buffer_gets / executions) "LIO/exec",
37 (cpu_time / executions / 1000000) "CPUTIM/exec",
38 (elapsed_time / executions / 1000000) "ETIME/exec",
39 (disk_reads / executions) "PIO/exec",
40 (ROWS_PROCESSED / executions) "ROWs/exec"
41 FROM dba_sqlset_statements
42 WHERE SQL_ID = '&A'
43 UNION ALL
44 SELECT DISTINCT
45 '4.dba_hist_sqlstat' || '实例号:' || SQL.INSTANCE_NUMBER
46 source,
47 sql_id,
48 PLAN_HASH_VALUE,
49 TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_tim
50 TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
51 SQL.executions_delta,
52 SQL.buffer_gets_delta
53 / DECODE (NVL (SQL.executions_delta, 0),
54 0, 1,
55 SQL.executions_delta)
56 "LIO/exec",
57 (SQL.cpu_time_delta / 1000000)
58 / DECODE (NVL (SQL.executions_delta, 0),
59 0, 1,
60 SQL.executions_delta)
61 "CPUTIM/exec",
62 (SQL.elapsed_time_delta / 1000000)
63 / DECODE (NVL (SQL.executions_delta, 0),
64 0, 1,
65 SQL.executions_delta)
66 "ETIME/exec",
67 SQL.DISK_READS_DELTA
68 / DECODE (NVL (SQL.executions_delta, 0),
69 0, 1,
70 SQL.executions_delta)
71 "PIO/exec",
72 SQL.ROWS_PROCESSED_DELTA
73 / DECODE (NVL (SQL.executions_delta, 0),
74 0, 1,
75 SQL.executions_delta)
76 "ROWs/exec"
77 FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
78 WHERE SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
79 AND SQL.dbid = (SELECT dbid FROM v$database)
80 AND s.snap_id = SQL.snap_id
81 AND sql_id IN ('&A'))
82 ORDER BY source, begin_time DESC;
输入 a 的值: 267u084sjc4rc
原值 14: WHERE sql_id = '&A'
新值 14: WHERE sql_id = '267u084sjc4rc'
输入 a 的值: 267u084sjc4rc
原值 28: WHERE SQL_ID = '&A'
新值 28: WHERE SQL_ID = '267u084sjc4rc'
输入 a 的值: 267u084sjc4rc
原值 42: WHERE SQL_ID = '&A'
新值 42: WHERE SQL_ID = '267u084sjc4rc'
输入 a 的值: 267u084sjc4rc
原值 81: AND sql_id IN ('&A'))
新值 81: AND sql_id IN ('267u084sjc4rc'))
未选定行
SQL>SQL> select plan_table_output
2 from table(dbms_xplan.display_awr('267u084sjc4rc',
3 null,
4 null,
5 'ADVANCED +PEEKED_BINDS'));
未选定行
全无结果， 确认你运行的环境正确，否则说明该SQL_ID对应的信息已不可见 Liu Maclean(刘相兵 发表于 2015-1-22 11:21 static/image/common/back.gif
全无结果， 确认你运行的环境正确，否则说明该SQL_ID对应的信息已不可见
这种不可见的信息，我是不是已经无法追溯了。
是否有可能与自动任务的自动搜集统计信息有关？比如到凌晨1点多正好搜集这张分区表数据，
这张表比较大，1亿3千万数据，每天大概是百万左右新增数据，我看了下搜集历史，
最后的搜集时间是14号晚上搜集完成的，目前正在使用的分区，是2月14日搜集完毕的。
太晚了…… 请问下 DATAGUARD 上如何做 ASH AWR 报表呢?
我好像做了,感觉都是主库上的信息 楼主辛苦了！
页:
[1]