Re: materialized view/_simple_view_merging weirdness

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Thu, 10 Sep 2015 11:36:53 +0800
Message-ID: <CABx0cSXvcSy8sEw_yJi3JYX0HDALSeAJZXTxFfB0aVanpaZ_eQ_at_mail.gmail.com>

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:36:53 CEST