Home » RDBMS Server » Performance Tuning » Performance issue with a query using DB links (am on Oracle 11.2.0.3 on Solaris 10 on one database and oracle 10.2.0.4 on Solaris 10 on another database )
Performance issue with a query using DB links [message #594433] Wed, 28 August 2013 07:58 Go to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Experts,



I am on Oracle 11.2.0.3 on Solaris 10 on one database and oracle 10.2.0.4 on Solaris 10 on another database accessed by DB link from the previous one. I have an issue with one query that uses DB link. I will be thankful for any pointers related to the same:

Following is the query:
variable SYS_B_01      VARCHAR2(30);
variable SYS_B_02      NUMBER;
variable SYS_B_03      NUMBER;
variable v_is_active   VARCHAR2(30);
variable v_included   VARCHAR2(30);
variable v_role_type_oid   NUMBER;
variable v_workflow_oid   VARCHAR2(30);
variable v_status_oid   VARCHAR2(30);
variable v_from_schema   VARCHAR2(30);


exec :SYS_B_01    :='Y';
exec :SYS_B_02    :=114500;
exec :SYS_B_03    :=2;
exec :v_is_active :='Y';
exec :v_included  :='Y';
exec :v_role_type_oid :=2;
exec :v_workflow_oid  :='18602_50';
exec :v_status_oid  :='SysMig:25:1';
exec :v_from_schema :='PORTALMAS02';

insert into active_mgr
                       SELECT r.clnt_oid,
           r.user_id,
           CASE
               WHEN EXISTS
                        (SELECT 1
                           FROM access_role@remotedb r2
                                INNER JOIN access_user_role_crossref@remotedb u2
                                    ON u2.role_oid = r2.oid
                                       AND u2.included = :"SYS_B_01"
                                INNER JOIN access_role_resource_crossref@remotedb x
                                    ON x.role_oid = u2.role_oid
                                       AND resource_oid = :"SYS_B_02"
                          WHERE     r2.clnt_oid = r.clnt_oid
                                AND u2.user_id = r.user_id
                                AND r2.role_type_oid = :"SYS_B_03"
                                AND r2.is_active = :v_is_active)
               THEN
                   'Y'
               ELSE
                   'N'
           END
               AS indirect,
               r.unique_user_id as unique_user_id
      FROM     access_user@remotedb r
      WHERE r.user_id in (
            select user_id from access_user_role_crossref@remotedb u
               INNER JOIN
                   access_role@remotedb z
               ON z.oid = u.role_oid
     WHERE u.included = :v_included and z.role_type_oid = :v_role_type_oid AND z.is_active = :v_is_active)
     AND r.clnt_oid in (select org_oid
                        from reference_clients ac
                       where ac.workflow_oid = :v_workflow_oid
                         and ac.org_oid= r.clnt_oid
                         and status_oid = :v_status_oid
                         and from_schema=:v_from_schema);

 

--here is the tkprof output of this sq....it takes about 5 seconds



call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.93       5.18          0          7        804         278
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.93       5.19          0          7        804         278

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS

 

I tried to add the driving_site hint but it did not help. Also at a few places I read that for inserts , this hint does not work.



Following is the output of select * from table(dbms_xplan.display_cursor()) command:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |                          |       |       |  4851 (100)|          |        |      |
|*  1 |  HASH JOIN                    |                          |     4 |   752 |  4851   (3)| 00:00:59 |        |      |
|   2 |   NESTED LOOPS                |                          |     4 |   584 |    14   (0)| 00:00:01 |        |      |
|*  3 |    TABLE ACCESS BY INDEX ROWID| REFERENCE_CLIENTS        |     1 |    52 |     2   (0)| 00:00:01 |        |      |
|*  4 |     INDEX RANGE SCAN          | IDX1_ASSOC_CLIENTS_WFOID |     2 |       |     1   (0)| 00:00:01 |        |      |
|   5 |    REMOTE                     | ACCESS_USER              |   359 | 33746 |    12   (0)| 00:00:01 | REMOT~ | R-   |
|   6 |   VIEW                        | VW_NSO_1                 |   168K|  6897K|  4832   (3)| 00:00:58 |        |      |
|   7 |    REMOTE                     |                          |       |       |            |          | REMOT~ | R->S |

----------------------------------------------------------------------------------------------------------------------

--


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("R"."USER_ID"="USER_ID")
   3 - filter(("FROM_SCHEMA"=:V_FROM_SCHEMA AND "STATUS_OID"=:V_STATUS_OID))
   4 - access("AC"."WORKFLOW_OID"=:V_WORKFLOW_OID)


34 rows selected.



For some strange reason, It does not show the sqls executed on remote database as part of the above output.



Following are the table definitions:
SQL>desc reference_clients
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- -----------------------------------

 OID                                                               NOT NULL VARCHAR2(32)
 ORG_OID                                                           NOT NULL VARCHAR2(32)
 WORKFLOW_OID                                                      NOT NULL VARCHAR2(32)
 MODIFIED_BY_USER                                                           VARCHAR2(32)
 CREATED_DATE                                                      NOT NULL DATE
 MODIFIED_ON                                                       NOT NULL DATE
 FARM_NAME                                                                  VARCHAR2(80)
 SCHEMA_NAME                                                                VARCHAR2(255)
 STATUS_OID                                                                 VARCHAR2(32)
 SCHEMA_CLIENT_TYPE_OID                                                     VARCHAR2(32)
 QA_CLIENT_TYPE_OID                                                         VARCHAR2(32)
 SIA_COMPLETE                                                               NUMBER(1)
 FROM_SCHEMA                                                                VARCHAR2(100)
 TO_EZ_POD                                                                NUMBER
 EZ_ASMNT_STATUS                                                          CHAR(1)
 HR_ASMNT_STATUS                                                           CHAR(1)
 SCHEMA_ASMNT_STATUS                                                        CHAR(1)
 PYX_ASMNT_STATUS                                                          CHAR(1)
 REPORTS_MIG_STATUS_OID                                                     VARCHAR2(32)
 ASSMT_STATUS                                                               CHAR(1)
 ACTION_NEEDED                                                              NUMBER(6)
 F_RTP_ENABLED                                                              NUMBER(1)
 F_RTP_PROCESSED                                                            NUMBER(1)
 MOVE_SYS_STATUS                                                            CHAR(1)
 SCHEMA_POST_STATUS                                                         CHAR(1)
 HRB_POST_STATUS                                                            CHAR(1)
 MOVE_FILES_STATUS                                                          CHAR(1)
 RESUBMIT_STATUS                                                            CHAR(1)
 MV_FILES_MSG_COPY_STATUS                                                   CHAR(1)

SQL>desc active_mgr
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- -----------------------------------

 CLNT_OID                                                                   VARCHAR2(48)
 USER_ID                                                                    VARCHAR2(240)
 INDIRECT                                                                   CHAR(1)
 UNIQUE_USER_ID                                                                VARCHAR2(80)

 

SQL>desc access_user
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CLNT_OID                                  NOT NULL VARCHAR2(16)
 USR_OID                                            VARCHAR2(16)
 UNIQUE_USER_ID                                     VARCHAR2(80)
 USER_ID                                   NOT NULL VARCHAR2(80)
 FIRST_NAME                                NOT NULL VARCHAR2(64)
 LAST_NAME                                 NOT NULL VARCHAR2(64)
 EMPLOYEE_ID                                        VARCHAR2(27)
 OPERATOR_ID                                        VARCHAR2(30)
 EMAIL                                              VARCHAR2(256)
 LOCATION                                           VARCHAR2(60)
 DEPARTMENT                                         VARCHAR2(60)
 JOB                                                VARCHAR2(45)
 IS_ACTIVE                                 NOT NULL CHAR(1)
 REGION_CODE                                        VARCHAR2(20)
 PARENT_CODE                                        VARCHAR2(20)
 FILE_NUMBER                                        VARCHAR2(20)
 MSGID                                              NUMBER(10)
 SERVICECENTERCODE                                  VARCHAR2(20)
LANGCODE                                           VARCHAR2(10)
 CHILD_CODE                                         VARCHAR2(20)
 CREATED_DATE                                       DATE
 MODIFIED_DATE                                      DATE

PORTALMAS02_ACS@prtd05>DESC ACCESS_ROLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID                                       NOT NULL NUMBER(12)
 CLNT_OID                                  NOT NULL VARCHAR2(16)
 ROLE_ID                                   NOT NULL VARCHAR2(70)
 ROLE_NAME                                          VARCHAR2(100)
 ROLE_TYPE_OID                             NOT NULL NUMBER(3)
 PARENT_ROLE_OID                                    NUMBER(12)
 IS_ACTIVE                                 NOT NULL CHAR(1)
 IS_BUILTIN                                NOT NULL CHAR(1)
 IN_USE                                             CHAR(1)
 IS_HIDDEN                                          CHAR(1)
 PROCESSING_STATUS                                  VARCHAR2(12)
 PROCESSING_OID                                     NUMBER(12)
 CREATED_DATE                                       DATE
 CREATED_BY                                         VARCHAR2(80)
 MODIFIED_DATE                                      DATE
 MODIFIED_BY                                        VARCHAR2(80)

PORTALMAS02_ACS@prtd05>DESC access_user_role_crossref
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CLNT_OID                                  NOT NULL VARCHAR2(16)
 ROLE_OID                                  NOT NULL NUMBER(12)
 USER_ID                                   NOT NULL VARCHAR2(80)
 INCLUDED                                  NOT NULL CHAR(1)
 ADDED_MANUALLY                            NOT NULL CHAR(1)
 CREATED_DATE                                       DATE
 CREATED_BY                                         VARCHAR2(80)
 MODIFIED_DATE                                      DATE
 MODIFIED_BY                                        VARCHAR2(80)

 

PORTALMAS02_ACS@prtd05>DESC access_role_resource_crossref
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CLNT_OID                                  NOT NULL VARCHAR2(16)
 ROLE_OID                                  NOT NULL NUMBER(12)
 RESOURCE_OID                              NOT NULL NUMBER(12)
 EXCLUDED                                  NOT NULL CHAR(1)
 CREATED_DATE                                       DATE
 CREATED_BY                                         VARCHAR2(80)
 MODIFIED_DATE                                      DATE
 MODIFIED_BY                                        VARCHAR2(80)




This query is taking more than 5 seconds and our target is to make it complete in less than 2 seconds. I will be very thankful for any pointers on what can help here. I have tried several opitons like adding the driving_site hint etc. but it did not help so far.

Thanks,
OrauserN




CM: fixed formatting of explain plan and added extra code tags for readability

[Updated on: Wed, 28 August 2013 08:14] by Moderator

Report message to a moderator

Re: Performance issue with a query using DB links [message #594436 is a reply to message #594433] Wed, 28 August 2013 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
How long does it take if you remove the case statement?
Re: Performance issue with a query using DB links [message #594437 is a reply to message #594436] Wed, 28 August 2013 08:30 Go to previous messageGo to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
Thanks for reviewing the question Cookiemonster.

Removing the case reduces about half a second and it then takes 4.71 seconds - so it is not making a huge difference. This time when I checked -removing the case, the tkprof showed the actual plan , which is as follows:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.85       4.71          0          7        806         278
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.86       4.72          0          7        806         278

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 610

Rows     Row Source Operation
-------  ---------------------------------------------------
    278  HASH JOIN  (cr=3 pr=0 pw=0 time=8718098 us)
    896   NESTED LOOPS  (cr=3 pr=0 pw=0 time=227270 us)
      2    TABLE ACCESS BY INDEX ROWID REFERENCE_CLIENTS (cr=3 pr=0 pw=0 time=125 us)
      2     INDEX RANGE SCAN IDX1_ASSOC_CLIENTS_WFOID (cr=2 pr=0 pw=0 time=51 us)(object id 468874)
    896    REMOTE  ACCESS_USER (cr=0 pr=0 pw=0 time=260146 us)
  22054   VIEW  VW_NSO_1 (cr=0 pr=0 pw=0 time=3002953 us)
  22054    REMOTE  (cr=0 pr=0 pw=0 time=2958836 us)

So the large part of the time is in the last operation named REMOTE.
Re: Performance issue with a query using DB links [message #594438 is a reply to message #594437] Wed, 28 August 2013 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
How long does this take:
select user_id from access_user_role_crossref@remotedb u
               INNER JOIN
                   access_role@remotedb z
               ON z.oid = u.role_oid
     WHERE u.included = :v_included and z.role_type_oid = :v_role_type_oid AND z.is_active = :v_is_active

And how long does it take if you run it directly on the remote DB?
Re: Performance issue with a query using DB links [message #594439 is a reply to message #594438] Wed, 28 August 2013 08:39 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
And the same for this:
SELECT r.clnt_oid,
       r.user_id,
           r.unique_user_id as unique_user_id
FROM access_user@remotedb r
WHERE r.user_id in (select user_id 
                    from access_user_role_crossref@remotedb u
                    INNER JOIN access_role@remotedb z
                    ON z.oid = u.role_oid
                    WHERE u.included = :v_included 
                    and z.role_type_oid = :v_role_type_oid 
                    AND z.is_active = :v_is_active)
;


And how many rows do the above retrieve?
And how many rows does this retrieve:
select org_oid
                        from reference_clients ac
                       where ac.workflow_oid = :v_workflow_oid
                         and ac.org_oid= r.clnt_oid
                         and status_oid = :v_status_oid
                         and from_schema=:v_from_schema
Re: Performance issue with a query using DB links [message #594443 is a reply to message #594439] Wed, 28 August 2013 09:07 Go to previous messageGo to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Cookiemonster,

Here are the details on the first sql - it retrieves 24050 rows both locally and remote it takes quite sometime and I guess here may lie some clue?
It takes 4.16 seconds when done remotely.
It takes 3.40 seconds when run in the that db locally.
via db link:
select user_id from access_user_role_crossref@remotedb u
               INNER JOIN
                   access_role@remotedb z
               ON z.oid = u.role_oid
     WHERE u.included = :v_included and z.role_type_oid = :v_role_type_oid AND z.is_active = :v_is_active

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.20          0          0          1           0
Execute      1      0.00       0.06          0          0          0           0
Fetch      963      0.46       3.90          0          0          0       24050
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      965      0.48       4.16          0          0          1       24050

locally: here the tkprof shows the execution plan too as below - a full table scan on two tables.
select user_id from access_user_role_crossref u
               INNER JOIN
                   access_role z
               ON z.oid = u.role_oid
     WHERE u.included = :v_included and z.role_type_oid = :v_role_type_oid AND z.is_active = :v_is_active

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1605      2.51       3.39       4878       6885          0       24050
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1607      2.51       3.40       4878       6885          0       24050

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 445

Rows     Row Source Operation
-------  ---------------------------------------------------
  24050  HASH JOIN  (cr=6885 pr=4878 pw=0 time=2128863 us)
   3729   TABLE ACCESS FULL ACCESS_ROLE (cr=379 pr=0 pw=0 time=27019 us)
 351565   TABLE ACCESS FULL ACCESS_USER_ROLE_CROSSREF (cr=6506 pr=4878 pw=0 time=712062 us)



Re: Performance issue with a query using DB links [message #594444 is a reply to message #594443] Wed, 28 August 2013 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
So we need to speed that up significantly or pretty much got no chance.
How many rows in each table?
What indexes are present on each table?
Re: Performance issue with a query using DB links [message #594446 is a reply to message #594444] Wed, 28 August 2013 09:40 Go to previous messageGo to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
You are right!!

Here are those details:
I think something can be done to modify the query, I will check that and update shortly if it helps to get it faster.

CREATE UNIQUE INDEX PK_ACCESS_ROLE ON ACCESS_ROLE
(OID);


CREATE UNIQUE INDEX ACCESS_ROLE_IDX01 ON ACCESS_ROLE
(CLNT_OID, ROLE_ID);


CREATE INDEX ACCESS_ROLE_IDX02 ON ACCESS_ROLE
(ROLE_TYPE_OID);


CREATE INDEX ACCESS_ROLE_IDX03 ON ACCESS_ROLE
(PARENT_ROLE_OID);


CREATE INDEX ACCESS_ROLE_IDX04 ON ACCESS_ROLE
(PROCESSING_OID);



CREATE UNIQUE INDEX PK_ACCESS_USER_ROLE_XREF ON ACCESS_USER_ROLE_CROSSREF
(CLNT_OID, ROLE_OID, USER_ID);


CREATE INDEX ACCESS_USER_ROLE_XREF_IDX04 ON ACCESS_USER_ROLE_CROSSREF
(CLNT_OID, USER_ID);


CREATE INDEX ACCESS_USER_ROLE_XREF_IDX05 ON ACCESS_USER_ROLE_CROSSREF
(USER_ID);


CREATE INDEX ACCESS_USER_ROLE_XREF_IDX06 ON ACCESS_USER_ROLE_CROSSREF
(ROLE_OID);

ALTER TABLE ACCESS_USER_ROLE_CROSSREF ADD (
  CONSTRAINT PK_ACCESS_USER_ROLE_XREF
  PRIMARY KEY
  (CLNT_OID, ROLE_OID, USER_ID)
  USING INDEX PK_ACCESS_USER_ROLE_XREF
  ENABLE VALIDATE);

count: ACCESS_ROLE: 24907
ACCESS_USER_ROLE_CROSSREF : 496054

[Updated on: Wed, 28 August 2013 09:41]

Report message to a moderator

Re: Performance issue with a query using DB links [message #594457 is a reply to message #594446] Wed, 28 August 2013 10:45 Go to previous messageGo to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Cookiemonster,

I tried to make this query go faster but I am not able to do so. Not sure what can be done here....the point is that while this query returns a lot of data, most of this data later gets removed from the main result set , but the problem is that, the other criteria to remove these rows in in main db table and not in remote db!

I mean this sql returns 24050 rows:

 
select user_id from access_user_role_crossref u
               INNER JOIN
                   access_role z
               ON z.oid = u.role_oid
     WHERE u.included = :v_included and z.role_type_oid = :v_role_type_oid AND z.is_active = :v_is_active

but there is another clause which I guess is responsible to further narrow down the result set but this table is in source db and the previous one is in remote db:
AND r.clnt_oid in (select org_oid
                        from reference_clients ac
                       where ac.workflow_oid = :v_workflow_oid
                         and ac.org_oid= r.clnt_oid
                         and status_oid = :v_status_oid
                         and from_schema=:v_from_schema);




Thanks again for the help!!
Re: Performance issue with a query using DB links [message #594458 is a reply to message #594457] Wed, 28 August 2013 10:48 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
try adding indexes on access_user_role_crossref (user_id , role_oid, included)
access_role (role_type_oid , oid, is_active )
Re: Performance issue with a query using DB links [message #594460 is a reply to message #594458] Wed, 28 August 2013 11:13 Go to previous messageGo to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
I tried adding these indexes but somehow the time taken looks like the same as it was with full table scan...here is the extract from tkprof:
It shows 3.30 seconds and that is unluckily so much similar to time in full table scan as seen earlier.
--indexes added

CREATE INDEX ACCESS_ROLE_test1 ON ACCESS_ROLE
(role_type_oid , oid, is_active );


CREATE INDEX access_ur_crossref_test1 ON access_user_role_crossref
(user_id , role_oid, included );

---tkprof output
select user_id from access_user_role_crossref u
               INNER JOIN
                   access_role z
               ON z.oid = u.role_oid
     WHERE u.included = :v_included and z.role_type_oid = :v_role_type_oid AND z.is_active = :v_is_active

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1605      1.64       3.29       2261       4186          0       24050
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1607      1.65       3.30       2261       4186          0       24050

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 445

Rows     Row Source Operation
-------  ---------------------------------------------------
  24050  HASH JOIN  (cr=4186 pr=2261 pw=0 time=1571387 us)
   3729   INDEX RANGE SCAN ACCESS_ROLE_TEST1 (cr=14 pr=0 pw=0 time=7545 us)(object id 493678)
 351565   INDEX FAST FULL SCAN ACCESS_UR_CROSSREF_TEST1 (cr=4172 pr=2261 pw=0 time=1406451 us)(object id 493679)



But your name should be BigAngle and not monster! thanks again....
Re: Performance issue with a query using DB links [message #594461 is a reply to message #594460] Wed, 28 August 2013 11:17 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
You may have to look at using materialized views to precompute some or all of the query result.
Re: Performance issue with a query using DB links [message #594462 is a reply to message #594460] Wed, 28 August 2013 11:20 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
The following comment may or may not be relevant to this discussion.
The CBO has been known to make sub-optimal decisions when ANSI joins are used in conjunction with WHERE clause.
The CBO may make better decision when ANSI join is not part of the SQL statement.
Re: Performance issue with a query using DB links [message #594463 is a reply to message #594461] Wed, 28 August 2013 11:22 Go to previous messageGo to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Cookiemonster,

Can you explain me some more on it....do you mean that the remote table and its query should be precomputed and stored in local db and thus avoid db link?...or do you mean in remote db itself we precompute and keep a Materialized view? I have not used this technique so would like to know more from you if possible.

[Updated on: Wed, 28 August 2013 11:23]

Report message to a moderator

Re: Performance issue with a query using DB links [message #594464 is a reply to message #594462] Wed, 28 August 2013 11:24 Go to previous messageGo to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
Hi BlackSwan,

Thank you!! I will try to rewrite the sql using non-ansi syntax and check it.

Thanks for this suggestion.
Re: Performance issue with a query using DB links [message #594488 is a reply to message #594463] Thu, 29 August 2013 02:48 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
nirav_hyd wrote on Wed, 28 August 2013 17:22
Hi Cookiemonster,

Can you explain me some more on it....do you mean that the remote table and its query should be precomputed and stored in local db and thus avoid db link?...or do you mean in remote db itself we precompute and keep a Materialized view? I have not used this technique so would like to know more from you if possible.


Either. You'll have to check the speed of each and weigh it against the refresh options available. If you have a materialized view on the local DB then it will not be on commit refresh and so won't always be completely up to date.
A materialized view on the remote probably can be made on commit refresh (you'll have to leave out the local table) but the resulting query may not be fast enough.
Re: Performance issue with a query using DB links [message #594535 is a reply to message #594488] Thu, 29 August 2013 12:16 Go to previous messageGo to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
Can you please suggest what part of the sql I should take out and make a Materialized view for it? Is it this one:

select user_id from access_user_role_crossref u
               INNER JOIN
                   access_role z
               ON z.oid = u.role_oid;
Re: Performance issue with a query using DB links [message #594569 is a reply to message #594535] Fri, 30 August 2013 03:10 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
Maybe. Like I already said, you'll need to try different combinations.
Re: Performance issue with a query using DB links [message #594612 is a reply to message #594569] Fri, 30 August 2013 13:06 Go to previous message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
Thanks again...I will try and update this forum. I hope to get a break but lets see.
Have a nice weekend.
Previous Topic: Please help on this
Next Topic: Parallel hints with cursor queries
Goto Forum:
  


Current Time: Tue Jul 29 21:16:00 CDT 2014

Total time taken to generate the page: 0.26187 seconds