After converting one SQL to materialized view takes long time

Hello Experts,

I am running a performance issue on my SQL which is now converted into a materialized view.

Here is my current SQL which takes 5 seconds to execute for one user and 200 seconds for 25 users.

Final SQL:
select 
FACILITY_ID,
 VISIT_ID                   
,FACILITY_NAME                   
,CITY                            
,STATE_PROVINCE                  
,COUNTRY                         
,START_DATE                      
,FILTER_STATUS                   
,GET_DISPLAY_VALUE('MS SRA VISIT STATUS',VISIT_STATUS) AS VISIT_STATUS 
, MS_SRA_VISIT_SETUP_HOOK_PKG.ms_sra_get_link(dd_process_instance_id,:1,visit_status,visit_id) LINK
,(select first_name||' '||last_name from si_users_t where user_id = SR_PROGRAM_MANAGER) SR_PROGRAM_MANAGER 
from MS_SRA_VS_SITEVISIT_MV

WHERE (:2                                              IS NULL
OR filter_status                                             = :2)
and 
(TRIM( :3)                                 IS NULL 
OR INSTR(LOWER(facility_name), LOWER( TRIM(:3)))       > 0
OR INSTR(LOWER(visit_id), LOWER( TRIM(:3)))            > 0
OR INSTR(LOWER(CITY), LOWER( TRIM(:3)))                > 0
OR INSTR(LOWER(STATE_PROVINCE), LOWER( TRIM(:3)))      > 0
OR INSTR(LOWER(TO_CHAR(START_DATE)), LOWER( TRIM(:3))) > 0
OR INSTR(LOWER(VISIT_STATUS), LOWER( TRIM(:3)))        > 0
OR INSTR(LOWER(SR_PROGRAM_MANAGER), LOWER( TRIM(:3)))  > 0
OR INSTR(LOWER(COUNTRY), LOWER( TRIM(:3)))             > 0)
AND NOT EXISTS (SELECT  1 FROM MS_SRA_DATA_UPLOAD_VISITS DU WHERE DU.VISIT_ID=MS_SRA_VS_SITEVISIT_MV.VISIT_ID)
ORDER BY VISIT_ID DESC 
;

Open in new window


Explain plan for the above SQL is :

Plan hash value: 2063151349
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |   488 |   140K|    23   (9)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SI_USERS_T                |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SI_USERS_T_N1             |     1 |       |     1   (0)| 00:00:01 |
|   3 |  SORT ORDER BY              |                           |   488 |   140K|    23   (9)| 00:00:01 |
|*  4 |   HASH JOIN RIGHT ANTI      |                           |   488 |   140K|    22   (5)| 00:00:01 |
|   5 |    TABLE ACCESS FULL        | MS_SRA_DATA_UPLOAD_VISITS |     1 |   202 |     2   (0)| 00:00:01 |
|*  6 |    MAT_VIEW ACCESS FULL     | MS_SRA_VS_SITEVISIT_MV    |   489 | 45477 |    20   (5)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$2" "SI_USERS_T"@"SEL$2" ("SI_USERS_T"."USER_ID"))
      SWAP_JOIN_INPUTS(@"SEL$8771BF6C" "DU"@"SEL$3")
      USE_HASH(@"SEL$8771BF6C" "DU"@"SEL$3")
      LEADING(@"SEL$8771BF6C" "MS_SRA_VS_SITEVISIT_MV"@"SEL$1" "DU"@"SEL$3")
      FULL(@"SEL$8771BF6C" "DU"@"SEL$3")
      FULL(@"SEL$8771BF6C" "MS_SRA_VS_SITEVISIT_MV"@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$1")
      UNNEST(@"SEL$3")
      OUTLINE_LEAF(@"SEL$8771BF6C")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      OPT_PARAM('optimizer_index_cost_adj' 40)
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      OPT_PARAM('_optimizer_cost_based_transformation' 'off')
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("USER_ID"=:B1)
   4 - access("DU"."VISIT_ID"="MS_SRA_VS_SITEVISIT_MV"."VISIT_ID")
   6 - filter(("FILTER_STATUS"=:2 OR :2 IS NULL) AND (TRIM(:3) IS NULL OR 
              INSTR(LOWER("FACILITY_NAME"),LOWER(TRIM(:3)))>0 OR INSTR(LOWER("VISIT_ID"),LOWER(TRIM(:3)))>0 OR 
              INSTR(LOWER("CITY"),LOWER(TRIM(:3)))>0 OR INSTR(LOWER("STATE_PROVINCE"),LOWER(TRIM(:3)))>0 OR 
              INSTR(LOWER("VISIT_STATUS"),LOWER(TRIM(:3)))>0 OR INSTR(LOWER("COUNTRY"),LOWER(TRIM(:3)))>0 OR 
              INSTR(LOWER(TO_CHAR("SR_PROGRAM_MANAGER")),LOWER(TRIM(:3)))>0 OR 
              INSTR(LOWER(TO_CHAR(INTERNAL_FUNCTION("START_DATE"))),LOWER(TRIM(:3)))>0))
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Open in new window


Here is my code for my MV:

Create materialized view MS_SRA_VS_SITEVISIT_MV 
REFRESH ON COMMIT 
ENABLE QUERY REWRITE AS
SELECT  f.Facility_id,
    f.facility_name,
    f.city,
    F.STATE_PROVINCE,
    F.COUNTRY,
    v.visit_id,
    v.start_date,
    DECODE(V.VISIT_STATUS, '1', 'Pending', '2','Pending', '3', 'Pending','4' ,'Active', '5','Active', '6','Active', 'D','Cancelled','8','Completed','9','Completed','10','Completed','11','Completed','12','Completed','13','Completed','14','Completed' ) AS filter_status,
    v.dd_process_instance_id,
    v.visit_status,
    v.SR_PROGRAM_MANAGER
  FROM MS_SRA_VISIT_SETUP V,
    (SELECT VISIT_ID ,
      FACILITY_ID,
      facility_name,
      city,
      STATE_PROVINCE,
      COUNTRY
    FROM MS_SRA_FACILITY_INFO p
    WHERE DD_OBJECT_TYPE='MS_SRA_VISIT_SETUP'
    ) F
  WHERE F.VISIT_ID = V.VISIT_ID
  AND NVL(V.DD_EDIT_FLAG,'Y') != 'D'
 ;

Open in new window



Base SQL plan is as below:

EXPLAIN PLAN FOR
SELECT  f.Facility_id,
    f.facility_name,
    f.city,
    F.STATE_PROVINCE,
    F.COUNTRY,
    v.visit_id,
    v.start_date,
    DECODE(V.VISIT_STATUS, '1', 'Pending', '2','Pending', '3', 'Pending','4' ,'Active', '5','Active', '6','Active', 'D','Cancelled','8','Completed','9','Completed','10','Completed','11','Completed','12','Completed','13','Completed','14','Completed' ) AS filter_status,
    v.dd_process_instance_id,
    v.visit_status,
    v.SR_PROGRAM_MANAGER
  FROM MS_SRA_VISIT_SETUP V,
    (SELECT VISIT_ID ,
      FACILITY_ID,
      facility_name,
      city,
      STATE_PROVINCE,
      COUNTRY
    FROM MS_SRA_FACILITY_INFO p
    WHERE DD_OBJECT_TYPE='MS_SRA_VISIT_SETUP'
    ) F
  WHERE F.VISIT_ID = V.VISIT_ID
  AND NVL(V.DD_EDIT_FLAG,'Y') != 'D' 
  ;

Plan hash value: 335331727
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |  3604 |   327K|    19   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MS_SRA_VS_SITEVISIT_MV |  3604 |   327K|    19   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$6F99B991" "MS_SRA_VS_SITEVISIT_MV"@"SEL$83A5F3B9")
      OUTLINE(@"SEL$1")
      REWRITE(@"SEL$1" "MS_SRA_VS_SITEVISIT_MV")
      OUTLINE(@"SEL$9E1704EB")
      REWRITE(@"SEL$9E1704EB" "MS_SRA_VS_SITEVISIT_MV")
      OUTLINE_LEAF(@"SEL$6F99B991")
      ALL_ROWS
      OPT_PARAM('optimizer_index_cost_adj' 40)
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      OPT_PARAM('_optimizer_cost_based_transformation' 'off')
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
  
  

Open in new window


Can you please guide me to reduce the Cost (%CPU) for the main SQL and why the MV is taking huge time.
LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
Your materialized query does not look anything like the "Final SQL" query.

To speed up a materialized view creation,  you may want to pre-create a table and then use the "ON PREBUILT TABLE" option on the MV create statement.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidSenior Oracle Database AdministratorCommented:
What's happening with the VISIT_ID and DD_EDIT_FLAG indexes -- I didn't see any mention in the explain plan.

Secondly, and MV is not a strong area for me, some reading suggests your delay is affected by not first having an MV log created:  http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6003.htm#SQLRF01303.
0
Swadhin RaySenior Technical Engineer Author Commented:
>>What's happening with the VISIT_ID and DD_EDIT_FLAG indexes -- I didn't see any mention in the explain plan.

Even I am also not getting it on the plan.
0
Swadhin RaySenior Technical Engineer Author Commented:
Hello Experts,

I have modified as per the inputs provided but still not gain in time. Still takes same 5 to 8 sec for one user and 200 seconds for 25 users.

Modified the MV as below:

 CREATE MATERIALIZED VIEW LOG ON MS_SRA_FACILITY_INFO WITH ROWID ,PRIMARY KEY INCLUDING NEW VALUES;
 
 CREATE MATERIALIZED VIEW LOG ON MS_SRA_VISIT_SETUP WITH ROWID ,PRIMARY KEY INCLUDING NEW VALUES;
 
CREATE MATERIALIZED VIEW MS_SRA_VS_SITE_MV 
NOCOMPRESS LOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT  
     v.rowid as row_id,
    f.rowid as row_id1,
    f.Facility_id,
    f.facility_name,
    f.city,
    F.STATE_PROVINCE,
    F.COUNTRY,
    v.visit_id,
    v.start_date,
    DECODE(V.VISIT_STATUS, '1', 'Pending', '2','Pending', '3', 'Pending','4' ,'Active', '5','Active', '6','Active', 'D','Cancelled','8','Completed','9','Completed','10','Completed','11','Completed','12','Completed','13','Completed','14','Completed' ) AS filter_status,
    v.dd_process_instance_id,
    v.visit_status,
    v.SR_PROGRAM_MANAGER
  FROM MS_SRA_VISIT_SETUP V,
    (SELECT VISIT_ID ,
      FACILITY_ID,
      facility_name,
      city,
      STATE_PROVINCE,
      COUNTRY
    FROM MS_SRA_FACILITY_INFO p
    WHERE DD_OBJECT_TYPE='MS_SRA_VISIT_SETUP'
    ) F
  WHERE F.VISIT_ID = V.VISIT_ID
  AND NVL(V.DD_EDIT_FLAG,'Y') != 'D'
 ;





EXPLAIN PLAN FOR
select 
FACILITY_ID,
 VISIT_ID                   
,FACILITY_NAME                   
,CITY                            
,STATE_PROVINCE                  
,COUNTRY                         
,START_DATE                      
,FILTER_STATUS                   
,GET_DISPLAY_VALUE('MS SRA VISIT STATUS',VISIT_STATUS) AS VISIT_STATUS 
, MS_SRA_VISIT_SETUP_HOOK_PKG.ms_sra_get_link(dd_process_instance_id,:1,visit_status,visit_id) LINK
,(select first_name||' '||last_name from si_users_t where user_id = SR_PROGRAM_MANAGER) SR_PROGRAM_MANAGER 
from MS_SRA_VS_SITE_MV
WHERE (:2                                              IS NULL
OR filter_status                                             = :2)
and 
(TRIM( :3)                                 IS NULL 
OR INSTR(LOWER(facility_name), LOWER( TRIM(:3)))       > 0
OR INSTR(LOWER(visit_id), LOWER( TRIM(:3)))            > 0
OR INSTR(LOWER(CITY), LOWER( TRIM(:3)))                > 0
OR INSTR(LOWER(STATE_PROVINCE), LOWER( TRIM(:3)))      > 0
OR INSTR(LOWER(TO_CHAR(START_DATE)), LOWER( TRIM(:3))) > 0
OR INSTR(LOWER(VISIT_STATUS), LOWER( TRIM(:3)))        > 0
OR INSTR(LOWER(SR_PROGRAM_MANAGER), LOWER( TRIM(:3)))  > 0
OR INSTR(LOWER(COUNTRY), LOWER( TRIM(:3)))             > 0)
AND NOT EXISTS (SELECT  1 FROM MS_SRA_DATA_UPLOAD_VISITS DU WHERE DU.VISIT_ID=MS_SRA_VS_SITE_MV.VISIT_ID)
ORDER BY VISIT_ID DESC 
;




  
SELECT PLAN_TABLE_OUTPUT  
FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',NULL,'typical +outline')); 





Plan hash value: 3656780558
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |    81 |   114K|    25   (8)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SI_USERS_T                |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SI_USERS_T_N1             |     1 |       |     1   (0)| 00:00:01 |
|   3 |  SORT ORDER BY              |                           |    81 |   114K|    25   (8)| 00:00:01 |
|*  4 |   HASH JOIN RIGHT ANTI      |                           |    81 |   114K|    24   (5)| 00:00:01 |
|   5 |    TABLE ACCESS FULL        | MS_SRA_DATA_UPLOAD_VISITS |     1 |   202 |     2   (0)| 00:00:01 |
|*  6 |    MAT_VIEW ACCESS FULL     | MS_SRA_VS_SITE_MV         |    82 |    99K|    21   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$2" "SI_USERS_T"@"SEL$2" ("SI_USERS_T"."USER_ID"))
      SWAP_JOIN_INPUTS(@"SEL$8771BF6C" "DU"@"SEL$3")
      USE_HASH(@"SEL$8771BF6C" "DU"@"SEL$3")
      LEADING(@"SEL$8771BF6C" "MS_SRA_VS_SITE_MV"@"SEL$1" "DU"@"SEL$3")
      FULL(@"SEL$8771BF6C" "DU"@"SEL$3")
      FULL(@"SEL$8771BF6C" "MS_SRA_VS_SITE_MV"@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$1")
      UNNEST(@"SEL$3")
      OUTLINE_LEAF(@"SEL$8771BF6C")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      OPT_PARAM('optimizer_index_cost_adj' 40)
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      OPT_PARAM('_optimizer_cost_based_transformation' 'off')
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("USER_ID"=:B1)
   4 - access("DU"."VISIT_ID"="MS_SRA_VS_SITE_MV"."VISIT_ID")
   6 - filter((:2 IS NULL OR "FILTER_STATUS"=:2) AND (TRIM(:3) IS NULL OR 
              INSTR(LOWER("FACILITY_NAME"),LOWER(TRIM(:3)))>0 OR INSTR(LOWER("VISIT_ID"),LOWER(TRIM(:3)))>0 OR 
              INSTR(LOWER("CITY"),LOWER(TRIM(:3)))>0 OR INSTR(LOWER("STATE_PROVINCE"),LOWER(TRIM(:3)))>0 OR 
              INSTR(LOWER("VISIT_STATUS"),LOWER(TRIM(:3)))>0 OR INSTR(LOWER("COUNTRY"),LOWER(TRIM(:3)))>0 OR 
              INSTR(LOWER(TO_CHAR("SR_PROGRAM_MANAGER")),LOWER(TRIM(:3)))>0 OR 
              INSTR(LOWER(TO_CHAR(INTERNAL_FUNCTION("START_DATE"))),LOWER(TRIM(:3)))>0))
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Open in new window

0
Swadhin RaySenior Technical Engineer Author Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.