I need your expert advice on the following:-
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.
- Code: Select all
INSERT /*+ ALL_ROWS PARALLEL(a, 8) */
INTO SWCDR_81T
SELECT :1,
7,
81,
POWER(2, 1),
CASE
WHEN (b.is_missing = 0) THEN
0
ELSE
POWER(2, 1)
END is_missing,
1 rec_type,
a.*
FROM SWCDR a,
(SELECT CASE
WHEN (src2.CALL_DT IS NULL OR src2.CDR_MSG_TYPE IS NULL) THEN
1
ELSE
0
END is_missing,
src1.CALL_DT,
src1.SPARE_1
FROM (WITH X AS (SELECT CALL_DT,
SPARE_1,
SUM(RECORD_COUNT) AS RECORD_COUNT,
SUM(DURATION) AS DURATION
FROM SWCDR_H_SUM_VW
WHERE CALL_DIRECTION = 2
AND CALL_DT BETWEEN :1 AND :2
GROUP BY CALL_DT, SPARE_1)
SELECT *
FROM X) src1
LEFT OUTER JOIN(WITH Y AS (SELECT CALL_DT,
CDR_MSG_TYPE,
SUM(RECORD_COUNT) AS RECORD_COUNT,
SUM(DURATION) AS DURATION
FROM MDCDR_H_SUM_VW
WHERE CALL_DIRECTION IN
(1, 2, 3, 4)
AND CALL_TYPE = 1
AND CALL_DT BETWEEN :3 AND :4
GROUP BY CALL_DT,
CDR_MSG_TYPE)
SELECT *
FROM Y) src2
ON src1.CALL_DT = src2.CALL_DT
AND src1.SPARE_1 = src2.CDR_MSG_TYPE
WHERE (ABS(((src1.RECORD_COUNT -
NVL(src2.RECORD_COUNT, 0)) /
DECODE(least(nvl(src1.RECORD_COUNT, 0),
nvl(src2.RECORD_COUNT, 0)),
0,
1,
least(nvl(src1.RECORD_COUNT, 0),
nvl(src2.RECORD_COUNT, 0)))) * 100) > 0 OR
ABS(((src1.DURATION - NVL(src2.DURATION, 0)) /
DECODE(least(nvl(src1.DURATION, 0),
nvl(src2.DURATION, 0)),
0,
1,
least(nvl(src1.DURATION, 0),
nvl(src2.DURATION, 0)))) * 100) > 0)
) b
WHERE ORIG_DT BETWEEN :7 AND :8
AND a.CALL_DIRECTION = 2
AND b.CALL_DT = TRUNC(a.ORIG_DT, 'hh')
AND b.SPARE_1 = a.SPARE_1;
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
*******************************************************
- Code: Select all
DATABASE SERVER CONFIGURATION
---------------------------------------------
CPU - Quad 8 Processor 2.
Total memory(RAM) - 64 GB
ADAPTED_PARALLEL - TRUE.
Oracle Version Info - Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production | PL/SQL Release 11.2.02.0.
INSTANCE(i.e AUDIT & DWH) CONFIGURATION(Residing on the the above database server).
- Code: Select all
1st Instance ("AUDIT") configuration 2nd Instance ("DWH") configuration
---------------------------------------- --------------------------------------
SGA - 28 GB SGA - 20 GB
PGA - 8 GB PGA - 8 GB
Please give your value able inputs as PROJECT TEAM is not ready with this PARALLEL solution, so I am dying to get some alternative solution.
Your prompt help will be highly appreciated.
Thanks & Regards,
Anand Kumar Ojha