Requirement*************************I have one database server over which I have 2 instances (AUDIT and DWH) , I have a query (as shown below) which perform the following activities on the 2 different instances:-On "DWH" Instance--------------------------1. INNER Query takes data from 2 sources , basically DAILY PARTITIONED tables which contains hourly summarized data (as 'SWCDR_H_SUM_VW' & 'MDCDR_H_SUM_VW' ) lying on "DWH" instance.2. Join these 2 sources based on some joining keys(we call them as DIMENSIONS KEYS - i.e. in the below query "src1.CALL_DT = src2.CALL_DT AND src1.SPARE_1 = src2.CDR_MSG_TYPE)" are such dimension keys).3. Find the difference of data based on some defined columns (we call them as MEASURE KEYS - i.e. in the below query "SUM(RECORDCOUNT) , SUM(DURATION) are such measures columns).4. Now the OUTER Query takes the INNER query output and further joins with the corresponding DETAIL table of the hourly summarized table .On "AUDIT" Instance-------------------------5. The INSERT statement takes the final output from OUTER query (from DWH instance) and Insert it into some temporary table on "AUDIT" instance.

Problem Statement*************************1. Each of the above 2 source i.e. SWCDR_H_SUM_VW and MDCDR_H_SUM_VW (which are DAILY PARTITIONED on some DATE columns) contains 30 millions of data into it, so fetching the whole data in one go from "DWH Instance" to "AUDIT" Instance takes a huge amount of time,in fact sometime system hangs and unable to proceed with it.Running the UNIX's "TOP" command shows the 100% CPU usage for the above query.

Question regarding the above Problem Statement********************************************************************1. Currently we are doing the following as a resolution to the above issue. i) Put the tables (i.e. SWCDR_H_SUM_VW, MDCDR_H_SUM_VW & SWCDR) into PARALLEL mode (i.e ALTER TABLE SWCDR PARALLEL(DEGREE 4)),so that selection of data from DWH would be faster. II) Make use of the "PARALLEL" hint while INSERTING data into temp table on AUDIT Instance.

Although this PARALLEL configuration has increased the overall query performance and the above query is able to INSERT the data within 1 hour or so, yet I have seen some discrepancies in the output for which " WOULD LIKE TO KNOW IF ENABLING PARALLELISM ON TABLE IS HAVING SOME DISCREPANCIES IN TERMS OF THE DATA FETCHING OR NOT?".

2. DO WE HAVE SOME OTHER ALTERNATIVES(SOME HINTS OR SOME TECHNIQUES) FOR FETCHING HUGE AMOUNT OF DATA OVER NETWORK?

Information might be Useful for you*******************************************************

You actually don't know what is causing the bad performance at this time. What you need to do is use SQL Trace to identify the operations and wait states that are soaking up all the time. Only then can you start to identify what the solutions could be.