szkking

8 积分	0 好友	7 主题

发消息

[RAC Clusterware] RAC+DATAGUARD，凌晨1点左右，执行计划改变，查找不出来原因

1^#

发表于 2015-1-22 11:10:00 | 查看: 4931| 回复: 7

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.png (11.88 KB, 下载次数: 472)

ash中显示的改变的SQL

ashrpt_1_0122_0155.html

37.54 KB, 下载次数: 802

变慢的SQL

分享0

收藏0 回复只看该作者道具举报

Liu Maclean(刘相兵

2^#

发表于 2015-1-22 11:15:26

给出针对该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;