This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Sep 1, 2013 8:20 PM by sb92075 RSS

Slow Performance

yxes2013 Newbie
Currently Being Moderated

Hi all,

 

11.2.0.1

Aix 6.1 5L

 

We have 2 database on the same server named PROD, and BATCH.

PROD is used in online transaction processing. BATCH is for batch.

At midnight BATCH is processing and EMP table which is dblink to PROD.

Previously the performance was ok and took only 1 hr. But today it took more than 5 hrs.

I can see in the addmrpt has network wait event. Is guess there is issue with the dblink?

What could be the problem caused by the dblink?

 

I run the addmrpt and this is the  output:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 

 

 

          Findings and Recommendations

          ----------------------------

 

 

Finding 1: Virtual Memory Paging

Impact is 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 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.

 

 

 

 

Finding 2: Unusual "Network" Wait Event

Impact is 1 active sessions, 100% of total activity.

----------------------------------------------------

Wait event "SQL*Net message from dblink" in wait class "Network" was consuming

significant database time.

 

 

   Recommendation 1: Application Analysis

   Estimated benefit is 1 active sessions, 100% of total activity.

   ---------------------------------------------------------------

   Action

      Investigate the cause for high "SQL*Net message from dblink" waits.

      Refer to Oracle's "Database Reference" for the description of this wait

      event.

   Action

      Look at the "Top SQL Statements" finding for SQL statements consuming

      significant time on the "SQL*Net message from dblink" wait event. For

      example, the SELECT statement with SQL_ID "1qc32qbzva4tk" is responsible

      for 99% of these waits.

 

 

   Recommendation 2: Application Analysis

   Estimated benefit is 1 active sessions, 100% of total activity.

   ---------------------------------------------------------------

   Action

      Investigate the cause for high "SQL*Net message from dblink" waits in

      Module "prg01_20@abcdbprod (TNS V1-V3)".

 

 

   Recommendation 3: Application Analysis

   Estimated benefit is 1 active sessions, 100% of total activity.

   ---------------------------------------------------------------

   Action

      Investigate the cause for high "SQL*Net message from dblink" waits in

      Service "SYS$USERS".

 

 

   Recommendation 4: Application Analysis

   Estimated benefit is 1 active sessions, 100% of total activity.

   ---------------------------------------------------------------

   Action

      Investigate the cause for high "SQL*Net message from dblink" waits with

      P1 ("driver id") value "1413697536" and P2 ("#bytes") value "1".

 

 

   Symptoms That Led to the Finding:

   ---------------------------------

      Wait class "Network" was consuming significant database time.

      Impact is 1 active sessions, 100% of total activity.

 

 

 

 

Finding 3: Top SQL Statements

Impact is 1 active sessions, 99.72% 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 1 active sessions, 99.72% of total activity.

   -----------------------------------------------------------------

   Action

      Investigate the SELECT statement with SQL_ID "1qc32qbzva4tk" 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 1qc32qbzva4tk.

         select respcode ,NVL(shcerror,9999) ,NVL(acctnum,'                |')

         from EMP where ((((((((settlement_date=TO_DATE(:b0,'MM-DD-YYYY')

         and acquirer=:b1) and termid=:b2) and origtrace=:b3) and msgtype=210)

         and pcode=:b4) and local_time<=:b5) and pan=:b6) and

         terminal_trace=NVL(:b7,0))

   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: 100% for SQL

      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

      execution.

   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 "1qc32qbzva4tk".

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

Thanks,

 

zxy

  • 1. Re: Slow Performance
    sb92075 Guru
    Currently Being Moderated

    something changed & only YOU can determine exactly what changed.

     

    is DBLINK the victim or the culprit? Again only you can solve this mystery since only you have access to either or both systems.

     

    Start troubleshooting the symptoms.

  • 2. Re: Slow Performance
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    The "problem" is either

    a) the query on the PROD database

    b) network throughput between the two

     

    Without information on the PROD database activity and statistics on the network, anything would be speculation.

     

     

    Hemant K Chitale


  • 3. Re: Slow Performance
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Hemant Sir,

     

    This is the counterpart addmrpt of PROD, Is there a hint why network waits on it?  Thanks

     

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

     

     

     

     

              Findings and Recommendations

              ----------------------------

     

     

    Finding 1: Virtual Memory Paging

    Impact is 1.04 active sessions, 100% of total activity.

    -------------------------------------------------------

    Significant virtual memory paging was detected on the host operating system.

     

     

       Recommendation 1: Host Configuration

       Estimated benefit is 1.04 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.

     

     

     

     

    Finding 2: Top SQL Statements

    Impact is 1.02 active sessions, 98.41% 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 .99 active sessions, 94.96% of total activity.

       -------------------------------------------------------------------

       Action

          Run SQL Tuning Advisor on the SELECT statement with SQL_ID

          "38frh2ms0wmd6".

          Related Object

             SQL statement with SQL_ID 38frh2ms0wmd6.

             SELECT "A1"."RESPCODE",NVL("A1"."SHCERROR",9999),NVL("A1"."ACCTNUM",'

             |') FROM "ADMIN"."EMP_OLD" "A1" WHERE

             "A1"."SETTLEMENT_DATE"=TO_DATE(:B0,'MM-DD-YYYY') AND

             "A1"."ACQUIRER"=:B1 AND "A1"."TERMID"=:B2 AND "A1"."ORIGTRACE"=:B3

             AND "A1"."MSGTYPE"=210 AND "A1"."PCODE"=:B4 AND

             "A1"."LOCAL_TIME"<=:B5 AND "A1"."PAN"=:B6 AND

             "A1"."TERMINAL_TRACE"=NVL(:B7,0)

       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 "38frh2ms0wmd6" was executed 1084 times and

          had an average elapsed time of 3.3 seconds.

     

     

       Recommendation 2: SQL Tuning

       Estimated benefit is .04 active sessions, 3.45% of total activity.

       ------------------------------------------------------------------

       Action

          Run SQL Tuning Advisor on the INSERT statement with SQL_ID

          "f7rwc892a8hh0".

          Related Object

             SQL statement with SQL_ID f7rwc892a8hh0.

             INSERT INTO EMP (msgtype,.....VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16

             ,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,

             :34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,:

             51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:6

             8,:69,:70,:71,:72,:73,:74,:75,:76,:77,:78,:79,:80,:81,:82,:83,:84,:85

             ,:86,:87,:88,:89,:90,:91,:92,:93,:94,:95,:96,:97,:98,:99,:100,:101,:1

             02,:103,:104,:105,:106,:107,:108,:109,:110,:111,:112,:113,:114,:115,:

             116,:117,:118,:119,:120,:121,:122,:123,:124,:125,:126,:127,:128)

       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 "f7rwc892a8hh0" was executed 13442 times and

          had an average elapsed time of 0.0046 seconds.

     

     

     

     

    Finding 3: Top Segments by "User I/O" and "Cluster"

    Impact is .14 active sessions, 13.38% of total activity.

    --------------------------------------------------------

    Individual database segments responsible for significant "User I/O" and

    "Cluster" waits were found.

     

     

       Recommendation 1: Segment Tuning

       Estimated benefit is .14 active sessions, 13.38% of total activity.

       -------------------------------------------------------------------

       Action

          Run "Segment Advisor" on TABLE "ADMIN.EMP_OLD" with object ID

          128993.

          Related Object

             Database object with ID 128993.

       Action

          Investigate application logic involving I/O on TABLE

          "ADMIN.EMP_OLD" with object ID 128993.

          Related Object

             Database object with ID 128993.

       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 "38frh2ms0wmd6" is responsible for 100% of "User I/O" and

          "Cluster" waits for this segment.

       Rationale

          The I/O usage statistics for the object are: 0 full object scans,

          242643475 physical reads, 0 physical writes and 0 direct reads.

     

     

       Symptoms That Led to the Finding:

       ---------------------------------

          Wait class "User I/O" was consuming significant database time.

          Impact is .18 active sessions, 17.16% of total activity.

     

     

     

     

     

     

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  • 4. Re: Slow Performance
    sb92075 Guru
    Currently Being Moderated

    >SQL statement with SQL_ID "38frh2ms0wmd6" was executed 1084 times and

    >     had an average elapsed time of 3.3 seconds.

     

    1084 * 3.3 = 59.62 minutes

    so about 1 hour was spent doing single SQL above

  • 5. Re: Slow Performance
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Sb,

     

    But this was already running good previously. Are there other hint why it got slow just today?

  • 6. Re: Slow Performance
    sb92075 Guru
    Currently Being Moderated

    yxes2013 wrote:

     

    Thanks Sb,

     

    But this was already running good previously. Are there other hint why it got slow just today?

     

    You need to compare & contrast between slow run and normal run to determine what was the root cause.

  • 7. Re: Slow Performance
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Sb,

     

    I have awrrpt from yesterday same time run which good, and today's awrrpt which bad.

    How do I spot the changes?



    Thanks

  • 8. Re: Slow Performance
    sb92075 Guru
    Currently Being Moderated

    yxes2013 wrote:

     

    Thanks Sb,

     

    I have awrrpt from yesterday same time run which good, and today's awrrpt which bad.

    How do I spot the changes?

     

    open your eyes & engage your brain if you can not recognize any differences you are helpless & hopeless.

  • 9. Re: Slow Performance
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    The only thing that the report tells us is :

          SQL statement with SQL_ID "38frh2ms0wmd6" was executed 1084 times and

          had an average elapsed time of 3.3 seconds.

     

    You need to figure out what has changed.  Is the number of executions changed ?  Is the elapsed time per execution changed ?

     

    BTW, if the job took 5 hours why are you looking at only 1 hour report ?

     

    Hemant K Chitale

  • 10. Re: Slow Performance
    yxes2013 Newbie
    Currently Being Moderated

    Thanks,

     

    I am just curious of this 1 hr report becuase it is the one that is being pointed out by addmrpt that is waiting for sqlnet?

     

    Finding 2: Unusual "Network" Wait Event

    Impact is 1 active sessions, 100% of total activity.

    ----------------------------------------------------

    Wait event "SQL*Net message from dblink" in wait class "Network" was consuming

    significant database time.

     

     

       Recommendation 1: Application Analysis

       Estimated benefit is 1 active sessions, 100% of total activity.

       ---------------------------------------------------------------

       Action

          Investigate the cause for high "SQL*Net message from dblink" waits.

          Refer to Oracle's "Database Reference" for the description of this wait

          event.

       Action

          Look at the "Top SQL Statements" finding for SQL statements consuming

          significant time on the "SQL*Net message from dblink" wait event. For

          example, the SELECT statement with SQL_ID "1qc32qbzva4tk" is responsible

          for 99% of these waits.

     

    How do you resolve sqlnet waits? Do I need to restart listener.ora?

     

    Thanks...

  • 11. Re: Slow Performance
    EdStevens Guru
    Currently Being Moderated

    yxes2013 wrote:

    <snip>

    How do you resolve sqlnet waits? Do I need to restart listener.ora?

     

    Thanks...

     

    <sigh!!>

     

     

    The listener is not involved.  Once the connection is established, you can drop the listener completely and existing connections are entirely unaffected.

  • 12. Re: Slow Performance
    nagulan.selvakumar Journeyer
    Currently Being Moderated

    Hi,

     

    Check the underlying indexes of the tables involved in the query and gather stats if they are old. Did the tables had a mass delete/insert?

    Run SQL Tuning Advisor on the SELECT statement with SQL_ID "38frh2ms0wmd6" as per the recommendation. This will give you some clue.

     

    Thank you!!

  • 13. Re: Slow Performance
    yxes2013 Newbie
    Currently Being Moderated

    So why is the dblink involved

  • 14. Re: Slow Performance
    sb92075 Guru
    Currently Being Moderated

    yxes2013 wrote:

     

    So why is the dblink involved

    because data on remote DB is being accessed

1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points