使用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.