Solved

After converting one SQL to materialized view takes long time

Posted on 2014-04-09
5
452 Views
Last Modified: 2014-04-16
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.
0
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 250 total points
ID: 39989613
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
 
LVL 23

Assisted Solution

by:David
David earned 250 total points
ID: 39989752
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39990935
>>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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39990997
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
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 40005791
thanks
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question