oracle+数据库性能调优,oracle数据库性能调优

这个是我做的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