使用dbms_addm包执行addm性能诊断

使用dbms_addm包可以以三种模式来执行addm性能诊断
.以数据库模式
.以实例模式
.以部分模式
.显示ADDM报告

以数据库模式语法如下:

begin
dbms_addm.analyze_db (
task_name in out varchar2,
begin_snapshot in number,
end_snapshot in number,
db_id in number := null);
end;
/

示例:

SQL> var tname varchar2(30);
SQL> begin
  2  :tname := 'addm for 17 to 18';
  3  dbms_addm.analyze_db(:tname, 3785, 3786);
  4  end;
  5  /

以实例模式语法如下:

begin
dbms_addm.analyze_inst (
task_name in out varchar2,
begin_snapshot in number,
end_snapshot in number,
instance_number in number := null,
db_id in number := null);
end;
/

示例:

SQL> var tname varchar2(30);
SQL> begin
  2  :tname := 'addm for 17 to 18';
  3  dbms_addm.analyze_inst(:tname, 3785, 3786,1);
  4  end;
  5  /

以部分模式语法如下:

begin
dbms_addm.analyze_partial (
task_name in out varchar2,
instance_numbers in varchar2,
begin_snapshot in number,
end_snapshot in number,
db_id in number := null);
end;
/

示例:

SQL> var tname varchar2(30);
SQL> begin
  2  :tname := 'addm for 17 to 18';
  3  dbms_addm.analyze_inst(:tname,'1,2',3785,3786);
  4  end;
  5  /

显示ADDM报告

SQL> set long 1000000 pagesize 0;
SQL> select dbms_addm.get_report(:tname) from dual;
          ADDM Report for Task 'addm_cs for 09 to 10'
          -------------------------------------------

Analysis Period
---------------
AWR snapshot range from 16201 to 16202.
Time period starts at 10-SEP-18 09.00.34 AM
Time period ends at 10-SEP-18 10.00.39 AM

Analysis Target
---------------
Database 'RLZY' with DB ID 1710982568.
Database version 11.2.0.4.0.
Analysis was requested for all instances, but ADDM analyzed instance RLZY2,
numbered 2 and hosted at db2.
See the "Additional Information" section for more information on the requested
instances.

Activity During the Analysis Period
-----------------------------------
Total database time was 22511 seconds.
The average number of active sessions was 6.24.
ADDM analyzed 1 of the requested 1 instances.

Summary of Findings
-------------------
   Description                               Active Sessions      Recommendation
s                                              Percent of Activity
   ----------------------------------------  -------------------  --------------
- 1  Undersized SGA                            1.91 | 30.55         1
2  Hard Parse Due to Literal Usage           1.78 | 28.58         1
3  Top SQL Statements                        1.13 | 18.17         4
4  Top Segments by "User I/O" and "Cluster"  .55 | 8.78           3
5  Global Cache Messaging                    .17 | 2.79           0


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


          Findings and Recommendations
          ----------------------------

Finding 1: Undersized SGA
Impact is 1.91 active sessions, 30.55% of total activity.
---------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "51200 M" during the analysis period.

   Recommendation 1: Database Configuration
   Estimated benefit is 1.91 active sessions, 30.55% of total activity.
   --------------------------------------------------------------------
   Action
      Increase the size of the SGA by setting the parameter "sga_target" to
      76800 M.

   Symptoms That Led to the Finding:
   ---------------------------------
      Hard parsing of SQL statements was consuming significant database time.
      Impact is 1.82 active sessions, 29.22% of total activity.
      Wait class "User I/O" was consuming significant database time.
      Impact is 1.21 active sessions, 19.36% of total activity.


Finding 2: Hard Parse Due to Literal Usage
Impact is 1.78 active sessions, 28.58% of total activity.
---------------------------------------------------------
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is 1.78 active sessions, 28.58% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate application logic for possible use of bind variables instead
      of literals.
   Action
      Alternatively, you may set the parameter "cursor_sharing" to "force".
   Rationale
      At least 106 SQL statements with FORCE_MATCHING_SIGNATURE
      10877138405225238976 and PLAN_HASH_VALUE 76216136 were found to be using
      literals. Look in V$SQL for examples of such SQL statements.
   Rationale
      At least 104 SQL statements with FORCE_MATCHING_SIGNATURE
      10877138405225238976 and PLAN_HASH_VALUE 3291498723 were found to be
      using literals. Look in V$SQL for examples of such SQL statements.
   Rationale
      At least 31 SQL statements with FORCE_MATCHING_SIGNATURE
      1212153288317564648 and PLAN_HASH_VALUE 4105729137 were found to be
      using literals. Look in V$SQL for examples of such SQL statements.
   Rationale
      At least 30 SQL statements with FORCE_MATCHING_SIGNATURE
      10877138405225238976 and PLAN_HASH_VALUE 4135929742 were found to be
      using literals. Look in V$SQL for examples of such SQL statements.
   Rationale
      At least 27 SQL statements with FORCE_MATCHING_SIGNATURE
      16030230994864437066 and PLAN_HASH_VALUE 2060270506 were found to be
      using literals. Look in V$SQL for examples of such SQL statements.

   Symptoms That Led to the Finding:
   ---------------------------------
      Hard parsing of SQL statements was consuming significant database time.
      Impact is 1.82 active sessions, 29.22% of total activity.


Finding 3: Top SQL Statements
Impact is 1.13 active sessions, 18.17% of total activity.
---------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.

   Recommendation 1: SQL Tuning
   Estimated benefit is .48 active sessions, 7.63% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "awb7dnusv1by2".
      Related Object
         SQL statement with SQL_ID awb7dnusv1by2.
         select distinct a.hospital_id as hospital_id,         (select
         d.hospital_name from bs_hospital d where f.hospital_id =
         d.hospital_id ) as hospital_name,         f.ACCOUNT_ID as ACCOUNT_ID,
         a.indi_id as indi_id,         a.serial_no as
         serial_no,a.treatment_type as treatment_type,         (select
         h.BIZ_NAME from bs_biztype h where h.center_id=a.center_id and
         h.biz_type=a.biz_type) as biz_name,         e.disease as disease,
         a.reg_man as reg_man,         a.begin_date as begin_date,
         a.end_date as end_date,         a.name as name,         a.sex as sex,
         a.IDCARD as idcard,         a.pers_type as pers_type,
         a.finish_flag as finish_flag,         (select g.pers_name from
         bs_person_type g where a.pers_type = g.pers_type and a.center_id =
         g.center_id ) as pers_name,         a.in_area_name as in_area_name,
         a.in_dept_name as in_dept_name,         a.in_bed as in_bed,
         a.bed_type as bed_type,         a.corp_name as corp_name,
         a.fin_date as fin_date,         (select sum(pay_money) from
         pm_account_fund k where  k.account_id = f.account_id) total_pay,
         (select sum(decode(k.FUND_ID,'996',0,'999',0,k.pay_money)) from
         pm_account_fund k where k.account_id = f.account_id ) fund_pay  from
         mt_biz_fin a,         pm_account_biz f,         bs_disease e,
         bs_center center ,bs_hospital bh  where
         decode(a.reimburse_flag,'2',a.rela_hosp_id ,a.hospital_id) =
         f.hospital_id      and f.hospital_id = bh.hospital_id        and
         a.serial_no = f.serial_no         and a.center_id = f.center_id
         and e.icd (+)= f.icd         and center.center_id (+)= f.center_id
         and e.center_id = nvl(center.catalog_center,center.center_id)
         and (bh.hospital_id in ('4307000009') or bh.up_hospital_id in
         ('4307000009')) and exists (select 'X' from bs_center  center where
         center.center_id=a.center_id and center.center_id  in ('430702'))
         and nvl(f.deal_flag,'0') = '0'          and f.valid_flag = '1'
         and to_number(to_char(f.fin_date,'yyyymmdd')) between 20180801 and
         20180831        and a.valid_flag = '1'         and a.lock_flag = '1'
         and a.reimburse_flag in ('0','2')          and
         to_number(to_char(a.fin_date,'yyyymmdd')) between 20180801  and
         20180831
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "awb7dnusv1by2" was executed 2 times and had
      an average elapsed time of 851 seconds.
   Rationale
      At least 2 distinct execution plans were utilized for this SQL statement
      during the analysis period.

   Recommendation 2: SQL Tuning
   Estimated benefit is .31 active sessions, 4.95% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "fw5gmdaxuyk3a".
      Related Object
         SQL statement with SQL_ID fw5gmdaxuyk3a.
         SELECT DISTINCT HOSPITAL_ID,
         SERIAL_NO,PERS_TYPE,RELA_HOSP_ID,A.REIMBURSE_FLAG FROM MT_BIZ_FIN A,
         BS_BIZTYPE B WHERE A.BIZ_TYPE = B.BIZ_TYPE AND (:B8 = 'A' OR
         B.BIZ_STAT = :B8 ) AND B.INSR_NO = :B7 AND B.BIZ_STAT IN ('2', '3')
         AND NVL(A.LOCK_FLAG, '0') = '0' AND A.VALID_FLAG = '1' AND
         (NVL(A.REIMBURSE_FLAG, '0') = '0' OR A.REIMBURSE_FLAG = '2') AND
         (A.HOSPITAL_ID = :B6 OR A.RELA_HOSP_ID = :B6 ) AND (A.CENTER_ID=:B5
         OR (:B5 ='431000' AND A.CENTER_ID IN ('431000','431002','431003')))
         AND ('A' = :B4 OR A.OP_TOWN_ID = :B4 ) AND ('A' = :B3 OR
         A.OP_VILLAGE_ID = :B3 ) AND A.FIN_DATE BETWEEN TO_DATE(:B2 || '
         00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(:B1 || ' 23:59:59',
         'yyyy-mm-dd hh24:mi:ss')
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "fw5gmdaxuyk3a" was executed 52 times and had
      an average elapsed time of 21 seconds.
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "8prz8w1bg5714" are responsible for 100% of the database time spent on
      the SELECT statement with SQL_ID "fw5gmdaxuyk3a".
      Related Object
         SQL statement with SQL_ID 8prz8w1bg5714.
         call usp_pay_account_declare(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10
         ,:11 ,:12 ,:13 )

   Recommendation 3: SQL Tuning
   Estimated benefit is .18 active sessions, 2.86% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "f63shdpjy5kgy".
      Related Object
         SQL statement with SQL_ID f63shdpjy5kgy.

   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      I/O and Cluster wait for TABLE "INSUR_CHANGDE.MT_PAY_RECORD_FIN" with
      object ID 925431 consumed 100% of the database time spent on this SQL
      statement.

   Recommendation 4: SQL Tuning
   Estimated benefit is .17 active sessions, 2.73% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "a81a3tafx4wzb".
      Related Object
         SQL statement with SQL_ID a81a3tafx4wzb.
         select distinct li.pers_type   from lv_indipar li,lv_cropfundpar
         lc,lv_insr_topay lt  where li.indi_id = :1     and li.period = :2
         and lc.money_no = li.money_no    and lt.pay_info_no = lc.pay_info_no
         and lt.insr_detail_code <> 21    and lt.topay_type not in (13) and
         li.pers_type is not null
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "a81a3tafx4wzb" was executed 14866 times and
      had an average elapsed time of 0.046 seconds.
   Rationale
      I/O and Cluster wait for TABLE "INSUR_CHANGDE.LV_INDIPAR" with object ID
      925315 consumed 90% of the database time spent on this SQL statement.


Finding 4: Top Segments by "User I/O" and "Cluster"
Impact is .55 active sessions, 8.78% of total activity.
-------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.

   Recommendation 1: Segment Tuning
   Estimated benefit is .24 active sessions, 3.81% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE
      "INSUR_CHANGDE.MT_PAY_RECORD_FIN" with object ID 925431.
      Related Object
         Database object with ID 925431.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the SELECT statement with
      SQL_ID "f63shdpjy5kgy" is responsible for 75% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 0 full object scans, 438356
      physical reads, 683 physical writes and 0 direct reads.

   Recommendation 2: Segment Tuning
   Estimated benefit is .18 active sessions, 2.87% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE
      "INSUR_CHANGDE.LV_INDIPAR" with object ID 925315.
      Related Object
         Database object with ID 925315.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the SELECT statement with
      SQL_ID "a81a3tafx4wzb" is responsible for 85% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 0 full object scans, 708526
      physical reads, 2431 physical writes and 0 direct reads.

   Recommendation 3: Segment Tuning
   Estimated benefit is .13 active sessions, 2.11% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE
      "INSUR_CHANGDE.MT_BIZ_FIN" with object ID 925378.
      Related Object
         Database object with ID 925378.
   Rationale
      The I/O usage statistics for the object are: 0 full object scans, 275264
      physical reads, 1026 physical writes and 0 direct reads.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "User I/O" was consuming significant database time.
      Impact is 1.21 active sessions, 19.36% of total activity.


Finding 5: Global Cache Messaging
Impact is .17 active sessions, 2.79% of total activity.
-------------------------------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
Read and write contention on database blocks was not consuming significant
database time in the cluster.
Global Cache Service Processes (LMSn) in other instances were performing
within acceptable limits of 1 milliseconds.
Waits on "buffer busy" events were not consuming significant database time.

   No recommendations are available.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Cluster" was consuming significant database time.
      Impact is .18 active sessions, 2.81% of total activity.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
Session connect and disconnect calls were not consuming significant database
time.