SyntaxHighlighter

Friday, January 22, 2010

OPQ - Oracle Parallel Queries

Let me clear one thing before you read out this post, 'I am not an oracle expert, I am just a Java developer'. [;)]
Recently I faced a problem , where in I had a materialized view based on a huge table [about a 500 Million].Due to some manual error this MV got disturbed.
We tried all means of getting this MV on track.
1. Fast refresh (It failed , suggesting complete / full refresh)
2. Complete Refresh (It was running for 2-2.5 days , locking all the base tables , so finally we killed it, as blocking base table was not at all feasible for production system)

It was very critical situation, as somehow we need to get that MV in place as this MV used to show up summary data graphically, on which various management decisions are based.
From business point of view ,its a very critical and we cant even think of building a small MV (for past 3-4 months and so on...)
I started exploring Oracle (I trust oracle and their products), from day 1 of this problem I was sure, there must be some way out of this.
Oracle must have taken care of this [:)]

While going through oracle 10g docs , I came across Oracle's Parallel Query (OPQ) feature, and that's it.
That's what I was looking for...
As my MV was calculating summary data, It's having a Full table scan of that huge table, which was causing processing delays..
I introduced parallelism in my MV definition as below
DROP MATERIALIZED VIEW MV_STAT;
CREATE MATERIALIZED VIEW MV_STAT
PARALLEL
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS {SELECT QUERY CALCULATING SUM}
The word in bold , done the trick "Parallel".

After adding this "Golden Keyword" , MV got created in less than 30 minutes..
Problem solved..

When Oracle has to perform a legitimate, large, full-table scan, OPQ can make a dramatic difference in the response time.
Using OPQ, Oracle partitions the table into logical chunks.Once the table has been partitioned into pieces,
Oracle fires off parallel query slaves (aka factotum processes), and each slave simultaneously reads a piece of the large table.
Upon completion of all slave processes,Oracle passes the results back to a parallel query coordinator,
which will reassemble the data, perform a sort if required, and return the results back to the end user.
OPQ can give you almost infinite scalability.

For more details about this Please visit

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm

Cheers,
Dhananjay