Oracle的AWR生产实践一
一.AWR常用功能
1.Snapshot的管理
---------------------------------查询具体快照信息:
SQL> select * from DBA_HIST_SNAPSHOT; SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL ERROR_COUNT ---------- ---------- --------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------- ---------- ----------- 1460 1435972043 1 23-12月-18 09.27.16.000 下午 23-12月-18 09.38.32.168 下午 23-12月-18 11.00.46.677 下午 +00000 00:00:01.3 1 0 1461 1435972043 1 02-1月 -19 08.56.09.000 下午 02-1月 -19 08.56.09.000 下午 02-1月 -19 09.04.22.484 下午 +00000 00:00:05.5 1 0 1459 1435972043 1 23-12月-18 09.27.16.000 下午 23-12月-18 09.27.16.000 下午 23-12月-18 09.38.32.168 下午 +00000 00:00:18.8 1 0
----------------------------------创建快照:
SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed
2.基线的设置
----------------------------------设置快照为1459~1461的基线，基线名为:test_baseline
SQL> exec dbms_workload_repository.create_baseline(start_snap_id=>1459,end_snap_id=>1461,baseline_name=>'test_baseline'); PL/SQL procedure successfully completed
3.所需空间开销和设置
----------------------------------查看AWR配置情况:
SQL> col snap_interval for a20; SQL> col retention format a20; SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- -------------------- -------------------- ---------- 1435972043 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
SNAP_INTERVAL=+00000 01:00:00.0 表示采样间隔是1小时。
RETENTION=+00007 00:00:00.0 表示采样数据保留期限是7天。
----------------------------------如果修改为30分钟采样一次，数据保留31天，可以如下设置:
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>31*24*60); PL/SQL procedure successfully completed SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- -------------------- -------------------- ---------- 1435972043 +00000 00:30:00.0 +00031 00:00:00.0 DEFAULT
4.AWR数据的迁移
(1)AW数据的导出，可以使用awrextr.sql这个自带的脚本来导出
SQL> @?/RDBMS/ADMIN/awrextr.sql ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Disclaimer: This SQL/Plus script should only be called under the guidance of Oracle Support. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~ AWR EXTRACT ~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ This script will extract the AWR data for a range of snapshots ~ ~ into a dump file. The script will prompt users for the ~ ~ following information: ~ ~ (1) database id ~ ~ (2) snapshot range to extract ~ ~ (3) name of directory object ~ ~ (4) name of dump file ~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Databases in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id DB Name Host ------------ ------------ ------------ * 1435972043 ORCL G505-PC * 1435972043 ORCL LLL-PC The default database id is the local one: '1435972043'. To use this database id, press <return> to continue, otherwise enter an alternative. 输入 dbid 的值: Using 1435972043 for Database ID 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. 输入 num_days 的值: 5 Listing the last 5 days of Completed Snapshots DB Name Snap Id Snap Started ------------ --------- ------------------ ORCL 1461 02 1月 2019 21:04 1462 02 1月 2019 21:43 1463 02 1月 2019 22:00 1464 02 1月 2019 22:30 1465 05 1月 2019 21:56 1466 05 1月 2019 22:30 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 begin_snap 的值: 1459 Begin Snapshot Id specified: 1459 输入 end_snap 的值: 1461 End Snapshot Id specified: 1461 Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~ Directory Name Directory Path ------------------------------ ------------------------------------------------- ADMIN_DIR C:\ADE\aime_10.2_nt_push\oracle/md/admin DATA_FILE_DIR F:\oracle\product\10.2.0\db_1\demo\schema\sales_history\ DATA_PUMP_DIR F:\oracle\product\10.2.0\db_1\admin\orcl\dpdump\ LOG_FILE_DIR F:\oracle\product\10.2.0\db_1\demo\schema\log\ MEDIA_DIR F:\oracle\product\10.2.0\db_1\demo\schema\product _media\ SUBDIR F:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Sep Directory Name Directory Path ------------------------------ ------------------------------------------------- WORK_DIR C:\ADE\aime_10.2_nt_push\oracle/work XMLDIR F:\oracle\product\10.2.0\db_1\demo\schema\order_entry\ Choose a Directory Name from the above list (case-sensitive). 输入 directory_name 的值: LOG_FILE_DIR Using the dump directory: LOG_FILE_DIR Specify the Name of the Extract Dump File ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The prefix for the default dump file name is awrdat_1459_1461. To use this name, press <return> to continue, otherwise enter an alternative. 输入 file_name 的值: Using the dump file prefix: awrdat_1459_1461 | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | The AWR extract dump file will be located | in the following directory/file: | F:\oracle\product\10.2.0\db_1\demo\schema\log\ | awrdat_1459_1461.dmp | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | | *** AWR Extract Started ... | | This operation will take a few moments. The | progress of the AWR extract operation can be | monitored in the following directory/file: | F:\oracle\product\10.2.0\db_1\demo\schema\log\ | awrdat_1459_1461.log | 启动 "SYS"."SYS_EXPORT_TABLE_01": 正在使用 BLOCKS 方法进行估计... 处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA 使用 BLOCKS 方法的总估计: 15.93 MB 处理对象类型 TABLE_EXPORT/TABLE/TABLE 处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX 处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT . . 导出了 "SYS"."WRH$_SQL_PLAN" 1.713 MB 4565 行 . . 导出了 "SYS"."WRH$_SYSMETRIC_SUMMARY" 37.20 KB 402 行 . . 导出了 "SYS"."WRH$_SQLTEXT" 468.4 KB 485 行 . . 导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_1435972043_1440" 9.476 KB 0 行 . . 导出了 "SYS"."WRH$_ENQUEUE_STAT" 15.92 KB 161 行 . . 导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_1435972043_1440" 23.03 KB 0 行 . . 导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAME_1435972043_1440" 6.75 KB 0 行 . . 导出了 "SYS"."WRH$_SYSSTAT":"WRH$_SYSSTA_1435972043_1440" 6.125 KB 0 行 . . 导出了 "SYS"."WRH$_BG_EVENT_SUMMARY" 8.890 KB 60 行 . . 导出了 "SYS"."WRH$_SEG_STAT":"WRH$_SEG_ST_1435972043_1440" 16.61 KB 0 行 . . 导出了 "SYS"."WRH$_SQL_BIND_METADATA" 78.42 KB 1249 行 . . 导出了 "SYS"."WRH$_EVENT_NAME" 67.81 KB 872 行 . . 导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_1435972043_1464" 9.476 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_BL" 66.27 KB 1146 行 . . 导出了 "SYS"."WRH$_LIBRARYCACHE" 10.96 KB 33 行 . . 导出了 "SYS"."WRH$_PARAMETER_NAME" 60.02 KB 1381 行 . . 导出了 "SYS"."WRH$_PGASTAT" 7.718 KB 36 行 . . 导出了 "SYS"."WRH$_PGA_TARGET_ADVICE" 9.539 KB 42 行 . . 导出了 "SYS"."WRH$_RESOURCE_LIMIT" 7.851 KB 13 行 . . 导出了 "SYS"."WRH$_ROWCACHE_SUMMARY":"WRH$_ROWCAC_1435972043_1440" 9.507 KB 0 行 . . 导出了 "SYS"."WRH$_SEG_STAT_OBJ" 36.70 KB 303 行 . . 导出了 "SYS"."WRH$_SERVICE_STAT":"WRH$_SERVIC_1435972043_1440" 6.437 KB 0 行 . . 导出了 "SYS"."WRH$_SHARED_POOL_ADVICE" 9.937 KB 31 行 . . 导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_1435972043_1464" 23.03 KB 0 行 . . 导出了 "SYS"."WRH$_SQLSTAT_BL" 64.11 KB 199 行 . . 导出了 "SYS"."WRH$_SYSTEM_EVENT":"WRH$_SYSTEM_1435972043_1440" 6.757 KB 0 行 . . 导出了 "SYS"."WRH$_TABLESPACE_SPACE_USAGE" 8 KB 24 行 . . 导出了 "SYS"."WRH$_UNDOSTAT" 13.37 KB 9 行 . . 导出了 "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_1435972043_1440" 15 .10 KB 0 行 . . 导出了 "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_1435972043_1464" 15 .10 KB 0 行 . . 导出了 "SYS"."WRH$_ACTIVE_SESSION_HISTORY_BL" 18.76 KB 26 行 . . 导出了 "SYS"."WRH$_BUFFER_POOL_STATISTICS" 12 KB 3 行 . . 导出了 "SYS"."WRH$_DATAFILE" 7.656 KB 7 行 . . 导出了 "SYS"."WRH$_DB_CACHE_ADVICE":"WRH$_DB_CAC_1435972043_1440" 8.625 KB 0 行 . . 导出了 "SYS"."WRH$_DB_CACHE_ADVICE":"WRH$_DB_CAC_1435972043_1464" 8.625 KB 0 行 . . 导出了 "SYS"."WRH$_DB_CACHE_ADVICE_BL" 12.32 KB 62 行 . . 导出了 "SYS"."WRH$_FILESTATXS":"WRH$_FILEST_1435972043_1440" 9.187 KB 0 行 . . 导出了 "SYS"."WRH$_FILESTATXS":"WRH$_FILEST_1435972043_1464" 9.187 KB 0 行 . . 导出了 "SYS"."WRH$_FILESTATXS_BL" 10.33 KB 21 行 . . 导出了 "SYS"."WRH$_INSTANCE_RECOVERY" 11.42 KB 3 行 . . 导出了 "SYS"."WRH$_JAVA_POOL_ADVICE" 8.617 KB 6 行 . . 导出了 "SYS"."WRH$_LATCH_MISSES_SUMMARY":"WRH$_LATCH__1435972043_1440" 7.07 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_MISSES_SUMMARY":"WRH$_LATCH__1435972043_1464" 7.07 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_MISSES_SUMMARY_BL" 7.890 KB 14 行 . . 导出了 "SYS"."WRH$_LATCH_NAME" 22.25 KB 382 行 . . 导出了 "SYS"."WRH$_LOG" 8.789 KB 9 行 . . 导出了 "SYS"."WRH$_METRIC_NAME" 24.78 KB 211 行 . . 导出了 "SYS"."WRH$_OPTIMIZER_ENV" 7.046 KB 8 行 . . 导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAME_1435972043_1464" 6.75 KB 0 行 . . 导出了 "SYS"."WRH$_PARAMETER_BL" 39.20 KB 789 行 . . 导出了 "SYS"."WRH$_PROCESS_MEMORY_SUMMARY" 8.640 KB 9 行 . . 导出了 "SYS"."WRH$_ROWCACHE_SUMMARY":"WRH$_ROWCAC_1435972043_1464" 9.507 KB 0 行 . . 导出了 "SYS"."WRH$_ROWCACHE_SUMMARY_BL" 16.39 KB 114 行 . . 导出了 "SYS"."WRH$_SEG_STAT":"WRH$_SEG_ST_1435972043_1464" 16.61 KB 0 行 . . 导出了 "SYS"."WRH$_SEG_STAT_BL" 30.75 KB 142 行 . . 导出了 "SYS"."WRH$_SERVICE_NAME" 5.960 KB 4 行 . . 导出了 "SYS"."WRH$_SERVICE_STAT":"WRH$_SERVIC_1435972043_1464" 6.437 KB 0 行 . . 导出了 "SYS"."WRH$_SERVICE_STAT_BL" 17.96 KB 336 行 . . 导出了 "SYS"."WRH$_SERVICE_WAIT_CLASS":"WRH$_SERVIC_1435972043_1440" 7.070 KB 0 行 . . 导出了 "SYS"."WRH$_SERVICE_WAIT_CLASS":"WRH$_SERVIC_1435972043_1464" 7.070 KB 0 行 . . 导出了 "SYS"."WRH$_SERVICE_WAIT_CLASS_BL" 9.773 KB 58 行 . . 导出了 "SYS"."WRH$_SGA" 6.562 KB 12 行 . . 导出了 "SYS"."WRH$_SGASTAT":"WRH$_SGASTA_1435972043_1440" 6.421 KB 0 行 . . 导出了 "SYS"."WRH$_SGASTAT":"WRH$_SGASTA_1435972043_1464" 6.421 KB 0 行 . . 导出了 "SYS"."WRH$_SGASTAT_BL" 10.39 KB 83 行 . . 导出了 "SYS"."WRH$_SGA_TARGET_ADVICE" 7.453 KB 21 行 . . 导出了 "SYS"."WRH$_SQL_SUMMARY" 6.867 KB 3 行 . . 导出了 "SYS"."WRH$_SQL_WORKAREA_HISTOGRAM" 8.054 KB 17 行 . . 导出了 "SYS"."WRH$_STAT_NAME" 21.52 KB 364 行 . . 导出了 "SYS"."WRH$_SYSSTAT":"WRH$_SYSSTA_1435972043_1464" 6.125 KB 0 行 . . 导出了 "SYS"."WRH$_SYSSTAT_BL" 34.39 KB 1041 行 . . 导出了 "SYS"."WRH$_SYSTEM_EVENT":"WRH$_SYSTEM_1435972043_1464" 6.757 KB 0 行 . . 导出了 "SYS"."WRH$_SYSTEM_EVENT_BL" 12.03 KB 151 行 . . 导出了 "SYS"."WRH$_SYS_TIME_MODEL":"WRH$_SYS_TI_1435972043_1440" 6.132 KB 0 行 . . 导出了 "SYS"."WRH$_SYS_TIME_MODEL":"WRH$_SYS_TI_1435972043_1464" 6.132 KB 0 行 . . 导出了 "SYS"."WRH$_SYS_TIME_MODEL_BL" 7.796 KB 57 行 . . 导出了 "SYS"."WRH$_TABLESPACE_STAT":"WRH$_TABLES_1435972043_1440" 7.679 KB 0 行 . . 导出了 "SYS"."WRH$_TABLESPACE_STAT":"WRH$_TABLES_1435972043_1464" 7.679 KB 0 行 . . 导出了 "SYS"."WRH$_TABLESPACE_STAT_BL" 8.976 KB 21 行 . . 导出了 "SYS"."WRH$_TEMPFILE" 7.125 KB 1 行 . . 导出了 "SYS"."WRH$_TEMPSTATXS" 9.343 KB 3 行 . . 导出了 "SYS"."WRH$_THREAD" 7.492 KB 3 行 . . 导出了 "SYS"."WRH$_WAITSTAT":"WRH$_WAITST_1435972043_1440" 6.429 KB 0 行 . . 导出了 "SYS"."WRH$_WAITSTAT":"WRH$_WAITST_1435972043_1464" 6.429 KB 0 行 . . 导出了 "SYS"."WRH$_WAITSTAT_BL" 8.343 KB 54 行 . . 导出了 "SYS"."WRM$_DATABASE_INSTANCE" 7.507 KB 2 行 . . 导出了 "SYS"."WRM$_SNAPSHOT" 8.601 KB 3 行 . . 导出了 "SYS"."WRM$_WR_CONTROL" 10.14 KB 1 行 . . 导出了 "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_SES_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_BUFFERED_QUEUES" 0 KB 0 行 . . 导出了 "SYS"."WRH$_BUFFERED_SUBSCRIBERS" 0 KB 0 行 . . 导出了 "SYS"."WRH$_COMP_IOSTAT" 0 KB 0 行 . . 导出了 "SYS"."WRH$_CR_BLOCK_SERVER" 0 KB 0 行 . . 导出了 "SYS"."WRH$_CURRENT_BLOCK_SERVER" 0 KB 0 行 . . 导出了 "SYS"."WRH$_DB_CACHE_ADVICE":"WRH$_DB_CACHE_AD_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_DLM_MISC":"WRH$_DLM_MISC_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_DLM_MISC":"WRH$_DLM_MI_1435972043_0" 0 KB 0 行 . . 导出了 "SYS"."WRH$_DLM_MISC_BL" 0 KB 0 行 . . 导出了 "SYS"."WRH$_FILEMETRIC_HISTORY" 0 KB 0 行 . . 导出了 "SYS"."WRH$_FILESTATXS":"WRH$_FILESTATXS_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_INST_CACHE_TRANSFER":"WRH$_INST_CACHE_MXDB_MXSN" 0 K B 0 行 . . 导出了 "SYS"."WRH$_INST_CACHE_TRANSFER":"WRH$_INST_C_1435972043_0" 0 KB 0 行 . . 导出了 "SYS"."WRH$_INST_CACHE_TRANSFER_BL" 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_CHILDREN":"WRH$_LATCH_CHILD_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_CHILDREN":"WRH$_LATCH__1435972043_0" 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_CHILDREN_BL" 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_MISSES_SUMMARY":"WRH$_LATCH_MISSE_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_PARENT":"WRH$_LATCH_PAREN_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_PARENT":"WRH$_LATCH__1435972043_0" 0 KB 0 行 . . 导出了 "SYS"."WRH$_LATCH_PARENT_BL" 0 KB 0 行 . . 导出了 "SYS"."WRH$_MTTR_TARGET_ADVICE" 0 KB 0 行 . . 导出了 "SYS"."WRH$_OSSTAT":"WRH$_OSSTAT_1435972043_1440" 0 KB 0 行 . . 导出了 "SYS"."WRH$_OSSTAT":"WRH$_OSSTAT_1435972043_1464" 0 KB 0 行 . . 导出了 "SYS"."WRH$_OSSTAT":"WRH$_OSSTAT_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_OSSTAT_BL" 0 KB 0 行 . . 导出了 "SYS"."WRH$_OSSTAT_NAME" 0 KB 0 行 . . 导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAMETER_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_ROWCACHE_SUMMARY":"WRH$_ROWCACHE_SU_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_RULE_SET" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SEG_STAT":"WRH$_SEG_STAT_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SERVICE_STAT":"WRH$_SERVICE_STAT_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SERVICE_WAIT_CLASS":"WRH$_SERVICE_WAIT_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SESSMETRIC_HISTORY" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SESS_TIME_STATS" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SGASTAT":"WRH$_SGASTAT_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTAT_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_STREAMS_APPLY_SUM" 0 KB 0 行 . . 导出了 "SYS"."WRH$_STREAMS_CAPTURE" 0 KB 0 行 . . 导出了 "SYS"."WRH$_STREAMS_POOL_ADVICE" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SYSMETRIC_HISTORY" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SYSSTAT":"WRH$_SYSSTAT_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SYSTEM_EVENT":"WRH$_SYSTEM_EVEN_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_SYS_TIME_MODEL":"WRH$_SYS_TIME_MO_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_TABLESPACE_STAT":"WRH$_TABLESPACE_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRH$_WAITCLASSMETRIC_HISTORY" 0 KB 0 行 . . 导出了 "SYS"."WRH$_WAITSTAT":"WRH$_WAITSTAT_MXDB_MXSN" 0 KB 0 行 . . 导出了 "SYS"."WRM$_SNAP_ERROR" 0 KB 0 行 已成功加载/卸载了主表 "SYS"."SYS_EXPORT_TABLE_01" ****************************************************************************** SYS.SYS_EXPORT_TABLE_01 的转储文件集为: F:\ORACLE\PRODUCT\10.2.0\DB_1\DEMO\SCHEMA\LOG\AWRDAT_1459_1461.DMP 作业 "SYS"."SYS_EXPORT_TABLE_01" 已于 22:56:08 成功完成
（2)数据加载,可以通过awrload.sql来完成,也是自带的脚本
SQL> @?/RDBMS/ADMIN/awrload.sql ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Disclaimer: This SQL/Plus script should only be called under the guidance of Oracle Support. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ AWR LOAD ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ This script will load the AWR data from a dump file. The ~ ~ script will prompt users for the following information: ~ ~ (1) name of directory object ~ ~ (2) name of dump file ~ ~ (3) staging schema name to load AWR data into ~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~ Directory Name Directory Path ------------------------------ ------------------------------------------------- ADMIN_DIR C:\ADE\aime_10.2_nt_push\oracle/md/admin DATA_FILE_DIR F:\oracle\product\10.2.0\db_1\demo\schema\sales_h istory\ DATA_PUMP_DIR F:\oracle\product\10.2.0\db_1\admin\orcl\dpdump\ LOG_FILE_DIR F:\oracle\product\10.2.0\db_1\demo\schema\log\ MEDIA_DIR F:\oracle\product\10.2.0\db_1\demo\schema\product _media\ SUBDIR F:\oracle\product\10.2.0\db_1\demo\schema\order_e ntry\/2002/Sep Directory Name Directory Path ------------------------------ ------------------------------------------------- WORK_DIR C:\ADE\aime_10.2_nt_push\oracle/work XMLDIR F:\oracle\product\10.2.0\db_1\demo\schema\order_e ntry\ Choose a Directory Name from the list above (case-sensitive). 输入 directory_name 的值: LOG_FILE_DIR Using the dump directory: LOG_FILE_DIR Specify the Name of the Dump File to Load ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Please specify the prefix of the dump file (.dmp) to load: 输入 file_name 的值: awrextr_TEST01_RECENT_5DAYS Loading from the file name: awrextr_TEST01_RECENT_5DAYS.dmp Staging Schema to Load AWR Snapshot Data ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The next step is to create the staging schema where the AWR snapshot data will be loaded. After loading the data into the staging schema, the data will be transferred into the AWR tables in the SYS schema. The default staging schema name is AWR_STAGE. To use this name, press <return> to continue, otherwise enter an alternative. 输入 schema_name 的值: Using the staging schema name: AWR_STAGE Choose the Default tablespace for the AWR_STAGE user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Choose the AWR_STAGE users's default tablespace. This is the tablespace in which the AWR data will be staged. TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE ------------------------------ --------- ------------------ EXAMPLE PERMANENT GX0315 PERMANENT ORACLELEARNING PERMANENT SYSAUX PERMANENT * USERS PERMANENT Pressing <return> will result in the recommended default tablespace (identified by *) being used. 输入 default_tablespace 的值: Using tablespace SYSAUX as the default tablespace for the AWR_STAGE Choose the Temporary tablespace for the AWR_STAGE user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Choose the AWR_STAGE user's temporary tablespace. TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE ------------------------------ --------- ----------------------- TEMP TEMPORARY * Pressing <return> will result in the database's default temporary tablespace (identified by *) being used. 输入 temporary_tablespace 的值: Using tablespace TEMP as the temporary tablespace for AWR_STAGE ... Creating AWR_STAGE user | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Loading the AWR data from the following | directory/file: | F:\oracle\product\10.2.0\db_1\demo\schema\log\ | .dmp | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | | *** AWR Load Started ... | | This operation will take a few moments. The | progress of the AWR load operation can be | monitored in the following directory/file: | F:\oracle\product\10.2.0\db_1\demo\schema\log\ | .log |
5.生成AWR相关的报告
@?/rdbms/admin/awrrpt.sql ----最常见的生成awr报告的方式
@?/rdbms/admin/awrsqrpt.sql ----生成awr报告中指定SQL_ID的执行计划
@?/rdbms/admin/awrddrpt.sql ---比较两个awr报告
@?/rdbms/admin/awrrpti.sql ----适用于rac环境
6.awr相关的视图和基表
dba_hist_snapshot
dba_hist_sql_plan
dba_hist_wr_control
总结:
1>分析AWR报告对于DBA而言是工作中的重要内容，就和医院看病的化验单一样,用浏览器打开。
2>数据库检查优化步骤:
A. 抓取AWR或者ASH报告分析慢语句；
B.跟踪慢的业务对应的存储以及存储里面的表，分析相关表以及Alter或者CREATE索引。
思路: 存储过程----表-----索引
分析表:
begin
dbms_stats.gather_table_stats(user,'IM_EXT_JYMX');
end;
/
重建索引：
select 'alter index '||index_name||' rebuild online;' from user_indexes where table_name in ('CW_PZ_FL','CW_PZ_ML');
创建索引:
DROP INDEX CW_PZ_ML_JGBM ;
DROP INDEX CW_PZ_ML_ND ;
DROP INDEX CW_PZ_ML_PZXH ;
DROP INDEX CW_PZ_ML_YD ;
DROP INDEX IDX_CW_PZ_ML_PZRQ;
DROP INDEX CW_PZ_FL_KMBH ;
DROP INDEX CW_PZ_FL_KMID ;
DROP INDEX CW_PZ_FL_PZID ;
DROP INDEX CW_PZ_FL_YHZHHM ;
DROP INDEX IDX_CW_PZ_FL_PZRQ ;
CREATE INDEX CW_PZ_ML_JGBM ON CW_PZ_ML (JGBM) ;
CREATE INDEX CW_PZ_ML_ND ON CW_PZ_ML (ND) ;
CREATE INDEX CW_PZ_ML_PZXH ON CW_PZ_ML (PZXH) ;
CREATE INDEX CW_PZ_ML_YD ON CW_PZ_ML (YD) ;
CREATE INDEX IDX_CW_PZ_ML_PZRQ ON CW_PZ_ML (JZRQ) ;
CREATE INDEX CW_PZ_FL_KMBH ON CW_PZ_FL (KMBH) ;
CREATE INDEX CW_PZ_FL_KMID ON CW_PZ_FL (KMID) ;
CREATE INDEX CW_PZ_FL_PZID ON CW_PZ_FL (PZID) ;
CREATE INDEX CW_PZ_FL_YHZHHM ON CW_PZ_FL (YHZHHM) ;
CREATE INDEX IDX_CW_PZ_FL_PZRQ ON CW_PZ_FL (JZRQ) ;
