Oracle Enterprise Manager 12c Grid Control, Jump into SPA- Part I

A SPA is a location where mineral-rich spring water (and sometimes sea water) is used to give medicinal baths.

Meaning Courtesy - Wiki

How would you feel If you were able to  diagnose database performance issues sitting in a SPA?

Interesting !!

Reading my below blogposts would give you the similar feeling when you want to encounter the performance issues in your production database environments.

• SPA stands for "SQL Performance Analyzer", Which perfectly inteegrates with our existing utilities like AWR, ADDM & SPM etc to provide a very easy manageable interface to solve the performance issues.
• SPA uses the method of FIND-VALIDATE-FIX 
• SPA is only available from OEM 12c (12.1.3) version. 

Oracle Enterprise Manager 12c Grid Control, Jump into SPA- Part I
Oracle Enterprise Manager 12c Grid Control, Jump into SPA- Part II
Oracle Enterprise Manager 12c Grid Control, Jump into SPA- Part III

Part-I will show on how to install the ASH Analytics plugin (Because in my case ASH Analytics Plugin was not installed in my target database), Which is the major instrument we will be using for diagnosing the performance issues effectively.

1) Login to the OEM 12c Grid Control - Console

2) Select our target database, Say "PRODDB", Just follow the orange boxes in the below screenshots.

3) You will be able to all the targets we already have added, you can just type your target name(say PRODDB) and click on the -> arrow next to it.

4) Now the searched target database is listed as shown below.

5) Click on our target database PRODDB and we will be redirected to the database home screen as shown below.

6) Click on Performance -> ASH Analytics.

7) If we had already installed the ASH analytics plugin, we would land directly into the ASH Analytics page, In our case, we are going to install the plugin for the first time in our PRODDB target.

8) Make a note of all the orange boxes in the below screenshot, You need to have a user with SYSDBA privilege or the password of DBSNMP schema to have the installation done.

9) Our connection test to target database as SYSTEM user was successful. After pressing the Install button installation starts.

10) Once the installation completes, Watch for the success message as shown below. Click on the Log Report to see what was installed in our target database.

11) Log Report can be checked for any errors. (Just for safer side, I did, because, I am  trying this for the first time)

12) Now, again navigate to the PRODDB database homepage, click on performance -> ASH Analytics again.

13) Perfect, Congratulations!!!!

You have successfully installed the ASH analytics plugin in your target database.

Below is the snippet of the O/P log for the ASH Analytics plugin installation.

Output Log

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/prvs_awr_data.sql

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/prvt_awr_data.sql

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/prvs_awr_data_cp.sql

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/prvt_awr_data_cp.sql

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/dbms_compare_period.sql

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/prvt_compare_period.sql

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/eaddm_pkgdef.sql

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/eaddm_pkgbody.sql

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/ashviewer_pkgdefs.sql

Package deployment driver file is at /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/ashviewer_pkgbodys.sql

Instantiated JDBC Engine with connect string erpora1s:1521:devl1

You can view the output of this execution at the following location /tmp/DB_Deploy_201503060811.log

Altering session to set schema to DBSNMP

Currently executing null :: ALTER SESSION SET CURRENT_SCHEMA = DBSNMP

Altered session to set schema to DBSNMP

Executing /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/prvs_awr_data.sql

Currently executing /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/prvs_awr_data.sql :: DECLARE

  obj_not_found EXCEPTION;

  PRAGMA EXCEPTION_INIT(obj_not_found, -4043);

BEGIN 

  BEGIN

    execute immediate 'DROP PACKAGE prvt_awr_data';

    EXCEPTION WHEN obj_not_found THEN NULL;   

  END;

  BEGIN

    execute immediate 'DROP TYPE prvt_awr_period';

    EXCEPTION WHEN obj_not_found THEN NULL;

  END;

  BEGIN

    execute immediate 'DROP TYPE prvt_awr_inst_meta_tab';

    EXCEPTION WHEN obj_not_found THEN NULL;

  END;

  BEGIN

    execute immediate 'DROP TYPE prvt_awr_inst_meta';

    EXCEPTION WHEN obj_not_found THEN NULL;

  END;

END;

Currently executing /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/prvs_awr_data.sql :: CREATE TYPE prvt_awr_inst_meta AUTHID CURRENT_USER

AS OBJECT

(

  m_instance_number   NUMBER,

  m_not_bounced       NUMBER,

  m_no_awr_errors     NUMBER,

  m_is_requested      NUMBER,

  m_name              VARCHAR2(16),

  m_host_name         VARCHAR2(64),

  m_platform_name     VARCHAR2(101),

  m_startup_time      TIMESTAMP(3),

  m_begin_time        DATE,

  m_end_time          DATE,

  m_db_name           VARCHAR2(9),

  m_db_version        VARCHAR2(17),

  m_db_time           NUMBER,

  m_ash_row_cnt       NUMBER,

  MEMBER FUNCTION to_xml RETURN XMLTYPE

) FINAL INSTANTIABLE;

..

..

..

..

..

..

..

..

..

..

    -- to ''

    IF (instr(i_report_level, item_list_tag, 1) = 1) THEN

      -- ajust the report level

      -- FIXME: this is a temp hack for now. This code should go

      -- to build_context 

      i_report_level := substr(report_level, length(item_list_tag) + 1);

      -- we need to get xml fragment for all top dimensions. So set

      -- appropriate level

      top_dim_level := '{treemap}{treemap}' ||

                       '{treemap}{treemap}' ||

                       '{treemap}{treemap}' ||

                       '{treemap}{treemap}';

      -- get the top_dimensions xml report fragment

      i_xml_build_report_fragment( 

          dbid

        , inst_id  

        , begin_time

        , end_time          

        , top_dim_level        

        , filter_list

        , true         

        , xml_top_dims);

    END IF;

    -- get the xml fragment for rest of dimension details

    -- requested by the caller if any

    IF (i_report_level IS NOT NULL AND length(i_report_level) > 0) THEN

      -- get the xml report fragment

      i_xml_build_report_fragment( 

          dbid

        , inst_id  

        , begin_time

        , end_time          

        , i_report_level        

        , filter_list

        , false 

        , xml_requested_dims);

    END IF;

    -- now we are ready to produce the report xml

    SELECT xmlelement("report",

                      xml_inputs,

                      xmlelement("body",

                                 xml_top_dims,

                                 xml_requested_dims)).getclobval()

    INTO out_xml

    FROM dual;

  

  END runreport;

FUNCTION getPackageVersion RETURN INT IS

BEGIN

  -- This is the first version of this package

  return 1;

END getPackageVersion;

END ashviewer;

Executed /u01/app/oracle/middleware3/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0/sql/db/latest/instance/ashviewer_pkgbodys.sql

Driver SQL Script executed normally.

Success

Do not forget to continue reading Part -II & Part -III of this blogpost.

HAPPY LEARNING!