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 completedSQL> 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 underthe 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-PCThe default database id is the local one: '1435972043'. To use thisdatabase id, pressto continue, otherwise enter an alternative.输入 dbid 的值:Using 1435972043 for Database IDSpecify 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 withoutspecifying a number lists all completed snapshots.输入 num_days 的值: 5Listing the last 5 days of Completed SnapshotsDB 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:30Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~输入 begin_snap 的值: 1459Begin Snapshot Id specified: 1459输入 end_snap 的值: 1461End Snapshot Id specified: 1461Specify the Directory Name~~~~~~~~~~~~~~~~~~~~~~~~~~Directory Name Directory Path------------------------------ -------------------------------------------------ADMIN_DIR C:\ADE\aime_10.2_nt_push\oracle/md/adminDATA_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/SepDirectory Name Directory Path------------------------------ -------------------------------------------------WORK_DIR C:\ADE\aime_10.2_nt_push\oracle/workXMLDIR 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_DIRUsing the dump directory: LOG_FILE_DIRSpecify the Name of the Extract Dump File~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~The prefix for the default dump file name is awrdat_1459_1461.To use this name, press to continue, otherwise enteran 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 KB0 行. . 导出了 "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.070 KB 0 行. . 导出了 "SYS"."WRH$_LATCH_MISSES_SUMMARY":"WRH$_LATCH__1435972043_1464" 7.070 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 KB0 行. . 导出了 "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.070KB 0 行. . 导出了 "SYS"."WRH$_SERVICE_WAIT_CLASS":"WRH$_SERVIC_1435972043_1464" 7.070KB 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 KB 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 KB0 行. . 导出了 "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" 0KB 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 underthe 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/adminDATA_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/SepDirectory Name Directory Path------------------------------ -------------------------------------------------WORK_DIR C:\ADE\aime_10.2_nt_push\oracle/workXMLDIR 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_DIRUsing the dump directory: LOG_FILE_DIRSpecify the Name of the Dump File to Load~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Please specify the prefix of the dump file (.dmp) to load:输入 file_name 的值: awrextr_TEST01_RECENT_5DAYSLoading from the file name: awrextr_TEST01_RECENT_5DAYS.dmpStaging Schema to Load AWR Snapshot Data~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~The next step is to create the staging schemawhere the AWR snapshot data will be loaded.After loading the data into the staging schema,the data will be transferred into the AWR tablesin the SYS schema.The default staging schema name is AWR_STAGE.To use this name, pressto continue, otherwise enteran alternative.输入 schema_name 的值:Using the staging schema name: AWR_STAGEChoose the Default tablespace for the AWR_STAGE user~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Choose the AWR_STAGE users's default tablespace. This is thetablespace in which the AWR data will be staged.TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE------------------------------ --------- ------------------EXAMPLE PERMANENTGX0315 PERMANENTORACLELEARNING PERMANENTSYSAUX PERMANENT *USERS PERMANENTPressing will result in the recommended defaulttablespace (identified by *) being used.输入 default_tablespace 的值:Using tablespace SYSAUX as the default tablespace for the AWR_STAGEChoose 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 will result in the database's default temporarytablespace (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) ;