Re: materialized view/_simple_view_merging weirdness

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Thu, 10 Sep 2015 11:49:45 +0800
Message-ID: <CABx0cSVctGnmb5f_oW0qWgegZUqBpwdvudEU=nWZ3uXSsmt4+w_at_mail.gmail.com>

We will resolve by un-setting parameter in cdbs and pdbs. However maybe shows I am lacking understanding in some fundamental concepts of mult-tennant architecture.
I assumed that query would be parsed using parameters from pdb, but seems not the case.

On 10 September 2015 at 11:36, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:

> SYS_at_JADE1.CDB$ROOT> alter session set container=CDB$ROOT;
>
> Session altered.
>
> SYS_at_JADE1.CDB$ROOT> show parameter simple
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> _simple_view_merging boolean FALSE
> SYS_at_JADE1.CDB$ROOT> alter session set container=JADE_LFTDWH;
>
> Session altered.
>
> SYS_at_JADE1.CDB$ROOT> show parameter simple
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> _simple_view_merging boolean TRUE
>
>
> Note, testcase is running inside CDB JADE_LFTDWH.
>
> On 10 September 2015 at 11:29, Patrick Jolliffe <jolliffe_at_gmail.com>
> wrote:
>
>> Double checking session setting rather than relying on "show parameter".
>> We have identified that spfile actually has _simple_view_merging=FALSE.
>> Note also this is multi-tenant, we restarted PDB yesterday and then
>> started getting this problem.
>> Maybe a bug?
>>
>> SQL> SET LINESIZE
>> 360
>>
>> SQL>
>>
>> SQL> select banner from v$version where banner like
>> '%Database%';
>>
>>
>>
>> BANNER
>>
>> --------------------------------------------------------------------------------
>>
>> Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
>> Production
>>
>>
>>
>> SQL> drop materialized view
>> test_mview;
>>
>>
>>
>> Materialized view
>> dropped.
>>
>>
>>
>> SQL> CREATE MATERIALIZED VIEW
>> test_mview
>>
>> 2 AS SELECT
>> dummy
>>
>> 3 FROM
>> dual;
>>
>>
>>
>> Materialized view
>> created.
>>
>>
>>
>> SQL> _at_pd
>> simple
>>
>> Show all parameters and session values from
>> x$ksppi/x$ksppcv...
>>
>>
>>
>> INDX I_HEX NAME
>> VALUE
>> DESCRIPTION
>> ---------- ----- --------------------------------------------------
>> ------------------------------
>> ----------------------------------------------------------------
>> ------
>>
>> 2801 AF1 _simple_view_merging
>> TRUE control simple view merging performed by the
>> optimizer
>>
>>
>> SQL> --show parameter
>> "_simple_view_merging";
>>
>> SQL> exec
>> dbms_mview.refresh('TEST_MVIEW');
>>
>> BEGIN dbms_mview.refresh('TEST_MVIEW');
>> END;
>>
>>
>>
>> *
>>
>> ERROR at line
>> 1:
>>
>> ORA-12008: error in materialized view refresh
>> path
>>
>> ORA-01732: data manipulation operation not legal on this
>> view
>>
>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>> 2821
>>
>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>> 3058
>>
>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>> 3017
>>
>> ORA-06512: at line
>> 1
>>
>>
>>
>>
>>
>> SQL> exec
>> dbms_mview.refresh('TEST_MVIEW');
>>
>> BEGIN dbms_mview.refresh('TEST_MVIEW');
>> END;
>>
>>
>>
>> *
>>
>> ERROR at line
>> 1:
>>
>> ORA-12008: error in materialized view refresh
>> path
>>
>> ORA-01732: data manipulation operation not legal on this
>> view
>>
>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>> 2821
>>
>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>> 3058
>>
>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>> 3017
>>
>> ORA-06512: at line
>> 1
>>
>>
>>
>>
>>
>> SQL> ALTER SESSION set
>> "_simple_view_merging"=TRUE;
>>
>>
>>
>> Session
>> altered.
>>
>>
>>
>> SQL> _at_pd
>> simple
>>
>> Show all parameters and session values from
>> x$ksppi/x$ksppcv...
>>
>>
>>
>> INDX I_HEX NAME
>> VALUE
>> DESCRIPTION
>> ---------- ----- --------------------------------------------------
>> ------------------------------
>> ----------------------------------------------------------------
>> ------
>>
>> 2801 AF1 _simple_view_merging
>> TRUE control simple view merging performed by the
>> optimizer
>>
>>
>> SQL> exec
>> dbms_mview.refresh('TEST_MVIEW');
>>
>>
>>
>> PL/SQL procedure successfully
>> completed.
>>
>>
>>
>> SQL>
>>
>> SQL>
>>
>> On 10 September 2015 at 11:07, Patrick Jolliffe <jolliffe_at_gmail.com>
>> wrote:
>>
>>> Anybody want to hazard a guess at what is going on there, I admit I am
>>> puzzled, seems very weird.
>>> Why such a simple mview refresh have a problem?
>>> Why does setting a parameter to same value it is currently seem to 'fix'
>>> the problem.
>>> Note this is exadata system.
>>>
>>> Thanks, Patrick
>>>
>>> SQL> select banner from v$version where banner like
>>> '%Database%';
>>>
>>>
>>>
>>> BANNER
>>>
>>> --------------------------------------------------------------------------------
>>>
>>> Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
>>> Production
>>>
>>>
>>>
>>> SQL> drop materialized view
>>> test_mview;
>>>
>>>
>>>
>>> Materialized view
>>> dropped.
>>>
>>>
>>>
>>> SQL> CREATE MATERIALIZED VIEW
>>> test_mview
>>>
>>> 2 AS SELECT
>>> dummy
>>>
>>> 3 FROM
>>> dual;
>>>
>>>
>>>
>>> Materialized view
>>> created.
>>>
>>>
>>>
>>> SQL> show parameter
>>> "_simple_view_merging";
>>>
>>>
>>>
>>> PARAMETER_NAME TYPE
>>> VALUE
>>>
>>> ------------------------------------------------------------ -----------
>>> ------------------------------------------------------------------------------------------
>>> ----------
>>>
>>> _simple_view_merging boolean
>>> TRUE
>>>
>>> SQL> exec
>>> dbms_mview.refresh('TEST_MVIEW');
>>>
>>> BEGIN dbms_mview.refresh('TEST_MVIEW');
>>> END;
>>>
>>>
>>>
>>> *
>>>
>>> ERROR at line
>>> 1:
>>>
>>> ORA-12008: error in materialized view refresh
>>> path
>>>
>>> ORA-01732: data manipulation operation not legal on this
>>> view
>>>
>>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>>> 2821
>>>
>>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>>> 3058
>>>
>>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>>> 3017
>>>
>>> ORA-06512: at line
>>> 1
>>>
>>>
>>>
>>>
>>>
>>> SQL> exec
>>> dbms_mview.refresh('TEST_MVIEW');
>>>
>>> BEGIN dbms_mview.refresh('TEST_MVIEW');
>>> END;
>>>
>>>
>>>
>>> *
>>>
>>> ERROR at line
>>> 1:
>>>
>>> ORA-12008: error in materialized view refresh
>>> path
>>>
>>> ORA-01732: data manipulation operation not legal on this
>>> view
>>>
>>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>>> 2821
>>>
>>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>>> 3058
>>>
>>> ORA-06512: at "SYS.DBMS_SNAPSHOT", line
>>> 3017
>>>
>>> ORA-06512: at line
>>> 1
>>>
>>>
>>>
>>>
>>>
>>> SQL> ALTER SESSION set
>>> "_simple_view_merging"=TRUE;
>>>
>>>
>>>
>>> Session
>>> altered.
>>>
>>>
>>>
>>> SQL> exec
>>> dbms_mview.refresh('TEST_MVIEW');
>>>
>>>
>>>
>>> PL/SQL procedure successfully
>>> completed.
>>>
>>>
>>>
>>> SQL>
>>>
>>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 10 2015 - 05:49:45 CEST