swgsw

70 积分	0 好友	22 主题

发消息

[SQL调优] 10.2.0.4 AWR快照信息问题

1^#

发表于 2013-8-29 11:02:51 | 查看: 3161| 回复: 4

数据库版本： oracle 10.2.0.4
主机版本： linux 5.2

问题：选择3天就1条快照信息！

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name    Inst Num Instance
----------- ------------ -------- ------------
1198386908 ENCHRAC          1 enchrac1

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id    Inst Num DB Name    Instance    Host
------------ -------- ------------ ------------ ------------
  1198386908       1 ENCHRAC    enchrac2    enchrac02
  1198386908       3 ENCHRAC    enchrac3    enchrac03
  1198386908       2 ENCHRAC    enchrac2    enchrac02
* 1198386908       1 ENCHRAC    enchrac1    enchrac01

Using 1198386908 for database Id
Using       1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 3

Listing the last 3 days of Completed Snapshots

                                                      Snap
Instance    DB Name       Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
enchrac1    ENCHRAC       40455 29 Aug 2013 08:50    1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Begin Snapshot Id specified:

信息收集：

SQL> select
  2    a.job_name,
  3    a.enabled
  4 from
  5    dba_scheduler_jobs a,
  6    dba_scheduler_wingroup_members b,
  7    dba_scheduler_windows c
  8 where
  9    job_name='GATHER_STATS_JOB'
10  and
11    a.schedule_name=b.window_group_name
12 and
13    b.window_name=c.window_name;

JOB_NAME                      ENABL
------------------------------ -----
GATHER_STATS_JOB             TRUE
GATHER_STATS_JOB             TRUE

SQL> SQL> SELECT snap_id,
  2 to_char(begin_interval_time,'dd/MON/yy hh24:mi') Begin_Interval,       to_char(end_interval_time,'dd/MON/yy hh24:mi') End_Interval
  3  FROM dba_hist_snapshot
  4  ORDER BY 1;

  Snap Id BEGIN_INTERVAL    END_INTERVAL
--------- ------------------ ------------------
40286 22/AUG/13 07:00 22/AUG/13 08:00
40286 22/AUG/13 07:00 22/AUG/13 08:00
40287 22/AUG/13 08:00 22/AUG/13 09:00
40287 22/AUG/13 08:00 22/AUG/13 09:00
40288 22/AUG/13 09:00 22/AUG/13 10:00
40288 22/AUG/13 09:00 22/AUG/13 10:00
40289 22/AUG/13 10:00 22/AUG/13 11:00
.......
40448 29/AUG/13 01:00 29/AUG/13 02:00
40449 29/AUG/13 02:00 29/AUG/13 03:00
40449 29/AUG/13 02:00 29/AUG/13 03:00
40450 29/AUG/13 03:00 29/AUG/13 04:00
40450 29/AUG/13 03:00 29/AUG/13 04:00
40451 29/AUG/13 04:00 29/AUG/13 05:00
40451 29/AUG/13 04:00 29/AUG/13 05:00
40452 29/AUG/13 05:00 29/AUG/13 06:00
40452 29/AUG/13 05:00 29/AUG/13 06:00
40453 29/AUG/13 06:00 29/AUG/13 07:00
40453 29/AUG/13 06:00 29/AUG/13 07:00
40454 29/AUG/13 07:00 29/AUG/13 08:00
40454 29/AUG/13 07:00 29/AUG/13 08:00
40455 29/AUG/13 08:00 29/AUG/13 08:50
40455 16/AUG/13 10:16 29/AUG/13 08:50
40455 29/AUG/13 08:00 29/AUG/13 08:50
40456 29/AUG/13 08:50 29/AUG/13 10:00

  Snap Id BEGIN_INTERVAL    END_INTERVAL
--------- ------------------ ------------------
40456 29/AUG/13 08:50 29/AUG/13 10:00

分享0

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

Maclean Liu(刘相兵

2^#

发表于 2013-8-29 11:21:38

先上传alert.log

回复只看该作者道具举报

swgsw

3^#

发表于 2013-8-29 11:55:46

alter.log文件

alert_enchrac1.rar

288.68 KB, 下载次数: 1310

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2013-8-29 13:33:08

DB Id    Inst Num DB Name    Instance    Host
------------ -------- ------------ ------------ ------------
  1198386908       1 ENCHRAC    enchrac2    enchrac02
  1198386908       3 ENCHRAC    enchrac3    enchrac03
  1198386908       2 ENCHRAC    enchrac2    enchrac02
* 1198386908       1 ENCHRAC    enchrac1    enchrac01

貌似instance 信息有点乱吧？

执行下述命令，并指定其他实例呢

@?/rdbms/admin/awrrpti

回复只看该作者道具举报

swgsw

5^#

发表于 2013-8-29 15:34:01

节点2和节点3是没问题的。

节点信息乱了如何排查？

SQL> @?/rdbms/admin/awrrpti

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id    Inst Num DB Name    Instance    Host
------------ -------- ------------ ------------ ------------
  1198386908       1 ENCHRAC    enchrac2    enchrac02
  1198386908       3 ENCHRAC    enchrac3    enchrac03
  1198386908       2 ENCHRAC    enchrac2    enchrac02
* 1198386908       1 ENCHRAC    enchrac1    enchrac01

Enter value for dbid: 1198386908
Using 1198386908 for database Id
Enter value for inst_num: 3
Using 3 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

                                                      Snap
Instance    DB Name       Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
enchrac3 ENCHRAC       40422 28 Aug 2013 00:00    1
                           40423 28 Aug 2013 01:00    1
                           40424 28 Aug 2013 02:00    1
                           40425 28 Aug 2013 03:00    1
                           40426 28 Aug 2013 04:00    1
                           40427 28 Aug 2013 05:00    1
                           40428 28 Aug 2013 06:00    1
                           40429 28 Aug 2013 07:00    1
                           40430 28 Aug 2013 08:00    1
                           40431 28 Aug 2013 09:00    1
....
                           40452 29 Aug 2013 06:00    1
                           40453 29 Aug 2013 07:00    1
                           40454 29 Aug 2013 08:00    1
                           40455 29 Aug 2013 08:50    1
                           40456 29 Aug 2013 10:00    1
                           40457 29 Aug 2013 11:00    1
                           40458 29 Aug 2013 12:00    1
                           40459 29 Aug 2013 13:00    1
                           40460 29 Aug 2013 14:00    1
                           40461 29 Aug 2013 15:00    1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

SQL> @?/rdbms/admin/awrrpti

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id    Inst Num DB Name    Instance    Host
------------ -------- ------------ ------------ ------------
  1198386908       1 ENCHRAC    enchrac2    enchrac02
  1198386908       3 ENCHRAC    enchrac3    enchrac03
  1198386908       2 ENCHRAC    enchrac2    enchrac02
* 1198386908       1 ENCHRAC    enchrac1    enchrac01

Enter value for dbid: 1198386908
Using 1198386908 for database Id
Enter value for inst_num: 2
Using 2 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

                                                      Snap
Instance    DB Name       Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
enchrac2 ENCHRAC       40422 28 Aug 2013 00:00    1
                           40423 28 Aug 2013 01:00    1
                           40424 28 Aug 2013 02:00    1
                           40425 28 Aug 2013 03:00    1
                           40426 28 Aug 2013 04:00    1
                           40427 28 Aug 2013 05:00    1
                           40428 28 Aug 2013 06:00    1
                           40429 28 Aug 2013 07:00    1
                           40430 28 Aug 2013 08:00    1
                           40431 28 Aug 2013 09:00    1
                           40432 28 Aug 2013 10:00    1
....
                           40454 29 Aug 2013 08:00    1
                           40455 29 Aug 2013 08:50    1
                           40456 29 Aug 2013 10:00    1
                           40457 29 Aug 2013 11:00    1
                           40458 29 Aug 2013 12:00    1
                           40459 29 Aug 2013 13:00    1
                           40460 29 Aug 2013 14:00    1
                           40461 29 Aug 2013 15:00    1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册