[SQL调优] 数据库大量log file switch completion等待

2^#

发表于 2013-9-5 18:10:27

给出下面的脚本输出：

select * from v$log;

  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 = '9mq90p42frzgz'
      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 = '9mq90p42frzgz'
      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 = '9mq90p42frzgz'
      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 ('9mq90p42frzgz'))
ORDER BY source, begin_time DESC;

alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."

WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
      sn.end_interval_time end_interval_time,
      ss.stat_name stat_name,
      ss.value e_value,
      lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) > sysdate-10
   and ss.snap_id = sn.snap_id
   and ss.dbid = sn.dbid
   and ss.instance_number = sn.instance_number
   and ss.dbid = (select dbid from v$database)
   and ss.instance_number = 1
   and ss.stat_name = 'redo size')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/

结果保存成文本上传

Mille

3^#

发表于 2013-9-6 09:59:31

ash呢?
关注buffer busy waits，

4^#

发表于 2013-9-6 10:10:24

执行结果见附件

sqlhc.txt

39.76 KB, 下载次数: 1110

5^#

发表于 2013-9-6 11:34:26

log file size 是2g

你可以看下9mq90p42frzgz 这个SQL, 这个SQL 仅在特定时间运行，且redo 的产生只要有这个SQL就会很多，

9月4日运行了 129363 次，此外9月2日 8月30日才有执行，说明不是业务常用SQL

SQL> @C:\Users\Administrator\Desktop\sqlhc.txt
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 143224 2146435072 2 NO CURRENT 1.2976E+13 06-9月 -13
2 1 143223 2146435072 2 YES ACTIVE 1.2976E+13 06-9月 -13
3 1 143220 2146435072 2 YES INACTIVE 1.2976E+13 06-9月 -13
4 1 143221 2146435072 2 YES INACTIVE 1.2976E+13 06-9月 -13
5 2 115471 2146435072 2 NO CURRENT 1.2976E+13 06-9月 -13
6 2 115467 2146435072 2 YES INACTIVE 1.2976E+13 06-9月 -13
7 2 115468 2146435072 2 YES INACTIVE 1.2976E+13 06-9月 -13
8 2 115469 2146435072 2 YES INACTIVE 1.2976E+13 06-9月 -13
9 1 143222 2146435072 2 YES INACTIVE 1.2976E+13 06-9月 -13
10 2 115470 2146435072 2 YES ACTIVE 1.2976E+13 06-9月 -13
SOURCE SQL_ID PLAN_HASH_VALUE BEGIN_TIME END_TIME
----------------------------------------------------------------- ------------- --------------- -------------------------------------- -------------------
No. of exec LIO/exec CPUTIM/exec ETIME/exec PIO/exec ROWs/exec
----------- ---------- ----------- ---------- ---------- ----------
1.v$sql实例号:1 9mq90p42frzgz 0 2013-01-02/10:13:48 在cursor cache中
3697889 12.3329656 .00065189 .082306822 .09084264 .99985343
1.v$sql实例号:1 9mq90p42frzgz 0 2013-01-02/10:13:48 在cursor cache中
56927 12.4600102 .000624653 .090532119 .08563599 .999929735
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-04 18:59:51 2013-09-04 20:00:06
172 11.9127907 .00097236 .290619203 .075581395 1
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-04 15:00:12 2013-09-04 15:59:52
22763 14.5034046 .002110365 .492444451 .097219171 .999736414
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-04 13:00:06 2013-09-04 14:00:34
17752 12.5549234 .000639635 .815298726 .093285264 .999887337
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-04 12:00:03 2013-09-04 13:00:06
2193 12.5253078 .00066296 .091827473 .09119927 1
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-04 11:00:23 2013-09-04 12:00:03
22327 13.6252967 .000664626 1.48487213 .098042729 .999149012
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-04 10:00:14 2013-09-04 11:00:23
30855 12.4217145 .000575781 .164361398 .086857884 .999675903
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-04 09:00:15 2013-09-04 10:00:14
33301 14.3895679 .000616915 .467403661 .102879793 .999819825
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-02 11:00:16 2013-09-02 12:00:06
29866 13.8948972 .000682883 .514948387 .120404473 .999698654
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-02 10:00:04 2013-09-02 11:00:16
45721 17.1321493 .000699357 3.04031092 .103584786 .999278231
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-09-02 09:00:15 2013-09-02 10:00:04
40984 16.3790016 .000633875 .88578189 .108603357 .999634004
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-08-30 16:59:58 2013-08-30 18:00:01
1597 15.4295554 .000788498 4.1427752 .130870382 1
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-08-30 16:00:06 2013-08-30 16:59:58
11034 12.6242523 .000663768 .02747442 .088725757 .999546855
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-08-30 15:00:10 2013-08-30 16:00:06
16358 12.449138 .000647657 .023908186 .090597873 .999633207
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-08-30 12:59:54 2013-08-30 14:00:09
13185 12.7670838 .000656884 .072110251 .091846796 .999848312
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-08-30 12:00:16 2013-08-30 12:59:54
2138 13.8868101 .000979355 .06803113 .16323667 .999064546
4.dba_hist_sqlstat实例号:1 9mq90p42frzgz 0 2013-08-30 10:00:44 2013-08-30 11:00:33
28259 12.7879967 .000631342 .021663407 .092501504 .999681517
"Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
DATE_TIME STAT_NAME PER_SEC
---------------------------------------- ------------------------- ----------
08/30/13_00_00_01_00 redo size 53309
08/30/13_01_00_02_00 redo size 52425
08/30/13_02_00_03_00 redo size 55712
08/30/13_03_00_04_00 redo size 65288
08/30/13_04_00_05_00 redo size 1559
08/30/13_05_00_05_59 redo size 1261
08/30/13_05_59_07_00 redo size 1098
08/30/13_07_00_07_59 redo size 1664
08/30/13_07_59_09_00 redo size 60339
08/30/13_09_00_10_00 redo size 1332107
08/30/13_10_00_11_00 redo size 2047646
08/30/13_11_00_12_00 redo size 2327533
08/30/13_12_00_12_59 redo size 539371
08/30/13_12_59_14_00 redo size 1189689
08/30/13_14_00_15_00 redo size 2031301
08/30/13_15_00_16_00 redo size 993439
08/30/13_16_00_16_59 redo size 558687
08/30/13_16_59_18_00 redo size 13219333
08/30/13_18_00_19_00 redo size 12027119
08/30/13_19_00_20_00 redo size 2727740
08/30/13_20_00_21_00 redo size 13125
08/30/13_21_00_22_00 redo size 2501853
08/30/13_22_00_23_00 redo size 12190769
08/30/13_23_00_00_00 redo size 5184760
08/31/13_00_00_01_00 redo size 36592
08/31/13_01_00_01_59 redo size 16709
08/31/13_01_59_03_00 redo size 20706
08/31/13_03_00_04_00 redo size 49200
08/31/13_04_00_04_59 redo size 1349
08/31/13_04_59_06_00 redo size 1088
08/31/13_06_00_07_00 redo size 1116
08/31/13_07_00_08_00 redo size 1257
08/31/13_08_00_09_00 redo size 8299
08/31/13_09_00_10_00 redo size 26913
08/31/13_10_00_11_00 redo size 285349
08/31/13_11_00_12_00 redo size 31620
08/31/13_12_00_13_00 redo size 15065
08/31/13_13_00_13_59 redo size 22631
08/31/13_13_59_15_00 redo size 29263
08/31/13_15_00_16_00 redo size 23942
08/31/13_16_00_17_00 redo size 63122
08/31/13_17_00_18_00 redo size 58735
08/31/13_18_00_19_00 redo size 108574
08/31/13_19_00_20_00 redo size 143100
08/31/13_20_00_21_00 redo size 125842
08/31/13_21_00_22_00 redo size 23714
08/31/13_22_00_23_00 redo size 59814
08/31/13_23_00_23_59 redo size 59858
08/31/13_23_59_01_00 redo size 7715
09/01/13_01_00_02_00 redo size 8945
09/01/13_02_00_03_00 redo size 7539
09/01/13_03_00_04_00 redo size 7992
09/01/13_04_00_05_00 redo size 6672
09/01/13_05_00_06_00 redo size 12300
09/01/13_06_00_07_00 redo size 16590
09/01/13_07_00_08_00 redo size 14958
09/01/13_08_00_09_00 redo size 14809
09/01/13_09_00_10_00 redo size 30747
09/01/13_10_00_11_00 redo size 25777
09/01/13_11_00_12_00 redo size 17814
09/01/13_12_00_12_59 redo size 5137
09/01/13_12_59_13_59 redo size 15249
09/01/13_13_59_15_00 redo size 17033
09/01/13_15_00_16_00 redo size 11987
09/01/13_16_00_17_00 redo size 4761
09/01/13_17_00_18_00 redo size 388367
09/01/13_18_00_19_00 redo size 6879102
09/01/13_19_00_20_00 redo size 3739285
09/01/13_20_00_20_59 redo size 5571026
09/01/13_20_59_22_00 redo size 2686647
09/01/13_22_00_23_00 redo size 2509191
09/01/13_23_00_00_00 redo size 2938070
09/02/13_00_00_01_00 redo size 2681479
09/02/13_01_00_02_00 redo size 2353733
09/02/13_02_00_02_59 redo size 335629
09/02/13_02_59_04_00 redo size 233477
09/02/13_04_00_05_00 redo size 259341
09/02/13_05_00_06_00 redo size 6719061
09/02/13_06_00_07_00 redo size 12043443
09/02/13_07_00_08_00 redo size 8957894
09/02/13_08_00_09_00 redo size 497748
09/02/13_09_00_10_00 redo size 8078672
09/02/13_10_00_11_00 redo size 9209795
09/02/13_11_00_12_00 redo size 8313923
09/02/13_12_00_13_00 redo size 482806
09/02/13_13_00_13_59 redo size 4845332
09/02/13_13_59_14_59 redo size 6000157
09/02/13_14_59_16_00 redo size 5866444
09/02/13_16_00_17_00 redo size 2224626
09/02/13_17_00_18_00 redo size 172937
09/02/13_18_00_19_00 redo size 319343
09/02/13_19_00_19_59 redo size 946339
09/02/13_19_59_20_59 redo size 2428651
09/02/13_20_59_22_00 redo size 4452629
09/02/13_22_00_23_00 redo size 3163180
09/02/13_23_00_00_00 redo size 2808460
09/03/13_00_00_01_00 redo size 59722
09/03/13_01_00_02_00 redo size 43366
09/03/13_02_00_03_00 redo size 35588
09/03/13_03_00_03_59 redo size 35089
09/03/13_03_59_05_00 redo size 13603
09/03/13_05_00_06_00 redo size 31494
09/03/13_06_00_07_00 redo size 21950
09/03/13_07_00_08_00 redo size 85624
09/03/13_08_00_09_00 redo size 251277
09/03/13_09_00_10_00 redo size 4598343
09/03/13_10_00_11_00 redo size 6638795
09/03/13_11_00_11_59 redo size 4166179
09/03/13_11_59_13_00 redo size 257087
09/03/13_13_00_13_59 redo size 2767106
09/03/13_13_59_15_00 redo size 2635135
09/03/13_15_00_16_00 redo size 1990030
09/03/13_16_00_17_00 redo size 1636738
09/03/13_17_00_18_00 redo size 1677014
09/03/13_18_00_19_00 redo size 1646093
09/03/13_19_00_19_59 redo size 567675
09/03/13_19_59_21_00 redo size 417098
09/03/13_21_00_22_00 redo size 2729864
09/03/13_22_00_23_00 redo size 256595
09/03/13_23_00_00_00 redo size 1086586
09/04/13_00_00_01_00 redo size 1699723
09/04/13_01_00_01_59 redo size 3401957
09/04/13_01_59_03_00 redo size 182866
09/04/13_03_00_03_59 redo size 257011
09/04/13_03_59_05_00 redo size 300613
09/04/13_05_00_06_00 redo size 301520
09/04/13_06_00_07_00 redo size 375466
09/04/13_07_00_08_00 redo size 629558
09/04/13_08_00_09_00 redo size 1139714
09/04/13_09_00_10_00 redo size 3430021
09/04/13_10_00_11_00 redo size 3540488
09/04/13_11_00_12_00 redo size 3556993
09/04/13_12_00_13_00 redo size 324496
09/04/13_13_00_14_00 redo size 1757338
09/04/13_14_00_15_00 redo size 2888496
09/04/13_15_00_15_59 redo size 2510646
09/04/13_15_59_17_00 redo size 1447001
09/04/13_17_00_18_00 redo size 1706435
09/04/13_18_00_18_59 redo size 363312
09/04/13_18_59_20_00 redo size 11699
09/04/13_20_00_21_00 redo size 3094
09/04/13_21_00_22_00 redo size 50706
09/04/13_22_00_23_00 redo size 2360
09/04/13_23_00_00_00 redo size 5714842
09/05/13_00_00_01_00 redo size 10094707
09/05/13_01_00_02_00 redo size 6417019
09/05/13_02_00_03_00 redo size 1874
09/05/13_03_00_04_00 redo size 1850
09/05/13_04_00_04_59 redo size 1875
09/05/13_04_59_05_59 redo size 1755
09/05/13_05_59_06_59 redo size 1781
09/05/13_06_59_08_00 redo size 4454
09/05/13_08_00_09_00 redo size 509166
09/05/13_09_00_10_00 redo size 3426988
09/05/13_10_00_11_00 redo size 4064717
09/05/13_11_00_12_00 redo size 2137286
09/05/13_12_00_12_59 redo size 387599
09/05/13_12_59_13_59 redo size 2136895
09/05/13_13_59_15_00 redo size 5922676
09/05/13_15_00_16_00 redo size 2694705
09/05/13_16_00_17_00 redo size 2449636
09/05/13_17_00_18_00 redo size 2676942
09/05/13_18_00_19_00 redo size 1438582
09/05/13_19_00_20_00 redo size 1387372
09/05/13_20_00_21_00 redo size 1563055
09/05/13_21_00_22_00 redo size 1557619
09/05/13_22_00_23_00 redo size 1565670
09/05/13_23_00_00_00 redo size 5218398
09/06/13_00_00_00_59 redo size 5511646
09/06/13_00_59_02_00 redo size 1361917
09/06/13_02_00_03_00 redo size 1462707
09/06/13_03_00_04_00 redo size 1429379
09/06/13_04_00_05_00 redo size 1444921
09/06/13_05_00_06_00 redo size 2007345
09/06/13_06_00_07_00 redo size 1952305
09/06/13_07_00_08_00 redo size 156563
09/06/13_08_00_09_00 redo size 257826
09/06/13_09_00_09_59 redo size 2393990
SQL> spool off

复制代码

6^#

发表于 2013-9-6 11:36:08

给出当时的alert.log

7^#

发表于 2013-9-6 11:36:16

给出当时的alert.log

8^#

发表于 2013-9-6 11:49:23

该时间点前后的alert日志记录
Wed Sep  4 10:34:44 2013
Thread 1 advanced to log sequence 142831 (LGWR switch)
  Current log# 4 seq# 142831 mem# 0: /dev/rcdld_redo04_1
  Current log# 4 seq# 142831 mem# 1: /dev/rcdld_redo04_11
Wed Sep  4 10:42:42 2013
Thread 1 advanced to log sequence 142832 (LGWR switch)
  Current log# 9 seq# 142832 mem# 0: /dev/rcdld_redo05_1
  Current log# 9 seq# 142832 mem# 1: /dev/rcdld_redo05_11
Wed Sep  4 10:47:37 2013
Thread 1 advanced to log sequence 142833 (LGWR switch)
  Current log# 2 seq# 142833 mem# 0: /dev/rcdld_redo02_1
  Current log# 2 seq# 142833 mem# 1: /dev/rcdld_redo02_11
Wed Sep  4 10:56:10 2013
Thread 1 advanced to log sequence 142834 (LGWR switch)
  Current log# 1 seq# 142834 mem# 0: /dev/rcdld_redo01_1
  Current log# 1 seq# 142834 mem# 1: /dev/rcdld_redo01_11
Wed Sep  4 11:02:01 2013
Thread 1 advanced to log sequence 142835 (LGWR switch)
  Current log# 3 seq# 142835 mem# 0: /dev/rcdld_redo03_1
  Current log# 3 seq# 142835 mem# 1: /dev/rcdld_redo03_11
Wed Sep  4 11:07:25 2013
Thread 1 advanced to log sequence 142836 (LGWR switch)
  Current log# 4 seq# 142836 mem# 0: /dev/rcdld_redo04_1
  Current log# 4 seq# 142836 mem# 1: /dev/rcdld_redo04_11
Wed Sep  4 11:07:37 2013
ALTER SYSTEM ARCHIVE LOG
Wed Sep  4 11:07:47 2013
Thread 1 advanced to log sequence 142837 (LGWR switch)
  Current log# 9 seq# 142837 mem# 0: /dev/rcdld_redo05_1
  Current log# 9 seq# 142837 mem# 1: /dev/rcdld_redo05_11
Wed Sep  4 11:11:19 2013
Thread 1 advanced to log sequence 142838 (LGWR switch)
  Current log# 2 seq# 142838 mem# 0: /dev/rcdld_redo02_1
  Current log# 2 seq# 142838 mem# 1: /dev/rcdld_redo02_11
Wed Sep  4 11:15:27 2013
Thread 1 advanced to log sequence 142839 (LGWR switch)
  Current log# 1 seq# 142839 mem# 0: /dev/rcdld_redo01_1
  Current log# 1 seq# 142839 mem# 1: /dev/rcdld_redo01_11
Wed Sep  4 11:20:57 2013
Thread 1 advanced to log sequence 142840 (LGWR switch)
  Current log# 3 seq# 142840 mem# 0: /dev/rcdld_redo03_1
  Current log# 3 seq# 142840 mem# 1: /dev/rcdld_redo03_11
Wed Sep  4 11:29:26 2013
Thread 1 advanced to log sequence 142841 (LGWR switch)
  Current log# 4 seq# 142841 mem# 0: /dev/rcdld_redo04_1
  Current log# 4 seq# 142841 mem# 1: /dev/rcdld_redo04_11
Wed Sep  4 11:39:22 2013
Thread 1 advanced to log sequence 142842 (LGWR switch)
  Current log# 9 seq# 142842 mem# 0: /dev/rcdld_redo05_1
  Current log# 9 seq# 142842 mem# 1: /dev/rcdld_redo05_11
Wed Sep  4 11:47:36 2013
Thread 1 advanced to log sequence 142843 (LGWR switch)
  Current log# 2 seq# 142843 mem# 0: /dev/rcdld_redo02_1
  Current log# 2 seq# 142843 mem# 1: /dev/rcdld_redo02_11
Wed Sep  4 11:57:33 2013
Thread 1 advanced to log sequence 142844 (LGWR switch)
  Current log# 1 seq# 142844 mem# 0: /dev/rcdld_redo01_1
  Current log# 1 seq# 142844 mem# 1: /dev/rcdld_redo01_11
Wed Sep  4 12:07:56 2013
Thread 1 advanced to log sequence 142845 (LGWR switch)
  Current log# 3 seq# 142845 mem# 0: /dev/rcdld_redo03_1
  Current log# 3 seq# 142845 mem# 1: /dev/rcdld_redo03_11

9^#

发表于 2013-9-6 12:02:42

8月30日到今天的alert日志，见附件

alert_xxxxx1.txt

323.57 KB, 下载次数: 1013

10^#

发表于 2013-9-6 12:10:58

这个sql需要关注些什么呢？

11^#

发表于 2013-9-6 15:26:52

log_archive_dest_2 SERVICE=rep LGWR DB_UNIQUE_NAME=rep REOPEN=60 MAX_FAILURE=600

Specifying Synchronous or Asynchronous Network Transmission

The LGWR process synchronously writes to the local online redo log files at the same time it transmits redo data to the remote destination:

The SYNC attribute performs all network I/O synchronously, in conjunction with each write operation to the online redo log file, and waits for the network I/O to complete. Section 5.3.2.2 shows an example of synchronous network transmission in a Data Guard configuration. This is the default network transmission setting.

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
events in waitclass Other 1,167,330 56.69 1,094 1 6.53
LGWR wait on LNS 241,171 0.00 468 2 1.35
LNS wait on SENDREQ 241,152 0.00 431 2 1.35

而且你的log_archive_dest_2 没指定async，默认使用的是LGWR SYNC同步传输

12^#

发表于 2013-9-10 12:34:35

修改log_archive_dest_2参数
alter system set log_archive_dest_2='SERVICE=rep LGWR async DB_UNIQUE_NAME=rep REOPEN=60 MAX_FAILURE=600' scope=both sid='xxxx';

用户反馈暂时未再出现峰值等待。我也巩固了下sync和async的区别。

结贴，谢谢刘大