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

    EBS 12.1.3 EM Database monitoring


      EBS 12.1.3






      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,


        • 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.




          • 2. Re: EBS 12.1.3 EM Database monitoring

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




              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.









              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.




              • 4. Re: EBS 12.1.3 EM Database monitoring



                   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

                  l_sql_tune_task_id  VARCHAR2(100);
                  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);

                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');