Uwe Hesse

This user hasn't shared any biographical information

Homepage: http://uhesse.com

Upgrade nach #Oracle 12c selbst testen: Mainz, 2. bis 3. Februar

noon2noon

Die Deutsche Oracle Anwender Gruppe (DOAG) bietet diesen interaktiven Workshop an. Eine sehr gute Gelegenheit, das Upgrade einmal selbst mit der Unterstützung renommierter Experten durchzuführen. Ich werde einen Vortrag zum Thema Common vs. Local beisteuern. Das ist eins der tendenziell komplizierten Themen, mit denen Einsteiger in Multitenant konfrontiert werden.

Leave a comment

Auto Sync for Password Files in #Oracle 12c Data Guard

sync

A useful enhancement in 12cR2 is that password files are automatically synchronized in a Data Guard environment:

[oracle@uhesse1 ~]$ dgmgrl sys/oracle_4U@cdb1
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Jan 10 07:38:15 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "cdb1"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxPerformance
  Members:
  cdb1   - Primary database
    cdb1sb - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

DGMGRL> exit
[oracle@uhesse1 ~]$ sqlplus sys/oracle_4U@cdb1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 10 07:38:49 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter user sys identified by oracle;

User altered.

SQL> connect sys/oracle@cdb1 as sysdba
Connected.
SQL> connect sys/oracle@cdb1sb as sysdba
Connected.

I have just changed the password of sys on the Primary and it got automatically also changed on the Standby. In earlier versions that had to be done manually, typically by replacing the password file on the standby with a new copy from the primary. One little thing less to keep in mind now 🙂

,

2 Comments

Real-Time Materialized Views in #Oracle 12c

helps

In 12cR2, a Materialized View that is STALE can still speed up queries while delivering correct results. The data from the stale MV is then on the fly combined with the change information from MV logs in an operation called ON QUERY COMPUTATION. The result is delivered slightly slower as if the MV were FRESH, so there is some overhead involved in the process. But it should be noticeable faster than having to do Full Table Scans as it was required in versions before 12c in that situation.

Operationally, that means that REFRESH can be done less frequently while keeping satisfactory query performance all the time. Let’s see that in action:

[oracle@uhesse ~]$ sqlplus adam/adam@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 5 14:31:00 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 05 2017 10:57:35 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set timing on
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
	 1	    4000000
	 2	    4000000
	 4	    4000000
	 3	    4000000
	 0	    4000000

Elapsed: 00:00:03.47
SQL> set timing off

The query takes more than three seconds without an MV initially.

SQL> create materialized view log on sales
     with rowid, sequence(channel_id,amount_sold)
     including new values;   

Materialized view log created.

SQL> create materialized view mv1
     refresh fast on demand
     enable query rewrite 
     enable on query computation
     as
     select channel_id,
     sum(amount_sold),
     count(amount_sold),
     count(*)
     from sales
     group by channel_id;  

Materialized view created.

SQL> set timing on                                                     
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
	 1	    4000000
	 2	    4000000
	 4	    4000000
	 3	    4000000
	 0	    4000000

Elapsed: 00:00:00.07
SQL> set timing off

The FRESH MV speeds up the query – not yet new. The same kind of execution plan would have been used in 11g:

SQL> @lastplan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID	9wwp2am6pm4dz, child number 1
-------------------------------------
select channel_id,sum(amount_sold) from sales group by channel_id

Plan hash value: 2958490228

-------------------------------------------------------------------------------------
| Id  | Operation		     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	    |	    |	    |	  3 (100)|	    |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |	  5 |	 30 |	  3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


13 rows selected.

Now I change something in the sales table, making the MV STALE:

SQL> update sales set amount_sold=2 where rownum<2; 

1 row updated. 

SQL> commit;

Commit complete.

SQL> select mview_name,staleness,on_query_computation from user_mviews;

MVIEW_NAME STALENESS	       O
---------- ------------------- -
MV1	   NEEDS_COMPILE       Y

In spite of the STALE MV, the next query is still fast, although not as fast as with the FRESH MV:

SQL> set timing on
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
	 2	    4000000
	 3	    4000000
	 4	    4000000
	 0	    4000000
	 1	    4000001

Elapsed: 00:00:00.12
SQL> set timing off

So what happens is roughly this:

realtime_mv

That there’s some work been done under the covers is revealed by looking at the (rather scary) execution plan now:

SQL> @lastplan

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID	9wwp2am6pm4dz, child number 2
-------------------------------------
select channel_id,sum(amount_sold) from sales group by channel_id

Plan hash value: 2525395710

---------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		  |	  |	  |    18 (100)|	  |
|   1 |  VIEW				    |		  |   363 |  9438 |    18  (23)| 00:00:01 |
|   2 |   UNION-ALL			    |		  |	  |	  |	       |	  |
|*  3 |    VIEW 			    | VW_FOJ_0	  |   100 |  2900 |	7  (15)| 00:00:01 |
|*  4 |     HASH JOIN FULL OUTER	    |		  |   100 |  4300 |	7  (15)| 00:00:01 |
|   5 |      VIEW			    |		  |	5 |   160 |	3   (0)| 00:00:01 |
|   6 |       MAT_VIEW ACCESS FULL	    | MV1	  |	5 |    60 |	3   (0)| 00:00:01 |
|   7 |      VIEW			    |		  |   100 |  1100 |	4  (25)| 00:00:01 |
|   8 |       HASH GROUP BY		    |		  |	  |	  |	4  (25)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL	    | MLOG$_SALES |	2 |    74 |	3   (0)| 00:00:01 |
|  10 |    VIEW 			    |		  |   263 |  6838 |    11  (28)| 00:00:01 |
|  11 |     UNION-ALL			    |		  |	  |	  |	       |	  |
|* 12 |      FILTER			    |		  |	  |	  |	       |	  |
|  13 |       NESTED LOOPS OUTER	    |		  |   250 | 16000 |	4  (25)| 00:00:01 |
|  14 |        VIEW			    |		  |   100 |  5200 |	4  (25)| 00:00:01 |
|* 15 | 	FILTER			    |		  |	  |	  |	       |	  |
|  16 | 	 HASH GROUP BY		    |		  |	  |	  |	4  (25)| 00:00:01 |
|* 17 | 	  TABLE ACCESS FULL	    | MLOG$_SALES |	2 |    74 |	3   (0)| 00:00:01 |
|* 18 |        INDEX UNIQUE SCAN	    | I_SNAP$_MV1 |	3 |    36 |	0   (0)|	  |
|  19 |      MERGE JOIN 		    |		  |    13 |   871 |	7  (29)| 00:00:01 |
|  20 |       MAT_VIEW ACCESS BY INDEX ROWID| MV1	  |	5 |    60 |	2   (0)| 00:00:01 |
|  21 |        INDEX FULL SCAN		    | I_SNAP$_MV1 |	5 |	  |	1   (0)| 00:00:01 |
|* 22 |       FILTER			    |		  |	  |	  |	       |	  |
|* 23 |        SORT JOIN		    |		  |   100 |  5500 |	5  (40)| 00:00:01 |
|  24 | 	VIEW			    |		  |   100 |  5500 |	4  (25)| 00:00:01 |
|  25 | 	 SORT GROUP BY		    |		  |	  |	  |	4  (25)| 00:00:01 |
|* 26 | 	  TABLE ACCESS FULL	    | MLOG$_SALES |	2 |    74 |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("AV$0"."OJ_MARK" IS NULL)
   4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
   9 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-01-05 14:32:07', 'syyyy-mm-dd hh24:mi:ss'))
  12 - filter(CASE  WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END  IS NULL)
  15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1)))>0)
  17 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-01-05 14:32:07', 'syyyy-mm-dd hh24:mi:ss'))
  18 - access("MV1"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
  22 - filter("MV1"."COUNT(*)"+"AV$0"."D0">0)
  23 - access("MV1"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
       filter("MV1"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
  26 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-01-05 14:32:07', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


60 rows selected.

But the query delivers the correct result – so that is not simply using the STALE MV only like QUERY_REWRITE_INTEGRITY=STALE_TOLERATED does. Just to confirm:

SQL> show parameter query_rewrite_integrity

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_integrity 	     string	 enforced

Still REFRESH should be done from time to time like here:

SQL> exec dbms_mview.refresh('MV1','F')

PL/SQL procedure successfully completed.

SQL> set timing on
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
	 1	    4000001
	 2	    4000000
	 4	    4000000
	 3	    4000000
	 0	    4000000

Elapsed: 00:00:00.06

Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option? At least I think so 🙂

Watch me on YouTube explaining and demonstrating the above:

, ,

6 Comments

%d bloggers like this: