Performance issue with a query using DB links [message #594433] |
Wed, 28 August 2013 07:58 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #594437 is a reply to message #594436] |
Wed, 28 August 2013 08:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #594446 is a reply to message #594444] |
Wed, 28 August 2013 09:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #594460 is a reply to message #594458] |
Wed, 28 August 2013 11:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #594463 is a reply to message #594461] |
Wed, 28 August 2013 11:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
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 #594488 is a reply to message #594463] |
Thu, 29 August 2013 02:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
nirav_hyd wrote on Wed, 28 August 2013 17:22Hi 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.
|
|
|
|
|
|