Posts Tagged Performance Tuning

Combining Resource Consumer Groups with Application Modules in #Oracle

This article contains a complete working example for the Resource Manager on the command line for those of you who can’t use the Enterprise Manager fort it. Believe me, I feel your pain 😉

As a good practice, PL/SQL procedures should be using DBMS_APPLICATION_INFO to mark their modules and actions. Not only for monitoring purpose but also to provide a way to tweak the system if things start going ugly in terms of performance. Here’s where the Resource Manager steps in.

Sessions can be assigned to different consumer groups depending on the module. Say we have an application with certain modules that sometimes consume an awful lot of CPU resources or way too much parallel processes. When the problem surfaces, you may not have enough time to fix the coding because it’s a live production run. The mentioned tweak – if prepared beforehand – may save the day. Let’s look at an example:

 
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
    EXPLICIT => 1,
    SERVICE_MODULE_ACTION => 2,
    SERVICE_MODULE => 3,
    MODULE_NAME_ACTION => 4,
    MODULE_NAME => 5,
    SERVICE_NAME => 6,
    ORACLE_USER => 7,
    CLIENT_PROGRAM => 8,
    CLIENT_OS_USER => 9,
    CLIENT_MACHINE => 10,
    CLIENT_ID => 11);

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

The above set the priority of MODULE_NAME over ORACLE_USER, which is not the default. The state of the priorities can be seen in DBA_RSRC_MAPPING_PRIORITY. Now I create two consumer groups:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
     CONSUMER_GROUP => 'A_GROUP',
     COMMENT        => 'A Group');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
     CONSUMER_GROUP => 'B_GROUP',
     COMMENT        => 'B Group');

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

My demo user ADAM gets the right to be a member of these consumer groups:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   GRANTEE_NAME   => 'ADAM',
   CONSUMER_GROUP => 'A_GROUP',
   GRANT_OPTION   =>  FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   GRANTEE_NAME   => 'ADAM',
   CONSUMER_GROUP => 'B_GROUP',
   GRANT_OPTION   =>  FALSE);

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

Now the part where consumer group and module is combined respectively mapped:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
     (DBMS_RESOURCE_MANAGER.MODULE_NAME, 'A_MODULE', 'A_GROUP');

  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
     (DBMS_RESOURCE_MANAGER.MODULE_NAME, 'B_MODULE', 'B_GROUP');

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

Next comes the Resource Manager Plan. The restrictions are a bit rigid to show an obvious effect – 95 to 5 percent favors Group A very much over Group B:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
     PLAN    => 'TESTPLAN',
     COMMENT => 'test');

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
     PLAN                     => 'MYPLAN', 
     GROUP_OR_SUBPLAN         => 'SYS_GROUP',    /* built-in group */
     COMMENT                  => 'SYS Group',
     MGMT_P1                  => 100);

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
     PLAN                     => 'MYPLAN', 
     GROUP_OR_SUBPLAN         => 'A_GROUP',
     COMMENT                  => 'A GROUP',
     parallel_degree_limit_p1 => 8 ,          /* RESTRICTION HERE */
     MGMT_P2                  => 95);

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
     PLAN                     => 'MYPLAN', 
     GROUP_OR_SUBPLAN         => 'B_GROUP',
     COMMENT                  => 'B GROUP',
      parallel_degree_limit_p1 => 2 ,          /* RESTRICTION HERE */
      MGMT_P2                  => 5);

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
     PLAN                     => 'MYPLAN', 
     GROUP_OR_SUBPLAN         => 'OTHER_GROUPS', /* built-in group */
     COMMENT                  => 'Others',
     MGMT_P3                  => 100);

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

END;
/

So far, no restriction is in place, because the plan is not yet active. But everything is now prepared. Should Module B consume too much CPU or demand too much parallel processes, the plan can be set with this :

BEGIN
    DBMS_RESOURCE_MANAGER.SWITCH_PLAN(plan_name => 'MYPLAN');
END;
/

The sessions that have the module set are subject to the restrictions as soon as the plan is activated. If a new module is set during an existing session, the session is switched into the new consumer group. The parallel restriction have precedence over parallel hints:

SQL> connect adam/adam@prima
Connected.
SQL> select distinct sid from v$mystat;

       SID
----------
	 4

SQL> exec dbms_application_info.set_module(module_name => 'A_MODULE',action_name => 'A-ACTION')

PL/SQL procedure successfully completed.

SQL> select resource_consumer_group from v$session where sid=4;

RESOURCE_CONSUMER_GROUP
--------------------------------
A_GROUP

SQL> select /*+ parallel (dual,16) */ * from dual;

D
-
X

SQL> select * from v$pq_sesstat; 

STATISTIC		       LAST_QUERY SESSION_TOTAL     CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized			1	      1 	 0
DML Parallelized			0	      0 	 0
DDL Parallelized			0	      0 	 0
DFO Trees				1	      1 	 0
Server Threads				8	      0 	 0
Allocation Height			8	      0 	 0
Allocation Width			1	      0 	 0
Local Msgs Sent 		       24	     24 	 0
Distr Msgs Sent 			0	      0 	 0
Local Msgs Recv'd		       22	     22 	 0
Distr Msgs Recv'd			0	      0 	 0
DOP					8	      0 	 0
Slave Sets				1	      0 	 0

13 rows selected.

SQL> exec dbms_application_info.set_module(module_name => 'B_MODULE',action_name => 'B-ACTION')

PL/SQL procedure successfully completed.

SQL> select resource_consumer_group from v$session where sid=4;

RESOURCE_CONSUMER_GROUP
--------------------------------
B_GROUP

SQL> select /*+ parallel (dual,16) */ * from dual;

D
-
X

SQL> select * from v$pq_sesstat; 

STATISTIC		       LAST_QUERY SESSION_TOTAL     CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized			1	      2 	 0
DML Parallelized			0	      0 	 0
DDL Parallelized			0	      0 	 0
DFO Trees				1	      2 	 0
Server Threads				2	      0 	 0
Allocation Height			2	      0 	 0
Allocation Width			1	      0 	 0
Local Msgs Sent 			8	     32 	 0
Distr Msgs Sent 			0	      0 	 0
Local Msgs Recv'd			8	     30 	 0
Distr Msgs Recv'd			0	      0 	 0
DOP					2	      0 	 0
Slave Sets				1	      0 	 0

13 rows selected.

To test the CPU restrictions, I used scripts like this:

set serveroutput on
declare
    v_starttime timestamp;
    v_endtime timestamp;
begin
    dbms_application_info.set_module(module_name => 'A_MODULE',action_name => 'A-ACTION');
    v_starttime:=current_timestamp;
    for i in 1..1000000000 loop
        for j in 1..1000000000 loop
            for k in 1..10000 loop
                null;
            end loop;
        end loop;
    end loop;
    v_endtime:=current_timestamp;
    dbms_output.put_line('Seconds elapsed Module A: '||to_char(extract(second from v_endtime-v_starttime)));
end;
/

With CPU_COUNT set to 1 (remember this is a dynamic parameter since 11g and this Instance Caging feature requires a Resource Manager plan to be active), two sessions each running scripts like that one setting module A and the other module B are enough to see the effect. On my system, both sessions need about 15 seconds without the plan while module A completes in about 10 seconds vs module B in 20 seconds with the plan active.

Apart from the shown restrictions, there are other useful options available like Active Session Pool, Maximum Estimated Execution Time, Undo Quota and Idle Blocker Time. Each of these can come in handy to tweak or troubleshoot a misbehaving application without having to touch the code. See here for a whole lot of more details.

The demo was done with 12c but works the same in 11g, probably also in 10g. As always: Don’t believe it, test it! 🙂

, ,

Leave a comment

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

How to reduce Buffer Busy Waits with Hash Partitioned Tables in #Oracle

fight_contention_2

Large OLTP sites may suffer from Buffer Busy Waits. Hash Partitioning is one way to reduce it on both, Indexes and Tables. My last post demonstrated that for Indexes, now let’s see how it looks like with Tables. Initially there is a normal table that is not yet hash partitioned. If many sessions do insert now simultaneously, the problem shows:

Contention with a heap table

Contention with a heap table

The last extent becomes a hot spot; all inserts go there and only a limited number of blocks is available. Therefore we will see Buffer Busy Waits. The playground:

SQL> create table t (id number, sometext varchar2(50));

Table created.

create sequence id_seq;

Sequence created.

create or replace procedure manyinserts as
begin
 for i in 1..10000 loop
  insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?');
 end loop;
 commit;
end;
/

Procedure created.

create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..100 LOOP
 dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/

Procedure created.

The procedure manysessions is the way how I simulate OLTP end user activity on my demo system. Calling it leads to 100 job sessions. Each does 10.000 inserts:

SQL> exec manysessions

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
T			    2985

So we got thousands of Buffer Busy Waits that way. Now the remedy:

SQL> drop table t purge;

Table dropped.

SQL> create table t (id number, sometext varchar2(50))
     partition by hash (id) partitions 32;

Table created.

 
SQL> alter procedure manyinserts compile;

Procedure altered.

SQL> alter procedure manysessions compile;

Procedure altered.

SQL> exec manysessions 

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';  

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
T	   SYS_P249	       0
T	   SYS_P250	       1
T	   SYS_P251	       0
T	   SYS_P252	       0
T	   SYS_P253	       0
T	   SYS_P254	       0
T	   SYS_P255	       0
T	   SYS_P256	       1
T	   SYS_P257	       0
T	   SYS_P258	       0
T	   SYS_P259	       1
T	   SYS_P260	       0
T	   SYS_P261	       0
T	   SYS_P262	       0
T	   SYS_P263	       0
T	   SYS_P264	       1
T	   SYS_P265	       1
T	   SYS_P266	       0
T	   SYS_P267	       0
T	   SYS_P268	       0
T	   SYS_P269	       0
T	   SYS_P270	       0
T	   SYS_P271	       1
T	   SYS_P272	       0
T	   SYS_P273	       0
T	   SYS_P274	       0
T	   SYS_P275	       1
T	   SYS_P276	       0
T	   SYS_P277	       0
T	   SYS_P278	       0
T	   SYS_P279	       2
T	   SYS_P280	       0

32 rows selected.

SQL> select sum(value) from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';

SUM(VALUE)
----------
	 9

SQL> select 2985-9 as waits_gone from dual;

WAITS_GONE
----------
      2976

The hot spot is gone:

hash_part_table

This emphasizes again that Partitioning is not only for the Data Warehouse. Hash Partitioning in particular can be used to fight contention in OLTP environments.

,

1 Comment

%d bloggers like this: