Query Performance Optimization Over Network.

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Query Performance Optimization Over Network.

Postby Anand » Tue Oct 02, 2012 7:20 am

Hi Tim,

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
Anand
Member
 
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Re: Query Performance Optimization Over Network.

Postby Tim... » Tue Oct 02, 2012 12:48 pm

Hi.

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.

http://www.oracle-base.com/articles/10g ... of-10g.php

Consider the DRIVING_SITE hint to make sure the processing is done on the database that holds the majority of the data being processed.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 8 guests