Archive for category TOI

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! 🙂

, ,

1 Comment

orapwd gives OPW-00029 Password complexity failed in #Oracle 12.2

When I tried to create a password file for a 12.2 database, it initially failed with my favorite (simple) password:

[oracle@uhesse dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprima password=oracle

OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters.

Two options to resolve this: Either provide a password that passes the complexity check, like:

[oracle@uhesse dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprima password=Very_Complex§1

Or create the password file in 12.1 format (default being 12.2 format)

[oracle@uhesse dbs]$ orapwd describe file=orapwprima
Password file Description : format=12.2 
[oracle@uhesse dbs]$ rm orapwprima
[oracle@uhesse dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprima password=oracle format=12
[oracle@uhesse dbs]$ orapwd describe file=orapwprima
Password file Description : format=12 

The only drawback of the 12.1 format seems to be the lack of support for granting administrative privileges to external users and enabling SSL and Kerberos authentication for administrative users, according to the documentation. Which means for me I will keep my passwords simple 🙂

, ,

2 Comments

Getting started with VPD in #Oracle

Virtual Private Database (VPD) aka Fine Grained Access Control may seem complicated at first when you look at the fine documentation about it.

This article gives a short and simple example to get you started easily. The demo below is with 12c Multitenant but works the same with 11g:

SQL> connect reports_owner/reports_owner@pdb1
Connected.
SQL> select * from reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

SQL> grant create session to monthly identified by monthly;

Grant succeeded.

SQL> grant select on reports_table to monthly;

Grant succeeded.

SQL> grant create session to daily identified by daily;

Grant succeeded.

SQL> grant select on reports_table to daily;

Grant succeeded.

Say this is a reporting system with a central table that is used for monthly and daily reports. We have separate users for the daily and monthly reports.
Initially, they see both kinds of entries in the table:

SQL> connect monthly/monthly@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

SQL> connect daily/daily@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

VPD enables both users to keep the same statement but they see only rows for monthly respectively daily frequency:

SQL> connect reports_owner/reports_owner@pdb1
Connected.
SQL> create or replace function frequency_function (schema varchar2, tab varchar2) 
return varchar2 
is
who varchar2(20);
freq char(1);
begin
 select  sys_context('userenv','session_user') into who from dual;
 case who
  when 'MONTHLY' then freq:='M';
  when 'DAILY' then freq:='D';
  else freq:='?';
 end case;

 return 'frequency=''' || freq || '''';
end;
/     

Function created.

SQL> begin
dbms_rls.add_policy(
 object_schema=>'REPORTS_OWNER',
 object_name=>'REPORTS_TABLE',
 policy_name=>'FREQUENCY_POLICY',
 function_schema=>'REPORTS_OWNER',
 policy_function=>'frequency_function');
end;
/   

PL/SQL procedure successfully completed.

SQL> connect monthly/monthly@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M

SQL> connect daily/daily@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Daily report R1 	       D
Daily report R2 	       D

This is possible because VPD silently attaches a WHERE condition to the seemingly unchanged statement:

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> grant select_catalog_role to monthly;

Grant succeeded.

SQL> connect monthly/monthly@pdb1
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	16xw6bqhp67gw, child number 0
-------------------------------------
select * from reports_owner.reports_table

Plan hash value: 2149466028

-----------------------------------------------------------------------------------
| Id  | Operation	  | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		  |	  |	  |	3 (100)|	  |
|*  1 |  TABLE ACCESS FULL| REPORTS_TABLE |	2 |    38 |	3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - filter("FREQUENCY"='M')


18 rows selected.

The VPD policy adds that WHERE condition depending on the session user. Funny effect:

SQL> connect reports_owner/reports_owner@pdb1
Connected.
SQL> select * from reports_table;

no rows selected

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8xc692y0axx0x, child number 0
-------------------------------------
select * from reports_table

Plan hash value: 2149466028
-----------------------------------------------------------------------------------
| Id  | Operation	  | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		  |	  |	  |	3 (100)|	  |
|*  1 |  TABLE ACCESS FULL| REPORTS_TABLE |	1 |    19 |	3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - filter("FREQUENCY"='?')


18 rows selected.

SYS is not subject to this policy, because of his privilege EXEMPT ACCESS POLICY:

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> select * from reports_owner.reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

SQL> grant exempt access policy to reports_owner;

Grant succeeded.

SQL> connect reports_owner/reports_owner@pdb1
Connected.
SQL> select * from reports_table;

REPORT_NAME		       F
------------------------------ -
Monthly report R1	       M
Monthly report R2	       M
Daily report R1 	       D
Daily report R2 	       D

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8xc692y0axx0x, child number 1
-------------------------------------
select * from reports_table

Plan hash value: 2149466028

-----------------------------------------------------------------------------------
| Id  | Operation	  | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		  |	  |	  |	3 (100)|	  |
|   1 |  TABLE ACCESS FULL| REPORTS_TABLE |	4 |    76 |	3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


13 rows selected.

So much for starters. Things get more complicated especially if there are no different database users as in the above simplified example. That is usually where an application context is being used – but that could be a topic for another article 🙂

Leave a comment

%d bloggers like this: