Solved

After converting one SQL to materialized view takes long time

Posted on 2014-04-09
5
411 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
  • 3
5 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now