Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

After converting one SQL to materialized view takes long time

Posted on 2014-04-09
5
Medium Priority
?
464 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 1000 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 1000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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 …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

916 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