41.数据库维护管理
1.管理优化程序统计信息
sys@ORCL10G 2023-03-04 23:03:54> select * from tab where tname like 'DBA_SCHE%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DBA_SCHEDULER_PROGRAMS VIEW
DBA_SCHEDULER_JOBS VIEW
DBA_SCHEDULER_JOB_CLASSES VIEW
DBA_SCHEDULER_WINDOWS VIEW
DBA_SCHEDULER_PROGRAM_ARGS VIEW
DBA_SCHEDULER_JOB_ARGS VIEW
DBA_SCHEDULER_JOB_LOG VIEW
DBA_SCHEDULER_JOB_RUN_DETAILS VIEW
DBA_SCHEDULER_WINDOW_LOG VIEW
DBA_SCHEDULER_WINDOW_DETAILS VIEW
DBA_SCHEDULER_WINDOW_GROUPS VIEW
DBA_SCHEDULER_WINGROUP_MEMBERS VIEW
DBA_SCHEDULER_SCHEDULES VIEW
DBA_SCHEDULER_RUNNING_JOBS VIEW
DBA_SCHEDULER_GLOBAL_ATTRIBUTE VIEW
DBA_SCHEDULER_CHAINS VIEW
DBA_SCHEDULER_CHAIN_RULES VIEW
DBA_SCHEDULER_CHAIN_STEPS VIEW
DBA_SCHEDULER_RUNNING_CHAINS VIEW
19 rows selected.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 23:04:11> select job_name from DBA_SCHEDULER_JOBS;
JOB_NAME
------------------------------
AUTO_SPACE_ADVISOR_JOB
GATHER_STATS_JOB
FGR$AUTOPURGE_JOB
PURGE_LOG
MGMT_STATS_CONFIG_JOB
MGMT_CONFIG_JOB
RLM$SCHDNEGACTION
RLM$EVTCLEANUP
8 rows selected.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 23:04:27>
--自动搜集统计信息的作业
GATHER_STATS_JOB登录oem配置自动维护任务
配置自动搜集统计信息
scott@ORCL11G 2023-03-04 23:43:41> select table_name,last_analyzed from user_tables;
TABLE_NAME LAST_ANALYZED
------------------------------ -------------------
TEST
SALGRADE
BONUS
EMP
DEPT
Elapsed: 00:00:00.07
scott@ORCL11G 2023-03-04 23:44:39> exec dbms_stats.gather_table_stats('scott','emp')
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.29
scott@ORCL11G 2023-03-04 23:44:55> select table_name,last_analyzed from user_tables;
TABLE_NAME LAST_ANALYZED
------------------------------ -------------------
TEST
SALGRADE
BONUS
EMP 2023-03-04 23:44:55
DEPT
Elapsed: 00:00:00.04
scott@ORCL11G 2023-03-04 23:44:57> select num_rows,blocks from user_tables where table_name = 'EMP';
NUM_ROWS BLOCKS
---------- ----------
14 5
Elapsed: 00:00:00.012.AWR报表的使用
begin
dbms_workload_repository.modify_snapshot_settings(14400,60)
end;
/
---14400 分钟 保留时间
---60 分钟 每次间隔时间
--awr 在10g 默认保留7天，每个小时搜集awr数据库一次
--awr 在11g 默认保留8天，每个小时搜集awr数据库一次
sys@ORCL11G 2023-03-04 23:56:49> select * from tab where tname like 'DBA_HIST_SNAPSHOT';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DBA_HIST_SNAPSHOT VIEW
Elapsed: 00:00:00.01
sys@ORCL11G 2023-03-04 23:57:05> select * from DBA_HIST_SNAPSHOT;
SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL ERROR_COUNT SNAP_FLAG SNAP_TIMEZONE
---------- ---------- --------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- ----------- ---------- ---------------------------------------------------------------------------
5 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 07.00.15.057 PM 04-MAR-23 08.00.16.557 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
3 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 05.23.13.000 PM 04-MAR-23 06.00.13.538 PM +00000 00:00:04.5 1 0 0 +0 08:00:00
6 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 08.00.16.557 PM 04-MAR-23 09.00.18.039 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
7 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 09.00.18.039 PM 04-MAR-23 10.00.19.532 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
2 1235547844 1 04-MAR-23 05.10.11.000 PM 04-MAR-23 05.10.11.000 PM 04-MAR-23 05.21.10.704 PM +00000 00:00:05.3 1 0 0 +0 08:00:00
8 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 10.00.19.532 PM 04-MAR-23 11.00.21.074 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
4 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 06.00.13.538 PM 04-MAR-23 07.00.15.057 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
7 rows selected.
Elapsed: 00:00:00.00
--创建快照
exec dbms_workload_repository.create_snapshot;
sys@ORCL11G 2023-03-04 23:57:11> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
sys@ORCL11G 2023-03-04 23:59:36> select * from DBA_HIST_SNAPSHOT;
SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL ERROR_COUNT SNAP_FLAG SNAP_TIMEZONE
---------- ---------- --------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- ----------- ---------- ---------------------------------------------------------------------------
5 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 07.00.15.057 PM 04-MAR-23 08.00.16.557 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
3 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 05.23.13.000 PM 04-MAR-23 06.00.13.538 PM +00000 00:00:04.5 1 0 0 +0 08:00:00
6 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 08.00.16.557 PM 04-MAR-23 09.00.18.039 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
7 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 09.00.18.039 PM 04-MAR-23 10.00.19.532 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
2 1235547844 1 04-MAR-23 05.10.11.000 PM 04-MAR-23 05.10.11.000 PM 04-MAR-23 05.21.10.704 PM +00000 00:00:05.3 1 0 0 +0 08:00:00
8 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 10.00.19.532 PM 04-MAR-23 11.00.21.074 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
9 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 11.00.21.074 PM 04-MAR-23 11.59.35.465 PM +00000 00:00:00.5 1 0 1 +0 08:00:00
4 1235547844 1 04-MAR-23 05.23.13.000 PM 04-MAR-23 06.00.13.538 PM 04-MAR-23 07.00.15.057 PM +00000 00:00:00.1 1 0 0 +0 08:00:00
8 rows selected.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-05 00:00:54> start ?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1235547844 ORCL11G 1 orcl11g
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
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
Elapsed: 00:00:00.00
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1235547844 1 ORCL11G orcl11g yuanzj.com
Using 1235547844 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:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl11g ORCL11G 2 04 Mar 2023 17:21 1
3 04 Mar 2023 18:00 1
4 04 Mar 2023 19:00 1
5 04 Mar 2023 20:00 1
6 04 Mar 2023 21:00 1
7 04 Mar 2023 22:00 1
8 04 Mar 2023 23:00 1
9 04 Mar 2023 23:59 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 7
Begin Snapshot Id specified: 7
Enter value for end_snap: 8
End Snapshot Id specified: 8
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_7_8.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_7_8.html3.分析AWR报表
--出addm报表(用法和awr一样)
start ?/rdbms/admin/addmrpt.sql
