Home » RDBMS Server » Performance Tuning » Performance on DB Link
Performance on DB Link [message #129261] Fri, 22 July 2005 16:39 Go to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
I am getting some strange behavior trying to insert some records from a remote database and I was wondering if anybody had any insights.

This query runs in about 7 seconds, which is perfectly fine for my requirements.
SQL> SELECT /*+DRIVING_SITE(rt)*/
  2   abs_relation_cd,
  3   entity_1_id,
  4   entity_2_id,
  5   is_active,
  6   relation_id
  7  FROM   fe_relation@stagingc.corp.finaplex.com rt,
  8         (SELECT DISTINCT local_key
  9          FROM   (SELECT definer_id local_key
 10                  FROM   fi_instr_master
 11                  UNION
 12                  SELECT ins_id local_key
 13                  FROM   fi_instr_master
 14                  UNION
 15                  SELECT upd_id local_key
 16                  FROM   fi_instr_master
 17                  UNION
 18                  SELECT ins_id local_key
 19                  FROM   fi_market_data
 20                  UNION
 21                  SELECT upd_id local_key
 22                  FROM   fi_market_data
 23                  MINUS
 24                  SELECT relation_id FROM fe_relation)
 25          WHERE  local_key IS NOT NULL) lt
 26  WHERE  rt.relation_id = lt.local_key;

ABS_RELATION_CD   ENTITY_1_ID   ENTITY_2_ID IS_ACTIVE   RELATION_ID
---------------- ------------ ------------- --------- -------------
FPX_APP_ROOT                1             2         1             1
FPX_APP_BATCH               1             3         1             2
CLIENT                      4        841375         1      13048692
CLIENT                      4        910670         1      39083577
CLIENT                      4        915431         1      39099762
CLIENT                      4       1029713         1      39569712
CLIENT                      4       1030820         1      39573929

7 rows selected

This block runs in about 7 seconds
SQL> BEGIN
  2    FOR r IN (SELECT /*+DRIVING_SITE(rt)*/
  3               abs_relation_cd,
  4               entity_1_id,
  5               entity_2_id,
  6               is_active,
  7               relation_id
  8              FROM   fe_relation@stagingc.corp.finaplex.com rt,
  9                     (SELECT DISTINCT local_key
 10                      FROM   (SELECT definer_id local_key
 11                              FROM   fi_instr_master
 12                              UNION
 13                              SELECT ins_id local_key
 14                              FROM   fi_instr_master
 15                              UNION
 16                              SELECT upd_id local_key
 17                              FROM   fi_instr_master
 18                              UNION
 19                              SELECT ins_id local_key
 20                              FROM   fi_market_data
 21                              UNION
 22                              SELECT upd_id local_key
 23                              FROM   fi_market_data
 24                              MINUS
 25                              SELECT relation_id FROM fe_relation)
 26                      WHERE  local_key IS NOT NULL) lt
 27              WHERE  rt.relation_id = lt.local_key)
 28    LOOP
 29      INSERT INTO fe_relation (abs_relation_cd, entity_1_id, entity_2_id, is_active, relation_id) VALUES (r.abs_relation_cd, r.entity_1_id, r.entity_2_id, r.is_active, r.relation_id);
 30    END LOOP;
 31  END;
 32  /

PL/SQL procedure successfully completed

But this doesn't ever seem to complete.
SQL> INSERT INTO fe_relation
  2  (abs_relation_cd,
  3   entity_1_id,
  4   entity_2_id,
  5   is_active,
  6   relation_id)
  7  SELECT /*+DRIVING_SITE(rt)*/
  8   abs_relation_cd,
  9   entity_1_id,
 10   entity_2_id,
 11   is_active,
 12   relation_id
 13  FROM   fe_relation@stagingc.corp.finaplex.com rt,
 14         (SELECT DISTINCT local_key
 15          FROM   (SELECT definer_id local_key
 16                  FROM   fi_instr_master
 17                  UNION
 18                  SELECT ins_id local_key
 19                  FROM   fi_instr_master
 20                  UNION
 21                  SELECT upd_id local_key
 22                  FROM   fi_instr_master
 23                  UNION
 24                  SELECT ins_id local_key
 25                  FROM   fi_market_data
 26                  UNION
 27                  SELECT upd_id local_key
 28                  FROM   fi_market_data
 29                  MINUS
 30                  SELECT relation_id FROM fe_relation)
 31          WHERE  local_key IS NOT NULL) lt
 32  WHERE  rt.relation_id = lt.local_key;

Before anybody gets too far into asking about table statistics and explain plans, please note that the select statement is exactly the same in each of the cases. As a cursor loop, it works just fine. Why does anything change when I use it as a subquery in an insert statement and how can I prevent whatever change is happening?

Thanks,
Scott
Re: Performance on DB Link [message #129526 is a reply to message #129261] Mon, 25 July 2005 10:56 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
What is the Oracle Version?

Thanks,
Sri
Re: Performance on DB Link [message #129559 is a reply to message #129526] Mon, 25 July 2005 14:28 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
Oracle9i Enterprise Edition Release 9.2.0.6.0
Re: Performance on DB Link [message #129576 is a reply to message #129559] Mon, 25 July 2005 16:18 Go to previous message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Have you tried to use the push_subq hint? If not, try it and let us know if it helped.

Thanks,
Sri
Previous Topic: Bitmap Indexes
Next Topic: statspack-buffer waits
Goto Forum:
  


Current Time: Tue Jan 31 18:32:57 CST 2023