Monday May 06, 2013

Database Partitioning A Source Table With Golden Gate Replication In Place

GoldenGateAt a client site there was a requirement for performance of a SIEBEL database to partition an existing source table that happened to be also replicated with golden gate. The requirement was only to partition the source table and NOT the replicated target table.  During this process an issue occurred with duplicate data being replicated after the database partitioning occurred.  This article covers the issue found in the process and a workaround to that issue using a simple test case to demonstrate.  It firstly recreates the issue then secondly explains the root cause and solution.

Please note that in this example of golden gate, 'DDL' replication is NOT used.  As stated in this case the partitioning is only at the source side only.  It is a simple extract of the table SCOTT.EMP containing 14 rows and replicating to a new schema / table in the same database called REP.EMP.  The primary key is defined on the EMP table at both sites based on the EMPNO field as is default in the SCOTT sample schema.

The document also does not cover set up for this replication as it focuses on what happens when the source table is changed into a partitioned table from a non partitioned table and replication is in place.  However it does cover some basic outline steps for setting up the test-case.

Please Note these scripts were written only for demonstration purposes. They are not optimized and they have almost no error checking, so be careful!

Download The Full PDF here: Partitioning A Source_database_Table With_Golden_Gate_Replication.pdf

Thursday Apr 25, 2013

'IS NULL' SQL Tuning

NULLFrequently at client sites some very slow queries contain the SQL involving the ‘IS NULL’ criteria in the where clause. It is commonly known Oracle does not store NULL values in indexes so even though an index may exist for the column it is NOT usable in this case. However there is a solution to this situation and FULL table scans can be avoided!

Example: SQL> Select name from emp where dept_id is null;

So in the above example even if the dept_id is indexed on the table emp, due to the ‘IS NULL’ criteria it will NOT be used.

Many developers believe that this type of statement cannot be tuned and that a FULL table scan is the only option. However here is a solution to this issue, simply create an appropriate index but add in an additional constant value to the end of the index so that now NULL values are actually stored!

Example: SQL> create index E1 on EMP (DEPT_ID, -1);

Running an explain plan and executing the SQL it can be seen that the new index will now be used as NULL values are now indexed.

Real life example: At a particular customer site there was a situation where a table had over 11 million rows. A SQL statement had a lot of table joins but only 2 constraints in the where clause as below.



From analyzing the table almost every row had the PRIV_FLG = ‘N’ so that was not going to help, however looking at the integration_id from the 11 million rows, only 51,167 were null values. The field 'integration_id' was already indexed however this would not be used due to only wanting NULL values. By indexing as in the above example the new index helped optimize the query significantly as the number of rows in the driving table had been significantly reduced without any full table scans!

Wednesday Jan 30, 2013

Taking the Black Magic Out Of Oracle Database Recovery



One of the biggest issues seen today with an Oracle database recovery is human error. More damage is done by administrators executing commands without a true understanding or an appreciation of the real condition of the existing Oracle database. Since the release of 11g it is interesting to question, how many administrators are actually using the RMAN recovery advisor which is now a tool built into the database?

The aim of this attached paper is to show that database recovery is not black magic. If a deep breath is taken at the point of failure and time is taken to properly diagnose the real condition of the database, whilst forming a firm action plan from what is available, and then the recovery becomes a simple straight forward process. That is providing any required backups are actually available of course!

Experience has shown that the main issue at the time of any fault is an administrator that jumps straight to the recovery phase with little or no analysis of the actual situation, potentially causing a bad situation to suddenly become worse.

The views expressed on this blog are those of the author and do not necessarily reflect the views of Oracle.  Please download the full paper for a detailed discussion as well as some recovery tricks you may not have come across.

Download The Full PDF hereMagic_Backup_Recovery.pdf


About Me Image
Andy Baker, Senior Principal Consultant for Oracle Consulting Services (@Bakers_byte), shares his news, views and ideas about the Oracle Database with a focus on innovation and emerging technologies.


« July 2014