Dr.Oracle

Another Performance mystery unfolded or not?

2011-01-05T05:10:00.000-08:00

FULL TABLE SCANS on LAST PARTITION of RANGE BASED PARTITIONED TABLE
----------------------------------------------------------------------------------------------------

A few days back I came accross a rather strange scenario. Our application team started complaining of BAD PERFORMANCE on a 3 node RAC server which until a day before was hardly 50% loaded. Transactions which used to complete in a second had started taking more than 5 seconds which, to a normal application may not sound as bad as it seems but for a financial application it pretty damn is...

Anyways, upon scrutizing the system I realized that major waits on DB were due to FULL TABLE SCANS on a 70GB WEEKLY partitioned table and the frequency of the query doing FTS was approx. 50/sec.

Surprisingly the relevant indexes were present but not being used despite the existence of latest statistics. Upon further investigation it was dug out that the data was now being inserted in last partition of table whose HIGH_VALUE in dba_partitions was MAXVALUE since the weekly partitions had exhausted.

...SURPRISE SURPRISE...

The resolution was to SPLIT the MAXVALUE partition into valid current week partition. After having done that the tables turned and FULL TABLE SCANS vanished...

Strange.. I did not come across a single document in Oracle which could relate our experience to any particular feature/constraint of Oracle. But somehow the optimizer refused to use EXISTING INDEXES when data was being queried from LAST PARITION (MAXVALUE).

SECRETS of ORACLE STREAMS REVEALED

2010-11-25T05:24:00.000-08:00

I recently had an exhausting experience to implement STREAMS for a HIGH END TRANSACTIONAL SYSTEM hosted on Oracle 10g RAC which was not less than a CHALLENGE.

Surprisingly I came across alot of issues which had not been published before and

The Business requirement was simple:
Make a SINGLE INSTANCE REPLICA of PRODUCTION SYSTEM running on TWO NODE RAC with near to real time REPLICATION of TWO SCHEMAS without impacting the PERFORMANCE of Production DB in any way. A challenge we took HEAD ON. Ultimately DOWNSTREAM CAPTURE using ARCHIVELOG SHIPPING was chosen as the OPTIMAL MECHANISM to acheive the target. Here under I will elaborate the never before documented steps to succesfully implement this solution.

Oracle documentation specifies you to create THREE BASIC COMPONENTS for STREAMS :
1- CAPTURE PROCESS - To capture the changes from SOURCE DB
2- PROPAGATION PROCESS - To propagate the captured changes tp APPLY site
3 APPLY PROCESS - To apply captures changes on TARGET DB

The above processes are hosted on 2 QUEUES : CAPTURE QUEUE which hosts CAPTURE and PROPAGATION PROCESS and APPLY QUEUE which hosts APPLY PROCESS.

There is a fundamental FLAW with the above configuration for the CHOSEN DEPLOYMENT : DOWNSTREAM CAPTURE using ARCHIVE LOG SHIPPING for TWO SCHEMAS.

Since the CAPTURE and APPLY process will both be hosted on TARGET DB so you do not require a PROPAGATION PROCESS as the latter is used only in the case where CAPTRUE PROCESS in hosted on SOURCE DB, a configuration for REAL TIME replication using LOG WRITER. Hence for our configuration we will OMIT the PROPAGATION PROCESS. Further we DO NOT require seperate queues to HOST CAPTURE and APPLY PROCESS. A single queue is sufficient to host both CAPTURE and APPLY PROCESS.

The above change , once in place, will ensure a succesful replication for more than one SCHEMA, without this configuration STREAMS REPLICATION will NEVER WORK for DOWNSTREAM CAPTURE using ARCHIVE LOG CAPTURE.

Secondly , the scary part for DBAs is to figure out how RAC will behave with STREAMS , what happens if one node fails, what impact would that have on the STREAMS configuration. Well, good news is that RAC handles this anomaly itself.
You just need to add the TNS entry of the TARGET DB on all nodes of RAC CLUSTER

Wait for my next article wherein I will explain the steps required to configure the STREAMS and some inetresting solutions to challenges faced in PRODUCTION STREAMS DEPLOYMENT.

Oracle Performance Tuning Secrets Revealed - Insight to BIND PEEKING

2010-09-17T02:55:00.000-07:00

The influence of BIND PEEKING (probability of Optimizer choosing an execution plan based on values of Bind Variables) is often underestimated in PERFORMANCE RELATED ISSUES by even DBAs and most often than not usually developers or flawed application design is blamed for poor performance of a application screen (process) which had been working perfect before.
Behind all this so called confusing behaviour of optimizer there is a LEGITIMATE reason...
Oracle Optimizer builds an execution plan for a query based on the values passed to its variables at run time and locks the EXECUTION PLAN in memory which is then referred to as a CURSOR. For subsequent execution of the same query the Optimzer uses the same CURSOR to execute the query thereby reducing PARSE TIMES by avoiding HARD PARSES.
It is quite possible that for a particular set of variables the same execution plan is not suitable hence the end user start to complain of SLOW performance.
Solution to such a scenario is very simple once your DBA , with the help of the architect has been able to figgure out the possible combination of variables and the data distribution in the base tables. Thats why I always say that a GOOD DBA is a GOOD ARCHITECT since he/she should be completely aware of the ARCHITECTURE of the system.
Now with the consent of Application Operation guys the DBA invalidates the relevant CURSOR,various methods to do so; common amongst them prior to 10.2.0.4 is to grant or revoke privileges on the MAIN TABLE involved in the query , so as to influence the Optimizer to build a FRESH execution plan upon the NEXT execution of this query.
The DBA can repeat this excerse with various combination of VARIABLES possible for the BIND VARIABLES present in the contending QUERY until an OPTIMAL PLAN is achieved.

DOWNSIDE to invalidating a CURSOR:
HARD PARSE occurs for all those queries who are using the TABLE which has been used by DBA to purge the CURSOR. Consequently all the CURSORS involving the table gets invalidated and your ENTRPRISE GRID shows a SPIKE which represents the HARD PARSING of multiple queries, this usually effects the performance of application and the end users must be ready for this effect which usually lasts for a few minutes. Best time to try this exercise on a production machine is in off peak hours.

Background is very simple. Consider the following example
Table COMPANY has two columns NAME , Value SULMAN appears 100000 times and value TOM appears 10 times.
imagine a user running following following command:
A)select * from COMPANY where NAME = 'TOM'
Second user runs the following command
B)select * from COMPANY where NAME = 'SULMAN'
Second user now starts to complain that performance of query has drastically degraded. REASON: Initially Optimizer chose to use INDEX RANGE SCAN for QUERY A and cached the plan in memory, when user 2 ran QUERY B, an optimal plan would have been a FULL TABLE SCAN since the user is querying more than 90% of total rows of the table , however due to the previous PLAN being cached in memory it is compelled to do a INDEX RANGE SCAN, hence a BAD PERFORMANCE experience.

However, it is NOT ALWAYS necessary that the NEW EXECUTION PLAN is a suitable one, or that the OPTIMIZER engine has infact generated TWO plans in memory for a particluar query.
In such situations the LINE OF ACTION for a DBA varies... Stay updated to look out for the solution to tackle such situations.... :)

#############################################################################################################################

This demonstration assumes that the DBA has already pinpointed the CONTENDING query which is causing the problem.
select executions, invalidations, sql_id, plan_hash_value from v$sql where sql_id = 'cuxp1ra2nkshf';

<><><><><><>

EXECUTIONS	INVALIDATIONS	SQL_ID	PLAN_HASH_VALUE
0	0	cuxp1ra2nkshf	3301931525
3295	0	cuxp1ra2nkshf	3839839006

By looking at both the execution plan after analyzing the dynamics of the underlying tables you determine that the plan against which EXECUTIONS is shown as null is a better plan and should be used instead.
select * from table(dbms_xplan.display_cursor('cuxp1ra2nkshf',null))
-----------------------------------------------------------
SQL_ID cuxp1ra2nkshf, child number 0
-------------------------------------
Plan hash value: 3301931525
---------------------------------------------------------------------------------------------------
| Id | Operation                       | Name            | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |       |       |     5 (100)|          |
|   1 | SORT ORDER BY                  |                 |     1 |   694 |     5 (20)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER            |                 |     1 |   694 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER           |                 |     1 |   553 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER          |                 |     1 |   532 |     2   (0)| 00:00:01 |
|* 5 |      TABLE ACCESS BY INDEX ROWID| CX_MNP_NPR      |     1 |   504 |     1   (0)| 00:00:01 |
|* 6 |       INDEX RANGE SCAN          | CX_MNP_NPR_I6   |     5 |       |     1   (0)| 00:00:01 |
|* 7 |      INDEX RANGE SCAN           | S_POSTN_M50     |     1 |    28 |     1   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID | S_USER          |     1 |    21 |     1   (0)| 00:00:01 |
|* 9 |      INDEX UNIQUE SCAN          | S_USER_P1       |     1 |       |     1   (0)| 00:00:01 |
| 10 |    TABLE ACCESS BY INDEX ROWID | CX_MNP_NPR_X    |     1 |   141 |     1   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN            | CX_MNP_NPR_X_U1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

SQL_ID cuxp1ra2nkshf, child number 1
-------------------------------------
Plan hash value: 3839839006

---------------------------------------------------------------------------------------------------
| Id | Operation                       | Name            | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |       |       |     5 (100)|          |
|   1 | SORT ORDER BY                  |                 |     1 |   694 |     5 (20)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER            |                 |     1 |   694 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER           |                 |     1 |   553 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER          |                 |     1 |   532 |     2   (0)| 00:00:01 |
|* 5 |      TABLE ACCESS BY INDEX ROWID| CX_MNP_NPR      |     1 |   504 |     1   (0)| 00:00:01 |
|* 6 |       INDEX SKIP SCAN           | CX_MNP_NPR_I3   |     1 |       |     1   (0)| 00:00:01 |
|* 7 |      INDEX RANGE SCAN           | S_POSTN_M50     |     1 |    28 |     1   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID | S_USER          |     1 |    21 |     1   (0)| 00:00:01 |
|* 9 |      INDEX UNIQUE SCAN          | S_USER_P1       |     1 |       |     1   (0)| 00:00:01 |
| 10 |    TABLE ACCESS BY INDEX ROWID | CX_MNP_NPR_X    |     1 |   141 |     1   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN            | CX_MNP_NPR_X_U1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Purge the relevant cursor through following command;
grant update on table to sulmandba; (name of the table should ideally be the one which is referenced in PREDICATE with bind variables)
Run the following query to ensure that the cursor has been invalidated. (the value INVALIDATION COLUMN would increment)
select executions, invalidations, sql_id, plan_hash_value from v$sql where sql_id = 'cuxp1ra2nkshf';
<><><><><><>

EXECUTIONS	INVALIDATIONS	SQL_ID	PLAN_HASH_VALUE
0	0	cuxp1ra2nkshf	3301931525
3295	1	cuxp1ra2nkshf	3839839006

Now re-execute the query from application process and see if the NEW CURSOR with NEW EXECUTION PLAN has been created.

EXECUTIONS	INVALIDATIONS	SQL_ID	PLAN_HASH_VALUE
2	0	cuxp1ra2nkshf	3301931525

dbca unable to open display on HP-UX Itanium mv 11.31

2010-09-16T00:02:00.000-07:00

Workaround ....

take backup of existing dbca file ($ORACLE_HOME/bin/dbca)

open dbca file and change path of JRE_DIR to point towards JAVA installation bundled with OS

vi dbca

JRE_DIR=/opt/java1.4/jre

Enjoy :)

KNOWN ISSUE: JRE bundled with ORacle 10g is incompatible with HO-UXI v11.31 hence to display any console you have to explicitly use the JRE bundled with the OS.

Unable to install Oracle 10g on HP-UX Itanium 11.31

2010-09-15T03:10:00.000-07:00

Today was a pleasant day since the task on hand was the one given to novice DBAs, however my assumption soon turned out to be a HUGE misconception....

While installing Oracle Database 10g base release on HP Unix Itanium Server with OS kernel version 11.31 we were unable to start the OUI and got following error:

Checking Operating System Version Must be B.11.23 Actual B.11.31

After hours of digging through the the documentation available on the internet specifies the following reason for it and proposes a workaround which unfortunately is not entirely correct...

The installer attempts to make sure that you are using a certified combination of product and OS.

If you want to override this, try using:

./runInstaller -ignoreSysPrereqs

To quote the documentation:
__________________________________________
Install following operating system patch.
PHKL_35936

To ignore the system pre-requisite test during installation use-ignoreSysPreReqs flag with runInstaller.
__________________________________________

It is telling you that install the required OS PATCH and when you run the Oracle installer use the -ignoreSysPrereqs option on the command line:

./runInstaller -ignoreSysPrereqs

However even after installing the required Kernel Patch and executing the runInstaller with the required switch the OUI failed to start...

Further investigation relvealed the following and a FIX for it.

This is due to internal OUI bug with Montecito Processor.

What it means is that the JRE version bundled with Oracle Installation is incompatible to invoke the OUI while running on a machine hosting MONTECITO PROCESSOR. It is worth to mention that it is not possible for any System Admin to diagnoze the exact Processor Family from command line. Your Harware vendor needs to be contacted for this diagnoses..

However, this provided the much required solution to our problem... and we were able to invoke OUI by using following command.

./runInstaller -jreLoc <path of JAVA1.4>/jre -ignoreSysPrereqs

Lesson learnt: never underestimate any task :)

Oracle RAC Architecture

2010-09-14T05:34:00.000-07:00

A peek into RAC Architecture

Let's begin with a brief overview of RAC architecture.

A cluster is a set of 2 or more machines (nodes) that share or coordinate resources to perform the same task.
A RAC database is 2 or more instances running on a set of clustered nodes, with all instances accessing a shared set of database files.
Depending on the O/S platform, a RAC database may be deployed on a cluster that uses vendor clusterware plus Oracle's own clusterware (Cluster Ready Services), or on a cluster that solely uses Oracle's own clusterware.

Thus, every RAC sits on a cluster that is running Cluster Ready Services. srvctl is the primary tool DBAs use to configure CRS for their RAC database and processes.

Cluster Ready Services and the OCR

Cluster Ready Services, or CRS, is a new feature for 10g RAC. Essentially, it is Oracle's own clusterware. On most platforms, Oracle supports vendor clusterware; in these cases, CRS interoperates with the vendor clusterware, providing high availability support and service and workload management. On Linux and Windows clusters, CRS serves as the sole clusterware. In all cases, CRS provides a standard cluster interface that is consistent across all
platforms.
CRS consists of four processes (crsd, occsd, evmd, and evmlogger) and two disks: the Oracle Cluster Registry (OCR), and the voting disk.
CRS manages the following resources:

The ASM instances on each node
Databases
The instances on each node
Oracle Services on each node
The cluster nodes themselves, including the following processes, or "nodeapps":
- VIP
- GSD
- The listener
- The ONS daemon

CRS stores information about these resources in the OCR. If the information in the OCR for one of these resources becomes damaged or inconsistent, then CRS is no longer able to manage that resource. Fortunately, the OCR automatically backs itself up regularly and frequently.

Interacting with CRS and the OCR: srvctl

srvctl is the tool Oracle recommends that DBAs use to interact with CRS and the cluster registry. Oracle does provide several tools to interface with the cluster registry and CRS more directly, at a lower level, but these tools are deliberately undocumented and intended only for use by Oracle Support. srvctl, in contrast, is well documented and easy to use. Using other tools to modify the OCR or manage CRS without the assistance of Oracle Support runs the risk of damaging the OCR.

Using srvctl

CLONE ORACLE RAC 10g

2010-09-14T05:31:00.000-07:00

Follow these easy steps to clone your existing RAC environment on the same machine hosted on ASM.

In this exercise we duplicate the Two node RAC-instance into a single instance Database and then put that single instace into a two node RAC database

1) Created passwdfile for the AUX_DB instace. This file is at $ORACLE_HOME/dbs

2)Updated the parameter file.

Following Two parameter must be included
*.db_file_name_convert='+DATADG/source/','+DATADG/clone/'
*.log_file_name_convert='+DATADG/source/','+DATADG/clone/'
Remove RAC specific paramaters from the parameter file.

3) place the AUXILARY instance in no-mount mode

Using RMAN restore the backup
$ rman target sys/xxxx@clone;
RMAN> connect AUXILIARY SYS/xxxx
connected to auxiliary database (not started)
RMAN> connect catalog username/password@service_catalogDB
connected to recovery catalog database

RMAN> run {
2> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL disk2 DEVICE TYPE DISK;
4> ALLOCATE CHANNEL disk3 DEVICE TYPE DISK;
5> ALLOCATE CHANNEL disk4 DEVICE TYPE DISK;
6> ALLOCATE AUXILIARY CHANNEL <ANY_NAME> DEVICE TYPE DISK;
7> DUPLICATE TARGET DATABASE TO <CLONE_DB_NAME>
8>   LOGFILE
9>   GROUP 1 ('+DATADG/clone/redo01.log') SIZE 512M,
10>   GROUP 2 ('+DATADG/clone/redo02.log') SIZE 512M,
11>   GROUP 3 ('+DATADG/clone/redo03.log') SIZE 512M,
12>   GROUP 4 ('+DATADG/clone/redo04.log') SIZE 512M;
13> RELEASE CHANNEL disk1;
14> RELEASE CHANNEL disk2;
15> RELEASE CHANNEL disk3;
16> RELEASE CHANNEL disk4;
17> release channel <NAME_GIVEN_IN_LINE_6>;
18> }

4) during Clone you may get the error
RMAN-06136: ORACLE error from auxiliary database: ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
its solution is to add the following hidden parameter in the parameter file
_no_recovery_through_resetlogs=TRUE
shutdown the instance, and open with RESETLOGS option.

5) Before moving the single instance i had to move it into TWO node RAC instance, so first i moved the controlfile onto ASM ,
so for that open the database in NO-MOUNt mode and using RMAN moved it onto ASM

RMAN> restore controlfile to '+DATADG/clone/controlfile/controlfile.ctl' from '$ORACLE_HOME/dbs/control01.ctl';

6) Now changed the single instance parameter file init.ora with initCLONE1.ora on NODE1 node and initCLONE2.ora on node-2.
(You can use your existing RAC parameter files as sample)

7) open the database on both the instances.

8) Add it into cluster with following command.

$ srvctl add database -d DB_NAME -o $ORACLE_HOME [Execute From only 1 instance, $ORACLE_HOME should be replaced with Actual Value]
$ srvctl add instance -d DB_NAME -i CLONE1 -n node1 [Execute From only 1 instance]
$ srvctl add instance -d DB_NAME -i CLONE2 -n node2 [Execute From only 1 instance]
===================== NOTE =================
As this clone was created with RAC DATABASE, So while moving it onto the two instance cluster database, i didnt need to add a UNDO tablespace,
but i had to create the redologs.
while starting the instance at node-2 i got the following error
ORA-01618: redo thread 2 is not enabled - cannot mount
so for that i followed the following steps
thread                               integer     1
SQL> select thread#, enabled from v$thread;
   THREAD# ENABLED
---------- --------
         1 PUBLIC
         2 DISABLED

SQL> alter database enable public thread 2;

Users might face multiple issues while performing this process, for example LOG GROUP THREAD 2 will have to be created from instance 1 before starting instance 2. You may post your errors here for me to investigate and give suggestions.