Why the execution plan changed? Case study

Overview

At some point in time the execution plan of the SQL_ID etp65bryqtd2d changed. The new execution plan drops on performance from an Avg. of 0.01 seconds per execution to an Avg. of 1.5 seconds per execution.
This increase on elapsed time per execution provoke that the application can’t process work orders fast enough, the work queue started to drastically grow leading the application team to escalate a severity 1 issue.

Analysis

Execution plan changes

The execution plan changes only when a parse is performed, this because the _optim_bind_peeking parameter is on default value “TRUE”. A hard parse is required due to cursor invalidation in the shared pool; a cursor can be invalidated for some reasons but the most happening is, object change (Index rebuild, DBMS_STATS, etc.).

Rolling Cursor Invalidation

Starting with Oracle10g cursors are marked for rolling invalidation instead of marked INVALID immediately. On the next execution of the query the Oracle server will generate a random number between 0 and the value of the _optimizer_invalidation_period parameter, which has a default value of 18000, and the cursor will remain valid for this number of seconds. Upon every following execution Oracle will check if this random selected timeout has expired. If that is the case then the cursor will be hard parsed again.
Things will change without notice! Cursor invalidation has changed in the past and the reason for this change is that previously all dependent cursors were invalidated immediately after gathering new object statistics. This massive invalidation might cause a serious degradation in performance right after statistics gathering due to a high number of hard parses. Rolling cursor invalidation spreads the invalidation of cursors out over a longer period of time thereby avoiding the performance degradation caused by hard parsing the invalidated cursors.

Reference: Metalink note:557661.1

Bind variable peeking

The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.
In this case we can clearly see that the elapsed time per execution is not changing because of the bind peeking; when Oracle peeks a bad execution plan, all elapsed times are bad.

CBO trace event 10053

Behind the scenes, what is the CBO doing when it comes to how it comes up with an execution plan? This is where the 10053 trace event comes to play. Other tools or settings show us WHAT the CBO comes up with; the 10053 setting tells us HOW the CBO came to its decision (the final execution plan).

conn / as sysdba
oradebug setmypid
oradebug unlimit
oradebug event 10053 trace name context forever, level 1
…your statement here…
oradebug event 10053 trace name context off
oradebug tracefile_name

We have performed this CBO trace when the query was performing bad and when it was performing well. With this method we have 2 trace files to compare and find out what is making the CBO to take such bad plan.
Notice that this method will produce a trace file only in the hard parse stage. So, in order to force the CBO to hard parse the SQL we have run it with a comment hint.

variable N1 VARCHAR2(128)
exec :N1 :=’125′;
select /* comment*/…

After comparing both trace files we can clearly see when the CBO chose the good plan:
***********************
Best so far: Table#: 1  cost: 7.3522  card: 3973.0000  bytes: 218515
             Table#: 7  cost: 16.2850  card: 9.0178  bytes: 612
             Table#: 2  cost: 21.6884  card: 33.7888  bytes: 3094
             Table#: 5  cost: 48.9215  card: 3565.0298  bytes: 406410
             Table#: 4  cost: 1475.7155  card: 3565.4846  bytes: 556140
             Table#: 0  cost: 1483.2886  card: 3559.8976  bytes: 644360
             Table#: 6  cost: 2195.7103  card: 3599.4022  bytes: 770186
             Table#: 3  cost: 2197.9000  card: 3599.4022  bytes: 820572
***********************
In the other hand, the trace of the bad plan is having a huge cost:
***********************
Best so far: Table#: 1  cost: 7.3510  card: 3928.0000  bytes: 216040
             Table#: 7  cost: 16.2836  card: 8.9798  bytes: 612
             Table#: 2  cost: 21.6870  card: 32.6678  bytes: 3003
             Table#: 5  cost: 153.8561  card: 20168.2755  bytes: 2299152
             Table#: 4  cost: 8225.5481  card: 20170.8485  bytes: 3146676
             Table#: 0  cost: 8233.2391  card: 20139.2414  bytes: 3645159
             Table#: 6  cost: 12263.4248  card: 20362.7290  bytes: 4357682
             Table#: 3  cost: 12266.6610  card: 20362.7290  bytes: 4642764
***********************
In the trace files we can clearly see that cost of table#5 messed up our total cost. Why is that?
***************
Now joining: TABLE5[TL2]#5
***************
NL Join
  Outer table: Card: 32.67  Cost: 21.69  Resp: 21.69  Degree: 1  Bytes: 91
  Inner table: TABLE5  Alias: TL2
  Access Path: TableScan
Index: TABLE5_IDX3
    resc_io: 20.00  resc_cpu: 360839
    ix_sel: 3.9216e-04  ix_sel_with_filters: 3.9216e-04
    NL Join: Cost: 153.86  Resp: 153.86  Degree: 1
      Cost_io: 152.55  Cost_cpu: 18392646
      Resp_io: 152.55  Resp_cpu: 18392646
****** finished trying bitmap/domain indexes ******
  Best NL cost: 153.86
          resc: 153.86 resc_io: 152.55 resc_cpu: 18392646
          resp: 153.86 resp_io: 152.55 resp_cpu: 18392646
Join Card:  20168.28 = outer (32.67) * inner (1574304.00) * sel (3.9216e-04)
Join Card – Rounded: 20168 Computed: 20168.28
Index selectivity on TABLE5_IDX3 is very low (3.9216e-04) compared with the good plan (6.7020e-05).

Index selectivity

After looking into the Index selectivity, the question arises; why the Index selectivity is low?
B*TREE Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table’s rows. This value may be higher in situations where all data can be retrieved from an index, or where the indexed columns can be used for joining to other tables.
The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index. The ideal selectivity is 1. Such selectivity can be reached only by unique indexes on NOT NULL columns.

Selectivity = Distinct Values / Total Number Rows

With this measure we can correlate the importance of the statistics in the objects. If we gather statistics only on the table, the total number of rows will change; leading to drastically change the Index selectivity. In the same manner, if we only gather statistics on the Index, the number of distinct values will change and the selectivity will dramatically change.
Apart of that, the accuracy of the statistics will also play an important role. The accurate the statistics, the accurate the Index selectivity.

Suggestions – How to fix this issue?

The suggestions came from the percept that Index selectivity is playing a bad game on this particular SQL_ID.
        As table and its indexes have a close relationship on how the CBO calculates the cost, we must gather statistics on the table and its indexes in the same time. This will avoid the Index selectivity to drastically change.
       When an index is rebuilt, we must gather statistics of the table too.
       Review the accuracy of the statistics. In some cases AUTO_SAMPLE_SIZE is not giving a good sample.

Some test on this SQL_ID have proven that gathering statistics to the TABLE5 table and its indexes with an estimate percent of 50%, plus invalidating the cursor immediately solved the issue.
The CBO was able to pick the good execution plan because the index selectivity was good enough.

Thanks,
Alfred

CRSCTL command examples

CRSCTL is an interface between you and Oracle Clusterware, parsing and calling
Oracle Clusterware APIs for Oracle Clusterware objects.
Oracle Clusterware 11g release 2 (11.2) introduces cluster-aware commands with
which you can perform check, start, and stop operations on the cluster. You can run
these commands from any node in the cluster on another node in the cluster, or on all
nodes in the cluster, depending on the operation.
You can use CRSCTL commands to perform several operations on Oracle Clusterware,
such as:
■ Starting and stopping Oracle Clusterware resources
■ Enabling and disabling Oracle Clusterware daemons
■ Checking the health of the cluster
■ Managing resources that represent third-party applications
■ Debugging Oracle Clusterware components
■ Integrating Intelligent Platform Management Interface (IPMI) with Oracle Clusterware to provide failure isolation support and to ensure cluster integrity
Some examples:

$ crs_stat -t
Name           Type           Target    State     Host        
————————————————————
ora.DATA.dg    ora….up.type ONLINE    ONLINE    ol5-…rac1 
ora….ER.lsnr ora….er.type ONLINE    ONLINE    ol5-…rac1 
ora….N1.lsnr ora….er.type ONLINE    ONLINE    ol5-…rac1 
ora.asm        ora.asm.type   ONLINE    ONLINE    ol5-…rac1 
ora.eons       ora.eons.type  ONLINE    ONLINE    ol5-…rac1 
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora….network ora….rk.type ONLINE    ONLINE    ol5-…rac1 
ora.oc4j       ora.oc4j.type  OFFLINE   OFFLINE               
ora….SM1.asm application    ONLINE    ONLINE    ol5-…rac1 
ora….C1.lsnr application    ONLINE    ONLINE    ol5-…rac1 
ora….ac1.gsd application    OFFLINE   OFFLINE               
ora….ac1.ons application    ONLINE    ONLINE    ol5-…rac1 
ora….ac1.vip ora….t1.type ONLINE    ONLINE    ol5-…rac1 
ora….SM2.asm application    ONLINE    ONLINE    ol5-…rac2 
ora….C2.lsnr application    ONLINE    ONLINE    ol5-…rac2 
ora….ac2.gsd application    OFFLINE   OFFLINE               
ora….ac2.ons application    ONLINE    ONLINE    ol5-…rac2 
ora….ac2.vip ora….t1.type ONLINE    ONLINE    ol5-…rac2 
ora.ons        ora.ons.type   ONLINE    ONLINE    ol5-…rac1 
ora.rac.db     ora….se.type ONLINE    ONLINE    ol5-…rac2 
ora….rts.svc ora….ce.type ONLINE    ONLINE    ol5-…rac1 
ora.scan1.vip  ora….ip.type ONLINE    ONLINE    ol5-…rac1 

CRS_STAT is deprecated in 11.2, below is the new command line to check the status of the resources:
$ crsctl status resource -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.asm
               ONLINE  ONLINE       ol5-112-rac1             Started             
               ONLINE  ONLINE       ol5-112-rac2             Started             
ora.eons
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.gsd
               OFFLINE OFFLINE      ol5-112-rac1                                 
               OFFLINE OFFLINE      ol5-112-rac2                                 
ora.net1.network
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
ora.ons
               ONLINE  ONLINE       ol5-112-rac1                                 
               ONLINE  ONLINE       ol5-112-rac2                                 
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol5-112-rac1                                 
ora.oc4j
      1        OFFLINE OFFLINE                                                   
ora.ol5-112-rac1.vip
      1        ONLINE  ONLINE       ol5-112-rac1                                 
ora.ol5-112-rac2.vip
      1        ONLINE  ONLINE       ol5-112-rac2                                 
ora.rac.db
      1        ONLINE  ONLINE       ol5-112-rac2             Open                
      2        ONLINE  ONLINE       ol5-112-rac1             Open                
ora.rac.reports.svc
      1        ONLINE  ONLINE       ol5-112-rac2                                 
ora.scan1.vip
      1        ONLINE  ONLINE       ol5-112-rac1       


In order to check the health of the cluster:

$ crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
To stop all resources in the cluster:
$ crsctl stop resource -all
CRS-2500: Cannot stop resource ‘ora.gsd’ as it is not running
CRS-2500: Cannot stop resource ‘ora.oc4j’ as it is not running
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.eons’ on ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.eons’ on ‘ol5-112-rac1’
CRS-2789: Cannot stop resource ‘ora.gsd’ as it is not running on server ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.rac.db’ on ‘ol5-112-rac2’
CRS-2789: Cannot stop resource ‘ora.gsd’ as it is not running on server ‘ol5-112-rac2’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.rac.reports.svc’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.rac.reports.svc’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.rac.db’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac2’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.ol5-112-rac2.vip’ on ‘ol5-112-rac2’
CRS-2677: Stop of ‘ora.ons’ on ‘ol5-112-rac1’ succeeded
CRS-2789: Cannot stop resource ‘ora.ons’ as it is not running on server ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.ol5-112-rac2.vip’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.net1.network’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.ons’ on ‘ol5-112-rac2’ succeeded
CRS-2789: Cannot stop resource ‘ora.ons’ as it is not running on server ‘ol5-112-rac2’
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘ol5-112-rac2’
CRS-2677: Stop of ‘ora.net1.network’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.eons’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.eons’ on ‘ol5-112-rac1’ succeeded
CRS-2675: Stop of ‘ora.DATA.dg’ on ‘ol5-112-rac2’ failed
CRS-2675: Stop of ‘ora.DATA.dg’ on ‘ol5-112-rac1’ failed
CRS-2677: Stop of ‘ora.rac.db’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.rac.db’ on ‘ol5-112-rac1’ succeeded
CRS-4000: Command Stop failed, or completed with errors.
To start all resources registered in the cluster:
$ crsctl start resource -all
CRS-2501: Resource ‘ora.gsd’ is disabled
CRS-2501: Resource ‘ora.oc4j’ is disabled
CRS-2501: Resource ‘ora.gsd’ is disabled
CRS-2501: Resource ‘ora.gsd’ is disabled
CRS-2664: Resource ‘ora.asm’ is already running on ‘ol5-112-rac1’
CRS-2672: Attempting to start ‘ora.net1.network’ on ‘ol5-112-rac1’
CRS-2664: Resource ‘ora.asm’ is already running on ‘ol5-112-rac2’
CRS-2672: Attempting to start ‘ora.net1.network’ on ‘ol5-112-rac2’
CRS-2676: Start of ‘ora.net1.network’ on ‘ol5-112-rac2’ succeeded
CRS-2672: Attempting to start ‘ora.ons’ on ‘ol5-112-rac2’
CRS-2676: Start of ‘ora.net1.network’ on ‘ol5-112-rac1’ succeeded
CRS-2672: Attempting to start ‘ora.ons’ on ‘ol5-112-rac1’
CRS-2676: Start of ‘ora.ons’ on ‘ol5-112-rac2’ succeeded
CRS-2676: Start of ‘ora.ons’ on ‘ol5-112-rac1’ succeeded
CRS-2672: Attempting to start ‘ora.eons’ on ‘ol5-112-rac1’
CRS-2672: Attempting to start ‘ora.eons’ on ‘ol5-112-rac2’
CRS-2672: Attempting to start ‘ora.ol5-112-rac2.vip’ on ‘ol5-112-rac2’
CRS-2672: Attempting to start ‘ora.scan1.vip’ on ‘ol5-112-rac1’
CRS-2676: Start of ‘ora.scan1.vip’ on ‘ol5-112-rac1’ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN1.lsnr’ on ‘ol5-112-rac1’
CRS-2676: Start of ‘ora.LISTENER_SCAN1.lsnr’ on ‘ol5-112-rac1’ succeeded
CRS-2676: Start of ‘ora.ol5-112-rac2.vip’ on ‘ol5-112-rac2’ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac2’
CRS-2676: Start of ‘ora.eons’ on ‘ol5-112-rac2’ succeeded
CRS-2676: Start of ‘ora.eons’ on ‘ol5-112-rac1’ succeeded
CRS-2676: Start of ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac2’ succeeded
CRS-2672: Attempting to start ‘ora.rac.db’ on ‘ol5-112-rac2’
CRS-2676: Start of ‘ora.rac.db’ on ‘ol5-112-rac2’ succeeded
CRS-2672: Attempting to start ‘ora.rac.reports.svc’ on ‘ol5-112-rac2’
CRS-2676: Start of ‘ora.rac.reports.svc’ on ‘ol5-112-rac2’ succeeded
CRS-5702: Resource ‘ora.rac.reports.svc’ is already running on ‘ol5-112-rac2’
CRS-4000: Command Start failed, or completed with errors.
Thanks

Basic Clusterware Administration Commands

In the previous blog we talk about Oracle Clusterware basics and definitions. Now let’s talk about basic administration commands.  

  • olsnodes

The OLSNODES command provides the list of nodes and other information for all nodes participating in the cluster. The syntax for the OLSNODES command is:

olsnodes [-n] [-i] [-l] [-v] [-g] [-p]
If you issue the OLSNODES command without any command parameters, the command returns a listing of the nodes in the cluster:

# olsnodes
node1
node2
node3
node4



Table B-1 describes the options you can include on the OLSNODES command to obtain additional cluster-related information.
Table B-1 OLSNODES Command Options
Option Description
-g
Logs cluster verification information with more details.
-i
Lists all nodes participating in the cluster and includes the Virtual Internet Protocol (VIP) address assigned to each node.
-l
Displays the local node name.
-n
Lists al nodes participating in the cluster and includes the assigned node numbers.
-p
Lists all nodes participating in the cluster and includes the private interconnect assigned to each node.
-v
Logs cluster verification information in verbose mode.

  • ocrcheck

OCRCHECK utility is used to verify the OCR integrity. The OCRCHECK utility displays the version of the OCR’s block format, total space available and used space, OCRID, and the OCR locations that you have configured. OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that you have configured. It also returns an individual status for each file as well as result for the overall OCR integrity check. The following is a sample of the OCRCHECK output:

# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2528
         Available space (kbytes) :     259592
         ID                       : 1197072699
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded
Note: Run this command as root in order to enable the Cluster registry integrity check, if you run this command with a non-privileged user this will be bypassed.

  • ocrcheck -local

In Oracle Clusterware 11g release 2 (11.2), each node in a cluster has a local registry for node-specific resources, called an Oracle Local Registry (OLR), that is installed and configured when Oracle Clusterware installs OCR. Multiple processes on each node have simultaneous read and write access to the OLR particular to the node on which they reside, regardless of whether Oracle Clusterware is running or fully functional.

By default, OLR is located at Grid_home/cdata/host_name.olr on each node. Manage OLR using the OCRCHECK, OCRDUMP, and OCRCONFIG utilities as root with the -local option.

# ./ocrcheck -local
Status of Oracle Local Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2184
         Available space (kbytes) :     259936
         ID                       : 1563540278
         Device/File Name         : /u01/app/11.2.0/grid/cdata/ol5-112-rac1.olr
                                    Device/File integrity check succeeded

         Local registry integrity check succeeded

         Logical corruption check succeeded

  • crsctl

CRSCTL is an interface between you and Oracle Clusterware, parsing and calling Oracle Clusterware APIs for Oracle Clusterware objects.

Oracle Clusterware 11g release 2 (11.2) introduces cluster-aware commands with which you can perform check, start, and stop operations on the cluster. You can run these commands from any node in the cluster on another node in the cluster, or on all nodes in the cluster, depending on the operation.

CRSCTL documentation is quite extensive and can be found in the below link:
(http://docs.oracle.com/cd/E11882_01/rac.112/e16794/crsref.htm)

CRSCTL can be also used to check the state of the Voting disks:


# ./crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
—  —–    —————–                ——— ———
 1. ONLINE   911b058da40b4f96bf456abb771dddc8 (/dev/oracleasm/disks/DISK1) [DATA]
Located 1 voting disk(s).

Thanks,
Alfred

Oracle Clusterware 11gR2 Basics

In this blog will talk about Oracle Clusterware basics. It is really needed to know the basics before we can start talking about Clusterware installation, configuration and administration. The below information is an extract from the Oracle Clusterware, Administration and Deployment Guide 11g Release 2 (11.2) (http://docs.oracle.com/cd/E14072_01/rac.112/e10717.pdf).

What is Oracle Clusterware?


Oracle Clusterware enables servers to communicate with each other, so that they 
appear to function as a collective unit. This combination of servers is commonly 
known as a cluster. Although the servers are standalone servers, each server has 
additional processes that communicate with other servers. In this way the separate 
servers appear as if they are one system to applications and end users. 
Oracle Clusterware provides the infrastructure necessary to run Oracle Real 
Application Clusters (Oracle RAC). Oracle Clusterware also manages resources, such 
as virtual IP (VIP) addresses, databases, listeners, services, and so on. These resources 
are generally named ora.resource_name.host_name. Oracle does not support 
editing these resources except under the explicit direction of Oracle support. 
Additionally, Oracle Clusterware can help you manage your applications.


Oracle Clusterware has two stored components, besides the binaries: The voting disk 
files, which record node membership information, and the Oracle Cluster Registry 
(OCR), which records cluster configuration information. Voting disks and OCRs must 
reside on shared storage available to all cluster member nodes.


Oracle Clusterware uses voting disk files to provide fencing and cluster node 
membership determination. The OCR provides cluster configuration information. You can place the Oracle Clusterware files on either Oracle ASM or on shared common 
disk storage. If you configure Oracle Clusterware on storage that does not provide file 
redundancy, then Oracle recommends that you configure multiple locations for OCR 
and voting disks. The voting disks and OCR are described as follows: 

■ Voting Disks
Oracle Clusterware uses voting disk files to determine which nodes are members 
of a cluster. You can configure voting disks on Oracle ASM, or you can configure 
voting disks on shared storage.
If you configure voting disks on Oracle ASM, then you do not need to manually 
configure the voting disks. Depending on the redundancy of your disk group, an 
appropriate number of voting disks are created. 
If you do not configure voting disks on Oracle ASM, then for high availability, 
Oracle recommends that you have a minimum of three voting disks on physically 
separate storage. This avoids having a single point of failure. If you configure a 
single voting disk, then you must use external mirroring to provide redundancy.
You should have at least three voting disks, unless you have a storage device, such 
as a disk array that provides external redundancy. Oracle recommends that you do 
not use more than five voting disks. The maximum number of voting disks that is 
supported is 15. 

■ Oracle Cluster Registry
Oracle Clusterware uses the Oracle Cluster Registry (OCR) to store and manage 
information about the components that Oracle Clusterware controls, such as 
Oracle RAC databases, listeners, virtual IP addresses (VIPs), and services and any 
applications. The OCR stores configuration information in a series of key-value 
pairs in a tree structure. To ensure cluster high availability, Oracle recommends 
that you define multiple OCR locations (multiplex). In addition:
– You can have up to five OCR locations
– Each OCR location must reside on shared storage that is accessible by all of the 
nodes in the cluster
– You can replace a failed OCR location online if it is not the only OCR location
– You must update the OCR through supported utilities such as Oracle 
Enterprise Manager, the Server Control Utility (SRVCTL), the OCR 
configuration utility (OCRCONFIG), or the Database Configuration Assistant 
(DBCA) 

Oracle Clusterware Processes on Linux and UNIX Systems


Oracle Clusterware processes on Linux and UNIX systems include the following:

■ crsd: Performs high availability recovery and management operations such as 
maintaining the OCR and managing application resources. This grid infrastructure 
process runs as root and restarts automatically upon failure.

When you install Oracle Clusterware in a single-instance database environment 
for Oracle ASM and Oracle Restart, ohasd manages application resources and 
crsd is not used.

■ cssdagent: Starts, stops, and checks the status of the CSS daemon, ocssd. In 
addition, the cssdagent and cssdmonitor provide the following services to 
guarantee data integrity:
– Monitors the CSS daemon; if the CSS daemon stops, then it shuts down the 
node 
– Monitors the node scheduling to verify that the node is not hung, and shuts 
down the node on recovery from a hang.
oclskd: (Oracle Clusterware Kill Daemon) CSS uses this daemon to stop 
processes associated with CSS group members for which stop requests have come 
in from other members on remote nodes.

■ ctssd: Cluster time synchronization service daemon: Synchronizes the time on all 
of the nodes in a cluster to match the time setting on the master node but not to an 
external clock.


■ diskmon (Disk Monitor daemon): Monitors and performs I/O fencing for HP 
Oracle Exadata Storage Server storage. Because Exadata storage can be added to 
any Oracle RAC node at any time, the diskmon daemon is always started when 
ocssd starts.

■ evmd (Event manager daemon): Distributes and communicates some cluster 
events to all of the cluster members so that they are aware of changes in the 
cluster. 

evmlogger (Event manager logger): This is started by EVMD at startup. This 
reads a configuration file to determine what events to subscribe to from EVMD 
and it runs user defined actions for those events. This facility maintains backward 
compatibility only. 

■ gpnpd (Grid Plug and Play daemon): Manages distribution and maintenance of 
the Grid Plug and Play profile containing cluster definition data.

■ mdnsd (Multicast Domain Name Service daemon): Manages name resolution and 
service discovery within attached subnets.

■ ocssd (Cluster Synchronization Service daemon): Manages cluster node 
membership and runs as the oracle user; failure of this process results in a node 
restart.

■ ohasd (Oracle High Availability Services daemon): Starts Oracle Clusterware 
processes and also manages the OLR and acts as the OLR server.

In a cluster, ohasd runs as root. However, in an Oracle Restart environment, 
where ohasd manages application resources, it runs as the oracle user


Next blog -> Basic Clusterware Administration Commands

Thanks,
Alfred

Cleanup After Failed Installation Oracle Clusterware 11gR2

In this blog I will talk about how to cleanup a failed installation of the Oracle Clusterware 11gR2 in one particular node. I was installing the new Oracle Clusterware 11.2.0.1 in my home lab by follwing Tom Kyte’s instructions (http://www.oracle-base.com/articles/11g/oracle-db-11gr2-rac-installation-on-oel5-using-virtualbox.php#install_grid_infrastructure), then went through the part that the installer GUI needs us to run the root.sh scripts.



The easy thought was to run the root.sh script in both nodes of my RAC (RAC1 & RAC2) at the same time, without reading the explicit instructions “Run the script on the local node first. After successful completion, you can run the script in parallel on all other nodes.”:



The script run successful in the RAC1 node but in the RAC2 node. So when the script finished in RAC1 tried to run it again in RAC2, but the same results “the output was that the script was already ran”.

What to do next?, do I have to start over from scratch?

Surfing the web found a good & useful article from Guenadi Jilevski (http://gjilevski.com/2010/08/12/how-to-clean-up-after-a-failed-11g-crs-install-what-is-new-in-11g-r2-2/), here shows how to perform a manual cleanup in 11gR1, but also shows the new features and scripts in 11gR2.

Summarized steps:

Deconfigure Oracle Clusterware 11.2.x.x without removing the binaries:

  • Log in as root user on the node you encountered the error. Change directory to $GRID_HOME/crs/install.
          # cd $GRID_HOME/crs/install

  • Run rootcrs.pl with the -deconfig -force flags on the node you have the issue.
          # perl rootcrs.pl -deconfig -force

  • If you are deconfiguring Oracle Clusterware on all the nodes in the cluster, then you have to add the -lastnode flag on the last one in order to deconfigure OCR and Voting disks.
          # perl rootcrs.pl -deconfig -force -lastnode

After these steps, run the root.sh script again. This time it will smoothly run.

You should also want to read the complete OSS note:
How to Deconfigure/Reconfigure(Rebuild OCR) or Deinstall Grid Infrastructure [ID 1377349.1]

Thanks,
Alfredo

Historical SQL Statistics And Execution Plan Change

How many times have you received user’s phone call stating that the DB is slow?, well sometimes this has nothing to do with the DB’s overall performance itself, but with a user’s query.

Now the question is, is this query really slow?, the best way to find this out is comparing the actual execution time with the ones in the past. Here’s a SQL statement which search inside AWR repository (Caution!!! a special license is required!!!), it compares the execution plan of all SQL_ID’s against the previous snapshot.

awr_planchanges.sql

  prompt enter the number of days in the past to scan
  SET LINES 500
  SELECT A.SNAP_ID,
         BEGIN_INTERVAL_TIME,
         SQL_ID,
         HASH_VALUE1,
         HASH_VALUE2,
         VALUE1 as “ELAPSED TIME PER EXEC 1” ,
         VALUE2 as “ELAPSED TIME PER EXEC 2”,
         ROUND (CHANGE_PERCENT) as “CHANGE PERCENT”
    FROM (  SELECT SNAP_ID,
                   SQL_ID,
                   SUM (pvalue1) HASH_VALUE1,
                   SUM (pvalue2) HASH_VALUE2,
                   SUM (value1) VALUE1,
                   SUM (value2) VALUE2,
                   (SUM (VALUE1) + 1) * 100 / (SUM (VALUE2) + 1)
                      AS CHANGE_PERCENT
              FROM (SELECT snap_id,
                           0 AS snap2,
                           sql_id,
                           plan_hash_value AS pvalue1,
                           0 AS pvalue2,
                           ROUND (
                              elapsed_time_delta / executions_delta / 1000000,
                              3)
                              AS value1,
                           0 AS value2
                      FROM dba_hist_sqlstat sql
                     WHERE executions_delta > 0
                    UNION
                    SELECT snap_id + 1,
                           snap_id AS snap2,
                           sql_id,
                           0 AS pvalue1,
                           plan_hash_value AS pvalue2,
                           0 AS vaule,
                           ROUND (
                              elapsed_time_delta / executions_delta / 1000000,
                              3)
                              AS value2
                      FROM dba_hist_sqlstat sql
                     WHERE executions_delta > 0)
          GROUP BY SNAP_ID, SQL_ID) A,
         dba_hist_snapshot B
   WHERE     A.SNAP_ID = B.SNAP_ID
         AND HASH_VALUE1 > 0
         AND HASH_VALUE2 > 0
         AND HASH_VALUE1 != HASH_VALUE2
         AND BEGIN_INTERVAL_TIME > SYSDATE – (&days)
ORDER BY A.SNAP_ID
/

This is the output of the script:

SNAP_ID BEGIN_INTERVAL_TIME       SQL_ID       HASH_VALUE1 HASH_VALUE2 SEC PER EXE 1 SEC PER EXE 2 CHANGE % 
—– ———————–  ————- ———– ———– ————- ————- ——–
9547 01-OCT-12 01.00.07.841 AM   4urszd9dt9fjv  4862331523   891004645          .015          .007       101
9585 02-OCT-12 03.00.41.798 PM   4urszd9dt9fjv  4862331523   891004645          .009          .003       101
9586 02-OCT-12 04.00.36.393 PM   4urszd9dt9fjv   891004645  4862331523          .006          .009       100
9587 02-OCT-12 05.00.25.306 PM   1k30v0pyg32vu   414828074   878600859          .179          .157       102
9587 02-OCT-12 05.00.25.306 PM   dsm86bzuqtd2r  2452407222  3005749068          .048         1.37         44
9616 03-OCT-12 10.00.34.499 PM   4urszd9dt9fjv  4862331523   891004645          .018          .023       100
9621 04-OCT-12 03.00.06.979 AM   4urszd9dt9fjv   891004645  4862331523          .03           .027       100
9640 04-OCT-12 09.00.40.250 PM   4urszd9dt9fjv  4862331523   891004645          .042          .012       103
9641 04-OCT-12 10.00.22.954 PM   dfmu8nm1cscx7  3810296266  4308029399          .938          .703       114
9710 07-OCT-12 07.00.09.269 PM   4urszd9dt9fjv  4862331523   891004645          .046          .015       103
9750 09-OCT-12 11.00.59.162 AM   1k30v0pyg32vu  1634868183   414828074          .16           .155       100
9758 09-OCT-12 07.00.10.659 PM   b70xavb9wv27v  1111647858  4256287279         5.937         6.475        93

12 rows selected.

You can clearly see how the HASH_VALUE changed and the execution time as well. If the CHANGE % is above 100% means that execution time decreased, on the other hand CHANGE % below 100% means the execution time increased.

ORAganism blog is having a really good script which searches by SQL_ID.

http://oraganism.wordpress.com/2011/12/14/a-dba_hist_sqlstat-query-that-i-am-very-fond-of/ 


Happy troubleshooting,
Alfred

Same post in Spanish here!

Estadísticas históricas de SQL

¿Cuántas veces hemos recibido la llamada de un usuario indicando que la base de datos está lenta?, la mayoría de las veces no tiene que ver con el desempeño general de la base de datos, sino con la consulta que el usuario ha lanzado al RDBMS.

De aquí surge la pregunta, ¿está la consulta (SELECT) lenta realmente?; la única manera de saberlo es haciendo una comparación en el pasado. A continuación comparto una consulta SQL, que busca dentro del repositorio AWR (¡¡Tengan cuidado, ya que requiere una licencia especial!!) y obtiene los SQL ID que han sufrido modificación en su plan de ejecución.

awr_planchanges.sql


   prompt enter the number of days in the past to scan
  SET LINES 500
  SELECT A.SNAP_ID,
         BEGIN_INTERVAL_TIME,
         SQL_ID,
         HASH_VALUE1,
         HASH_VALUE2,
         VALUE1 as “ELAPSED TIME PER EXEC 1” ,
         VALUE2 as “ELAPSED TIME PER EXEC 2”,
         ROUND (CHANGE_PERCENT) as “CHANGE PERCENT”
    FROM (  SELECT SNAP_ID,
                   SQL_ID,
                   SUM (pvalue1) HASH_VALUE1,
                   SUM (pvalue2) HASH_VALUE2,
                   SUM (value1) VALUE1,
                   SUM (value2) VALUE2,
                   (SUM (VALUE1) + 1) * 100 / (SUM (VALUE2) + 1)
                      AS CHANGE_PERCENT
              FROM (SELECT snap_id,
                           0 AS snap2,
                           sql_id,
                           plan_hash_value AS pvalue1,
                           0 AS pvalue2,
                           ROUND (
                              elapsed_time_delta / executions_delta / 1000000,
                              3)
                              AS value1,
                           0 AS value2
                      FROM dba_hist_sqlstat sql
                     WHERE executions_delta > 0
                    UNION
                    SELECT snap_id + 1,
                           snap_id AS snap2,
                           sql_id,
                           0 AS pvalue1,
                           plan_hash_value AS pvalue2,
                           0 AS vaule,
                           ROUND (
                              elapsed_time_delta / executions_delta / 1000000,
                              3)
                              AS value2
                      FROM dba_hist_sqlstat sql
                     WHERE executions_delta > 0)
          GROUP BY SNAP_ID, SQL_ID) A,
         dba_hist_snapshot B
   WHERE     A.SNAP_ID = B.SNAP_ID
         AND HASH_VALUE1 > 0
         AND HASH_VALUE2 > 0
         AND HASH_VALUE1 != HASH_VALUE2
         AND BEGIN_INTERVAL_TIME > SYSDATE – (&days)
ORDER BY A.SNAP_ID
/

La salida del script será algo parecido a esto:


SNAP_ID BEGIN_INTERVAL_TIME       SQL_ID       HASH_VALUE1 HASH_VALUE2 SEC PER EXE 1 SEC PER EXE 2 CHANGE % 
—– ———————–  ————- ———– ———– ————- ————- ——–
9547 01-OCT-12 01.00.07.841 AM   4urszd9dt9fjv  4862331523   891004645          .015          .007       101
9585 02-OCT-12 03.00.41.798 PM   4urszd9dt9fjv  4862331523   891004645          .009          .003       101
9586 02-OCT-12 04.00.36.393 PM   4urszd9dt9fjv   891004645  4862331523          .006          .009       100
9587 02-OCT-12 05.00.25.306 PM   1k30v0pyg32vu   414828074   878600859          .179          .157       102
9587 02-OCT-12 05.00.25.306 PM   dsm86bzuqtd2r  2452407222  3005749068          .048         1.37         44
9616 03-OCT-12 10.00.34.499 PM   4urszd9dt9fjv  4862331523   891004645          .018          .023       100
9621 04-OCT-12 03.00.06.979 AM   4urszd9dt9fjv   891004645  4862331523          .03           .027       100
9640 04-OCT-12 09.00.40.250 PM   4urszd9dt9fjv  4862331523   891004645          .042          .012       103
9641 04-OCT-12 10.00.22.954 PM   dfmu8nm1cscx7  3810296266  4308029399          .938          .703       114
9710 07-OCT-12 07.00.09.269 PM   4urszd9dt9fjv  4862331523   891004645          .046          .015       103
9750 09-OCT-12 11.00.59.162 AM   1k30v0pyg32vu  1634868183   414828074          .16           .155       100
9758 09-OCT-12 07.00.10.659 PM   b70xavb9wv27v  1111647858  4256287279         5.937         6.475        93

12 rows selected.

Claramente se puede ver el cambio en el Hash Value y el cambio en el tiempo de ejecución del SQL. Si el Change % es menor a 100, significa que el tiempo de ejecución se redujo, por el contrario si es mayor a 100 significa que el tiempo de ejecución aumentó.

Nota: Este script solo compara el Snapshot contra el Snapshot anterior.

El Blog de ORAganism, contiene un script bastante bueno, el cual busca la información por SQL ID.

http://oraganism.wordpress.com/2011/12/14/a-dba_hist_sqlstat-query-that-i-am-very-fond-of/

Saludos,
Alfred

Bienvenid@,

“hello, world”



Utilizando la salida del famoso programa en C de Brian Kernighan, doy vida a este Blog, el cual abarcará temas interesantes sobre problemas cotidianos y otros no tantos sobre la administración y configuración del RDBMS de Oracle.

¿Quién soy yo?

Mi nombre es Alfredo Krieg, llevo más de 6 años administrando bases de datos Oracle y algunos otros productos. Durante este tiempo me he encontrado con diversos problemas, algunos sencillos y otros no tanto; me apasiona el área de Performance Tuning. De aquí la necesidad de documentar las diversas soluciones a los problemas que me he topado y así mismo ayudar a otros DBA’s que se encuentren el mismo problema.


¿Te interesa mi trabajo?, ¿Quieres contactarme?

Saludos,
Alfred