Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :- ajith.narayanan@infrastack-labs.in

Sunday, March 8, 2015

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

Hi Friends,

This is the third and final part of this blogpost series, I would strongly recommend to go through the Part-I & Part-II of this series if you have directly landed to this post.

In Part-II, we identified the SQL statements which were the main contributors for USER I/O. In this post we will learn how to reduce the DB_Time considerably. In short, let try to explore the opportunities to reduce the 

Where,

DB_Time = CPU Time + Non-Idle Wait time ( If you are new to these matrices, I would recommend you to get quick web trainings from Craig Shallahamer from OraPub )

We will use SQL Tuning Advisor to find out for some advice. 

1) Below screenshot is again of the ASH Analytics home screen for my database. 


As we can see there are a couple of SQL Statements that cause the major part of the activity. You should see top SQL id.


Select top three SQL's, 


Please Note: Ignore below screenshot which has top five selected and in the following screenshots you will find some other SQL-id's, The reason is that my main main is to show you the way you can use OEM to diaognise performance problems. So, it takes lot of time & effort  to get these screenshots created arranged and post in self explanatory format.

The number of SQLs to tune is different, because by the time i start creating remaining screenshots, the short spikes had gone out of the ASH window and So will be showing the navigation with any top 3 SQL's available for me from then. d


Click Tune "Schedule SQL Tuning Advisor"



2)  Review job detail and for easier identification you can change the name too.

Click Submit


3) The tuning task will run for 5 to 10 minutes. You can see the progress and the cumulative benefits on the screen.



4) As we can see Tuning advisor has given tuning recommendations for us in the form of SQL profiles & indexes that we should investigate further.
 

Click on Show all results.


5) This constitutes the “Fix” or the solution for the problem identified as part of the Find-Fix-Validate performance methodology.

SQL Tuning Advisor has now given us advice to create SQL Profiles & create indexes. We will now implement the advice that will give us most benefits. But since we are not allowed to jeopardize current performance then we need to validate that our advices are risk free to implement. This will be done with SPA Quick Check.

Note:- The largest benefit comes by creating SQL Profiles. New indexes have almost as high a benefit. But indexes will definitely cause performance penalty during insert and updates and can also cause other SQL to regress.

Let’s start with SQL profiles. Will we be able to gain what SQL Tuning Advisor have predicted?
 

Click on “Validate All Profiles with SPA”


6) Enterprise Manager has now created a SQL Performance Analyzer Task for validating the performance.

Click on the SPA Task



7) The SPA Task will run for about 1 minute when “Last Run Status” is “completed click on the Name.




8) There are four trials executed, first and second are only comparing execution plans, third and forth are full executions of regressed SQL statements (subset of the workload)
 

Let’s see the comparison result from the third and fourth trial.
 

Click on the glasses for the second comparison report.




9) As we can see the performance improvement is not in line with what the SQL Tuning Advisor recommended. 

The benefit by SQL Profile is just 4% as compared to 99% Per SQL Tuning Advisor's recommendations,Now we have validated the fix in real time in the same database, So if the Validation was in line with SQL Tuning Advisor's recommendations we can blindly implement the SQL profiles.

As I said in the beginning my aim is to show you the complete navigation and options to use for diagonizing the performance issues and not the actual results because of 2 reasons.

**I am not running this on a prod db, 
** and while creating screenshots as I navigate, the ASH analytics window keeps moving, and even the window I select may just have a spike, which is not a performance issue at all.

However, let’s implement the SQL Profiles. (Which I should not do according to my validation result  :)   )

Click on the breadcrumb for Advisor Central.



10 ) Click on "Advisor Central" breadcrum & In the next screen, Identify your tuning task and click on the name

Click on SQL Profile




11) Click on Implement All SQL Profiles


12) Check “Implement the new profile(s) with forced matching” and click Yes.

13) We have now implemented our new profiles. Let’s see if this had any impact on the workload.
Go to Performance -> ASH Analytics to see if the graph has come down, which means the performance issue was identified-validated & fixed on the fly.



14) Similarly, below screenshots give the navigation of how to go by the Index recommendations.











Hope you have gone through all the three parts of this blogpost, if not please read Part-IPart-II without fail.

HAPPY LEARNING!




Saturday, March 7, 2015

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

Hi Friends,

This is the second part of the blogpost series, I would strongly recommend to go through the Part-I of this series if you have directly landed to this post.

In this Part-II, I will be explaining on how to use the ASH Analytics + SQL Performance Analyzer(SPA) together and quickly arrive at the story that can be used to explain your management about the database performance issues.

Not restricting us with only stories, We can further give birth to validated solutions/fix for the performance issue....Yes I said...."VALIDATED SOLUTION/FIX". This will be in Part-III

Now let's see how to quickly identify the performance issues and drill down in few seconds & few navigation. Just follow the screenshots and the orange boxes.



1) You would have noticed the same screenshot was in the tail end of Part-I 

Just proving that this is the sequential post ;)

This is the ASH Analytics screen, and I will be concentrating on the highlighted portion of this ASH graph.

Ideally you should be hihglighting on some flat plateaus instead of spikes that lasts for short duration. I am highlighting this, because, while creating the screenshots, I was not patient enough to wait for a actual performance issue to come up with graphs at top. But yes, you can try on your own on the performance issues to see if it really works for you.



2) Click on the "Load Map" and the "Wait Class, Wait Event" on the drop down, You can see during the highlighted period, "db file sequential read" is the top wait event, For the moment let's not look at What is consuming CPU (We don't have CPU issue's) nor we are interested about idle wait events(So ignore the big GREEN box), We are worried only about the non-idle wait events always.



3) Click on the BROWN box, to drill down to the "Modules, Action" that is contributing to the USER I/O and doing db sequential reads.


4) Ignore Other's BOX, Concentrate on the SYSADMIN module click on the box.

Note:- Other's BOX constitutes very badly or zero instrumented modules, So no much details.



5) Drill down to the SYSADMIN module (Look at the filter marked in black box).


6) From the drop down, Select "Wait Class, SQL_ID" to get the SQL_ID's from SYSADMIN mudule that contributed to "db sequential read" wait event.


7) Wow, now you have the WAIT EVENT -> MODULE/ACTION -> SQL_ID relationship chain and all the sql_id's that is contributing to the performance issues, Pick the top SQL_ID's with bigger boxes for further analysis and fix.


8) By the time I reached this screen, You can see my actual highlighted section came to the center.
and notice the top sql_id's , those were indeed in the the LOAD MAP findings we did in previous steps.


This is all about using ASH Analytics + SPA to efficiently find the underlying SQL_IDs that contributed to the performance issue.

Continue reading the upcoming Part-III


HAPPY LEARNING!

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!