Blog Feed

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,
Alfred

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

Understanding Oracle Histograms

Histograms are a feature of the Cost Based Optimizer (CBO) that helps to know the data distribution in a column. Histograms should be created in columns with highly skew data and are included in the filter clause (WHERE) of a SQL statement.
Histograms play a very important role in the creation of the execution plan and we must be careful in the method used to gather statistics in the DB objects.
Let me show you how histograms work, for this test I have created a table T_HIST with some data in it:
 SQL> CREATE TABLE T_HIST AS SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,mod(round(dbms_random.value(1,100000)),254) c3, ‘C’ C4 from dual connect by level < 529101;

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
mod(round(dbms_random.value(1,100000)),254) c3, ‘P’ from dual connect by level < 301;

300 rows created.

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
mod(round(dbms_random.value(1,100000)),254) c3, ‘R’ from dual connect by level < 301;

300 rows created.

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
mod(round(dbms_random.value(1,100000)),254) c3, ‘T’ from dual connect by level < 301;

300 rows created.

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
  2  mod(round(dbms_random.value(1,100000)),254) c3, ‘A’ from dual connect by level < 500001;

500000 rows created.

SQL> COMMIT;

Commit complete.

SQL>

We will now create an index in the column C4:
SQL> CREATE INDEX T_HIST_IDX1 ON T_HIST(C4);

Index created.

SQL>

Let us see how the data is distributed inside column C4:
SQL> SELECT C4,COUNT(*) FROM T_HIST GROUP BY C4 ORDER BY C4;
C   COUNT(*)
– ———-
A     500000
C     529100
P        300
R        300
T        300
You can see that we have 5 distinct values in column C4 (A, C, P, R & T) and the number of values that correspond to each. Let us take value ‘R’ as example, if we query the table the expected execution plan is to use the Index created, because we need only 300 rows of the 1030000 rows total. Let’s query:
SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = ‘R’
  4  order by
  5      C1
  6  ;
Explained.
SQL>
SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);  2    3    4    5    6
Explained.

SQL> SQL> SQL> SQL>   2
Plan hash value: 3146893614

——————————————————————————————–
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————–
|   0 | SELECT STATEMENT             |             |    30 |  1260 |   387   (1)| 00:00:05 |
|   1 |  SORT ORDER BY               |             |    30 |  1260 |   387   (1)| 00:00:05 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HIST      |    30 |  1260 |   386   (0)| 00:00:05 |
|*  3 |    INDEX RANGE SCAN          | T_HIST_IDX1 |  3607 |       |   375   (0)| 00:00:05 |
——————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   3 – access(“C4″=’R’)

Note
—–
   – dynamic sampling used for this statement (level=2)

What happened? Execution plan is saying that our query will return only 30 rows! obviously this is totally wrong; also look at the estimated elapsed time (5 sec). Ok, we are being unfair to the CBO as we are not giving all the information required, so let’s gather statistics but without histograms:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘ALFRED’, tabname=>’T_HIST’, estimate_percent=>dbms_stats.auto_sample_size,method_opt=>’for all columns size 1′);
PL/SQL procedure successfully completed.
SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = ‘R’
  4  order by
  5      C1
  6  ;
Explained.
SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2911980760
————————————————————————————-
| Id  | Operation          | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT   |        |   206K|  3017K|       |  1832   (2)| 00:00:22 |
|   1 |  SORT ORDER BY     |        |   206K|  3017K|  5672K|  1832   (2)| 00:00:22 |
|*  2 |   TABLE ACCESS FULL| T_HIST |   206K|  3017K|       |   774   (2)| 00:00:10 |
————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   2 – filter(“C4″=’R’)
What happened? Now the CBO decided to choose a Full Table Scan, it also states that will return 206k rows and the estimated elapsed time is 22 sec. That’s because we used AUTO_SAMPLE_SIZE so Oracle we decide how many rows to sample and for sure values of ‘A’ & ‘C’ represent more than 99% of the samples. But how can we tell the CBO that our data distribution is skewed? that’s where histograms enter into play. Let’s now gather statistics using histogram for column C4:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘ALFRED’, tabname=>’T_HIST’, estimate_percent=>dbms_stats.auto_sample_size,method_opt=>’for columns C4 size 254′);
PL/SQL procedure successfully completed.
SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = ‘R’
  4  order by
  5      C1
  6  ;
Explained.
SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3146893614
——————————————————————————————–
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————–
|   0 | SELECT STATEMENT             |             |   941 | 14115 |     8  (13)| 00:00:01 |
|   1 |  SORT ORDER BY               |             |   941 | 14115 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HIST      |   941 | 14115 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_HIST_IDX1 |   941 |       |     4   (0)| 00:00:01 |
——————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
   3 – access(“C4″=’R’)
Now is using the Index again, states that will return 941 rows but look how the cost decreased and the estimated elapsed time to 1 sec! Even if the CBO wasn’t able to calculate the real number of rows being returned the cost was fixed, in order to have the most accurate execution plan we must increase the sample size. Let us make a test with 100% of the samples:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘ALFRED’, tabname=>’T_HIST’, estimate_percent=>100,method_opt=>’for columns C4 size 254′);
PL/SQL procedure successfully completed.
SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = ‘R’
  4  order by
  5      C1
  6  ;
Explained.
SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3146893614
——————————————————————————————–
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————–
|   0 | SELECT STATEMENT             |             |   300 |  4500 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY               |             |   300 |  4500 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HIST      |   300 |  4500 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_HIST_IDX1 |   300 |       |     3   (0)| 00:00:01 |
——————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
   3 – access(“C4″=’R’)
Now is totally accurate! Right? The execution plan generated by the CBO is using the Index, the estimated rows are 300 which is correct and the cost is even better. This is an example of a very small table, but is really an option to gather statistics with estimate of 100% to a big table?, I don’t think so; that’s why AUTO_SAMPLE_SIZE can be a good balance between sample size and accuracy.
Column C4 now is having a frequency histogram because is having less than 254 distinct values (NDV), only 5; that means that frequency histogram has the restriction of 254 buckets (254 NDV’s), seriously?. Let test it, we will gather statistics to all columns of the table using AUTO_SAMPLE_SIZE and AUTO for histograms:
SQL> exec dbms_stats.gather_table_stats(ownname=>’ALFRED’,tabname=>’T_HIST’, estimate_percent=>null, method_opt=>’FOR ALL COLUMNS SIZE AUTO’);
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, HISTOGRAM from user_tab_columns where table_name = ‘T_HIST’;
C1                                   529100  .00000189           1 NONE
C2                                      254 .003937008           1 NONE
C3                                      254 4.8544E-07         254 FREQUENCY
C4                                        5 4.8544E-07           5 FREQUENCY
What happened?, well column C3 was also candidate for a histogram because it’s density (skewed) but look at the number of buckets is 254; that means that we are on the edge of the limit in order to have a histogram in this column. Don’t believe me? let’s make a quick test, we will insert a new distinct value into this column, gather statistics again and see what happen:
SQL> insert into T_HIST values (100001,42, 99999999999,’B’);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>’ALFRED’,tabname=>’T_HIST’, estimate_percent=>null, method_opt=>’FOR ALL COLUMNS SIZE AUTO’);
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, HISTOGRAM from user_tab_columns where table_name = ‘T_HIST’;
C1                                   529100  .00000189           1 NONE
C2                                      254 .003937008           1 NONE
C3                                      255 .003921569           1 NONE
C4                                        6 4.8544E-07           6 FREQUENCY
Can you see how the number of distinct values (NVD) changed? now we can’t have a histogram in that column, really?; well we can force it but look how low is our density. Let’s try to force it and specify to the gather stats that we want all columns with maximum of 254 buckets:
SQL> exec dbms_stats.gather_table_stats(ownname=>’ALFRED’,tabname=>’T_HIST’, estimate_percent=>null, method_opt=>’FOR ALL COLUMNS SIZE 254′);
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, HISTOGRAM from user_tab_columns where table_name = ‘T_HIST’;
C1                                   529100 1.9186E-06         254 HEIGHT BALANCED
C2                                      254 4.8544E-07         254 FREQUENCY
C3                                      255 .003935563         254 HEIGHT BALANCED
C4                                        6 4.8544E-07           6 FREQUENCY
Look how it works? it can create frequency histogram on columns with less than 254 NDV’s and height balanced histogram in columns with more than 254 NDV’s. Also note the first example, we used size of 1 (1 bucket) that means no histogram because all values are inside only one bucket.
This is a basic example of how histograms work, honestly I was having a lot of doubts before making this test and some of them are clarified reading Jonathan Lewis new post in allthinngsoracle.com, however there’s a lot of path to walk through and even difficult as 12c have 2 more types of histograms to help CBO.

Thanks,
Alfredo  

MRP0: Background Media Recovery terminated with error 1237

Some days back checking an Oracle physical standby database found that the DB was some hours back than the primary database.

alter session set nls_date_format=’DD-MM-yyyy HH24:MI:SS’;
show parameter dest
select thread#,max(sequence#) from gv$log_history group by thread#;
select (a.amct-b.bmct)*24 “Hours Standby is Behind: ”
from (select max(completion_time) amct from v$archived_log) a,
(select max(completion_time) bmct from v$archived_log where applied=’YES’) b;
Hours Standby is Behind:
————————-
45.000054
The very next thing to check is what is going on with the MRP process.
select inst_id, process,status,sequence#, thread# from gv$managed_standby where process=’MRP0′;
no rows selected
So, the MRP process wasn’t running in the standby database. Let’s check the alert.log file.
MRP0: Background Media Recovery terminated with error 1237
…
ORA-01237: cannot extend datafile 13
The mount point where the datafile 13 resides is 100% full, that’s why the MRP couldn’t resize the datafile and was terminated by the instance.
In order to fix this you should increase the size of the mount point or if you have another mount point with enough free space you can do the following:

      ·        Shutdown standby database

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
·                  ·         Copy the datafile to the new location
#> cp –p users03.dbf /u02/oradata/test/users03.dbf
·          Startup mount standby database
SQL> startup nomount
ORACLE instance started.
SQL> alter database mount standby database;
Database altered.
·                    ·        Modify “standby_file_management” parameter to manual
As per Oracle documentation:
STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
SQL> alter system set standby_file_management=’MANUAL’ scope=both;
System altered.
·          Rename the datafile in order to reflect the changes in the standby control file.
SQL> alter database rename file ‘/u01/oradata/test/users03.dbf’ to ‘/u02/oradata/test/users03.dbf’;
Database altered.
·          Now let’s reset “standby_file_management” to AUTO.
SQL> alter system set standby_file_management=’AUTO’ scope=both;
System altered.
·          And start the MRP process again.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
After this MRP was able to successfully apply archive logs from primary database.
We have to be sure that every time we increase the size of a datafile in the primary database, have enough free space in the standby server to fit the new size of the datafile.
Thanks,

Alfredo

Testing user calls

In the performance tuning world specially talking about Response Time Analysis (RTA), user calls is often used as a workload metric; however this instance statistic was not yet completely clear.

When this metric is incremented?, How this metric is instrumented?, Why sometimes user calls is less with the same SQL statement?; these were some questions rounding my mind.

My friend Craig Shallahamer did a great post in his blog “A Wider View” related to user calls (http://shallahamer-orapub.blogspot.mx/2010/05/understanding-user-calls.html), however looking into Tanel Poder’s blog today everything got clear.

Let’s start tracing an Oracle session using trace events ‘10046’ & ‘10051’. 

Event ‘10051’ will trace OPI calls, more information (http://blog.tanelpoder.com/2011/03/20/lobread-sql-trace-entry-in-oracle-11-2/), what are OPI calls?, well as per Oracle documentation:

Oracle Program Interface (OPI)

A networking layer responsible for responding to each of the possible messages sent by OCI. For example, an OCI request to fetch 25 rows would have an OPI response to return the 25 rows once they have been fetched.


Let’s do it!

SQL> ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
ALTER SESSION SET EVENTS ‘10051 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
Session altered.
SQL>
Session altered.
SQL> SELECT TRACEFILE FROM V$PROCESS WHERE ADDR =(SELECT PADDR FROM V$SESSION WHERE SID=49);
TRACEFILE
——————————————————————————–
/u01/app/oracle/diag/rdbms/rac/RAC1/trace/RAC1_ora_5393.trc

In another window open the trace file using tail –f in order to monitor the trace execution. Now let’s issue a small query:

SQL> select * from dual;
D
–
X
SQL>
Here’s the output of the trace file:

*** 2013-08-05 21:42:15.721
WAIT #3: nam=’SQL*Net message from client’ ela= 342028978 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1375756935721909
OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
CLOSE #3:c=0,e=18,dep=0,type=0,tim=1375756935722017
OPI CALL: type=94 argc=28 cursor=  0 name=V8 Bundled Exec
WAIT #1: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935722368
WAIT #1: nam=’ges message buffer allocation’ ela= 0 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935722448
WAIT #1: nam=’library cache lock’ ela= 568 handle address=1287268616 lock address=1263092292 100*mode+namespace=65538 obj#=-1 tim=1375756935723089
WAIT #1: nam=’ges message buffer allocation’ ela= 1 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935723137
WAIT #1: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935723205
WAIT #1: nam=’library cache pin’ ela= 473 handle address=1287268616 pin address=1263092428 100*mode+namespace=65538 obj#=-1 tim=1375756935723815
=====================
PARSING IN CURSOR #4 len=210 dep=1 uid=0 oct=3 lid=0 tim=1375756935724353 hv=864012087 ad=’4cb562e8′ sqlid=’96g93hntrzjtr’
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #4:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375756935724351
BINDS #4:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=0040a79c  bln=22  avl=03  flg=05
  value=116
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=0040a778  bln=24  avl=02  flg=05
  value=1
EXEC #4:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375756935724524
FETCH #4:c=0,e=51,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,plh=2239883476,tim=1375756935724594
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=424 op=’TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=0 pw=0 time=0 us)’
STAT #4 id=2 cnt=1 pid=1 pos=1 obj=426 op=’INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=0 us)’
CLOSE #4:c=0,e=40,dep=1,type=3,tim=1375756935724660
=====================
PARSING IN CURSOR #1 len=18 dep=0 uid=0 oct=3 lid=0 tim=1375756935725402 hv=942515969 ad=’4b5c8e54′ sqlid=’a5ks9fhw2v9s1′
select * from dual
END OF STMT
PARSE #1:c=3999,e=3334,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,plh=272002086,tim=1375756935725401
EXEC #1:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=272002086,tim=1375756935725457
WAIT #1: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1375756935725480
WAIT #1: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935725627
WAIT #1: nam=’gc cr grant 2-way’ ela= 1035 p1=1 p2=928 p3=4 obj#=116 tim=1375756935726935
WAIT #1: nam=’db file sequential read’ ela= 634 file#=1 block#=928 blocks=1 obj#=116 tim=1375756935737381
WAIT #1: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=116 tim=1375756935741146
WAIT #1: nam=’gc cr grant 2-way’ ela= 470 p1=1 p2=929 p3=1 obj#=116 tim=1375756935741693
WAIT #1: nam=’db file sequential read’ ela= 1312 file#=1 block#=929 blocks=1 obj#=116 tim=1375756935743074
FETCH #1:c=1999,e=17670,p=2,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=272002086,tim=1375756935743169
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=116 op=’TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=0 us cost=2 size=2 card=1)’
WAIT #1: nam=’SQL*Net message from client’ ela= 221 driver id=1650815232 #bytes=1 p3=0 obj#=116 tim=1375756935743513
OPI CALL: type= 5 argc= 2 cursor=  1 name=FETCH
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=272002086,tim=1375756935743579
WAIT #1: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=116 tim=1375756935743629
Snapper shows the below metrics for our session:

SQL> @snapper4 all 5 1 49
Sampling SID 49 with interval 5 seconds, taking 1 snapshots…
— Session Snapper v4.09 BETA – by Tanel Poder ( http://blog.tanelpoder.com ) – Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! 🙂
——————————————————————————————————————————————————————————————————————
 SID @INST, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
——————————————————————————————–
    49  @1, SYS       , STAT, opened cursors cumulative,             1,         .2,         ,             ,          ,           ,          1 per execution
    49  @1, SYS       , STAT, user calls,             3,        .59,         ,             ,          ,           ,          3 per execution
Now let’s create a dummy table and insert some rows:
SQL> insert into test_user (user_name) select username from dba_users;
36 rows created.

From trace file:
*** 2013-08-05 21:55:34.386
WAIT #1: nam=’SQL*Net message from client’ ela= 58636486 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1375757734386174
OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
CLOSE #1:c=0,e=15,dep=0,type=0,tim=1375757734386272
OPI CALL: type=94 argc=28 cursor=  0 name=V8 Bundled Exec
=====================
PARSING IN CURSOR #3 len=37 dep=1 uid=0 oct=3 lid=0 tim=1375757734387095 hv=1398610540 ad=’4cbf4cb0′ sqlid=’grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #3:c=0,e=375,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1375757734387093
BINDS #3:
 Bind#0
  oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
  kxsbbbfp=1184df90  bln=16  avl=16  flg=05
  value=00002068.0000.0001
EXEC #3:c=2000,e=54991,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3684871272,tim=1375757734442193
FETCH #3:c=0,e=34,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1375757734442283
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=69 op=’TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=15 card=1)’
CLOSE #3:c=0,e=51,dep=1,type=0,tim=1375757734442357
WAIT #2: nam=’ges message buffer allocation’ ela= 4 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734443204
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734443382
WAIT #2: nam=’library cache lock’ ela= 549 handle address=1222331724 lock address=1220909652 100*mode+namespace=65538 obj#=-1 tim=1375757734444345
WAIT #2: nam=’KJC: Wait for msg sends to complete’ ela= 10 msg=1361877012 dest|rcvr=65536 mtype=12 obj#=-1 tim=1375757734444389
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734444433
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734444564
WAIT #2: nam=’library cache pin’ ela= 640 handle address=1222331724 pin address=1220909516 100*mode+namespace=65538 obj#=-1 tim=1375757734445329
WAIT #2: nam=’KJC: Wait for msg sends to complete’ ela= 9 msg=1361877012 dest|rcvr=65536 mtype=12 obj#=-1 tim=1375757734445367
=====================
PARSING IN CURSOR #6 len=210 dep=1 uid=0 oct=3 lid=0 tim=1375757734475342 hv=864012087 ad=’4cb562e8′ sqlid=’96g93hntrzjtr’
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #6:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375757734475341
BINDS #6:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=0040210c  bln=22  avl=04  flg=05
  value=74760
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=004020e8  bln=24  avl=02  flg=05
  value=1
EXEC #6:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375757734475532
FETCH #6:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375757734475567
STAT #6 id=1 cnt=0 pid=0 pos=1 obj=424 op=’TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=0 us)’
STAT #6 id=2 cnt=0 pid=1 pos=1 obj=426 op=’INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=0 us)’
CLOSE #6:c=0,e=1,dep=1,type=3,tim=1375757734475616
=====================
PARSING IN CURSOR #2 len=64 dep=0 uid=0 oct=2 lid=0 tim=1375757734477276 hv=1206268670 ad=’48d0a418′ sqlid=’4x5wmqj3yccry’
insert into test_user (user_name) select username from dba_users
END OF STMT
PARSE #2:c=10999,e=90955,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,plh=3422547789,tim=1375757734477275
WAIT #2: nam=’ges message buffer allocation’ ela= 5 pool=0 request=1 allocated=0 obj#=74760 tim=1375757734478040
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=74760 tim=1375757734478200
WAIT #2: nam=’enq: TM – contention’ ela= 616 name|mode=1414332419 object #=74760 table/partition=0 obj#=74760 tim=1375757734478936
WAIT #2: nam=’asynch descriptor resize’ ela= 4 outstanding #aio=0 current aio limit=384 new aio limit=390 obj#=-1 tim=1375757734486405
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734486564
WAIT #2: nam=’gc cr grant 2-way’ ela= 519 p1=1 p2=1976 p3=4 obj#=281 tim=1375757734487242
WAIT #2: nam=’db file sequential read’ ela= 788 file#=1 block#=1976 blocks=1 obj#=281 tim=1375757734488113
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=281 tim=1375757734488224
WAIT #2: nam=’gc cr grant 2-way’ ela= 431 p1=1 p2=1977 p3=1 obj#=281 tim=1375757734488790
WAIT #2: nam=’db file sequential read’ ela= 889 file#=1 block#=1977 blocks=1 obj#=281 tim=1375757734489741
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=281 tim=1375757734491203
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=281 tim=1375757734491352
WAIT #2: nam=’enq: HW – contention’ ela= 1020 name|mode=1213661190 table space #=0 block=4282248 obj#=281 tim=1375757734492503
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=281 tim=1375757734492596
WAIT #2: nam=’gc current grant 2-way’ ela= 422 p1=1 p2=87945 p3=33554433 obj#=74760 tim=1375757734493138
EXEC #2:c=5000,e=15461,p=2,cr=41,cu=7,mis=0,r=36,dep=0,og=1,plh=3422547789,tim=1375757734493365
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op=’LOAD TABLE CONVENTIONAL  (cr=41 pr=2 pw=0 time=0 us)’
STAT #2 id=2 cnt=36 pid=1 pos=1 obj=0 op=’HASH JOIN  (cr=40 pr=2 pw=0 time=35 us cost=24 size=1092 card=13)’
STAT #2 id=3 cnt=36 pid=2 pos=1 obj=0 op=’HASH JOIN  (cr=37 pr=2 pw=0 time=35 us cost=22 size=1053 card=13)’
STAT #2 id=4 cnt=36 pid=3 pos=1 obj=0 op=’HASH JOIN  (cr=28 pr=2 pw=0 time=35 us cost=17 size=1014 card=13)’
STAT #2 id=5 cnt=36 pid=4 pos=1 obj=0 op=’HASH JOIN OUTER (cr=19 pr=2 pw=0 time=140 us cost=13 size=975 card=13)’
STAT #2 id=6 cnt=36 pid=5 pos=1 obj=0 op=’HASH JOIN  (cr=16 pr=2 pw=0 time=35 us cost=10 size=598 card=13)’
STAT #2 id=7 cnt=36 pid=6 pos=1 obj=0 op=’HASH JOIN  (cr=13 pr=0 pw=0 time=0 us cost=8 size=572 card=13)’
STAT #2 id=8 cnt=2 pid=7 pos=1 obj=0 op=’MERGE JOIN CARTESIAN (cr=6 pr=0 pw=0 time=3 us cost=4 size=16 card=1)’
STAT #2 id=9 cnt=1 pid=8 pos=1 obj=280 op=’TABLE ACCESS FULL PROFILE$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=8 card=1)’
STAT #2 id=10 cnt=2 pid=8 pos=2 obj=0 op=’BUFFER SORT (cr=3 pr=0 pw=0 time=1 us cost=2 size=8 card=1)’
STAT #2 id=11 cnt=2 pid=10 pos=1 obj=280 op=’TABLE ACCESS FULL PROFILE$ (cr=3 pr=0 pw=0 time=1 us cost=2 size=8 card=1)’
STAT #2 id=12 cnt=36 pid=7 pos=2 obj=22 op=’TABLE ACCESS FULL USER$ (cr=7 pr=0 pw=0 time=0 us cost=3 size=1008 card=36)’
STAT #2 id=13 cnt=2 pid=6 pos=2 obj=281 op=’TABLE ACCESS FULL PROFNAME$ (cr=3 pr=2 pw=0 time=1 us cost=2 size=2 card=1)’
STAT #2 id=14 cnt=2 pid=5 pos=2 obj=297 op=’TABLE ACCESS FULL RESOURCE_GROUP_MAPPING$ (cr=3 pr=0 pw=0 time=1 us cost=2 size=29 card=1)’
STAT #2 id=15 cnt=7 pid=4 pos=2 obj=16 op=’TABLE ACCESS FULL TS$ (cr=9 pr=0 pw=0 time=48 us cost=4 size=21 card=7)’
STAT #2 id=16 cnt=7 pid=3 pos=2 obj=16 op=’TABLE ACCESS FULL TS$ (cr=9 pr=0 pw=0 time=12 us cost=4 size=21 card=7)’
STAT #2 id=17 cnt=9 pid=2 pos=2 obj=292 op=’TABLE ACCESS FULL USER_ASTATUS_MAP (cr=3 pr=0 pw=0 time=8 us cost=2 size=27 card=9)’
WAIT #2: nam=’SQL*Net message to client’ ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=74760 tim=1375757734493823
And from Snapper:
SQL> @snapper4 all 10 1 49
Sampling SID 49 with interval 10 seconds, taking 1 snapshots…
— Session Snapper v4.09 BETA – by Tanel Poder ( http://blog.tanelpoder.com ) – Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! 🙂
——————————————————————————————————————————————————————————————————————
 SID @INST, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
——————————————————————————————————————————————————————————————————————
    49  @1, SYS       , STAT, opened cursors cumulative,             3,        .33,         ,             ,          ,           ,          1 per execution
    49  @1, SYS       , STAT, user calls,             2,        .22,         ,             ,          ,           ,        .67 per execution
Now let’s commit:
SQL> commit;
Commit complete.
SQL>
From trace file:
*** 2013-08-05 22:01:49.281
WAIT #2: nam=’SQL*Net message from client’ ela= 374787222 driver id=1650815232 #bytes=1 p3=0 obj#=74760 tim=1375758109281068
OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
CLOSE #2:c=0,e=20,dep=0,type=0,tim=1375758109281167
OPI CALL: type=94 argc=28 cursor=  0 name=V8 Bundled Exec
=====================
PARSING IN CURSOR #4 len=6 dep=0 uid=0 oct=44 lid=0 tim=1375758109281299 hv=3480936638 ad=’0′ sqlid=’23wm3kz7rps5y’
commit
END OF STMT
PARSE #4:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1375758109281298
XCTEND rlbk=0, rd_only=0, tim=1375758109281345
EXEC #4:c=0,e=102,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,plh=0,tim=1375758109281434
WAIT #4: nam=’log file sync’ ela= 24619 buffer#=4018 sync scn=1381492 p3=0 obj#=74760 tim=1375758109306071
WAIT #4: nam=’SQL*Net message to client’ ela= 351 driver id=1650815232 #bytes=1 p3=0 obj#=74760 tim=1375758109307375
From Snapper:
SQL> @snapper4 all 6 1 49
Sampling SID 49 with interval 6 seconds, taking 1 snapshots…
— Session Snapper v4.09 BETA – by Tanel Poder ( http://blog.tanelpoder.com ) – Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! 🙂
——————————————————————————————————————————————————————————————————————
 SID @INST, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
——————————————————————————————————————————————————————————————————————
    49  @1, SYS       , STAT, opened cursors cumulative,             1,        .17,         ,             ,          ,           ,          ~ per execution
    49  @1, SYS       , STAT, user commits,             1,        .17,         ,             ,          ,           ,          ~ per execution
    49  @1, SYS       , STAT, user calls,             2,        .34,         ,             ,          ,           ,          ~ per execution
This is just a little example of how OPI CALL is related to user calls metric. You can play and test more scenarios of select statements to see how they are being affected by ARRAYSIZE setting in SqlPlus.
Craig posted a table containing the result of his testing which is having the same results as this test.

SQL
User Calls
Select 1 row
3
Insert
2
Commit
2
Thanks,

Alfredo

Oracle 12c ASM new features


Today I will talk about some new features for ASM on 12c version.
Oracle Flex ASM
Oracle Flex ASM is a set of new features that provide critical capabilities required for cloud computing. Oracle Flex ASM redefines the traditional ASM cluster architecture of having one ASM instance on every node in the cluster; this means you can have less ASM instances than nodes in your cluster. In this configuration the number of ASM instances running is called ASM cardinality, by default the ASM cardinality is 3 and can be changed with a Clusterware command.
What are the benefits?
On the traditional architecture when an ASM instance fails; all DB instances connected to that instance in the node will also fail. With Oracle Flex ASM if the ASM instance fail the Clusterware will relocate that ASM instance to a different node and the DB instances will remotely (through private network) connect to the relocated ASM instance, all without disruption to the DB client.
Dedicated ASM Network
This new 12c feature provides the option to dedicate a private network for ASM network traffic only. There’s also the option to use the Oracle Clusterware interconnect private network.
Remote Access
As per the new Oracle Flex ASM feature, the DB instance can remotely connect to ASM instance hence the need of a password file in order to authenticate remote DB instances to ASM. Oracle Flex ASM has the ability of storing password files in a Disk Group and is extended to DB clients; this is really useful to avoid synchronizing multiple password files within the cluster.
Oracle Flex ASM is one of the most important enhancements to ASM in 12c version, however in the below whitepaper describes the rest of new features present.
http://www.oracle.com/technetwork/products/cloud-storage/oracle-12c-asm-overview-1965430.pdf
Bertrand Drouvot has already tested this Oracle Flex ASM feature and uploaded the contents into his own blog (http://bdrouvot.wordpress.com/2013/06/29/build-your-own-flex-asm-12c-lab-using-virtual-box/).
Another interesting feature is the Extent Reading Selection Enhancement which evenly distributes the selection of which copy of a block is read in ASM Disk Groups with normal or high redundancy. This feature is enabled by default in 12c and states that users on I/O bound systems should notice a performance improvement while reading blocks from disks.
Thanks,
Alfredo