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);

 

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

 

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.

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)

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)

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

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)
;

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

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)

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

 
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

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);

--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)

select user_id from access_user_role_crossref u
               INNER JOIN
                   access_role z
               ON z.oid = u.role_oid;