oracle的性能报告获取
来源：互联网 发布：mysql if函数用法 编辑：程序博客网 时间：2024/06/04 01:13
体检报告--AWR
awrrpt.sql方式获取
SQL> exec dbms_workload_repository.create_snapshot();PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/awrrpt.sqlCurrent Instance~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance----------- ------------ -------- ------------ 1475756829 ORCL 1 orclSpecify the Report Type~~~~~~~~~~~~~~~~~~~~~~~Would you like an HTML report, or a plain text report?Enter 'html' for an HTML report, or 'text' for plain textDefaults to 'html'Enter value for report_type: Type Specified: htmlInstances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host------------ -------- ------------ ------------ ------------* 1475756829 1 ORCL orcl orasqlUsing 1475756829 for database IdUsing 1 for instance numberSpecify 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> withoutspecifying a number lists all completed snapshots.Enter value for num_days: 1Listing the last day's Completed Snapshots SnapInstance DB Name Snap Id Snap Started Level------------ ------------ --------- ------------------ -----orcl ORCL 139 15 Jul 2017 00:00 1 140 15 Jul 2017 01:00 1 141 15 Jul 2017 02:00 1 142 15 Jul 2017 03:00 1 143 15 Jul 2017 04:00 1 144 15 Jul 2017 05:00 1 145 15 Jul 2017 06:00 1 146 15 Jul 2017 07:00 1 147 15 Jul 2017 08:00 1 148 15 Jul 2017 09:00 1 149 15 Jul 2017 10:00 1 150 15 Jul 2017 11:00 1 151 15 Jul 2017 12:00 1 152 15 Jul 2017 13:00 1 153 15 Jul 2017 14:00 1 154 15 Jul 2017 15:00 1 155 15 Jul 2017 16:00 1 156 15 Jul 2017 17:00 1 157 15 Jul 2017 18:00 1 158 15 Jul 2017 19:00 1 159 15 Jul 2017 20:00 1 160 15 Jul 2017 21:00 1 161 15 Jul 2017 21:45 1Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 160Begin Snapshot Id specified: 160Enter value for end_snap: 161End Snapshot Id specified: 161Specify the Report Name~~~~~~~~~~~~~~~~~~~~~~~The default report file name is awrrpt_1_160_161.html. To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name: awrrpt_1_160_161.html
调用命令包获取
格式：select output from table(dbms_workload_repository.awr_report_html(DB Id,Inst Num,begin_snap,end_snap));
SQL> set pagesize 0SQL> set linesize 121SQL> spool awrrpt_2.htmlSQL> select output from table(dbms_workload_repository.awr_report_html(1475756829,1,160,161));SQL> spool off
体检报告显示有胃病，需要获取胃镜报告---ASH
ashrpt.sql方式获取
SQL> alter session set nls_date_language='american';Session altered.
SQL> @?/rdbms/admin/ashrpt.sqlCurrent Instance~~~~~~~~~~~~~~~~ 1475756829 ORCL 1 orclSpecify the Report Type~~~~~~~~~~~~~~~~~~~~~~~Enter 'html' for an HTML report, or 'text' for plain textDefaults to 'html'Enter value for report_type: htmlType Specified: htmlInstances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host------------ -------- ------------ ------------ ------------* 1475756829 1 ORCL orcl orasqlDefaults to current databaseUsing database id: 1475756829Enter instance numbers. Enter 'ALL' for all instances in aRAC cluster or explicitly specify list of instances (e.g., 1,2,3).Defaults to current instance.Using instance number(s): 1ASH Samples in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Oldest ASH sample available: 09-Jul-17 22:47:42 [ 8599 mins in the past]Latest ASH sample available: 15-Jul-17 22:06:06 [ 1 mins in the past]Specify the timeframe to generate the ASH report~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter begin time for report:-- Valid input formats:-- To specify absolute begin time:-- [MM/DD[/YY]] HH24:MI[:SS]-- Examples: 02/23/03 14:30:15-- 02/23 14:30:15-- 14:30:15-- 14:30-- To specify relative begin time: (start with '-' sign)-- -[HH24:]MI-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)-- -25 (SYSDATE - 25 Mins)Defaults to -15 minsEnter value for begin_time: 21:00Report begin time specified: 21:00Enter duration in minutes starting from begin time:Defaults to SYSDATE - begin_timePress Enter to analyze till current timeEnter value for duration: 45Report duration specified: 45Using 15-Jul-17 21:00:00 as report begin timeUsing 15-Jul-17 21:45:00 as report end timeSpecify Slot Width (using ashrpti.sql) for 'Activity Over Time' section~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- Explanation:-- In the 'Activity Over Time' section of the ASH report,-- the analysis period is divided into smaller slots-- and top wait events are reported in each of those slots.-- Default:-- The analysis period will be automatically split upto 10 slots-- complying to a minimum slot width of-- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or-- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.Specify Slot Width in seconds to use in the 'Activity Over Time' section:Defaults to a value as explained above:Slot Width specified:Specify Report Targets (using ashrpti.sql) to generate the ASH report~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- Explanation:-- ASH Report can accept "Report Targets",-- like a particular SQL statement, or a particular SESSION,-- to generate the report on. If one or more report targets are-- specified, then the data used to generate the report will only be-- the ASH samples that pertain to ALL the specified report targets.-- Default:-- If none of the report targets are specified,-- then the target defaults to all activity in the database instance.Specify SESSION_ID (eg: from V$SESSION.SID) report target:Defaults to NULL:SESSION report target specified:Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:Defaults to NULL: (% and _ wildcards allowed)SQL report target specified:Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:[Enter 'CPU' to investigate CPU usage]Defaults to NULL: (% and _ wildcards allowed)WAIT_CLASS report target specified:Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:Defaults to NULL:SERVICE report target specified:Specify MODULE name (eg: from V$SESSION.MODULE) report target:Defaults to NULL: (% and _ wildcards allowed)MODULE report target specified:Specify ACTION name (eg: from V$SESSION.ACTION) report target:Defaults to NULL: (% and _ wildcards allowed)ACTION report target specified:Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:Defaults to NULL: (% and _ wildcards allowed)CLIENT_ID report target specified:Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:Defaults to NULL: (% and _ wildcards allowed)PLSQL_ENTRY report target specified:Specify the Report Name~~~~~~~~~~~~~~~~~~~~~~~The default report file name is ashrpt_1_0715_2145.html. To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name: ashrpt_1_0715_2145.html
ASH报告的获取不同于AWR的地方在于，快照之间有无重启动作不影响报告的获取。
调用命令包获取
格式：select output from table(dbms_workload_repository.ash_report_html(DB Id,Inst Num,begin_time,end_time));
SQL> set pagesize 0SQL> set linesize 121SQL> spool ashrpt_3.htmlSQL> select output from table(dbms_workload_repository.ash_report_html(1475756829,1,SYSDATE-85/1440,SYSDATE-45/1440));SQL> spool offSYSDATE-85/1440 表示当前时间往后推85分钟
病历卡记录--方便阅读的体检报告--ADDM
SQL> @?/rdbms/admin/addmrpt.sqlCurrent Instance~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance----------- ------------ -------- ------------ 1475756829 ORCL 1 orclInstances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host------------ -------- ------------ ------------ ------------* 1475756829 1 ORCL orcl orasqlUsing 1475756829 for database IdUsing 1 for instance numberSpecify 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> withoutspecifying a number lists all completed snapshots.Listing the last 3 days of Completed SnapshotsInstance DB Name Snap Id Snap Started Level------------ ------------ --------- ------------------ -----orcl ORCL 147 15 Jul 2017 08:00 1 148 15 Jul 2017 09:00 1 149 15 Jul 2017 10:00 1 150 15 Jul 2017 11:00 1 151 15 Jul 2017 12:00 1 152 15 Jul 2017 13:00 1 153 15 Jul 2017 14:00 1 154 15 Jul 2017 15:00 1 155 15 Jul 2017 16:00 1 156 15 Jul 2017 17:00 1 157 15 Jul 2017 18:00 1 158 15 Jul 2017 19:00 1 159 15 Jul 2017 20:00 1 160 15 Jul 2017 21:00 1 161 15 Jul 2017 21:45 1Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 160Begin Snapshot Id specified: 160Enter value for end_snap: 161End Snapshot Id specified: 161Specify the Report Name~~~~~~~~~~~~~~~~~~~~~~~The default report file name is addmrpt_1_160_161.txt. To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name: addmrpt_1_160_161.txt
体检报告的对比---AWRDD
SQL> @?/rdbms/admin/awrddrpt.sqlCurrent Instance~~~~~~~~~~~~~~~~ DB Id DB Id DB Name Inst Num Inst Num Instance----------- ----------- ------------ -------- -------- ------------ 1475756829 1475756829 ORCL 1 1 orclSpecify the Report Type~~~~~~~~~~~~~~~~~~~~~~~Would you like an HTML report, or a plain text report?Enter 'html' for an HTML report, or 'text' for plain textDefaults to 'html'Enter value for report_type: htmlType Specified: htmlInstances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host------------ -------- ------------ ------------ ------------* 1475756829 1 ORCL orcl orasqlDatabase Id and Instance Number for the First Pair of Snapshots~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Using 1475756829 for Database Id for the first pair of snapshotsUsing 1 for Instance Number for the first pair of snapshotsSpecify 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> withoutspecifying a number lists all completed snapshots.Enter value for num_days: 1Listing the last day's Completed Snapshots SnapInstance DB Name Snap Id Snap Started Level------------ ------------ --------- ------------------ -----orcl ORCL 139 15 Jul 2017 00:00 1 140 15 Jul 2017 01:00 1 141 15 Jul 2017 02:00 1 142 15 Jul 2017 03:00 1 143 15 Jul 2017 04:00 1 144 15 Jul 2017 05:00 1 145 15 Jul 2017 06:00 1 146 15 Jul 2017 07:00 1 147 15 Jul 2017 08:00 1 148 15 Jul 2017 09:00 1 149 15 Jul 2017 10:00 1 150 15 Jul 2017 11:00 1 151 15 Jul 2017 12:00 1 152 15 Jul 2017 13:00 1 153 15 Jul 2017 14:00 1 154 15 Jul 2017 15:00 1 155 15 Jul 2017 16:00 1 156 15 Jul 2017 17:00 1 157 15 Jul 2017 18:00 1 158 15 Jul 2017 19:00 1 159 15 Jul 2017 20:00 1 160 15 Jul 2017 21:00 1 161 15 Jul 2017 21:45 1Specify the First Pair of Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 160First Begin Snapshot Id specified: 160Enter value for end_snap: 161First End Snapshot Id specified: 161Instances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host------------ -------- ------------ ------------ ------------* 1475756829 1 ORCL orcl orasqlDatabase Id and Instance Number for the Second Pair of Snapshots~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Using 1475756829 for Database Id for the second pair of snapshotsUsing 1 for Instance Number for the second pair of snapshotsSpecify 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> withoutspecifying a number lists all completed snapshots.Enter value for num_days2: 1Listing the last day's Completed Snapshots SnapInstance DB Name Snap Id Snap Started Level------------ ------------ --------- ------------------ -----orcl ORCL 139 15 Jul 2017 00:00 1 140 15 Jul 2017 01:00 1 141 15 Jul 2017 02:00 1 142 15 Jul 2017 03:00 1 143 15 Jul 2017 04:00 1 144 15 Jul 2017 05:00 1 145 15 Jul 2017 06:00 1 146 15 Jul 2017 07:00 1 147 15 Jul 2017 08:00 1 148 15 Jul 2017 09:00 1 149 15 Jul 2017 10:00 1 150 15 Jul 2017 11:00 1 151 15 Jul 2017 12:00 1 152 15 Jul 2017 13:00 1 153 15 Jul 2017 14:00 1 154 15 Jul 2017 15:00 1 155 15 Jul 2017 16:00 1 156 15 Jul 2017 17:00 1 157 15 Jul 2017 18:00 1 158 15 Jul 2017 19:00 1 159 15 Jul 2017 20:00 1 160 15 Jul 2017 21:00 1 161 15 Jul 2017 21:45 1Specify the Second Pair of Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap2: 158Second Begin Snapshot Id specified: 158Enter value for end_snap2: 159Second End Snapshot Id specified: 159Specify the Report Name~~~~~~~~~~~~~~~~~~~~~~~The default report file name is awrdiff_1_160_1_158.html To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name: awrdiff_1_160_1_158.html
胃镜ASH发现发现胃里有痘痘，获取痘痘的信息，活检报告--AWRSQRPT
SQL> @?/rdbms/admin/awrsqrpt.sqlCurrent Instance~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance----------- ------------ -------- ------------ 1475756829 ORCL 1 orclSpecify the Report Type~~~~~~~~~~~~~~~~~~~~~~~Would you like an HTML report, or a plain text report?Enter 'html' for an HTML report, or 'text' for plain textDefaults to 'html'Enter value for report_type: htmlType Specified: htmlInstances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host------------ -------- ------------ ------------ ------------* 1475756829 1 ORCL orcl orasqlUsing 1475756829 for database IdUsing 1 for instance numberSpecify 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> withoutspecifying a number lists all completed snapshots.Enter value for num_days: 1Listing the last day's Completed Snapshots SnapInstance DB Name Snap Id Snap Started Level------------ ------------ --------- ------------------ -----orcl ORCL 139 15 Jul 2017 00:00 1 140 15 Jul 2017 01:00 1 141 15 Jul 2017 02:00 1 142 15 Jul 2017 03:00 1 143 15 Jul 2017 04:00 1 144 15 Jul 2017 05:00 1 145 15 Jul 2017 06:00 1 146 15 Jul 2017 07:00 1 147 15 Jul 2017 08:00 1 148 15 Jul 2017 09:00 1 149 15 Jul 2017 10:00 1 150 15 Jul 2017 11:00 1 151 15 Jul 2017 12:00 1 152 15 Jul 2017 13:00 1 153 15 Jul 2017 14:00 1 154 15 Jul 2017 15:00 1 155 15 Jul 2017 16:00 1 156 15 Jul 2017 17:00 1 157 15 Jul 2017 18:00 1 158 15 Jul 2017 19:00 1 159 15 Jul 2017 20:00 1 160 15 Jul 2017 21:00 1 161 15 Jul 2017 21:45 1Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 160Begin Snapshot Id specified: 160Enter value for end_snap: 161End Snapshot Id specified: 161Specify the SQL Id~~~~~~~~~~~~~~~~~~Enter value for sql_id: 输入SQL_ID
阅读全文
0 0
- oracle的性能报告获取
- Oracle性能awr报告
- Oracle 性能报告ADDM
- Oracle AWR性能分析报告
- 生成oracle awr 性能分析报告
- 从AWR报告分析Oracle性能
- oracle AWR性能监控报告生成方法
- oracle AWR性能监控报告生成方法
- oracle的AWR报告
- 获取Oracle数据库awr报告方法
- Oracle的AWR报告分析
- Oracle的AWR报告分析
- oracle的awr报告导出
- Oracle的AWR报告分析
- Oracle AWR报告的生成
- Oracle的AWR报告分析
- oracle: awr 报告的生成
- Oracle的AWR报告分析
- Python 多版本共存之 pyenv
- Scrapy-基本用法
- 8位有符号数的补码表示范围
- 十三天
- 《自控力》 【美】凯利·麦格尼格尔 第一章读书笔记
- oracle的性能报告获取
- java日志组件介绍（common-logging，log4j，slf4j，logback ）
- 关于RAC中SCN原理和机制的探索
- 结构体的大小
- jsp登录（mysql数据库）
- Scrapy-爬虫多开技能
- HEVC官方软件HM源代码简单分析-解码器TAppDecoder
- [NOIp复习计划]:二分答案
- windos 下MySQL 安装和启动步骤
