5 Replies Latest reply on Apr 11, 2018 8:40 AM by Beauty_and_dBest

    EBS 12.1.3 EM Database monitoring

    Beauty_and_dBest

      EBS 12.1.3

      11gR2

      OL6

      EM

       

       

      We are monitoring our EBS database usage using OEM and

      We found long runnIng sql in the top sql resource program  list.

      How do we know who is running this program?

      Or how do we tune it?

       

      Kind regards,

      jc

        • 1. Re: EBS 12.1.3 EM Database monitoring
          Eric Z-Oracle

          Hi JC,

           

          AWR report is needed to analyze the cause of low performance, as you can see there is a button on the screen.

          You may check this out.

           

              Performance Diagnosis with Automatic Workload Repository (AWR) (Doc ID 1674086.1)

           

          And...we don't run AWR for too long, typically one hour is good.

           

          Cheers!

          Eric

          • 2. Re: EBS 12.1.3 EM Database monitoring
            Beauty_and_dBest

            Thanks Eric,

             

            I been running this AWR many times, but does not know how to interpret it.

            I always look for Hi-cpu, hi-memory, hi-I/O users but still they are even belongs to the Oracle programs, and can not do about it.

            • 3. Re: EBS 12.1.3 EM Database monitoring
              Eric Z-Oracle

              Hi JC,

               

              AWR focuses on database and SQL statistics during a specific period of time in the past, if you are trying to identify a concurrent request (of course you will get user info in the records), you need to know the database session.

              Try the ASH report - Active Session History - the output contains top session statistics, in which you will find sid and serial# to be linked to concurrent requests.

              You can find many useful queries to achieve it by searching, for example "find concurrent program session id".

               

              OR,

               

              You can identify particular problematic SQLs and use the SQL ID and SQL address to locate associated records in the v$sessions table, then join the concurrent requests tables to find particular requests, then particular requesters (users).

              Following tables may be of good use.

              apps.fnd_concurrent_requests

              apps.fnd_concurrent_processes

              gv$process

              gv$session

              v$sqlarea

              v$sql

              v$session

               

              For the AWR interpretation, please review the note that I provided.

              There are also other notes that may cover slightly different details regarding AWR interpretation.

               

              Collecting Diagnostic Data for Performance Issues in Oracle E-Business Suite (Doc ID 1121043.1)

              How to Generate an AWR Report and Create Baselines (Doc ID 748642.1)

              How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)

               

              Hope it helps.

               

              Cheers!

              Eric

              • 4. Re: EBS 12.1.3 EM Database monitoring
                VishnuVinnakota

                HI,

                 

                   Its pretty simple. Lets start with SQL Tuning Advisor.

                 

                   In EM Go to: Performance --> Advisors Home --> SQL Advisors --> SQL Tuning Advisor

                 

                   I can see SQL ID in your screenshot. Provide the SQL ID.

                • Once you run advisor, you can see recommendations
                • Analyze recommendations
                • Don't apply the recommendations unless you review

                 

                  You can alternatively do all this from backend as well - Replace SQL ID with  your ID:

                 

                1. Create Task

                DECLARE
                  l_sql_tune_task_id  VARCHAR2(100);
                BEGIN
                  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                                          sql_id      => '87s8z2zzpsg88',
                                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                                          time_limit  => 500,
                                          task_name   => '87s8z2zzpsg88_tuning_task11',
                                          description => 'Tuning task1 for statement 87s8z2zzpsg88');
                  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
                END;
                /

                2. Execute Tuning Task

                EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '87s8z2zzpsg88_tuning_task11');

                3. Get recommendations/report



                set long 65536
                set longchunksize 65536
                set linesize 100
                select dbms_sqltune.report_tuning_task('87s8z2zzpsg88_tuning_task11') from dual;

                4. Execute only if satisfied by analysis and result

                exec dbms_sqltune.accept_sql_profile(task_name => '87s8z2zzpsg88_tuning_task11', task_owner => 'SYS', replace => TRUE);

                5. Drop Tuning Task Later

                EXEC DBMS_SQLTUNE.drop_tuning_task(task_name => '87s8z2zzpsg88_tuning_task11');

                 

                Thanks,

                Vishnu