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 Go to next message
d_seng
Messages: 61
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 #609007 is a reply to message #608885] Thu, 27 February 2014 23:55 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
From memory, I recall that dbms_mview.explain_MVIEW will tell you any reasons why the MV may be blocked from rewrite.

Probably not a problem, but just make sure your system/session parameter for Optimizer_Features_Enable is set to a version of Oracle that supports query rewrite.

Ross Leishman
Re: Materialized View not being used on production (works elsewhere) [message #609008 is a reply to message #609007] Thu, 27 February 2014 23:56 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
P.S. Nice summary of what you have already tried.
Re: Materialized View not being used on production (works elsewhere) [message #609057 is a reply to message #609008] Fri, 28 February 2014 09:57 Go to previous messageGo to next message
d_seng
Messages: 61
Registered: November 2011
Location: UK
Member
Thanks Ross.

The parameter optimizer_features_enable is set to 11.2.0.2 in all environments. In fact, all parameters are exactly the same, barring the SGA allocation and the data file names/locations.

On all environments, running DBMS_MVIEW.EXPLAIN_MVIEW give a Y value for REWRITE, REWRITE_FULL_TEXT_MATCH, REWRITE_PARTIAL_TEXT_MATCH & REWRITE_GENERAL. Only REWRITE_PCT is N.

I'm still at a loss.

I'll try and raise a change to grant QUERY REWRITE role to IM_APL and see what happens, but that might take a few days, unless the DBAs object to it for some reason.

In the meanwhile if anybody can suggest any other ideas, I'll be grateful.

Cheers
Re: Materialized View not being used on production (works elsewhere) [message #611051 is a reply to message #609057] Thu, 27 March 2014 06:04 Go to previous messageGo to next message
d_seng
Messages: 61
Registered: November 2011
Location: UK
Member
Unfortunately, granting QUERY REWRITE to the user didn't solve the issue. The query is still scanning the tables.
Re: Materialized View not being used on production (works elsewhere) [message #611052 is a reply to message #611051] Thu, 27 March 2014 06:10 Go to previous messageGo to next message
BlackSwan
Messages: 22810
Registered: January 2009
Senior Member
>I'll try and raise a change to grant QUERY REWRITE role to IM_APL
privilege acquired via ROLE does not apply within named PL/SQL procedure.
Re: Materialized View not being used on production (works elsewhere) [message #611121 is a reply to message #611052] Thu, 27 March 2014 15:04 Go to previous messageGo to next message
d_seng
Messages: 61
Registered: November 2011
Location: UK
Member
But this post is not about procedures, it is about a query not using a suitable MV
Re: Materialized View not being used on production (works elsewhere) [message #611263 is a reply to message #611121] Mon, 31 March 2014 20:00 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I imagine you have already looked at this page in the doco - http://docs.oracle.com/cd/E11882_01/server.112/e25554/qrbasic.htm#CHDCGGID

You're not using Rule based optimization ar you?

There are also some references to GLOBAL QUERY REWRITE vs QUERY REWRITE in that link. Are all of the underlying tables owned by the same user as the MV?

Ross Leishman
Re: Materialized View not being used on production (works elsewhere) [message #611287 is a reply to message #608885] Tue, 01 April 2014 02:27 Go to previous messageGo to next message
Roachcoach
Messages: 1206
Registered: May 2010
Location: UK
Senior Member
Would a 10053 tell you some useful information? i.e. if it is even considering it or not? And if yes, why it is eliminating it?

It'll also give you a big list of optimizer stuff that is enabled (or not) that you could diff with the places it is working.


Caveat: I've not looked at these for YOUR express problem, but this is where I go when I start banging my head into "why wont you do x/y/z".

[Updated on: Tue, 01 April 2014 02:28]

Report message to a moderator

Re: Materialized View not being used on production (works elsewhere) [message #611711 is a reply to message #611287] Mon, 07 April 2014 10:32 Go to previous message
d_seng
Messages: 61
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 
Previous Topic: SQL performance
Next Topic: 5M record insert takes two hours
Goto Forum:
  


Current Time: Tue Sep 23 21:29:05 CDT 2014

Total time taken to generate the page: 0.05903 seconds