Search

OakieTags

Who's online

There are currently 0 users and 28 guests online.

Recent comments

Oakies Blog Aggregator

Oracle 11.2.0.3 client not relinking properly

One of the tasks I am performing quite regularly is to deploy Oracle software in form of an RPM. In a previous post I described how this proces could work, based on a post by Frits Hoogland.

Employing the same method, I ran into problems with Oracle 11.2.0.x clients. A few facts to start with:

  • Oracle 11.2.0.3 client 64bit
  • Golden image created on Oracel Linux 5
  • Destination: SuSE Enterprise 10 SP4

The problem described here is most likely applicable to other Oracle clients as well although I haven’t verified that.

The problem

After the clone of the client to the SuSE server I couldn’t start SQL*Plus. Fair enough, I hadn’t set the LD_LIBRARY_PATH. After that I still couldn’t launch sqlplus because of a segmention fault. So if the clone wants to play it difficult, then I can always try to relink it all. But to my great astonishment that didn’t solve the problem either! Still same error.

The relink operation writes information into a logfile, called $ORACLE_HOME/install/relink.log. Please note that this isn’t the RDBMS home, when referring to ORACLE_HOME in this article I specifically speak of the client home!

The log looked normal until I came to this line:

/u01/app/oracle/product/client/11.2.0.3/bin/genclntsh
genclntsh: genclntsh: Could not locate  /u01/app/oracle/product/client/11.2.0.3/network/admin/shrept.lst
make: *** [client_sharedlib] Error 1
Error in invoking target 'client_sharedlib' of makefile '/u01/app/oracle/product/client/11.2.0.3/rdbms/lib/ins_rdbms.mk'. See '/u01/app/oracle/product/client/11.2.0.3/install/relinkActions2012-01-24_09-46-10-AM.log' for details.

Well ok then. In fact, there was no file in $ORACLE_HOME/network/admin at all. Not on the clone, neither in the golden image. This looked like a bug to me and I opened a SR with Oracle. The suggestion (unsurprisingly) was to copy the file either from the 11.2.0.3 RDBMS installation media or alternatively copy the file from an existing 11.2.0.3 RDBMS home.

Trying the relink operation now proved that the file was indeed essential for a relink operation. SQL*Plus now works.

Parent-Child Relationships and the Questions Left Unanswered by TKPROF, Re-Learning Something Old

January 30, 2012 As we have seen in the past, TKPROF output sometimes lies, and in a recent OTN thread I was reminded of another case or two where TKPROF output may be misleading.  In the OTN thread, the original poster (OP) started the thread by asking a simple question about an execution plan that appeared [...]

FRM-92095: Oracle Jnitiator version too low - please install version 1.1.8.2 or higher

A customer of mine who deploys Oracle Forms 10g  on the wild wild web, ran into a little problem.
They don't have control over the java plugin being installed on the client machine and with the latest java 1.7 update
they encountered the following forms error :
FRM-92095: Oracle Jnitiator version too low - please install version 1.1.8.2 or higher
Doh - we're running java 1.7 - not Oracle's obscure Jnitiator.
This seems to be a know issue on metalink see 
FRM-92095 Error When Running Forms Using JRE 7 (JRE 1.7) [ID 1348436.1]
A bug has been filed Bug:11782681 APPS6: FORMS DO NOT LAUNCH WITH BETA JRE 1.7
There's no patch yet(not even for the latest 11g Forms). And because Forms 10.1.2.x permier support
ended in December 2011... God only knows there will be a back-port. So for now - we're on our own !
However ... why does the Forms applet thinks it is running on an Oracle Jnitiator ?
Can it be that Java's vendor system property changed from 'Sun Microsystems Inc' to 'Oracle Corporation' ?
A little test program will show :
import java.util.* ;
class test
{
public static void main(String args[])
{
System.out.println("java.version =" + System.getProperty("java.version") ) ;
       System.out.println("java.vendor =" + System.getProperty("java.vendor") ) ;
}
}
On java 1.6:
java test
java.version =1.6.0_30
java.vendor =Sun Microsystems Inc.
On java 1.7:
java.version =1.7.0_02
java.vendor =Oracle Corporation
This property string for Java 1.6 and below is 'Sun Microsystems Inc' - starting from 1.7 it is 'Oracle Corporation'.
Oracle Jnitiator also used the vendor string 'Oracle Corporation'.
So - Forms now thinks it is running on an old jnitiator instead of the latest java 1.7 plugin.
I used a bootstrapper applet which sets the vendor property back to 'Sun Microsystems inc' like this :
- System.setProperty("java.vendor", "Sun Microsystems Inc.") ;
Then the applet(stub) loads Oracle Forms' main engine class.
- Class oforms = Class.forName("oracle.forms.engine.Main");
- init and start the Forms applet
I can't share the code nor class as the IP belongs to my employer but this fixed the problem and we can now run Forms on Java 1.7 !
The great part of this is - we don't touch the original frmall.jar, which is important if 3000 end users need a new jar downloaded over limited bandwidth ! 
Then, I went into a creative overdrive :-) 
In the spirit of the 80's and 90's atari and amiga demo scene I added an intro with plasma effect. 
Update - see also a very elegant workaround by Didier Marcelis in the comments (add -Djava.vendor="Sun Microsystems Inc.")
Update - if you are interested in the Forms bootstrapper (without plasma intro) - send me a mail.
Check out the flash screen capture I've made of the application loading :-)))
Update - it looks like there is a patch for forms 10g (bundle patch 10.1.2.3.2) that will solve the problem !
The patch number is 14825718 - and can be downloaded here.
One of the fixed bugs is 
#  11782681 - APPS6  FORMS DO NOT LAUNCH WITH BETA JRE 1.7
I have not yet installed and tested it - so if someone has - please leave a comment !

Statement level constraint consistency

In the past week I've been investigating how Oracle fires triggers with the Merge and Multi-Table-Insert statements. Also took a look at 'statement-level constraint consistency' with these two types of statements. My findings are here: http://rulegen.blogspot.com/2012/01/statement-level-constraint-validation.html

Normal transmission on harmful triggers should resume shortly.

Did you know the cluvfy healthcheck?

While I was performing a three day seminar recently in Switzerland I came across this new option in cluvfy.

Normally you’d run cluvfy in preparation of the installation of Grid Infrastructure or a set of RAC binaries to ensure everything is ready for the next step in the RAC install process. Beginning with 11.2.0.3, there is another option that’s been sneaked in without too much advertisement: the healthcheck.

Part of the “comp” checks, it takes the following options:

cluvfy comp healthcheck [-collect {cluster|database}] [-db db_unique_name] [-bestpractice|-mandatory] [-deviations] [-html] [-save [-savedir directory_path]

The most extensive report is run without any options, as shown in the appendix (the output is too long to display at this stage of the post) You have the following options:

  • collect: you can either specify to collect information about the cluster, or the database. Don’t specify the collect option and the health check will collect information about both. See also the next option if you want to collect database related information!
  • db: database unique name to collect information for. Before you can use this option, you need to run the script cvusys.sql in $GRID_HOME/cv/admin. It will create a user the healthcheck tool will connect against to find recommendations. If omitted, all cluster databases registered in the OCR will be probed.
  • bestpractice, mandatory, deviations are self explanatory. As always, don’t take the “best practice” literally!
  • html: Creates a HTML report in savedir, see below. If not used, a text based output is created
  • save, savedir: save the report in savedir

Now this seems to be a nice way to produce a report at the end of the RAC installation which shows that the system is correctly set up.

Appendix

Here is a text version of a full report I generated on my 2 node cluster, comprised of rac11203node1 and rac11203node2. I didn’t have a database ready at the time hence the limitation to the cluster health check.


******************************************************************************************
Summary of environment
******************************************************************************************

Date (mm/dd/yyyy)    :  01/28/2012
Time (hh:mm:ss)      :  21:57:03
Cluster name         :  rac11203
Clusterware version  :  11.2.0.3.0
Grid home            :  /u01/crs/11.2.0.3
Grid User            :  oracle
Operating system     :  Linux2.6.32-100.34.1.el6uek.x86_64

******************************************************************************************
System requirements
******************************************************************************************

Verification Check          :  Network Time Protocol (NTP)
Verification Description    :  This task verifies cluster time synchronization on
clusters that use Network Time Protocol (NTP).
Verification Result         :  FAILED
Cluster-wide error details  :  Error -  If you plan to use CTSS for time
synchronization then NTP configuration must be
uninstalled on all nodes of the cluster.

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       FAILED    not available                 not available
rac11203node1       FAILED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Swap Size
Verification Description  :  This is a prerequisite condition to test whether
sufficient total swap space is available on the system.
Verification Result       :  WARNING
Verification Summary      :  Swap configuration did not meet the recommended value of
null on rac11203node2,rac11203node1

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       FAILED    3.9127GB (4102768.0KB)        1023.9922MB (1048568.0KB)
rac11203node1       FAILED    3.9127GB (4102768.0KB)        1023.9922MB (1048568.0KB)

__________________________________________________________________________________________

Verification Check        :  Time zone consistency
Verification Description  :  This task checks for the consistency of time zones across
systems.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Task resolv.conf Integrity
Verification Description  :  This task checks consistency of file /etc/resolv.conf file
across nodes
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  User Mask
Verification Description  :  This is a prerequisite condition to make sure the user
file creation mask (umask) is "0022".
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    0022                          0022
rac11203node1       PASSED    0022                          0022

__________________________________________________________________________________________

Verification Check        :  User Not In Group: oracle
Verification Description  :  This is a prerequisite condition to make sure user
"oracle" is not part of "root" group.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Same core file name pattern
Verification Description  :  This task checks the consistency of core file name pattern
across systems.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Root user consistency
Verification Description  :  This test checks the consistency of the primary group of
the root user across the cluster nodes
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Current Group ID
Verification Description  :  This test verifies that the user is currently logged in to
the user's primary group.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Users With Same UID
Verification Description  :  This test checks that multiple users do not exist with
user id as "0".
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  OS Kernel Version
Verification Description  :  This is a prerequisite condition to test whether the
system kernel version is at least "2.6.32".
Verification Result       :  PASSED
Verification Summary      :  Kernel version meets recommendation

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    2.6.32                        2.6.32-100.34.1.el6uek.x86_6
4
rac11203node1       PASSED    2.6.32                        2.6.32-100.34.1.el6uek.x86_6
4

__________________________________________________________________________________________

Verification Check        :  Architecture
Verification Description  :  This is a prerequisite condition to test whether the
system has a certified architecture.
Verification Result       :  PASSED
Verification Summary      :  Architecture recommendation is met

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    x86_64                        x86_64
rac11203node1       PASSED    x86_64                        x86_64

__________________________________________________________________________________________

Verification Check        :  Soft Limit: maximum user processes
Verification Description  :  This is a prerequisite condition to test whether the soft
limit for "maximum user processes" is set to at least 2047.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    2047                          2047
rac11203node1       PASSED    2047                          2047

__________________________________________________________________________________________

Verification Check        :  Hard Limit: maximum user processes
Verification Description  :  This is a prerequisite condition to test whether the hard
limit for "maximum user processes" is set to at least
16384.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    16384                         16384
rac11203node1       PASSED    16384                         16384

__________________________________________________________________________________________

Verification Check        :  Soft Limit: maximum open file descriptors
Verification Description  :  This is a prerequisite condition to test whether the soft
limit for "maximum open file descriptors" is set to at
least 1024.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    1024                          1024
rac11203node1       PASSED    1024                          1024

__________________________________________________________________________________________

Verification Check        :  Hard Limit: maximum open file descriptors
Verification Description  :  This is a prerequisite condition to test whether the hard
limit for "maximum open file descriptors" is set to at
least 65536.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    65536                         65536
rac11203node1       PASSED    65536                         65536

__________________________________________________________________________________________

Verification Check        :  Run Level
Verification Description  :  This is a prerequisite condition to test whether the
system is running with proper run level.
Verification Result       :  PASSED
Verification Summary      :  Run level recommendation are met

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    3,5                           3
rac11203node1       PASSED    3,5                           3

__________________________________________________________________________________________

Verification Check        :  Group Membership: oinstall(Primary)
Verification Description  :  This is a prerequisite condition to test whether user
"oracle" has group "oinstall" as its primary group.
Verification Result       :  PASSED
Verification Summary      :  Group oinstall is the primary group of user oracle

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Group Existence: oinstall
Verification Description  :  This is a prerequisite condition to test whether group
"oinstall" exists on the system.
Verification Result       :  PASSED
Verification Summary      :  Group oinstall exists

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    true                          true
rac11203node1       PASSED    true                          true

__________________________________________________________________________________________

Verification Check        :  User Existence: oracle
Verification Description  :  This is a prerequisite condition to test whether user
"oracle" exists on the system.
Verification Result       :  PASSED
Verification Summary      :  User oracle exists

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    true                          true
rac11203node1       PASSED    true                          true

__________________________________________________________________________________________

Verification Check        :  Available Physical Memory
Verification Description  :  This is a prerequisite condition to test whether the
system has at least 50MB (51200.0KB) of available physical
memory.
Verification Result       :  PASSED
Verification Summary      :  Available memory meets or exceeds recommendation

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    50MB (51200.0KB)              3.029GB (3176136.0KB)
rac11203node1       PASSED    50MB (51200.0KB)              2.7401GB (2873240.0KB)

__________________________________________________________________________________________

Verification Check        :  Physical Memory
Verification Description  :  This is a prerequisite condition to test whether the
system has at least 1.5GB (1572864.0KB) of total physical
memory.
Verification Result       :  PASSED
Verification Summary      :  Physical memory meets or exceeds recommendation

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    1.5GB (1572864.0KB)           3.9127GB (4102768.0KB)
rac11203node1       PASSED    1.5GB (1572864.0KB)           3.9127GB (4102768.0KB)

__________________________________________________________________________________________

Verification Check        :  ASMLib installation and configuration verification.
Verification Description  :  This task checks the ASMLib installation and configuration
across the systems.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Multicast check
Verification Description  :  This task checks that network interfaces in subnet are
able to communicate over multicast IP address
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Node Connectivity
Verification Description  :  This is a prerequisite condition to test whether
connectivity exists amongst all the nodes.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________


******************************************************************************************
System recommendations
******************************************************************************************

Verification Check        :  Reverse path filter setting
Verification Description  :  Checks if reverse path filter setting for all private
interconnect network interfaces is correct
Verification Result       :  MET
Verification Summary      :  Check for Reverse path filter setting passed
Additional Details        :  Reverse path filter parameter "rp_filter" must be set to
the value of 0 or 2 for all the private interconnect
network interfaces, this will disable or relax the
filtering and allow Clusterware to function correctly

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    0|2                           0|2
rac11203node1       PASSED    0|2                           0|2

__________________________________________________________________________________________

Verification Check        :  availability of port 8888
Verification Description  :  availability of port 8888
Verification Result       :  MET
Verification Summary      :  Check for availability of port 8888 passed

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    true                          true
rac11203node1       PASSED    true                          true

__________________________________________________________________________________________

Verification Check        :  Hardware Clock synchronization at shutdown
Verification Description  :  Checks whether Hardware Clock is synchronized with the
system clock during system shutdown
Verification Result       :  MET
Verification Summary      :  Check for Hardware Clock synchronization at shutdown passed

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    HWCLOCKSYNC=1                 HWCLOCKSYNC=1
rac11203node1       PASSED    HWCLOCKSYNC=1                 HWCLOCKSYNC=1

__________________________________________________________________________________________


******************************************************************************************
Clusterware requirements
******************************************************************************************

Verification Check          :  Clock Synchronization
Verification Description    :  This test checks the Oracle Cluster Time Synchronization
Services across the cluster nodes.
Verification Result         :  FAILED
Cluster-wide error details  :  Error -  Look at the accompanying error messages for the
nodes on which the check failed and fix the problem. If
you plan to use CTSS for time synchronization then NTP
configuration should be uninstalled on all nodes of the
cluster. Refer to "Preparing Your Cluster" of "Oracle
Database 2 Day+ Real Application Clusters Guide".

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       FAILED    not available                 not available
rac11203node1       FAILED    not available                 not available

__________________________________________________________________________________________

Verification Check          :  Node Connectivity
Verification Description    :  This is a prerequisite condition to test whether
connectivity exists amongst all the nodes. The
connectivity is being tested for the subnets
"192.168.99.0,192.168.100.0,192.168.101.0"
Verification Result         :  FAILED
Cluster-wide error details  :  Error -  Verify the interface configurations for the
network interfaces identified on the nodes indicated
using utilities like ipconfig or ping.

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node1 :     FAILED    not available                 not available
192.168.99.27
rac11203node2       FAILED    not available                 not available
rac11203node1       FAILED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  VIP Subnet configuration check
Verification Description  :  This task checks that all VIP subnetworks match each other
and at least one public network interface of the cluster
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  User Not In Group: oracle
Verification Description  :  This is a prerequisite condition to make sure user
"oracle" is not part of "root" group.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  ACFS Integrity
Verification Description  :  This test checks the integrity of Oracle ASM Cluster File
System across the cluster nodes.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  OLR Integrity
Verification Description  :  This test checks the integrity of OLR on the local node.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Single Client Access Name (SCAN)
Verification Description  :  This test verifies the Single Client Access Name
configuration.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Node Application Existence
Verification Description  :  This test checks the existence of Node Applications on the
system.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  CRS Integrity
Verification Description  :  This test checks the integrity of Oracle Clusterware stack
across the cluster nodes.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  OCR Integrity
Verification Description  :  This test checks the integrity of OCR across the cluster
nodes.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Cluster Integrity
Verification Description  :  This test checks the integrity of the cluster.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  User Mask
Verification Description  :  This is a prerequisite condition to make sure the user
file creation mask (umask) is "0022".
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    0022                          0022
rac11203node1       PASSED    0022                          0022

__________________________________________________________________________________________

Verification Check        :  UDev attribtes check
Verification Description  :  This is a pre-check condition to check if the devices
entries in the Udev permissions file have been set up
correctly.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  UDev attribtes check
Verification Description  :  This is a pre-check condition to check if the devices
entries in the Udev permissions file have been set up
correctly.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Cluster Manager Integrity
Verification Description  :  This test checks the integrity of cluster manager across
the cluster nodes.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Voting Disk
Verification Description  :  This test verifies the Oracle Clusterware voting disk
configuration which is used to determine which instances
are members of a cluster.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Time zone consistency
Verification Description  :  This task checks for the consistency of time zones across
systems.
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________

Verification Check        :  Multicast check
Verification Description  :  This task checks that network interfaces in subnet are
able to communicate over multicast IP address
Verification Result       :  PASSED

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    not available                 not available
rac11203node1       PASSED    not available                 not available

__________________________________________________________________________________________


******************************************************************************************
Clusterware recommendations
******************************************************************************************

Verification Check        :  CSS disktimeout parameter
Verification Description  :  Checks if the CSS disktimeout is set correctly on the
system
Verification Result       :  MET
Verification Summary      :  Check for CSS disktimeout parameter passed
Additional Details        :  The maximum amount of time allowed for a voting file I/O
to complete; if this time is exceeded the voting disk will
be marked as offline.  Note that this is also the amount
of time that will be required for initial cluster
formation, i.e. when no nodes have previously been up and
in a cluster.
References (URLs/Notes)   :  https://support.oracle.com/CSP/main/article?cmd=show&type=N
OT&id=294430.1

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    200                           200
rac11203node1       PASSED    200                           200

__________________________________________________________________________________________

Verification Check        :  CSS reboottime parameter
Verification Description  :  Checks if the CSS reboottime is set correctly on the system
Verification Result       :  MET
Verification Summary      :  Check for CSS reboottime parameter passed
Additional Details        :  reboottime (default 3 seconds) is the amount of time
allowed for a node to complete a reboot after the CSS
daemon has been evicted.
References (URLs/Notes)   :  https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=294430.1

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    3                             3
rac11203node1       PASSED    3                             3

__________________________________________________________________________________________

Verification Check        :  CSS misscount parameter
Verification Description  :  Checks if the CSS misscount is set correctly on the system
Verification Result       :  MET
Verification Summary      :  Check for CSS misscount parameter passed
Additional Details        :  The CSS misscount parameter represents the maximum time,
in seconds, that a network heartbeat can be missed before
entering into a cluster reconfiguration to evict the node
References (URLs/Notes)   :  https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=294430.1

Node                Status    Expected Value                Actual Value
------------------------------------------------------------------------------------------

rac11203node2       PASSED    30                            30
rac11203node1       PASSED    30                            30

__________________________________________________________________________________________

As this is lab kit, I don’t worry about the NTP and SWAP errors-you however should!

Purrpetual Motion

This is a really old idea – but it’s the first time I’ve seen it illustrated.

(The obvious flaw in the concept appears  in comment 22.)

Friday Philosophy – The Answer To Everything

For those of us acquainted with the philosophical works of Douglas Adams we know that the the answer to everything is 42.

mdw1123> select all knowledge from everything
  2  /

 KNOWLEDGE
----------
        42

This above is a real SQL statement (version 11.2.0.3, just in case you wanted to know :-) ).

This was prompted by a silly discussion at lunch time about the answer to everything and databases and I wondered aloud how you could go about getting Oracle to respond with 42 when you “selected all from everything”. My colleagues looked at me like I was an idiot and said “create a table called everything with a column called all and select it”. Yeah, of course, and I laughed. So much for being an expert at Oracle huh?

Well, I tried. It did not work:

mdw1123> create table EVERYTHING (ALL number not null)
  2  /
create table EVERYTHING (ALL number not null)
                         *
ERROR at line 1:
ORA-00904: : invalid identifier

Damn. It’s a reserved word. But for what? Off the top of my head I could not remember what ALL is used for in Oracle select syntax. Never mind, I could get around the issue to some degree by the use of quotes around the column name (and just for fun, I made the column name lowercase too – this is how you can create lowercase columns but you have to be careful with this, as you will see below):

mdw1123> create table everything ("all" number not null)
  2  /
mdw1123> desc everything
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------

 all                                                               NOT NULL NUMBER

mdw1123> insert into everything values (42)
  2  /
mdw1123> select "all" from everything
  2  /

       all
----------
        42

-- but be careful of case
mdw1123> select "ALL" from everything
  2  /
select "ALL" from everything
       *
ERROR at line 1:
ORA-00904: "ALL": invalid identifier

I was not happy with this though, I was having to put the quotes in my line and be careful about the syntax.

So, what is the word ALL used for? A quick check of the SQL reference manual:

It is the opposite of DISTINCT and the default, so we never have to put it in the statement.

With the above in mind I was able to quickly come up with something close, but not quite, what I originally asked for. See below for how.

mdw1123> create table everything (KNOWLEDGE NUMBER NOT NULL)
  2  /

mdw1123> insert into everything values (42)
  2  /

mdw1123> select all knowledge from everything
  2  /

 KNOWLEDGE
----------
        42
mdw1123>

Having said it was not quite what I had originally set out to do, I actually prefer this version.

Of course, I cleaned up after myself. It feels odd typing in commands that have an English meaning that would not be what you want to do – I could not get over the nagging feeling that the below was going to cause a lot of data to disappear :-) :

mdw1123> drop table everything purge;

Oracle Documentation: The broken links fiasco continues…

So I was just patting myself on the back for finishing my website clean up, then I happened on a few pages with broken links to Oracle documentation. That annoyed me, but I figured I better do a quick scan to see how many broken external links I had. The first attempt was a complete fail because the tool I used clicked all my Google Adsense adverts, making me a DotCom millionaire in about 3 minutes. I wrote to Google and apologised profusely. In my defense, the tool I used was right at the top of the list in the Chrome Web App Store…

Once I got a link checker that didn’t put me at risk of a jail sentence, things got a little more depressing. A very large number of my articles contain broken links to Oracle documentation. As I started looking at links it became apparent that Oracle have used at least 3 main URLs for documentation over the years:

The versions listed are based on the links I’ve added in my articles. If you check today, all/most docs come from the “http://docs.oracle.com” address.

This in itself shouldn’t present a problem, because any company with an involvement in the web knows that URLs should never change. If by chance you do have to change something, you put a redirect in place. The problem is, Oracle don’t do this, or at least not consistently. Check out the following three URLs:

They are the same document, just using the three base URLs I mentioned previously. If you click them, you’ll notice the first one fails and the following two work. My guess is Oracle have created a 301 permanent redirect from http://download.oracle.com/docs to http://docs.oracle.com, but not bothered to maintain the http://download-west.oracle.com/docs URL, thereby breaking just about every link to its docs on the internet that references anything older than about 11gR1. That includes forums (including their own), blog posts, documents containing URLs etc. It’s just a nightmare.

So PLEASE Oracle:

  • Stop changing URLs.
  • When you do change them, PLEASE use rewrites/redirects properly.
  • Remember, your rewrites/redirects should be permanent, not just long enough for search engines to update their indexes.

This would solve the vast majority of my gripes about the links to the Oracle docs…

Notes:

  • For those not familiar with web servers, this kind of rewrite/redirect for a whole domain name is really simple. It’s one line in your “.htaccess” file, not a separate one for each page, so I’m not asking for the world here. :)
  • I am aware there are other issues with changing URLs at Oracle that a blanket redirect would not solve. I’m not even going to start on whitepapers and PDFs…

Cheers

Tim…




Autotrace Polluting The Shared Pool?

Introduction

Another random note that I made during the sessions attended at OOW was about the SQL*Plus AUTOTRACE feature. As you're hopefully already aware of this feature has some significant shortcomings, the most obvious being that it doesn't pull the actual execution plan from the Shared Pool after executing the statement but simply runs an EXPLAIN PLAN on the SQL text which might produce an execution plan that is different from the actual one for various reasons.

Now the claim was made that in addition to these shortcomings the plan generated by the AUTOTRACE feature will stay in the Shared Pool and is eligible for sharing, which would mean that other statement executions could be affected by a potentially bad execution plan generated via AUTOTRACE rather then getting re-optimized on their own.

Now that claim initially struck me as odd because so far I was under the impression that the shortcoming of AUTOTRACE was the fact that it simply used the EXPLAIN PLAN facility to get the execution plan details - and I don't think that any plan generated by EXPLAIN PLAN is eligible for sharing with actual statement execution. After thinking about it for a while I realized however that there are some interesting side effects possible, but it depends on how you actually use AUTOTRACE.

Using Default AUTOTRACE

So in order to see what AUTOTRACE does behind the scenes I've decided to trace AUTOTRACE. Here is what I've tried:

set echo on timing on

alter session set tracefile_identifier = 'autotrace';

alter session set sql_trace = true;

set autotrace on

var n number

exec :n := 1

select * from dual where 1 = :n;

select * from dual where dummy = 'X';

And that's a snippet from the corresponding SQL trace file:

.
.
.
=====================
PARSING IN CURSOR #7 len=31 dep=0 uid=91 oct=3 lid=91 tim=651497870527 hv=868568466 ad='7ff0ce23638' sqlid='b9j0230twamck'
select * from dual where 1 = :n
END OF STMT
PARSE #7:c=0,e=460,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=651497870525
=====================
.
.
.
EXEC #7:c=0,e=1306,p=1,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=3752461848,tim=651497871918
FETCH #7:c=0,e=654,p=2,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3752461848,tim=651497872660
STAT #7 id=1 cnt=1 pid=0 pos=1 obj=0 op='FILTER (cr=3 pr=2 pw=0 time=0 us)'
STAT #7 id=2 cnt=1 pid=1 pos=1 obj=116 op='TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=0 us cost=2 size=2 card=1)'
FETCH #7:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3752461848,tim=651497873015
CLOSE #7:c=0,e=16,dep=0,type=0,tim=651497876511
.
.
.
=====================
PARSING IN CURSOR #9 len=79 dep=0 uid=91 oct=3 lid=91 tim=651497880846 hv=3377064296 ad='7ff0ce196a8' sqlid='1tfgxbv4nmub8'
EXPLAIN PLAN SET STATEMENT_ID='PLUS6499083' FOR select * from dual where 1 = :n
END OF STMT
PARSE #9:c=0,e=583,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=651497880843
=====================
.
.
.
=====================
PARSING IN CURSOR #2 len=74 dep=0 uid=91 oct=3 lid=91 tim=651497888595 hv=920998108 ad='7ff0cdd8b00' sqlid='3s1hh8cvfan6w'
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
END OF STMT
PARSE #2:c=0,e=264,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=651497888593
=====================
.
.
.
PARSING IN CURSOR #7 len=36 dep=0 uid=91 oct=3 lid=91 tim=651498044006 hv=3267611628 ad='7ff0cdbd0f8' sqlid='4k6g7vr1c7kzc'
select * from dual where dummy = 'X'
END OF STMT
PARSE #7:c=0,e=1071,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=272002086,tim=651498044003
EXEC #7:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=272002086,tim=651498044138
FETCH #7:c=0,e=60,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=272002086,tim=651498044289
STAT #7 id=1 cnt=1 pid=0 pos=1 obj=116 op='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us cost=2 size=2 card=1)'
FETCH #7:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=272002086,tim=651498044616
CLOSE #7:c=0,e=28,dep=0,type=0,tim=651498062083
.
.
.
=====================
PARSING IN CURSOR #2 len=84 dep=0 uid=91 oct=50 lid=91 tim=651498073656 hv=290419607 ad='7ff0cdb8a28' sqlid='5jx46tw8nywwr'
EXPLAIN PLAN SET STATEMENT_ID='PLUS6499083' FOR select * from dual where dummy = 'X'
END OF STMT
PARSE #2:c=0,e=1295,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=272002086,tim=651498073653
=====================
.
.
.
=====================
PARSING IN CURSOR #9 len=74 dep=0 uid=91 oct=3 lid=91 tim=651498076015 hv=920998108 ad='7ff0cdd8b00' sqlid='3s1hh8cvfan6w'
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
END OF STMT
PARSE #9:c=0,e=254,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=651498076013
=====================
.
.
.

So that looks pretty much like the expected behaviour I've mentioned above - AUTOTRACE executes the statements and afterwards runs an EXPLAIN PLAN to show the execution plan.

As a side note it's interesting that the SQL trace doesn't contain the queries used to gather the delta of the session statistics. The reason is simple: They are not issued by this session. SQL*Plus establishes temporarily a second session for that purpose, using one of the modes provided by the OCI allowing to create a second session on the same connection / process. You can tell this by looking at the corresponding V$SESSION.PADDR resp. the entry in V$PROCESS: For both sessions the same process entry will be used (dedicated server model). By the way I've adopted the same approach for SQLTools++, the GUI that I maintain, for all activities that potentially could interfere with the main session, like collecting session statistics delta or calling DBMS_XPLAN.DISPLAY_CURSOR.

So when using AUTOTRACE in this way the only potential threat comes from the actual execution of the statement - but this is no different from executing a statement in any other way. Of course you'll appreciate that using an odd bind value in the execution as part of the AUTOTRACE activity could theoretically lead to issues with the shared usage of such a cursor afterwards - again this is nothing that is special to AUTOTRACE.

The potentially "wrong" execution plan that can be reported via the EXPLAIN PLAN cannot cause problems for other cursors, simply because it is generated via EXPLAIN PLAN. To make this point clear, here is another script that demonstrates:

- How AUTOTRACE can lie
- How EXPLAIN PLAN cursors are unshared by default

-- Demonstrate that AUTOTRACE can lie
set echo on linesize 200 pagesize 0 trimspool on tab off

drop table t;

purge table t;

create table t
as
select
rownum as id
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000
;

exec dbms_stats.gather_table_stats(null, 't')

create index t_idx on t (id);

-- Compare the execution plan
-- reported by AUTOTRACE
-- to the one reported by DBMS_XPLAN.DISPLAY_CURSOR
set autotrace on

var n number

exec :n := 500000

select /* FIND_ME */ * from (
select * from t where id > :n
)
where rownum > 1;

set autotrace off

select /* FIND_ME */ * from (
select * from t where id > :n
)
where rownum > 1;

select * from table(dbms_xplan.display_cursor(null, null));

-- Demonstrate that EXPLAIN PLAN cursors get special treatment
-- They are unshared by default
set echo off timing off feedback off long 1000000 longchunksize 1000000

spool %TEMP%\explain_plan_example.sql

select * from (
select
sql_fulltext
from
v$sqlstats
where
sql_text like 'EXPLAIN PLAN%/* FIND_ME */%rownum > 1%'
and sql_text not like '%v$sql%'
order by
last_active_time desc
)
where
rownum <= 1
;

spool off

-- Each execution of the same parent EXPLAIN PLAN cursor
-- leads to a new child cursor
set echo on feedback on timing on pagesize 999

@%TEMP%\explain_plan_example
/

/

column sql_id new_value sql_id

select * from (
select
sql_id
from
v$sqlstats
where
sql_text like 'EXPLAIN PLAN%/* FIND_ME */%rownum > 1%'
and sql_text not like '%v$sql%'
order by
last_active_time desc
)
where
rownum <= 1
;

select
sql_id
, child_number
, explain_plan_cursor
from
v$sql_shared_cursor
where
sql_id = '&sql_id';

set serveroutput on

@sql_shared_cursor &sql_id

So if you run this script you'll see an example where AUTOTRACE gets it wrong because the plan generated via EXPLAIN PLAN is different from the actual plan used. Furthermore the plan generated via EXPLAIN PLAN can only match other EXPLAIN PLAN cursors, and on top these are then unshared by default - so no threat to any other SQL issued possible.

Here's a sample output I got from 11.2.0.1:

SQL> -- Demonstrate that AUTOTRACE can lie
SQL> set echo on linesize 200 pagesize 0 trimspool on tab off
SQL>
SQL> drop table t;

Table dropped.

Elapsed: 00:00:00.03
SQL>
SQL> purge table t;

Table purged.

Elapsed: 00:00:00.04
SQL>
SQL> create table t
2 as
3 select
4 rownum as id
5 , rpad('x', 100) as filler
6 from
7 dual
8 connect by
9 level <= 1000000
10 ;

Table created.

Elapsed: 00:00:02.38
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't')

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.40
SQL>
SQL> create index t_idx on t (id);

Index created.

Elapsed: 00:00:01.63
SQL>
SQL> -- Compare the execution plan
SQL> -- reported by AUTOTRACE
SQL> -- to the one reported by DBMS_XPLAN.DISPLAY_CURSOR
SQL> set autotrace on
SQL>
SQL> var n number
SQL>
SQL> exec :n := 500000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> select /* FIND_ME */ * from (
2 select * from t where id > :n
3 )
4 where rownum > 1;

no rows selected

Elapsed: 00:00:01.51

Execution Plan
----------------------------------------------------------
Plan hash value: 2383791439

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 5175K| 162 (0)| 00:00:02 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 50000 | 5175K| 162 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T_IDX | 9000 | | 23 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - filter(ROWNUM>1)
4 - access("ID">TO_NUMBER(:N))

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15390 consistent gets
15385 physical reads
0 redo size
304 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> select /* FIND_ME */ * from (
2 select * from t where id > :n
3 )
4 where rownum > 1;

no rows selected

Elapsed: 00:00:00.98
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null));
SQL_ID 8q13ghbwgsmkv, child number 0
-------------------------------------
select /* FIND_ME */ * from ( select * from t where id > :n ) where
rownum > 1

Plan hash value: 4220795399

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4204 (100)| |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL| T | 500K| 50M| 4204 (1)| 00:00:51 |
----------------------------------------------------------------------------

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

2 - filter(ROWNUM>1)
3 - filter("ID">:N)

22 rows selected.

Elapsed: 00:00:00.12
SQL>
SQL> -- Demonstrate that EXPLAIN PLAN cursors get special treatment
SQL> -- They are unshared by default
SQL> set echo off timing off feedback off long 1000000 longchunksize 1000000
EXPLAIN PLAN SET STATEMENT_ID='PLUS6552708' FOR select /* FIND_ME */ * from (
select * from t where id > :n
)
where rownum > 1

SQL>
SQL> @%TEMP%\explain_plan_example
SQL> EXPLAIN PLAN SET STATEMENT_ID='PLUS6552708' FOR select /* FIND_ME */ * from (
2 select * from t where id > :n
3 )
4 where rownum > 1
5
SQL> /

Explained.

Elapsed: 00:00:00.00
SQL>
SQL> /

Explained.

Elapsed: 00:00:00.00
SQL>
SQL> column sql_id new_value sql_id
SQL>
SQL> select * from (
2 select
3 sql_id
4 from
5 v$sqlstats
6 where
7 sql_text like 'EXPLAIN PLAN%/* FIND_ME */%rownum > 1%'
8 and sql_text not like '%v$sql%'
9 order by
10 last_active_time desc
11 )
12 where
13 rownum <= 1
14 ;

SQL_ID
-------------
ctms62wkwp7nz

1 row selected.

Elapsed: 00:00:00.03
SQL>
SQL> select
2 sql_id
3 , child_number
4 , explain_plan_cursor
5 from
6 v$sql_shared_cursor
7 where
8 sql_id = '&sql_id';

SQL_ID CHILD_NUMBER E
------------- ------------ -
ctms62wkwp7nz 0 N
ctms62wkwp7nz 1 Y
ctms62wkwp7nz 2 Y

3 rows selected.

Elapsed: 00:00:00.03
SQL>
SQL> set serveroutput on
SQL>
SQL> @sql_shared_cursor &sql_id
SQL> declare
2 c number;
3 col_cnt number;
4 col_rec dbms_sql.desc_tab;
5 col_value varchar2(4000);
6 ret_val number;
7 begin
8 c := dbms_sql.open_cursor;
9 dbms_sql.parse(c,
10 'select q.sql_text, s.*
11 from v$sql_shared_cursor s, v$sql q
12 where s.sql_id = q.sql_id
13 and s.child_number = q.child_number
14 and q.sql_id = ''&1''',
15 dbms_sql.native);
16 dbms_sql.describe_columns(c, col_cnt, col_rec);
17
18 for idx in 1 .. col_cnt loop
19 dbms_sql.define_column(c, idx, col_value, 4000);
20 end loop;
21
22 ret_val := dbms_sql.execute(c);
23
24 while(dbms_sql.fetch_rows(c) > 0) loop
25 for idx in 1 .. col_cnt loop
26 dbms_sql.column_value(c, idx, col_value);
27 if col_rec(idx).col_name in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS',
28 'CHILD_NUMBER', 'SQL_TEXT') then
29 dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
30 ' = ' || col_value);
31 elsif col_value = 'Y' then
32 dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
33 ' = ' || col_value);
34 end if;
35 end loop;
36 dbms_output.put_line('--------------------------------------------------');
37 end loop;
38
39 dbms_sql.close_cursor(c);
40 end;
41 /
SQL_TEXT = EXPLAIN PLAN SET STATEMENT_ID='PLUS6552708' FOR select /* FIND_ME */ * from ( select * from t where id > :n ) where rownum > 1
SQL_ID = ctms62wkwp7nz
ADDRESS = 000007FF0DD90180
CHILD_ADDRESS = 000007FF0DD87E70
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = EXPLAIN PLAN SET STATEMENT_ID='PLUS6552708' FOR select /* FIND_ME */ * from ( select * from t where id > :n ) where rownum > 1
SQL_ID = ctms62wkwp7nz
ADDRESS = 000007FF0DD90180
CHILD_ADDRESS = 000007FF0DCD0D10
CHILD_NUMBER = 1
EXPLAIN_PLAN_CURSOR = Y
--------------------------------------------------
SQL_TEXT = EXPLAIN PLAN SET STATEMENT_ID='PLUS6552708' FOR select /* FIND_ME */ * from ( select * from t where id > :n ) where rownum > 1
SQL_ID = ctms62wkwp7nz
ADDRESS = 000007FF0DD90180
CHILD_ADDRESS = 000007FF0DCAAA20
CHILD_NUMBER = 2
EXPLAIN_PLAN_CURSOR = Y
--------------------------------------------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL>

Other Autotrace Options

The perhaps less expected aspect comes into the picture if you attempt to use AUTOTRACE differently - there are various options and when using a particular combination AUTOTRACE doesn't really execute the statement but reports only the execution plan, so if you change the first example above from:

SET AUTOTRACE ON

to

SET AUTOTRACE TRACEONLY EXPLAIN

then have a close look at the SQL trace generated:

.
.
.
=====================
PARSING IN CURSOR #2 len=45 dep=0 uid=91 oct=3 lid=91 tim=416642144779 hv=3626603586 ad='7ff13a1c8b0' sqlid='9pj321gc2m522'
select /* FIND_ME */ * from dual where 1 = :n
END OF STMT
PARSE #2:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3752461848,tim=416642144777
CLOSE #2:c=0,e=14,dep=0,type=0,tim=416642145372
=====================
.
.
.
=====================
PARSING IN CURSOR #3 len=93 dep=0 uid=91 oct=3 lid=91 tim=416642148753 hv=2987003528 ad='7ff13cd8ea0' sqlid='fu0myxft0n3n8'
EXPLAIN PLAN SET STATEMENT_ID='PLUS6510526' FOR select /* FIND_ME */ * from dual where 1 = :n
END OF STMT
PARSE #3:c=0,e=689,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=416642148749
=====================
.
.
.
=====================
PARSING IN CURSOR #6 len=50 dep=0 uid=91 oct=3 lid=91 tim=416642233676 hv=37196885 ad='7ff138c8570' sqlid='f8cyn9w13g52p'
select /* FIND_ME */ * from dual where dummy = 'X'
END OF STMT
PARSE #6:c=0,e=116,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=272002086,tim=416642233673
CLOSE #6:c=0,e=32,dep=0,type=0,tim=416642237105
=====================
.
.
.
=====================
PARSING IN CURSOR #3 len=98 dep=0 uid=91 oct=50 lid=91 tim=416642243694 hv=390050481 ad='7ff1374bcf8' sqlid='8vvq0ncbmzcpj'
EXPLAIN PLAN SET STATEMENT_ID='PLUS6510526' FOR select /* FIND_ME */ * from dual where dummy = 'X'
END OF STMT
PARSE #3:c=0,e=1261,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=272002086,tim=416642243691
=====================
.
.
.

Can you spot the difference? SQL*Plus now only parses the SQL before actually running the EXPLAIN PLAN command.

Let's see what happens if the second example from above gets executed with the AUTOTRACE TRACEONLY EXPLAIN option:

-- Demonstrate that AUTOTRACE TRACEONLY EXPLAIN
-- can cause problems for other SQL executions
set echo on linesize 200 pagesize 0 trimspool on tab off

drop table t;

purge table t;

create table t
as
select
rownum as id
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000
;

exec dbms_stats.gather_table_stats(null, 't')

create index t_idx on t (id);

set autotrace traceonly explain

var n number

exec :n := 500000

select /* FIND_ME */ * from (
select * from t where id > :n
)
where rownum > 1;

set autotrace off

select /* FIND_ME */ * from (
select * from t where id > :n
)
where rownum > 1;

-- Now the execution plan generated by the PARSE call issued by SQL*Plus
-- will be re-used by the subsequent executions
select * from table(dbms_xplan.display_cursor(null, null));

Here's again a sample output from 11.2.0.1:

SQL> drop table t;

Table dropped.

SQL>
SQL> purge table t;

Table purged.

SQL>
SQL> create table t
2 as
3 select
4 rownum as id
5 , rpad('x', 100) as filler
6 from
7 dual
8 connect by
9 level <= 1000000
10 ;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't')

PL/SQL procedure successfully completed.

SQL>
SQL> create index t_idx on t (id);

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> var n number
SQL>
SQL> exec :n := 500000

PL/SQL procedure successfully completed.

SQL>
SQL> select /* FIND_ME */ * from (
2 select * from t where id > :n
3 )
4 where rownum > 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2383791439

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 5175K| 162 (0)| 00:00:02 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 50000 | 5175K| 162 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T_IDX | 9000 | | 23 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - filter(ROWNUM>1)
4 - access("ID">TO_NUMBER(:N))

SQL>
SQL> set autotrace off
SQL>
SQL> select /* FIND_ME */ * from (
2 select * from t where id > :n
3 )
4 where rownum > 1;

no rows selected

SQL>
SQL> -- Now the execution plan generated by the PARSE call issued by SQL*Plus
SQL> -- will be re-used by the subsequent executions
SQL> select * from table(dbms_xplan.display_cursor(null, null));
SQL_ID 8q13ghbwgsmkv, child number 0
-------------------------------------
select /* FIND_ME */ * from ( select * from t where id > :n ) where
rownum > 1

Plan hash value: 2383791439

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 162 (100)| |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 50000 | 5175K| 162 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T_IDX | 9000 | | 23 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - filter(ROWNUM>1)
4 - access("ID">:N)

23 rows selected.

SQL>

So that's interesting: By using the TRACEONLY EXPLAIN option I now ended up with a potentially "wrong" execution plan that is actually eligible for sharing with other executions.

What surprised me most was the fact that I expected a bind variable type mismatch (CHAR vs. NUMBER, check the "Predicate Information" section) between the parse and the execution and therefore a re-optimization that actually peeked at the bind variables rather than re-using and sharing the existing cursor, but obviously the cursor was eligible for sharing. Very likely this is due to the fact that the parse call didn't actually bind any variables hence the mentioned mismatch wasn't possible.

Summary

So in summary I think what can be said is this:

- Don't use AUTOTRACE if you want to get the actual execution plan

- The potentially "wrong" execution plans reported by AUTOTRACE usually do not represent a threat because these are EXPLAIN PLAN cursor

- The potential threat of AUTOTRACE variants that actually execute the statement is the fact that the plan used by this actual execution is definitely eligible for sharing with other executions, but this no different from any other execution, so nothing special about AUTOTRACE here either

- There is a potential threat when using the AUTOTRACE TRACEONLY EXPLAIN option - the PARSE only but not execute behaviour could leave undesirable cursors behind that are eligible for sharing. This applies in particular to SQL statements using bind variables

Ouch!

Here’s a set of Instance Activity stats I’ve never seen before, and I’d rather never see again. From an active standby running 11.1.0.7 on AIX:

select
        name, value
from    v$sysstat
where
        name in (
                'consistent gets - examination',
                'consistent gets',
                'session logical reads'
        )
or      name like 'transaction tables%'
;

NAME                                                                            VALUE
---------------------------------------------------------------- --------------------
session logical reads                                                 102,731,023,313
consistent gets                                                       102,716,499,376
consistent gets - examination                                          98,170,595,252
transaction tables consistent reads - undo records applied             96,590,314,116
transaction tables consistent read rollbacks                                2,621,019

5 rows selected.

The instance has been up for about 60 hours – and 95% of the work it has done has been trying to find the commit times for transactions affecting blocks that are in need of cleanout. If you look at the two statistics about the transaction tables (those are the things in the undo segment header blocks) you can see that the average work done to find a commit time was a massive 48,000 visits to undo blocks.

The solution was fairly simple – kill all the reports which had been running for the last six hours, because they were the ones that were causing a problem, while simultaneously suffering most from the problem – at the time I killed the worst offender it was managing to read about 50 blocks per minute from the database, and doing about 100,000 buffer visits to undo blocks per second.

You probably won’t see this every again, but if you do, a quick check is:

select * from v$sess_io order by consistent_changes; 

Repeat a couple of times and check if any sessions are doing a very large number (viz: tens of thousands) of consistent changes per second.