一个sql导致oracle接近崩溃

taoy_2008

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.