1 2 3 Previous Next 31 Replies Latest reply: Sep 1, 2013 10:20 PM by sb92075 RSS

    Slow Performance

    yxes2013

      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

          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

            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

              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

                >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

                  Thanks Sb,

                   

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

                  • 6. Re: Slow Performance
                    sb92075

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  So why is the dblink involved

                                  • 14. Re: Slow Performance
                                    sb92075

                                    yxes2013 wrote:

                                     

                                    So why is the dblink involved

                                    because data on remote DB is being accessed

                                    1 2 3 Previous Next