asked on

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.

ASKER CERTIFIED SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

David VanZandt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Swadhin Ray

ASKER

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

Swadhin Ray

ASKER

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

Swadhin Ray

ASKER

thanks