OraFAQ Forum: Performance Tuning » Distributed query running slow

Home » RDBMS Server » Performance Tuning » Distributed query running slow (Oracle9i Enterprise Edition Release 9.2.0.6.0 / Linux 2.4.21-50.ELsmp #1 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Distributed query running slow [message #549060]

Tue, 27 March 2012 16:58

SSharma
Messages: 17
Registered: July 2011
Location: INDIA

Junior Member

Hi Experts ,

We are facing one isssue . we have one procedure which inserts data fetched from source database via database link .

That is running forever .

Target database (i.e where procedure is running ) :--Oracle9i Enterprise Edition Release 9.2.0.6.0 / Linux 2.4.21-50.ELsmp #1

Source database (from where data is fetched) :-- Oracle 11g Release 2 /Linux .

Below is the query which is fired when procedure is run::

INSERT INTO GB_SEC_MARS_DATA_VAL
SELECT *
FROM ( SELECT 'FY' || SUBSTR (PERIOD_YEAR, 3) YEAR,
DECODE (SUBSTR (PERIOD_NAME, 1, 2),
'Q1', 'Mar',
'Q2', 'Jun',
'Q3', 'Sep',
'Q4', 'Dec')
AS TIME,
'EN_' || ME ENTITY,
NVL (
SUM (
DECODE (ACCOUNT_TYPE,
'R', (-1 * PTD_BALANCE),
'E', (PTD_BALANCE),
'A', (YTD_BALANCE),
'L', (-1 * YTD_BALANCE),
'O', (-1 * YTD_BALANCE))),
0)
AS AMOUNT
FROM MARS_BALANCES_BUSINESS_S@DB_MARSPROD A
WHERE A.PERIOD_NAME = :B2 || '-' || SUBSTR (:B1, 3)
AND A.SOB_SHORT_NAME = 'USDRQ'
AND 'EN_' || A.ME IN (SELECT BSLA
FROM GB_SEC_BSLA_MAPPING
WHERE SOURCE = 'GL')
OR A.ME IN ('BCXMXN')
GROUP BY 'EN_' || ME,
SUBSTR (PERIOD_YEAR, 3),
SUBSTR (PERIOD_NAME, 1, 2)
UNION ALL
SELECT 'FY' || SUBSTR (PERIOD_YEAR, 3) YEAR,
DECODE (SUBSTR (PERIOD_NAME, 1, 2),
'Q1', 'Mar',
'Q2', 'Jun',
'Q3', 'Sep',
'Q4', 'Dec')
AS TIME,
'EN_' || ME ENTITY,
NVL (
SUM (
DECODE (ACCOUNT_TYPE,
'R', (-1 * PTD_BALANCE),
'E', (PTD_BALANCE),
'A', (YTD_BALANCE),
'L', (-1 * YTD_BALANCE),
'O', (-1 * YTD_BALANCE))),
0)
AS AMOUNT
FROM MARS_BALANCES_BUSINESS_S@DB_MARSPROD A
WHERE A.PERIOD_NAME = :B2 || '-' || SUBSTR (:B1, 3)
AND A.SOB_SHORT_NAME = 'USDRQ'
AND ME IN (SELECT BSLA FROM GB_SEC_BSA_ENTITY)
GROUP BY 'EN_' || ME,
SUBSTR (PERIOD_YEAR, 3),
SUBSTR (PERIOD_NAME, 1, 2));

/////////////////////////////////////

This is the explain plan :

INSERT STATEMENT CHOOSECost: 834,854
12 VIEW Cost: 834,854 Bytes: 101,325,708 Cardinality: 1,876,402
11 UNION-ALL
4 SORT GROUP BY Cost: 423,075 Bytes: 141,195,308 Cardinality: 1,857,833
3 FILTER
1 REMOTE SERIAL_FROM_REMOTE Cost: 411,646 Bytes: 141,195,308 Cardinality: 1,857,833
2 TABLE ACCESS FULL GB_HDB_OWNER.GB_SEC_BSLA_MAPPING Cost: 2 Bytes: 13 Cardinality: 1
10 SORT GROUP BY Cost: 411,779 Bytes: 1,578,365 Cardinality: 18,569
9 HASH JOIN Cost: 411,651 Bytes: 1,578,365 Cardinality: 18,569
7 VIEW Cost: 2 Bytes: 513 Cardinality: 57
6 SORT UNIQUE Bytes: 399 Cardinality: 57
5 TABLE ACCESS FULL GB_HDB_OWNER.GB_SEC_BSA_ENTITY Cost: 2 Bytes: 399 Cardinality: 57
8 REMOTE SERIAL_FROM_REMOTE Cost: 411,646 Bytes: 1,411,244 Cardinality: 18,569

////////////////////////////////

I also traced the whole session :

begin
PRC_SEC_CUBE_VS_MARS_VAL_YTD('FY11','Dec');
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 10.36 33.57 9120 2012 73 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 10.37 33.58 9120 2012 73 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 70

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 199.33 199.33
********************************************************************************

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.39 0 4 1 0
Execute 1 9.93 32.39 8192 10 9 89
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 9.94 32.78 8192 14 10 89

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 70 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
single-task message 1 0.10 0.10
SQL*Net message to dblink 97 0.00 0.00
SQL*Net message from dblink 97 1.18 4.41
SQL*Net more data from dblink 12802 0.09 15.23
direct path write 4062 0.00 0.01
direct path read 4095 0.00 0.00
db file sequential read 2 0.00 0.00
********************************************************************************

Above may be helpful in analyzing the issue . Let me know if some more info is needed .

Thanks,
Saurabh

Report message to a moderator

Re: Distributed query running slow [message #549062 is a reply to message #549060]

Tue, 27 March 2012 17:04

SSharma
Messages: 17
Registered: July 2011
Location: INDIA

Junior Member


/* Formatted on 3/27/2012 6:02:11 PM (QP5 v5.163.1008.3004) */
INSERT INTO GB_SEC_MARS_DATA_VAL
   SELECT *
     FROM (  SELECT 'FY' || SUBSTR (PERIOD_YEAR, 3) YEAR,
                    DECODE (SUBSTR (PERIOD_NAME, 1, 2),
                            'Q1', 'Mar',
                            'Q2', 'Jun',
                            'Q3', 'Sep',
                            'Q4', 'Dec')
                       AS TIME,
                    'EN_' || ME ENTITY,
                    NVL (
                       SUM (
                          DECODE (ACCOUNT_TYPE,
                                  'R', (-1 * PTD_BALANCE),
                                  'E', (PTD_BALANCE),
                                  'A', (YTD_BALANCE),
                                  'L', (-1 * YTD_BALANCE),
                                  'O', (-1 * YTD_BALANCE))),
                       0)
                       AS AMOUNT
               FROM MARS_BALANCES_BUSINESS_S@DB_MARSPROD A
              WHERE     A.PERIOD_NAME = :B2 || '-' || SUBSTR (:B1, 3)
                    AND A.SOB_SHORT_NAME = 'USDRQ'
                    AND 'EN_' || A.ME IN (SELECT BSLA
                                            FROM GB_SEC_BSLA_MAPPING
                                           WHERE SOURCE = 'GL')
                    OR A.ME IN ('BCXMXN')
           GROUP BY 'EN_' || ME,
                    SUBSTR (PERIOD_YEAR, 3),
                    SUBSTR (PERIOD_NAME, 1, 2)
           UNION ALL
             SELECT 'FY' || SUBSTR (PERIOD_YEAR, 3) YEAR,
                    DECODE (SUBSTR (PERIOD_NAME, 1, 2),
                            'Q1', 'Mar',
                            'Q2', 'Jun',
                            'Q3', 'Sep',
                            'Q4', 'Dec')
                       AS TIME,
                    'EN_' || ME ENTITY,
                    NVL (
                       SUM (
                          DECODE (ACCOUNT_TYPE,
                                  'R', (-1 * PTD_BALANCE),
                                  'E', (PTD_BALANCE),
                                  'A', (YTD_BALANCE),
                                  'L', (-1 * YTD_BALANCE),
                                  'O', (-1 * YTD_BALANCE))),
                       0)
                       AS AMOUNT
               FROM MARS_BALANCES_BUSINESS_S@DB_MARSPROD A
              WHERE     A.PERIOD_NAME = :B2 || '-' || SUBSTR (:B1, 3)
                    AND A.SOB_SHORT_NAME = 'USDRQ'
                    AND ME IN (SELECT BSLA FROM GB_SEC_BSA_ENTITY)
           GROUP BY 'EN_' || ME,
                    SUBSTR (PERIOD_YEAR, 3),
                    SUBSTR (PERIOD_NAME, 1, 2));

* [/code] tag corrected by BlackSwan

[Updated on: Tue, 27 March 2012 17:17] by Moderator

Report message to a moderator

Re: Distributed query running slow [message #549074 is a reply to message #549060]

Wed, 28 March 2012 01:12

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Fri, 29 July 2011 11:13

For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: Distributed query running slow [message #549102 is a reply to message #549074]

Wed, 28 March 2012 03:11

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Your query appears to be missing a bracket:

      FROM MARS_BALANCES_BUSINESS_S@DB_MARSPROD A
      WHERE     A.PERIOD_NAME = :B2 || '-' || SUBSTR (:B1, 3)
      AND A.SOB_SHORT_NAME = 'USDRQ'
      AND 'EN_' || A.ME IN (SELECT BSLA
                            FROM GB_SEC_BSLA_MAPPING
                            WHERE SOURCE = 'GL')
      OR A.ME IN ('BCXMXN')

That Or applies all the previous ANDs. It works like:

      FROM MARS_BALANCES_BUSINESS_S@DB_MARSPROD A
      WHERE (A.PERIOD_NAME = :B2 || '-' || SUBSTR (:B1, 3)
             AND A.SOB_SHORT_NAME = 'USDRQ'
             AND 'EN_' || A.ME IN (SELECT BSLA
                                   FROM GB_SEC_BSLA_MAPPING
                                   WHERE SOURCE = 'GL')
      OR A.ME IN ('BCXMXN')

I suspect it should be:

      FROM MARS_BALANCES_BUSINESS_S@DB_MARSPROD A
      WHERE (A.PERIOD_NAME = :B2 || '-' || SUBSTR (:B1, 3)
      AND A.SOB_SHORT_NAME = 'USDRQ'
      AND ('EN_' || A.ME IN (SELECT BSLA
                            FROM GB_SEC_BSLA_MAPPING
                            WHERE SOURCE = 'GL')
           OR A.ME IN ('BCXMXN')
          )

Report message to a moderator

Re: Distributed query running slow [message #549336 is a reply to message #549102]

Thu, 29 March 2012 10:58

SSharma
Messages: 17
Registered: July 2011
Location: INDIA

Junior Member

Thanks for replying cookiemonster !!!!

I tried the where clause as you have posted , but the query is still runnning the same way . Costs have not decreaed , explain plan is same as it was before . I also tried using RULE and DRIVING_SITE hint . As RULE hint solved my performance issue once (didnt expected this was just be chance ) and DRIVING_SITE hint has also no effect . Still confused how to proceed .

Some things going in my mind .

1) Create a view on source daabase as per the complex query and selecting data directly from it .

2) if query starts giving results , will make MView here on target , so that time of processing will be saved .

please advice !!!!!!!!!!

Report message to a moderator

Re: Distributed query running slow [message #549338 is a reply to message #549336]

Thu, 29 March 2012 11:11

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

The first thing I would advise is making sure the where clause is right.
What I pointed out wasn't primarily a performance issue. It was a you're going to get the wrong data issue.
So work out whether or not my suggestion is correct from a data point of view. There's no point looking at the performance until you know you have something that'll give the correct data.

Report message to a moderator

Re: Distributed query running slow [message #549478 is a reply to message #549338]

Fri, 30 March 2012 19:04

SSharma
Messages: 17
Registered: July 2011
Location: INDIA

Junior Member

yes cookiemonster , you were right , i checked with application team , they have corrected as per yr post . I ran it with corrected where clause , still its not giving results .

Report message to a moderator