Home » RDBMS Server » Performance Tuning » Materialized View not being used on production (works elsewhere) (Oracle 11.2.0.2 Enterprise 64 bit)
| Materialized View not being used on production (works elsewhere) [message #608885] |
Wed, 26 February 2014 05:10  |
 |
d_seng
Messages: 78
Registered: November 2011
Location: UK
|
Member |
|
|
Hi all,
I have a materialized view which is created using joins of fact tables and dimension tables. The MV has the "ENABLE QUERY REWRITE" clause.
The problem is that the SQL that is meant to use the MV is actually using the MV in DEV/TEST environments, but not in production and ends up scanning the tables instead. Stats on the tables are more or less the same. The table definitions, constraints and dimension definitions are exactly the same.
All the tables in the MV and the MV itself are present in a single schema (IM_APL), and IM_APL on production has GLOBAL_QUERY_REWRITE grant, whereas IM_APL on DEV/TEST has both QUERY_REWRITE & GLOBAL_QUERY_REWRITE grants. I may be wrong, but I'm guessing that this lack of one grant is not the problem as GLOBAL_QUERY_REWRITE is a "wider" grant than QUERY_REWRITE.
The MV has undergone a full refresh and is not stale, and has also been analyzed. I have tried to set ALTER SESSION QUERY_REWRITE_ENABLED=TRUE and ALTER SESSION QUERY_REWRITE_INTEGRITY=TRUSTED, and also provided the REWRITE hint specifying the actual MV name (in case the cost is a problem), but it still ends up scanning the base tables.
I also stripped the SQL from the MV definition and tried to run it as it is, with and without where clauses, but without luck.
Using dbms_mview.explain_rewrite results in the message "QSM-01219: no suitable materialized view found to rewrite this query".
Please can someone provide an insight into what could be wrong?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Materialized View not being used on production (works elsewhere) [message #611711 is a reply to message #611287] |
Mon, 07 April 2014 10:32  |
|
d_seng
Messages: 78
Registered: November 2011
Location: UK
|
Member |
|
|
A slightly bizzare outcome...the dev, test and user acceptance databases have QUERY_REWRITE_INTEGRITY set to ENFORCED. There do exist NOVALIDATE RELY constraints, and the MV is being used by the query in all the databases EXCEPT for production!
Changing the session parameter to TRUSTED on production solves the problem, but a new question now arises, why is the query using the MV when NOVALIDATE RELY constraints exist and the QUERY_REWRITE_INTEGRITY is set to ENFORCED?
On Dev box:
SHOW PARAMETER QUERY_REWRITE_INTEGRITY
NAME TYPE VALUE
-------------------------------------------------- ----------- ---------
query_rewrite_integrity string enforced
EXPLAIN PLAN FOR
SELECT * FROM
(SELECT COM_D_REPMONTH.D_YEAR,
NAREP_D_SAP_COMPANY.D_COMPANY_CODE,
NAREP_V_PREM_CLM_BROKER.PREMIUM_CLAIM,
NAREP_V_PREM_CLM_BROKER.F_LOSS_YEAR,
SUM(NAREP_V_PREM_CLM_BROKER.F_EARNED_PREMIUM) F_EARNED_PREMIUM ,
SUM(NAREP_V_PREM_CLM_BROKER.F_PAID_LOSS) F_PAID_LOSS
FROM IM_APL_01.COM_D_REPMONTH,
IM_APL_01.NAREP_D_SAP_LOB,
IM_APL_01.NAREP_D_BROKER,
IM_APL_01.NAREP_D_SAP_COMPANY,
IM_APL_01.NAREP_D_SAP_CURRENCY,
IM_APL_01.NAREP_D_SAP_BUSINESS_CATG,
IM_APL_01.NAREP_D_INSURED,
IM_APL_01.NAREP_V_PREM_CLM_BROKER
WHERE NAREP_D_BROKER.D_GROUP_NAME IS NOT NULL
AND COM_D_REPMONTH.SID_REPMONTH = NAREP_V_PREM_CLM_BROKER.SID_REPMONTH
AND NAREP_D_BROKER.SID_BROKER = NAREP_V_PREM_CLM_BROKER.SID_BROKER
AND NAREP_D_SAP_LOB.SID_SAP_LOB = NAREP_V_PREM_CLM_BROKER.SID_SAP_LOB
AND NAREP_D_SAP_COMPANY.SID_SAP_COMPANY = NAREP_V_PREM_CLM_BROKER.SID_SAP_COMPANY
AND NAREP_D_SAP_CURRENCY.SID_SAP_CURRENCY = NAREP_V_PREM_CLM_BROKER.SID_SAP_CURRENCY
AND NAREP_D_SAP_BUSINESS_CATG.SID_SAP_BUSINESS_CATG = NAREP_V_PREM_CLM_BROKER.SID_SAP_BUSINESS_CATG
AND NAREP_D_INSURED.SID_INSURED = NAREP_V_PREM_CLM_BROKER.SID_INSURED
GROUP BY COM_D_REPMONTH.D_YEAR,
COM_D_REPMONTH.D_QUARTER,
NAREP_D_SAP_COMPANY.D_COMPANY_CODE,
NAREP_V_PREM_CLM_BROKER.PREMIUM_CLAIM,
NAREP_V_PREM_CLM_BROKER.F_LOSS_YEAR
) "SQL1"
WHERE "SQL1"."D_YEAR" =2013
AND "SQL1"."D_COMPANY_CODE" IN ('US01','US05','US54','US64')
AND ("SQL1"."PREMIUM_CLAIM"='PREMIUM'
OR "SQL1"."PREMIUM_CLAIM" ='CLAIM'
AND "SQL1"."F_LOSS_YEAR" IN (2013));
Explained.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 2674047930
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124K| 21M| | 6745 (1)| 00:01:21 |
| 1 | HASH GROUP BY | | 124K| 21M| 30M| 6745 (1)| 00:01:21 |
|* 2 | HASH JOIN | | 124K| 21M| | 1705 (2)| 00:00:21 |
|* 3 | TABLE ACCESS FULL | COM_D_REPMONTH | 12 | 132 | | 9 (0)| 00:00:01 |
|* 4 | MAT_VIEW REWRITE ACCESS FULL| NAREP_MV_PREM_CLM_BROKER | 124K| 20M| | 1695 (2)| 00:00:21 |
-------------------------------------------------------------------------------------------------------------------
SELECT CONSTRAINT_TYPE, STATUS, VALIDATED, RELY, COUNT(*)
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'NAREP%'
AND CONSTRAINT_TYPE IN ('P','R')
GROUP BY CONSTRAINT_TYPE, STATUS, VALIDATED, RELY;
CONSTRAINT_TYPE STATUS VALIDATED RELY COUNT(*)
--------------- -------- ------------- ---- ----------
R DISABLED NOT VALIDATED RELY 59
P ENABLED VALIDATED RELY 17
On Prod box:
SHOW PARAMETER QUERY_REWRITE_INTEGRITY
NAME TYPE VALUE
-------------------------------------------------- ----------- ---------
query_rewrite_integrity string enforced
EXPLAIN PLAN FOR
SELECT * FROM
(SELECT COM_D_REPMONTH.D_YEAR,
NAREP_D_SAP_COMPANY.D_COMPANY_CODE,
NAREP_V_PREM_CLM_BROKER.PREMIUM_CLAIM,
NAREP_V_PREM_CLM_BROKER.F_LOSS_YEAR,
SUM(NAREP_V_PREM_CLM_BROKER.F_EARNED_PREMIUM) F_EARNED_PREMIUM ,
SUM(NAREP_V_PREM_CLM_BROKER.F_PAID_LOSS) F_PAID_LOSS
FROM IM_APL_01.COM_D_REPMONTH,
IM_APL_01.NAREP_D_SAP_LOB,
IM_APL_01.NAREP_D_BROKER,
IM_APL_01.NAREP_D_SAP_COMPANY,
IM_APL_01.NAREP_D_SAP_CURRENCY,
IM_APL_01.NAREP_D_SAP_BUSINESS_CATG,
IM_APL_01.NAREP_D_INSURED,
IM_APL_01.NAREP_V_PREM_CLM_BROKER
WHERE NAREP_D_BROKER.D_GROUP_NAME IS NOT NULL
AND COM_D_REPMONTH.SID_REPMONTH = NAREP_V_PREM_CLM_BROKER.SID_REPMONTH
AND NAREP_D_BROKER.SID_BROKER = NAREP_V_PREM_CLM_BROKER.SID_BROKER
AND NAREP_D_SAP_LOB.SID_SAP_LOB = NAREP_V_PREM_CLM_BROKER.SID_SAP_LOB
AND NAREP_D_SAP_COMPANY.SID_SAP_COMPANY = NAREP_V_PREM_CLM_BROKER.SID_SAP_COMPANY
AND NAREP_D_SAP_CURRENCY.SID_SAP_CURRENCY = NAREP_V_PREM_CLM_BROKER.SID_SAP_CURRENCY
AND NAREP_D_SAP_BUSINESS_CATG.SID_SAP_BUSINESS_CATG = NAREP_V_PREM_CLM_BROKER.SID_SAP_BUSINESS_CATG
AND NAREP_D_INSURED.SID_INSURED = NAREP_V_PREM_CLM_BROKER.SID_INSURED
GROUP BY COM_D_REPMONTH.D_YEAR,
COM_D_REPMONTH.D_QUARTER,
NAREP_D_SAP_COMPANY.D_COMPANY_CODE,
NAREP_V_PREM_CLM_BROKER.PREMIUM_CLAIM,
NAREP_V_PREM_CLM_BROKER.F_LOSS_YEAR
) "SQL1"
WHERE "SQL1"."D_YEAR" =2013
AND "SQL1"."D_COMPANY_CODE" IN ('US01','US05','US54','US64')
AND ("SQL1"."PREMIUM_CLAIM"='PREMIUM'
OR "SQL1"."PREMIUM_CLAIM" ='CLAIM'
AND "SQL1"."F_LOSS_YEAR" IN (2013));
Explained.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 3458524311
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 928 | | 515K (1)| 01:43:04 | | |
| 1 | HASH GROUP BY | | 8 | 928 | | 515K (1)| 01:43:04 | | |
|* 2 | HASH JOIN | | 5739K| 634M| | 514K (1)| 01:43:00 | | |
| 3 | INDEX FAST FULL SCAN | NAREP_D_LOB_PK | 4078 | 16312 | | 4 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 5739K| 613M| | 514K (1)| 01:42:59 | | |
| 5 | INDEX FAST FULL SCAN | NAREP_D_BUSINESS_CATEGORY_PK | 865 | 3460 | | 2 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 5739K| 591M| | 514K (1)| 01:42:59 | | |
| 7 | INDEX FULL SCAN | NAREP_D_CURRENCY_PK | 202 | 808 | | 1 (0)| 00:00:01 | | |
|* 8 | HASH JOIN | | 5739K| 569M| 7320K| 514K (1)| 01:42:59 | | |
| 9 | INDEX FAST FULL SCAN | NAREP_D_INSURED_PK | 440K| 2150K| | 229 (2)| 00:00:03 | | |
|* 10 | HASH JOIN | | 5739K| 541M| | 484K (1)| 01:36:49 | | |
|* 11 | TABLE ACCESS FULL | COM_D_REPMONTH | 12 | 132 | | 10 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 6696K| 561M| | 484K (1)| 01:36:49 | | |
|* 13 | TABLE ACCESS FULL | NAREP_D_BROKER | 2560 | 20480 | | 68 (0)| 00:00:01 | | |
|* 14 | HASH JOIN | | 7983K| 609M| | 483K (1)| 01:36:47 | | |
|* 15 | TABLE ACCESS FULL | NAREP_D_SAP_COMPANY | 4 | 32 | | 3 (0)| 00:00:01 | | |
| 16 | VIEW | NAREP_V_PREM_CLM_BROKER | 14M| 1027M| | 483K (1)| 01:36:46 | | |
| 17 | UNION-ALL | | | | | | | | |
|* 18 | HASH JOIN | | 14M| 761M| | 304K (2)| 01:00:50 | | |
| 19 | MERGE JOIN CARTESIAN| | 1246 | 17444 | | 14 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS FULL | NAREP_D_KIND_FOR_GROUP | 7 | 42 | | 3 (0)| 00:00:01 | | |
| 21 | BUFFER SORT | | 178 | 1424 | | 11 (0)| 00:00:01 | | |
| 22 | TABLE ACCESS FULL | NAREP_D_REVENUE_CODE | 178 | 1424 | | 2 (0)| 00:00:01 | | |
| 23 | PARTITION LIST ALL | | 16M| 676M| | 303K (1)| 01:00:48 | 1 | 109 |
|* 24 | TABLE ACCESS FULL | NAREP_F_HIST_TECHPREM | 16M| 676M| | 303K (1)| 01:00:48 | 1 | 109 |
|* 25 | HASH JOIN | | 956K| 40M| | 179K (1)| 00:35:57 | | |
| 26 | TABLE ACCESS FULL | NAREP_D_KIND_FOR_GROUP | 7 | 42 | | 3 (0)| 00:00:01 | | |
| 27 | PARTITION LIST ALL | | 956K| 34M| | 179K (1)| 00:35:57 | 1 | 109 |
|* 28 | TABLE ACCESS FULL | NAREP_F_HIST_TECHCLM | 956K| 34M| | 179K (1)| 00:35:57 | 1 | 109 |
----------------------------------------------------------------------------------------------------------------------------------------
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
Session altered.
EXPLAIN PLAN FOR
SELECT * FROM
(SELECT COM_D_REPMONTH.D_YEAR,
NAREP_D_SAP_COMPANY.D_COMPANY_CODE,
NAREP_V_PREM_CLM_BROKER.PREMIUM_CLAIM,
NAREP_V_PREM_CLM_BROKER.F_LOSS_YEAR,
SUM(NAREP_V_PREM_CLM_BROKER.F_EARNED_PREMIUM) F_EARNED_PREMIUM ,
SUM(NAREP_V_PREM_CLM_BROKER.F_PAID_LOSS) F_PAID_LOSS
FROM IM_APL_01.COM_D_REPMONTH,
IM_APL_01.NAREP_D_SAP_LOB,
IM_APL_01.NAREP_D_BROKER,
IM_APL_01.NAREP_D_SAP_COMPANY,
IM_APL_01.NAREP_D_SAP_CURRENCY,
IM_APL_01.NAREP_D_SAP_BUSINESS_CATG,
IM_APL_01.NAREP_D_INSURED,
IM_APL_01.NAREP_V_PREM_CLM_BROKER
WHERE NAREP_D_BROKER.D_GROUP_NAME IS NOT NULL
AND COM_D_REPMONTH.SID_REPMONTH = NAREP_V_PREM_CLM_BROKER.SID_REPMONTH
AND NAREP_D_BROKER.SID_BROKER = NAREP_V_PREM_CLM_BROKER.SID_BROKER
AND NAREP_D_SAP_LOB.SID_SAP_LOB = NAREP_V_PREM_CLM_BROKER.SID_SAP_LOB
AND NAREP_D_SAP_COMPANY.SID_SAP_COMPANY = NAREP_V_PREM_CLM_BROKER.SID_SAP_COMPANY
AND NAREP_D_SAP_CURRENCY.SID_SAP_CURRENCY = NAREP_V_PREM_CLM_BROKER.SID_SAP_CURRENCY
AND NAREP_D_SAP_BUSINESS_CATG.SID_SAP_BUSINESS_CATG = NAREP_V_PREM_CLM_BROKER.SID_SAP_BUSINESS_CATG
AND NAREP_D_INSURED.SID_INSURED = NAREP_V_PREM_CLM_BROKER.SID_INSURED
GROUP BY COM_D_REPMONTH.D_YEAR,
COM_D_REPMONTH.D_QUARTER,
NAREP_D_SAP_COMPANY.D_COMPANY_CODE,
NAREP_V_PREM_CLM_BROKER.PREMIUM_CLAIM,
NAREP_V_PREM_CLM_BROKER.F_LOSS_YEAR
) "SQL1"
WHERE "SQL1"."D_YEAR" =2013
AND "SQL1"."D_COMPANY_CODE" IN ('US01','US05','US54','US64')
AND ("SQL1"."PREMIUM_CLAIM"='PREMIUM'
OR "SQL1"."PREMIUM_CLAIM" ='CLAIM'
AND "SQL1"."F_LOSS_YEAR" IN (2013));
Explained.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 2474134474
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 144 | 4060 (1)| 00:00:49 |
| 1 | HASH GROUP BY | | 4 | 144 | 4060 (1)| 00:00:49 |
|* 2 | HASH JOIN | | 183K| 6454K| 4050 (1)| 00:00:49 |
|* 3 | TABLE ACCESS FULL | COM_D_REPMONTH | 12 | 132 | 10 (0)| 00:00:01 |
|* 4 | MAT_VIEW REWRITE ACCESS FULL| NAREP_MV_PREM_CLM_BROKER_TEST | 214K| 5229K| 4038 (1)| 00:00:49 |
----------------------------------------------------------------------------------------------------------------
SELECT CONSTRAINT_TYPE, STATUS, VALIDATED, RELY, COUNT(*)
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'NAREP%'
AND CONSTRAINT_TYPE IN ('P','R')
GROUP BY CONSTRAINT_TYPE, STATUS, VALIDATED, RELY;
CONSTRAINT_TYPE STATUS VALIDATED RELY COUNT(*)
--------------- -------- ------------- ---- ----------
R DISABLED NOT VALIDATED RELY 59
P ENABLED VALIDATED RELY 17
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 02:33:00 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
