Blog Feed

Understanding Oracle SQL Plan Management SPM – Part 1

This time I want to talk about SQL Plan Management, explain how this feature works and how to make the best use of it. As is a very extensive topic I will split it in three posts.
Introduction
The Oracle Cost Based Optimizer (CBO) introduced in Oracle version 7. It determines the most efficient way to execute SQL statements after considering several factors such as database initialization parameters, optimizer statistics and many others like bind peeking, cardinality feedback, etc.

The CBO’s goal is to produce an optimal execution plan for the SQL statements executed in the database system. This is the reason why we want the CBO to be flexible enough to produce the best execution plans for the SQL statements. We must not forget that DB systems are dynamic enough to have data distribution changes, new Indexes may be added and initialization parameters could be modified, leading the CBO to produce multiple execution plans for the same SQL statement. This is an expected behavior of the CBO and we should be glad that is smart enough to produce optimal plans most of the times during the hard parsing, however sometimes it may also produce sub-optimal plans under special circumstances.

Here’s where plan stability tools come on the scene. Tools like hints, outlines, SQL Plan Management and custom SQL Profiles help the DBA and developers to allow only optimal plans be executed, hence avoiding severe performance problems within the database.
SQL Plan Management (SPM) is a new feature of Oracle 11g. SPM provides a framework for plan stability and control by ensuring that only selected plans are executed. If new plans are created by the CBO, they will not be executed until they are verified by the database or by the administrator and marked as accepted.

SPM controls the execution plans by using three control flags. The “ENABLED” flag that accepts two values YES & NO controls if the plan is available or not for the CBO to be considered. If the “ACCEPTED” flag is set to YES and “ENABLED” is set to YES, then the CBO will execute the plan. If “ACCEPTED” is set to NO, the plan should be verified by the database system or the administrator to check if it has comparable or better performance than the current plan. This process of making not “ACCEPTED” plans into “ACCEPTED” is called plan evolution. The last control flag name is “FIXED” and can only be set to YES if the plan is “ENABLED” and “ACCEPTED”, if set to YES plans have priority over “ACCEPTED” plans just because they are not expected to change.

SPM uses a mechanism called SQL plan baseline (baseline). Baseline is a group or set of “ACCEPTED” plans the CBO is allowed to use for a particular SQL statement. These baselines and the not “ACCEPTED” plans are stored into the SQL plan history. All baselines and the plan history are logically stored into the SQL management base (SMB) in the data dictionary. The SQL management base stores the statement log, plan histories, SQL plan baselines, and SQL profiles. Oracle provides information about SQL baselines in the DBA_SQL_PLAN_BASELINES and v$SQL system views. Database administrators can query information about what SQL baselines exists, their status and origin.

More information about DBA_SQL_PLAN_BASELINES can be found here https://docs.oracle.com/database/121/REFRN/refrn23714.htm#REFRN23714.

Oracle matches SQL statements with SQL baselines by using signatures. SQL signature is a unique identifier created from the normalized SQL text, uncased and whitespaces removed. SQL signatures ensure that the same SQL statement is always having the same SQL signature independent of the upper/lower case or spaces. SQL signatures are stored into the SQL management log in the SMB. If a SQL statement has its signature stored in the SMB, means to be a repeatable statement and a baseline will be created during the next execution.

SQL baselines also have three status flags. The first flag “REPRODUCED” is automatically set to YES when the CBO is able to reproduce the plan for the given SQL statement and NO when not possible, like when an Index is dropped. The second flag “AUTOPURGE” is user modifiable, if set to YES the plan will be purged when not used and reaches the SPM plan retention limit parameter. The last status flag “REJECTED” is set to YES when “ACCEPTED” is set to NO and the plan was verified (has LAST_VERIFIED), or “ENABLED” is set to no in 11gR2 and 12c versions.

SQL plan management is controlled by two initialization parameters. When the optimizer_use_sql_plan_baselines parameter is set to TRUE (default), the CBO will make use of the baselines created and stored in the SMB. The second parameter optimizer_capture_sql_plan_baselines is set to FALSE by default and controls if the database will automatically capture the SQL signature in the SQL management log and automatically create a SQL baseline on the second execution of the SQL statement. As a best practice and personal recommendation, don’t set optimizer_capture_sql_plan_baselines to TRUE. If set to TRUE, any baseline that is automatically created is also set to “ACCEPTED”, this means that the second execution of a SQL statement will always be used by the CBO even if there’s a better execution plan available. 

In the next post I’m going to cover how to capture and evolve SQL baselines in 11g and 12c version, stay tuned!

Thanks,

Alfredo

Why you should attend to IOUG Collaborate 2015?

Today I want to share a personal story about IOUG’s Collaborate, a great conference that supports products from Engineered Systems, Oracle Databases to Fusion Middleware and Siebel. Last year was my first time as a speaker and was one of kind experience as I was able to share my knowledge in Oracle Database performance tuning with professionals that work on a daily basis with Oracle technologies.

This is what makes Collaborate great, professionals sharing their experiences to other professionals. You can be sure that the guys who are speaking at Collaborate really know what they are talking about, that’s a guarantee. Apart of education, networking is also great at Collaborate, so you can meet IT professionals, Oracle Product Managers and technology gurus (you may already seen their blogs).

This year Collaborate will be greater than ever, just imagine to learn from Tom Kyte, Craig Shallahamer and Kellyn Pot’Vin-Gorman, you don’t want to miss the opportunity!

If you want to be part of this big IT event, want to learn great stuff that will help you with your daily work and want to network with great people, you should register right away!

More information available at http://collaborate.ioug.org/

BTW. If you attend the Collaborate 2015, be sure to stop by my sessions about Oracle DB Performance and Oracle Enterprise Manager

Oracle Enterprise Manager at Collaborate 2015 https://lnkd.in/e3G6ZzK
Thanks,

Alfredo

Oracle Enterprise Manager Security– Disable SYSMAN access

In Enterprise Manager 12c SYSMAN user is the schema owner and as a best practice all the users should log in using their own individual accounts. To enforce this you can prevent SYSMAN from login into the console and/or emcli by setting SYSTEM_USER to -1 in the MGMT_CREATED_USERS table:
UPDATE MGMT_CREATED_USERS
SET SYSTEM_USER=’-1’
WHERE user_name=’SYSMAN’
To re-enable the access just set it to 1.
UPDATE MGMT_CREATED_USERS
SET SYSTEM_USER=’1’
WHERE user_name=’SYSMAN’
Refer to Oracle Support’s note:
How To Disable SYSMAN & SYSTEM Users from Logging into Grid Console? (Doc ID 867360.1)
Thanks,

Alfredo

Oracle Enterprise Manager – Reducing the noise, Part 1

Enterprise Manager 12c is a great monitoring tool, with it you can monitor a wide range of target types from databases to middleware; although out-of-the-box metrics can suit your monitoring requirements they can generate a considerable amount of white noise. In order to reduce this noise first you have to identify which are the top alerts in your system; Cloud Control comes with several predefined reports that help you to dig into multiple areas of your system, there’s a report “20 Most Common Alerts” which shows you the incidence of common alerts.


In the picture above, you can clearly see that metric “Database Time Spent Waiting (%)” appears twice in my Top 3, let’s find out our metric setting for my DB targets; in order to do this we must go to a DB home page then Oracle Database -> Monitoring -> Metrics and Collection Settings.  

 
Wait a minute! Why I’m receiving alerts if there are no thresholds setup for any of those metrics?, this behavior is clearly explained in MOS note 1500074.1 about a default warning threshold of 30% inside the database configuration. Let’s take a look to dba_threshold to confirm.
set lines 300
column METRICS_NAME format a30
column WARNING_OPERATOR format a30
column WARNING_VALUE format a30
column CRITICAL_OPERATOR format a30
column CRITICAL_VALUE format a30
SELECT METRICS_NAME,WARNING_OPERATOR ,WARNING_VALUE,CRITICAL_OPERATOR ,CRITICAL_VALUE FROM DBA_THRESHOLDS;
METRICS_NAME                        WARNING_OPERATOR               WARNING_VALUE                  CRITICAL_OPERATOR              CRITICAL_VALUE
———————————– —————————— —————————— —————————— ——————————
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Blocked User Session Count          GT                             0                              NONE
Current Open Cursors Count          GT                             1200                           NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             50                             NONE
Database Time Spent Waiting (%)     GT                             50                             NONE
Logons Per Sec                      GE                             100                            NONE
Session Limit %                     GT                             90                             GT                             97
Tablespace Bytes Space Usage        DO NOT CHECK                   0                              DO_NOT_CHECK                   0
Tablespace Space Usage              GE                             85                             GE                             97
22 rows selected.
There you go!, all metrics for “Database Time Spent Waiting (%)” are set to 30% or 50% values, now the trick to disable these metrics is to set them to a different value like 99%; this will override the default value as follows:

  
Let’s look at the database setting again:
set lines 300
column METRICS_NAME format a30
column WARNING_OPERATOR format a30
column WARNING_VALUE format a30
column CRITICAL_OPERATOR format a30
column CRITICAL_VALUE format a30
METRICS_NAME                        WARNING_OPERATOR               WARNING_VALUE                  CRITICAL_OPERATOR              CRITICAL_VALUE
———————————– —————————— —————————— —————————— ——————————
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Blocked User Session Count          GT                             0                              NONE
Current Open Cursors Count          GT                             1200                           NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Logons Per Sec                      GE                             100                            NONE
Session Limit %                     GT                             90                             GT                             97
Tablespace Bytes Space Usage        DO NOT CHECK                   0                              DO_NOT_CHECK                   0
Tablespace Space Usage              GE                             85                             GE                             97
25 rows selected.
We successfully modified these metrics to a very high value; at this point you can decide to stay at 99% or you can remove that threshold in order to completely disable them.

Now let’s confirm those settings in the database:
set lines 300
column METRICS_NAME format a30
column WARNING_OPERATOR format a30
column WARNING_VALUE format a30
column CRITICAL_OPERATOR format a30
column CRITICAL_VALUE format a30
METRICS_NAME                        WARNING_OPERATOR               WARNING_VALUE                  CRITICAL_OPERATOR              CRITICAL_VALUE
———————————– —————————— —————————— —————————— ——————————
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Blocked User Session Count          GT                             0                              NONE
Current Open Cursors Count          GT                             1200                           NONE
Logons Per Sec                      GE                             100                            NONE
Session Limit %                     GT                             90                             GT                             97
Tablespace Bytes Space Usage        DO NOT CHECK                   0                              DO_NOT_CHECK                   0
Tablespace Space Usage              GE                             85                             GE                             97
14 rows selected.
The metrics are not there anymore and hopefully the alerts neither. This behavior is also noted for “Average Users Waiting Counts” metric, if you are receiving considerable white noise for this metric you can disable as well following the same procedure. A good practice is to create a Monitoring template to help you modify these thresholds for multiple targets at once.
Stay tuned for my next post about reducing OEM 12c noise.
Thanks,

Alfredo

Upgrade Oracle Enterprise Manager to 12.1.0.4

Enterprise Manager 12.1.0.4 was just released and now is time to test and document the process. The official documentation can be obtained from here (http://docs.oracle.com/cd/E24628_01/upgrade.121/e22625/upgrading_12101_PS1_gtgstrtd.htm) and this document cover the steps I followed and may not work on yours (I’m not responsible of any issues/problems caused to your system).
This document covers Oracle Enterprise Manager upgrade from 12.1.0.3 to 12.1.0.4 version in a Single-OMS configuration using one system upgrade method and is divided in 3 sections describing prerequisites, upgrade and post upgrade tasks.


Prerequisites


  • Download the software from (http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html) on your server and extract it to a stage directory.


mkdir /stage
unzip em12104_linux64_disk1.zip -d /stage
unzip em12104_linux64_disk2.zip -d /stage
unzip em12104_linux64_disk3.zip -d /stage


  • Ensure that you have a valid OMS (middleware home & inventory), Repository and Software Library backup.
  • If you have BI Publisher installed, then stop BIP server through WebLogic Admin Console.
  • Drop any snapshot created against Repository tables.


SQL> select master , log_table from all_mview_logs where log_owner=’SYSMAN’


no rows selected


  • Check and compile invalid objects in the Repository schema.


SQL> select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile;’ from all_objects
 2  where status=’INVALID’;


no rows selected


  • Execute privileges on DBMS_RANDOM are required during the upgrade, run the below commands to set them.


SQL> grant execute on DBMS_RANDOM to DBSNMP;
SQL> grant execute on DBMS_RANDOM to SYSMAN;
SQL> revoke execute on DBMS_RANDOM from PUBLIC;


  • Copy the emkey from OMS to the Management Repository.


$ emctl config emkey -copy_to_repos
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
The EMKey has been copied to the Management Repository. This operation will cause the EMKey to become unsecure.
After the required operation has been completed, secure the EMKey by running “emctl config emkey -remove_from_repos”.



  • Stop OMS, extended instances and both OMS and Repository agents.


$ emctl extended oms jvmd stop -all
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Please enter the SYSMAN password:
Stopping all Engines
{}
No engines found for this operation
Emctl Extended verb failed. Please check /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for more details


$ emctl extended oms adp stop -all
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Please enter the SYSMAN password:
Stopping all Engines
{}
No engines found for this operation
Emctl Extended verb failed. Please check /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for more details


$ emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Stopping WebTier…
WebTier Successfully Stopped
Stopping Oracle Management Server…
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down


$ ./emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Stopping agent ….. stopped.


Upgrade

  • Run the runInstaller executable from the folder where the software was extracted.


$ cd /stage
$ ./runInstaller



  • Type My Oracle Support account details.


  • Choose if you want to search for any software updates.


  • Review and fix any prerequisite failure.


  • Choose the upgrade method, in my case One system Upgrade then select the Oracle Home.


  • Type the “NEW” Middleware Home location.


  • Type the required Repository credentials.


  • Some prerequisites failed, click Yes to let the installer fix them automatically.


  • Review the list of plug-ins that are going to be automatically upgraded.


  • Select any additional plug-ins to be deployed after the upgrade.


  • Type the required WebLogic Domain credentials.


  • Review the all the information provided and click Install.


  • Review the progress.


  • Run the required allroot.sh script as root.


  • Review the final information about the upgrade process.



  • Login to Cloud Control and verify the version. Note that some targets are marked as down, we are going to fix them in the post upgrade tasks.


  • Upgrade Oracle Management Agents (at least OMS and Repository). Go to Setup -> Manage Cloud Control -> Upgrade Agents.



  • Type a Job Name (or use the default name) and click Add to select the agents you want to upgrade to 12.1.0.4.
  • Select the agents from the list and click OK.


  • Choose credentials and click Submit. There’s the option to schedule the upgrade for later under Additional Inputs.



  • You must run the root.sh script after the upgrade, click OK.


  • Review the agent upgrade process.



  • Agent upgrade was successful.


Post Upgrade

Deleting the Old OMS Home is an optional task, however I have decided to perform it in order to save space and have a clean Inventory.


  • Invoke the installer from the old OMS home


$ export ORACLE_HOME=/u01/app/oracle/oms12cr3/oms
$ /oui/bin/runInstaller -deinstall



  • Select only the OMS-related plug-ins homes (from the Old Home only!!) and click Remove.


  • Select the Java Development Kit JDK home and click Remove.


  • Select the Oracle WebTier home and click Remove. After finishing the deinstall click Close.


  • Finally select the OMS home & the Oracle Common directories and click Remove.


All targets marked up.

Thanks,
Alfredo

Oracle Recursive Sessions ORA-00018

Today I want to talk about Oracle Database session usage; last week there was a problem with a database reporting ORA-00018: maximum number of sessions exceeded, but the number of sessions from v$session was much less than the one specified in SESSIONS parameter.
So, what was going on?
Turns out this is explained by internal recursive sessions that also account but they are not seen in the dictionary views. Note ID 419130.1 explains this in detail but I wanted to see this behavior in my 12c test database.
SQL> select count(*) from v$session;
  COUNT(*)
———-
       104
SQL> select count(*) from x$ksuse where bitand(ksspaflg,1) !=0 ;
  COUNT(*)
———-
       132
Also wanted to know what sessions were recursive and seems like all of them are performing DDL operations.
SQL> select INDX,decode(bitand(ksuseflg,19),17,’BACKGROUND’,1,’USER’,2,’RECURSIVE’,’?’),ksuudsna from x$ksuse s WHERE decode(bitand(ksuseflg,19),17,’BACKGROUND’,1,’USER’,2,’RECURSIVE’,’?’)=’RECURSIVE’;
      INDX DECODE(BIT KSUUDSNA
———- ———- ——————————
         8 RECURSIVE  SYS
        11 RECURSIVE  SYS
        14 RECURSIVE  SYS
        15 RECURSIVE  SYS
        17 RECURSIVE  SYS
        19 RECURSIVE  SYS
        20 RECURSIVE  SYS
        21 RECURSIVE  SYS
        29 RECURSIVE  SYS
        32 RECURSIVE  SYS
        38 RECURSIVE  SYS
        39 RECURSIVE  SYS
        40 RECURSIVE  SYS
        42 RECURSIVE  SYS
       123 RECURSIVE  SYS
       126 RECURSIVE  SYS
       129 RECURSIVE  SYS
       134 RECURSIVE  SYS
       136 RECURSIVE  SYS
       137 RECURSIVE  SYS
       138 RECURSIVE  SYS
       139 RECURSIVE  SYS
       142 RECURSIVE  SYS
       144 RECURSIVE  SYS
       145 RECURSIVE  SYS
       148 RECURSIVE  SYS
       154 RECURSIVE  SYS
       155 RECURSIVE  SYS
       156 RECURSIVE  SYS
       157 RECURSIVE  SYS
       242 RECURSIVE  SYS
       247 RECURSIVE  SYS
       249 RECURSIVE  SYS
       250 RECURSIVE  SYS
       252 RECURSIVE  SYS
       253 RECURSIVE  SYS
       255 RECURSIVE  SYS
       257 RECURSIVE  SYS
       259 RECURSIVE  SYS
       264 RECURSIVE  SYS
       265 RECURSIVE  SYS
       269 RECURSIVE  SYS
       272 RECURSIVE  SYS
       273 RECURSIVE  SYS
       275 RECURSIVE  SYS
       276 RECURSIVE  SYS
       278 RECURSIVE  SYS
       367 RECURSIVE  SYS
       369 RECURSIVE  SYS
       371 RECURSIVE  SYS
       375 RECURSIVE  SYS
       376 RECURSIVE  SYS
       379 RECURSIVE  SYS
       381 RECURSIVE  SYS
       383 RECURSIVE  SYS
       384 RECURSIVE  SYS
       388 RECURSIVE  SYS
       389 RECURSIVE  SYS
       391 RECURSIVE  SYS
       392 RECURSIVE  SYS
60 rows selected.
This is something to keep in mind when setting SESSIONS parameter in future.
Thanks,

Alfredo

Extract AWR data to build Response Time graphs (awrrtsys.sql)

This SQL script extract AWR data required to perform Response Time analysis for Oracle Databases. All information about it will be presented in the IOUG Collaborate 2014 session “553: Oracle Database Performance: Are Database Users Telling Me The Truth?”
–#********************************************************************
–#– Filename           : awrrtsys.sql
–#– Author             : Alfredo Krieg
–#– Original           : 17-Oct-12
–#– Last Update        : 21-Ago-13
–#– Description        : awrrtsys.sql- System Response time per snapshot in the last x days
–#                        per desired unit of work –
–#– Usage              : start awrrtsys.sql
–#– This script use AWR data (licensing is required)
–#********************************************************************
set termout on
set feedback off
set heading on
set linesize 150
set pagesize 100
set feedback off verify off
col snap_id           format  999999     heading “Snapshot Id”
col instance_number   format  99         heading “Instance Number”
col snap_time         format  a25        heading “Snap Begin Time”
col non_idle_wait     format  9999999.99 heading “Qt (s)”
col cpu_time          format  9999999.99 heading “St (s)”
col lio               format  9999999.99 heading “LIO”
col pio               format  9999999.99 heading “PIO”
col uw_variable       format  9999999.99 heading “UC”
col rt_ms_per_lio     format  9999999.99 heading “RT (ms/lio)”
col rt_ms_per_pio     format  9999999.99 heading “RT (ms/pio)”
col rt_ms_per_uw      format  9999999.99 heading “RT (ms/uc)”
SELECT a.SNAP_ID,
         b.instance_number,
         TO_CHAR (END_INTERVAL_TIME, ‘mon/dd/yyyy HH24:mi’) AS snap_time,
         non_idle_wait,
         background_cpu+
         db_cpu as cpu_time,
         lio,
         pio,
         uwvariable AS UW_VARIABLE,
         ROUND (
            ( ( (background_cpu + db_cpu) / lio) + (non_idle_wait / lio))
            * 1000,
            4)
            AS RT_ms_per_lio,
         ROUND (
            ( ( (background_cpu + db_cpu) / pio) + (non_idle_wait / pio))
            * 1000,
            4)
            AS RT_ms_per_pio,
         ROUND (
            ( ( (background_cpu + db_cpu) / (uwvariable))
             + (non_idle_wait / (uwvariable)))
            * 1000,
            4)
            AS RT_ms_per_uw
    FROM ( 
    SELECT SNAP_ID,
                   SUM (non_idle_wait) AS non_idle_wait,
                   ROUND (SUM (background_cpu), 0) AS background_cpu,
                   ROUND (SUM (db_cpu), 0) AS db_cpu,
                   SUM (lio) AS lio,
                   SUM (pio) AS pio,
                   SUM (uwvariable) AS uwvariable
              FROM (
              SELECT SNAP_ID,
                           ROUND (time_secs, 0) non_idle_wait,
                           0 AS background_cpu,
                           0 AS db_cpu,
                           0 AS lio,
                           0 AS pio,
                           0 AS uwvariable
                      FROM (  SELECT snap_id,
                                     ‘WAIT’ AS wait_class,
                                     ‘Non Idle’ AS name,
                                     (SUM (VALUE) – SUM (value1)) / 1000000
                                        AS time_secs
                                FROM (  SELECT snap_id,
                                               0 AS snap2,
                                               SUM (time_waited_micro) AS VALUE,
                                               0 AS value1
                                          FROM dba_hist_system_event
                                         WHERE wait_class ‘Idle’
                                      GROUP BY snap_id
                                      UNION
                                        SELECT snap_id + 1,
                                               snap_id,
                                               0,
                                               SUM (time_waited_micro) AS value1
                                          FROM dba_hist_system_event
                                         WHERE wait_class ‘Idle’
                                      GROUP BY snap_id)
                            GROUP BY snap_id)
                    UNION
                      SELECT snap_id,
                             0,
                             (SUM (VALUE) – SUM (value1)) / 1000000
                                AS background_cpu,
                             0,
                             0,
                             0,
                             0                       
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM dba_hist_sys_time_model
                               WHERE stat_name IN (‘background cpu time’)
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM dba_hist_sys_time_model
                               WHERE stat_name IN (‘background cpu time’))
                    GROUP BY snap_id, stat_name
                    UNION
                      SELECT snap_id,
                             0,
                             0,
                             (SUM (VALUE) – SUM (value1)) / 1000000 AS db_cpu,
                             0,
                             0,
                             0                          
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM dba_hist_sys_time_model
                               WHERE stat_name IN (‘DB CPU’)
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM dba_hist_sys_time_model
                               WHERE stat_name IN (‘DB CPU’))
                    GROUP BY snap_id, stat_name
                    UNION
                      SELECT snap_id,
                             0,
                             0,
                             0,
                             SUM (VALUE) – SUM (value1) AS lio,
                             0,
                             0                           
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN (‘session logical reads’)
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN (‘session logical reads’))
                    GROUP BY snap_id, stat_name
                    UNION
                      SELECT snap_id,
                             0,
                             0,
                             0,
                             0,
                             SUM (VALUE) – SUM (value1) AS pio,
                             0                           
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN (‘physical reads’)
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN (‘physical reads’))
                    GROUP BY snap_id, stat_name
                    UNION
                      SELECT snap_id,
                             0,
                             0,
                             0,
                             0,
                             0,
                             SUM (VALUE) – SUM (value1) AS uwvariable                          
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN (‘&1’)
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN (‘&1’))
                    GROUP BY snap_id, stat_name
                    )
          GROUP BY snap_id
            HAVING SUM (background_cpu) >= 0
          ORDER BY snap_id DESC) a, DBA_HIST_SNAPSHOT B
   WHERE a.SNAP_ID = b.SNAP_ID AND BEGIN_INTERVAL_TIME > SYSDATE – &2

ORDER BY 1;

How to remove OEM agent in the release 3 of the Oracle Enterprise Manager.

OEM 12c R3 has a very useful Perl script which allows removing an installed OEM agent in the host. The process is quite straightforward just by invoking the AgentDeinstall.pl script located under:

$AGENT_HOME/sysman/install/AgentDeinstall.pl

[oracle@ol6-112 12.1.0.3.0]$ /u01/app/oracle/agent12cr3/core/12.1.0.3.0/perl/bin/perl /u01/app/oracle/agent12cr3/core/12.1.0.3.0/sysman/install/AgentDeinstall.pl -agentHome $AGENT_HOME

Agent Oracle Home: /u01/app/oracle/agent12cr3/core/12.1.0.3.0

NOTE: The agent base directory: /u01/app/oracle/agent12cr3 will be removed after successful deinstallation of agent home.

DetachHome Command executed:/u01/app/oracle/agent12cr3/core/12.1.0.3.0/oui/bin/runInstaller -detachHome -force -depHomesOnly -silent ORACLE_HOME=/u01/app/oracle/agent12cr3/core/12.1.0.3.0 -waitForCompletion -invPtrLoc /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
The inventory pointer is located at /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
'DetachHome' was successful.
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
The inventory pointer is located at /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
The Oracle home '/u01/app/oracle/agent12cr3/sbin' could not be updated as it does not exist.

Deinstall Command executed:/u01/app/oracle/agent12cr3/core/12.1.0.3.0/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={/u01/app/oracle/agent12cr3/core/12.1.0.3.0}" -waitForCompletion -removeAllFiles -invPtrLoc /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-11-11_02-22-46PM. Please wait ...Oracle Universal Installer, Version 11.1.0.11.0 Production
Copyright (C) 1999, 2013, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Monday, November 11, 2013 2:23:00 PM CST)
Configuration assistant "Agent Deinstall Assistant" succeeded
............................................................... 100% Done.

Deinstall successful

End of install phases.(Monday, November 11, 2013 2:23:37 PM CST)
End of deinstallations
Please check '/u01/app/oraInventory/logs/silentInstall2013-11-11_02-22-46PM.log' for more details.

Do you want us to delete the old oracle home [yes/no] :y

Hope this help you when need to remove an OEM agent installation.

Thanks,
Alfredo

Oracle Enterprise Manager 12c Release 3 New Features

Today I will talk about some new features in the release 3 of the Oracle Enterprise Manager.
  • All Metrics Chart Enhancements

This new feature show more data in the metrics page, like minimum and maximum values, it will highlight periods when the agent was unreachable or target was down, it will also show the period when the metric was in warning or critical severity.
This really helps DBAs to better understand the behavior of the target including the metric data.
  • Metrics Schedule Enhancements

Metrics now support additional collection schedule like weekly on specified days of the week and monthly on specified days of the month.



  • Service Target Dashboard

Provides a quickly out-of-box overview of the health of services, this is really useful when looking for a quick view of what service is having problem in the environment.


  • Performance Diagnostics Enhancements

Even when this is not a new feature of the OEM 12c R3, ASH Analytics is really cool. It allows DBAs to diagnose performance exactly when DBA desire; it has customized selection of the desired point in time when issues appeared.
Well, these are some new and cool features of the Oracle Enterprise Manager 12c Release 3. Hope you can enjoy this features as much as me.
Thanks,

Alfredo