- 最后登录
- 2015-6-2
- 在线时间
- 38 小时
- 威望
- 40
- 金钱
- 122
- 注册时间
- 2012-1-4
- 阅读权限
- 10
- 帖子
- 48
- 精华
- 0
- 积分
- 40
- UID
- 143
|
1#
发表于 2012-3-28 22:48:54
|
查看: 3869 |
回复: 0
ction
Run SQL Tuning Advisor on the UPDATE statement with SQL_ID
"3nq2ywacppnbd".
Related Object
SQL statement with SQL_ID 3nq2ywacppnbd.
UPDATE CARD_INFO T SET CUSTOMERID= (SELECT CUSTOMERID FROM
TEMP_CRM_SS_RELA S WHERE SSCUSTOMERID=T.MFCUSTOMERID AND ROWNUM <=1)
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 "3nq2ywacppnbd" was executed 1 times and had
an average elapsed time of 132 seconds.
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"gswytux9zmmna" are responsible for 100% of the database time spent on
the UPDATE statement with SQL_ID "3nq2ywacppnbd".
Related Object
SQL statement with SQL_ID gswytux9zmmna.
declare
runType number;
taskRunID varchar2(8);
taskDate varchar2(8);
rtnCode number;
rtnMsg varchar2(2000);
begin
SP_ODS_TRANSFORM(1,'20120323','20120323',rtnCode,rtnMsg);
dbms_output.put_line('????@'||rtnCode||',????????:'||rtnMsg);
end;
Recommendation 4: SQL Tuning
Estimated benefit is .03 active sessions, 3.78% of total activity.
------------------------------------------------------------------
Action
Investigate the SELECT statement with SQL_ID "f0q7dnpgvwwa6" for
possible performance improvements. You can supplement the information
given here with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID f0q7dnpgvwwa6.
select A.INPUTDATE||'|'||A.INPUTORG||'|'||FN_SUP_SPACE_40@DC01(A.INPU
TUSER)||'|'||A.INVESTMENTPROP||'|'||A.INVESTMENTSUM||'|'||A.RELATIONS
HIP||'|'||A.RELATIVEID||'|'||A.RELATIVETYPE||'|'||FN_SUP_SPACE_250@DC
01(A.REMARK)||'|'||A.STOCKCERTNO||'|'||A.UPDATEDATE||'|'||A.CERTTYPE|
|'|'||FN_SUP_SPACE_80@DC01(A.CORPORATION)||'|'||A.CURRENCYTYPE||'|'||
A.CUSTOMERID||'|'||FN_SUP_SPACE_80@DC01(A.CUSTOMERNAME)||'|'||A.DESCR
IBE||'|'||A.EFFECT||'|'||FN_SUP_SPACE_80@DC01(A.EMPLOYMENT)||'|' from
CUSTOMER_RELATIVE A
Rationale
The SQL spent only 9% of its database time on CPU, I/O and Cluster
waits. Therefore, the SQL Tuning Advisor is not applicable in this case.
Look at performance data for the SQL to find potential improvements.
Rationale
Database time for this SQL was divided as follows: 0% for SQL execution,
0% for parsing, 100% for PL/SQL execution and 0% for Java execution.
Rationale
SQL statement with SQL_ID "f0q7dnpgvwwa6" was executed 1 times and had
an average elapsed time of 108 seconds.
Rationale
Waiting for event "SQL*Net message from dblink" in wait class "Network"
accounted for 63% of the database time spent in processing the SQL
statement with SQL_ID "f0q7dnpgvwwa6".
Recommendation 5: SQL Tuning
Estimated benefit is .02 active sessions, 2.41% of total activity.
------------------------------------------------------------------
Action
Investigate the INSERT statement with SQL_ID "63uqvkrdrrqb1" for
possible performance improvements. You can supplement the information
given here with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID 63uqvkrdrrqb1.
insert into DC01.CARD_WASTEBOOK(LASTUPDATEDATE,SEQNO,TRANSSEQNUMBER,T
RANSDATE,SETTLEMENTDATE,MFTRANSBRANCHID,TRANSBRANCHID,MFACCOUNTBRANCH
ID,ACCOUNTBRANCHID,CLERKID,TRANSCODE,CARDID,TRANSBRIEFCODE,ACCOUNTID,
INTERNALACCOUNTID,ACCOUNTSEQNO,CASHFLAG,DCFLAG,TRANSAMOUNT,REVERSEFLA
G,REVERSETRANSSEQNUMBER,TRANSCHANNELTYPE,TRANSCHANNELNUMBER,MERCHANTI
D,COMMISSIONCHARGEAMOUNT1,COMMISSIONCHARGEAMOUNT2) select
TO_CHAR(TO_DATE('20120323','YYYYMMDD'),'YYYY/MM/DD'),FN_GETSEQNO_CARD
_WASTEBOOK@DC01(),A.TRANSSEQNUMBER,A.TRANSDATE,A.SETTLEMENTDATE,A.MFT
RANSBRANCHID,'',A.ACCOUNTORGID,'',A.CLERKID,A.TRANSCODE,A.CARDID,A.TR
ANSBRIEFCODE,A.ACCOUNTID,A.INTERNALACCOUNTID,A.ACCOUNTSEQNO,A.CASHFLA
G,A.DCFLAG,A.TRANSAMOUNT,A.REVERSEFLAG,A.REVERSETRANSSEQNUMBER,A.TRAN
SCHANNELTYPE,A.TRANSCHANNELNUMBER,A.MERCHANTNUMBER,A.COMMISSIONCHARGE
AMOUNT1,A.COMMISSIONCHARGEAMOUNT2 from DC01.CARDWASTEBOOK A where
TRANSDATE>='2012/03/23'
Rationale
The SQL spent only 0% of its database time on CPU, I/O and Cluster
waits. Therefore, the SQL Tuning Advisor is not applicable in this case.
Look at performance data for the SQL to find potential improvements.
Rationale
Database time for this SQL was divided as follows: 0% for SQL execution,
0% for parsing, 100% for PL/SQL execution and 0% for Java execution.
Rationale
SQL statement with SQL_ID "63uqvkrdrrqb1" was executed 1 times and had
an average elapsed time of 72 seconds.
Rationale
Waiting for event "SQL*Net message from dblink" in wait class "Network"
accounted for 100% of the database time spent in processing the SQL
statement with SQL_ID "63uqvkrdrrqb1".
Finding 2: "User I/O" wait Class
Impact is .08 active sessions, 9.42% of total activity.
-------------------------------------------------------
Wait class "User I/O" was consuming significant database time.
The throughput of the I/O subsystem was not significantly lower than expected.
The Oracle instance memory (SGA and PGA) was adequately sized. |
|