admin管理员组文章数量:1593955
这个是我做的ADDM报告,我有点看不懂,看的七七八八,麻烦大神们帮忙看看数据库主要问题出在哪里呀?
Activity During the Analysis Period
-----------------------------------
Total database time was 1342415 seconds.
The average number of active sessions was 9.1.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
---------------------------------------- ------------------- ---------------
1 Virtual Memory Paging 9.1 | 100 2
2 I/O Throughput 9.1 | 100 1
3 Top SQL Statements 1.72 | 18.93 5
4 Top Segments by "User I/O" and "Cluster" 1 | 11.03 4
5 Row Lock Waits .24 | 2.59 5
6 Undersized instance memory .13 | 1.47 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Virtual Memory Paging
Impact is 9.1 active sessions, 100% of total activity.
------------------------------------------------------
Significant virtual memory paging was detected on the host operating system.
Recommendation 1: Host Configuration
Estimated benefit is 9.1 active sessions, 100% of total activity.
-----------------------------------------------------------------
Action
Host operating system was experiencing significant paging but no
particular root cause could be detected. Investigate processes that do
not belong to this instance running on the host that are consuming
significant amount of virtual memory. Also consider adding more physical
memory to the host.
Recommendation 2: Database Configuration
Estimated benefit is 9.1 active sessions, 100% of total activity.
-----------------------------------------------------------------
Action
Consider enabling Automatic Shared Memory Management by setting the
parameter "sga_target" to control the amount of SGA consumed by this
instance.
Finding 2: I/O Throughput
Impact is 9.1 active sessions, 100% of total activity.
------------------------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
Recommendation 1: Host Configuration
Estimated benefit is 9.1 active sessions, 100% of total activity.
-----------------------------------------------------------------
Action
Consider increasing the throughput of the I/O subsystem. Oracle's
recommended solution is to stripe all data files using the SAME
methodology. You might also need to increase the number of disks for
better performance.
Rationale
During the analysis period, the average data files' I/O throughput was
117 M per second for reads and 377 K per second for writes. The average
response time for single block reads was 2.5 milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 3.35 active sessions, 36.8% of total activity.
Finding 3: Top SQL Statements
Impact is 1.72 active sessions, 18.93% 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 .55 active sessions, 6.08% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"aqfhk2s90v0a7".
Related Object
SQL statement with SQL_ID aqfhk2s90v0a7.
SELECT * FROM (SELECT * FROM BP_BANKINSTRINFO t
WHERE STATUSID = 8 AND RECBANK = :1 ORDER BY
SENDLEVEL,MODIFYTIME) WHERE ROWNUM <= :2
Rationale
The SQL spent 99% 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 "aqfhk2s90v0a7" was executed 1712 times and
had an average elapsed time of 42 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .33 active sessions, 3.66% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"2usbpg6umxcqm".
Related Object
SQL statement with SQL_ID 2usbpg6umxcqm.
select td.id,td.payaccountid ,a.accountno
payaccountno,td.amount,td.extaccountno
recaccountno,t.transferdate,td.memo,1 direction,1 directionname
from ts_transfer t ,ts_transferdetail td,ts_account a where
t.id=td.transferid and td.payaccountid=a.id and t.statusid=1 and
td.statusid=1 and a.status=1 and td.bankpaytype=:1 and t.type=:2
and t.transferstatus=:3 and td.transferdetailstatus=:4 and
td.ispaychecked=:5 and td.payaccountid=:6 and
t.transferdate > to_date(:7 ,'yyyy-MM-dd')
and t.transfertypeid not in ('YW0019')
union all select td.id,td.payaccountid ,a.accountno
payaccountno,td.amount,td.extaccountno recaccountno,b.MODIFYTIME
transferdate,td.memo,1 direction,1 directionname from ts_transfer
t ,ts_transferdetail td,ts_account a,bp_bankinstrinfo b where
t.id=td.transferid and td.payaccountid=a.id and t.statusid=1 and
td.statusid=1 and a.status=1 and td.subtransfercode=b.transactionno
and b.statusid=4 and t.type=:8 and t.transferstatus=:9 and
td.ispaychecked=:10 and td.payaccountid=:11 and
t.transferdate > to_date(:12 ,'yyyy-MM-dd')
and t.transfertypeid not in ('YW0019')
union all select td.id,td.payaccountid, a.accountno
payaccountno,td.amount,b.accountno recaccountno,bp.MODIFYTIME
transferdate,td.memo,1 direction,1 directionname from ts_transfer
t ,ts_transferdetail td ,ts_account a,ts_account b,bp_bankinstrinfo
bp where t.id=td.transferid and a.id=td.payaccountid and
b.id=td.receiveaccountid and td.subtransfercode=bp.transactionno and
bp.statusid=4 and t.statusid=1 and td.statusid=1 and a.status=1
and b.status=1 and t.type=:13 and t.transferstatus=:14 and
td.ispaychecked=:15 and td.payaccountid=:16 and
t.transferdate > to_date(:17 ,'yyyy-MM-dd')
and t.transfertypeid not in ('YW0019') union all
select td.id,td.receiveaccountid payaccountid,b.accountno
payaccountno,td.amount,a.accountno recaccountno,bp.MODIFYTIME
transferdate,td.memo,2 direction,2 directionname from ts_transfer
t ,ts_transferdetail td ,ts_account a,ts_account b,bp_bankinstrinfo
bp where t.id=td.transferid and a.id=td.payaccountid and
b.id=td.receiveaccountid and t.statusid=1 and td.statusid=1 and
a.status=1 and b.status=1 and td.subtransfercode=bp.transactionno and
bp.statusid=4 and t.type=:18 and t.transferstatus=:19 and
td.isrecchecked=:20 and td.receiveaccountid=:21 and
t.transferdate > to_date(:22 ,'yyyy-MM-dd')
and t.transfertypeid not in ('YW0019')
Rationale
The SQL spent 75% 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 "2usbpg6umxcqm" was executed 1091 times and
had an average elapsed time of 66 seconds.
Rationale
At least 2 distinct execution plans were utilized for this SQL statement
during the analysis period.
Recommendation 3: SQL Tuning
Estimated benefit is .33 active sessions, 3.58% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"b11ctz7v1a36y".
Related Object
SQL statement with SQL_ID b11ctz7v1a36y.
select nvl(sum(g.returngoodsamount),0) total_returngoodsamount from
cash_cardreturning g where g.banktransferid=:1 and g.statusid=1
and g.cardreturnstatus=3 and g.instructstatusid=4
Rationale
The SQL spent 99% 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 "b11ctz7v1a36y" was executed 34779 times and
had an average elapsed time of 1.3 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is .2 active sessions, 2.24% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"7tkvpwmuxynbm".
Related Object
SQL statement with SQL_ID 7tkvpwmuxynbm.
SELECT SA.ID AGENCYID, CIS.TRANSDATE, CIS.ID CISID, CIB.ID CIBID FROM
CASH_INTER_SHOPDETAIL CIS, CASH_INTER_BANKTRANSFER CIB,
SYS_STORESINFORMATION SSI, SYS_AGENCY SA WHERE CIS.TRANSAMOUNT >= 0
AND CIB.TRANSFERDATE = TO_DATE(:B5 , 'yyyy-mm-dd') AND CIS.TRANSDATE
= TO_DATE(:B5 , 'yyyy-mm-dd') AND CIS.ISTRANSFERMATCHED = 2 AND
CIB.ISTRANSFERCHECKED = 2 AND SA.AGENCYTYPE = 5 AND SA.STATUSID = 1
AND CIB.STATUSID = 1 AND CIS.STATUSID = 1 AND SSI.STATUSID = 1 AND
SA.ID = :B4 AND SSI.STORESID = SA.ID AND CIB.CUSTOMERNO =
SSI.STORESCODE AND CIS.AGENCYNO = SA.STORECODE AND CIS.TRANSAMOUNT =
CIB.TRANSFERAMOUNT AND TRUNC(CIB.TRANSFERDATE, 'dd') =
TRUNC(CIS.TRANSDATE, 'dd') AND ((CIS.CARDNO IS NOT NULL AND
LENGTH(CIB.CARDNO) = LENGTH(CIS.CARDNO) AND SUBSTR(CIB.CARDNO, 0, 6)
= SUBSTR(CIS.CARDNO, 0, 6) AND SUBSTR(CIB.CARDNO, -4) =
SUBSTR(CIS.CARDNO, -4)) OR (CIS.CARDNO IS NULL AND
SUBSTR(CIB.REFERENCENO, -4) = SUBSTR(CIS.ISSUINGNUMBER, -4))) AND
(SSI.STORESNOFROM = CIB.THIRDCARDTYPE OR SSI.STORESNOFROM =
CIB.SOURCE) AND CIS.CASHIERNO = NVL(:B3 , CIS.CASHIERNO) AND
CIS.TRANSAMOUNT >= NVL(:B2 , CIS.TRANSAMOUNT) AND CIS.TRANSAMOUNT <=
NVL(:B1 , CIS.TRANSAMOUNT) AND (CIB.TRANSFERTYPE = '消费' OR
CIB.TRANSFERTYPE = '消费撤销')
Action
Use bigger fetch arrays while fetching results from the SELECT statement
with SQL_ID "7tkvpwmuxynbm".
Related Object
SQL statement with SQL_ID 7tkvpwmuxynbm.
SELECT SA.ID AGENCYID, CIS.TRANSDATE, CIS.ID CISID, CIB.ID CIBID FROM
CASH_INTER_SHOPDETAIL CIS, CASH_INTER_BANKTRANSFER CIB,
SYS_STORESINFORMATION SSI, SYS_AGENCY SA WHERE CIS.TRANSAMOUNT >= 0
AND CIB.TRANSFERDATE = TO_DATE(:B5 , 'yyyy-mm-dd') AND CIS.TRANSDATE
= TO_DATE(:B5 , 'yyyy-mm-dd') AND CIS.ISTRANSFERMATCHED = 2 AND
CIB.ISTRANSFERCHECKED = 2 AND SA.AGENCYTYPE = 5 AND SA.STATUSID = 1
AND CIB.STATUSID = 1 AND CIS.STATUSID = 1 AND SSI.STATUSID = 1 AND
SA.ID = :B4 AND SSI.STORESID = SA.ID AND CIB.CUSTOMERNO =
SSI.STORESCODE AND CIS.AGENCYNO = SA.STORECODE AND CIS.TRANSAMOUNT =
CIB.TRANSFERAMOUNT AND TRUNC(CIB.TRANSFERDATE, 'dd') =
TRUNC(CIS.TRANSDATE, 'dd') AND ((CIS.CARDNO IS NOT NULL AND
LENGTH(CIB.CARDNO) = LENGTH(CIS.CARDNO) AND SUBSTR(CIB.CARDNO, 0, 6)
= SUBSTR(CIS.CARDNO, 0, 6) AND SUBSTR(CIB.CARDNO, -4) =
SUBSTR(CIS.CARDNO, -4)) OR (CIS.CARDNO IS NULL AND
SUBSTR(CIB.REFERENCENO, -4) = SUBSTR(CIS.ISSUINGNUMBER, -4))) AND
(SSI.STORESNOFROM = CIB.THIRDCARDTYPE OR SSI.STORESNOFROM =
CIB.SOURCE) AND CIS.CASHIERNO = NVL(:B3 , CIS.CASHIERNO) AND
CIS.TRANSAMOUNT >= NVL(:B2 , CIS.TRANSAMOUNT) AND CIS.TRANSAMOUNT <=
NVL(:B1 , CIS.TRANSAMOUNT) AND (CIB.TRANSFERTYPE = '消费' OR
CIB.TRANSFERTYPE = '消费撤销')
Rationale
The SQL spent 97% 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 "7tkvpwmuxynbm" was executed 1744 times and
had an average elapsed time of 13 seconds.
Rationale
At least one execution of the statement ran in parallel.
Rationale
At least 4 distinct execution plans were utilized for this SQL statement
during the analysis period.
Recommendation 5: SQL Tuning
Estimated benefit is .19 active sessions, 2.11% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"d5m60n4h89vft".
Related Object
SQL statement with SQL_ID d5m60n4h89vft.
select count(*) countNum from ( select v.id voucherid,
l.id voucherdetailid, v.versionno, v.transtype,
v.vouchertype, v.transfercode, v.voucherdate,
v.voucherstatus, v.exportfilename,
v.vouchercategory, l.debitorcredit, l.amount,
s.dictname eventcode, l.bucode, l.affiliate,
l.location, l.department, l.subjectcode,
l.subjectname, l.attribute1, l.oik,
l.abstract abstractinfo from vo_voucher v, vo_voucherdetail l
left join sys_datadictionary_value s on s.dicttype='EventCode'
where v.id = l.voucherid and v.id in (select VOUCHERID from (
select t.id VOUCHERID,t.versionno,t.transtype,t.transfercode from
vo_voucher t, vo_voucherdetail l where t.statusid = :1 and
t.id = l.voucherid and exists (select 1 from
sys_agency a where a.agencylevelcode like :2
and l.bucode = a.agencycode) AND
t.voucherdate >= TO_DATE(:3 , 'yyyy-mm-dd') AND
t.voucherdate < add_days(TO_DATE(:4 , 'yyyy-mm-dd'),1)
AND t.transtype= :5 group by
t.id,t.versionno,t.transtype,t.transfercode )) and
l.eventcode=s.dictname order by v.versionno desc )
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 "d5m60n4h89vft" was executed 29 times and had
an average elapsed time of 977 seconds.
Rationale
At least 2 distinct execution plans were utilized for this SQL statement
during the analysis period.
Finding 4: Top Segments by "User I/O" and "Cluster"
Impact is 1 active sessions, 11.03% of total activity.
------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.
Recommendation 1: Segment Tuning
Estimated benefit is .32 active sessions, 3.47% of total activity.
------------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE PARTITION
"FMSPRODUCT.CASH_INTER_BANKTRANSFER.SYS_P204375" with object ID 818786.
Related Object
Database object with ID 818786.
Action
Investigate application logic involving I/O on TABLE PARTITION
"FMSPRODUCT.CASH_INTER_BANKTRANSFER.SYS_P204375" with object ID 818786.
Related Object
Database object with ID 818786.
Rationale
The I/O usage statistics for the object are: 4539086 full object scans,
604254114 physical reads, 288990 physical writes and 604921598 direct
reads.
Recommendation 2: Segment Tuning
Estimated benefit is .27 active sessions, 2.93% of total activity.
------------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE PARTITION
"FMSPRODUCT.CASH_INTER_BANKTRANSFER.SYS_P204414" with object ID 818970.
Related Object
Database object with ID 818970.
Action
Investigate application logic involving I/O on TABLE PARTITION
"FMSPRODUCT.CASH_INTER_BANKTRANSFER.SYS_P204414" with object ID 818970.
Related Object
Database object with ID 818970.
Rationale
The I/O usage statistics for the object are: 3727713 full object scans,
505564130 physical reads, 402353 physical writes and 504612278 direct
reads.
Recommendation 3: Segment Tuning
Estimated benefit is .23 active sessions, 2.57% of total activity.
------------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE PARTITION
"FMSPRODUCT.CASH_INTER_BANKTRANSFER.SYS_P204794" with object ID 819423.
Related Object
Database object with ID 819423.
Action
Investigate application logic involving I/O on TABLE PARTITION
"FMSPRODUCT.CASH_INTER_BANKTRANSFER.SYS_P204794" with object ID 819423.
Related Object
Database object with ID 819423.
Rationale
The I/O usage statistics for the object are: 3782605 full object scans,
428072745 physical reads, 466372 physical writes and 427289521 direct
reads.
Recommendation 4: Segment Tuning
Estimated benefit is .19 active sessions, 2.06% of total activity.
------------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE PARTITION
"FMSPRODUCT.CASH_INTER_BANKTRANSFER.SYS_P204289" with object ID 816657.
Related Object
Database object with ID 816657.
Action
Investigate application logic involving I/O on TABLE PARTITION
"FMSPRODUCT.CASH_INTER_BANKTRANSFER.SYS_P204289" with object ID 816657.
Related Object
Database object with ID 816657.
Rationale
The I/O usage statistics for the object are: 2554686 full object scans,
313598566 physical reads, 189637 physical writes and 313084289 direct
reads.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 3.35 active sessions, 36.8% of total activity.
Finding 5: Row Lock Waits
Impact is .24 active sessions, 2.59% of total activity.
-------------------------------------------------------
SQL statements were found waiting for row lock waits.
Recommendation 1: Application Analysis
Estimated benefit is .13 active sessions, 1.47% of total activity.
------------------------------------------------------------------
Action
Significant row contention was detected in the TABLE PARTITION
"FMSPRODUCT.CASH_INTER_SHOPDETAIL.SYS_P204372" with object ID 818778.
Trace the cause of row contention in the application logic using the
given blocked SQL.
Related Object
Database object with ID 818778.
Rationale
The session with ID 785 and serial number 2014 in instance number 1 was
the blocking session responsible for 29% of this recommendation's
benefit.
Rationale
The session with ID 951 and serial number 489 in instance number 1 was
the blocking session responsible for 19% of this recommendation's
benefit.
Recommendation 2: Application Analysis
Estimated benefit is .06 active sessions, .65% of total activity.
-----------------------------------------------------------------
Action
Significant row contention was detected in the TABLE PARTITION
"FMSPRODUCT.CASH_INTER_SHOPDETAIL.SYS_P203689" with object ID 807239.
Trace the cause of row contention in the application logic using the
given blocked SQL.
Related Object
Database object with ID 807239.
Rationale
The session with ID 1232 and serial number 155 in instance number 1 was
the blocking session responsible for 99% of this recommendation's
benefit.
Recommendation 3: Application Analysis
Estimated benefit is .04 active sessions, .42% of total activity.
-----------------------------------------------------------------
Action
Significant row contention was detected in the TABLE
"FMSPRODUCT.SYS_TRANSCODE" with object ID 105746. Trace the cause of row
contention in the application logic using the given blocked SQL.
Related Object
Database object with ID 105746.
Recommendation 4: Application Analysis
Estimated benefit is 0 active sessions, .03% of total activity.
---------------------------------------------------------------
Action
Significant row contention was detected in the TABLE PARTITION
"FMSPRODUCT.CASH_INTER_SHOPDETAIL.SYS_P204411" with object ID 818962.
Trace the cause of row contention in the application logic using the
given blocked SQL.
Related Object
Database object with ID 818962.
Recommendation 5: Application Analysis
Estimated benefit is 0 active sessions, .02% of total activity.
---------------------------------------------------------------
Action
Significant row contention was detected in the TABLE PARTITION
"FMSPRODUCT.CASH_INTER_SHOPDETAIL.SYS_P204788" with object ID 819412.
Trace the cause of row contention in the application logic using the
given blocked SQL.
Related Object
Database object with ID 819412.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Application" was consuming significant database time.
Impact is .24 active sessions, 2.6% of total activity.
Finding 6: Undersized instance memory
Impact is .13 active sessions, 1.47% of total activity.
-------------------------------------------------------
The Oracle instance memory (SGA and PGA) was inadequately sized, causing
additional I/O and CPU usage.
The value of parameter "memory_target" was "13120 M" during the analysis
period.
Recommendation 1: Database Configuration
Estimated benefit is .08 active sessions, .86% of total activity.
-----------------------------------------------------------------
Action
Increase memory allocated to the instance by setting the parameter
"memory_target" to 14760 M.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 3.35 active sessions, 36.8% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
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.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
The database's maintenance windows were active during 53% of the analysis
period.
task_name
---------
TASK_74445
版权声明:本文标题:oracle+数据库性能调优,oracle数据库性能调优 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/dianzi/1728178813a1148189.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论