Skip to Main Content
  • Questions

  • What does argument "shares" stand for in create_plan_directive()?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michel.

Asked: February 18, 2019 - 10:07 am UTC

Last updated: February 22, 2019 - 6:53 am UTC

Version: 12c+

Viewed 1000+ times

You Asked

Hi,

In procedures create_cdb_plan_directive() or create_cdb_profile_directive() of dbms_resource_manager package, it is clear what role plays The parameter called "shares". Now, I fail to see what can be done with this parameter in create_plan_directive(). I didn't find any example using it and Oracle documentation is not clear about it.

Thanks in advance

and Connor said...

"Shares" are an easier of nominating the division of resources to 'n' things, in the case where 'n' is variable.

Let's say I have 2 pdbs. If I used the old style mechanism of percentages, then I'd probably allocate resources for example:

pdb1 = 50%
pdb2 = 50%

But when if pdb2 is down? What if I create pdb3? Then that number '50%' just doesn't really make a lot of sense, or it needs to be modified. Both aren't great in a world where 'n' (the number of pdbs is variable).

So we use shares, eg

pdb1 = 4 shares
pdb2 = 4 shares

That *currently* is the same as 50% (4 shares out of a total 8). But if I add another pdb, I can just do:

pdb1 = 4 shares
pdb2 = 4 shares
pdb3 = 4 shares

and now it *still* makes sense (they are getting 33% each). By using "shares" I can better represent the pdb's relationship to *each other*. If pdb3 was a less critical one, I could do:

pdb1 = 4 shares
pdb2 = 4 shares
pdb3 = 2 shares

so we can say that pdb1 and pdb2 are twice as "important" as pdb3.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

My point is elsewhere

Michel SALAIS, February 20, 2019 - 12:21 pm UTC

Thank you for the reply but I knew already that shares is good for PDBs.

Now my question is about using shares in create_plan_directive(). I had to post my script with the question...

SQL> alter session set container = mypdb;

Session altered.

SQL> alter system set resource_manager_plan = '';

System altered.

SQL>
SQL> BEGIN
  2    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  3    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  4    DBMS_RESOURCE_MANAGER.DELETE_PLAN
  5    (
  6      plan => 'test_shares'
  7    );
  8    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  9    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  3    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  4    DBMS_RESOURCE_MANAGER.CREATE_PLAN
  5    (
  6      plan => 'test_shares',
  7      comment => 'it is a test'
  8    );
  9    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
 10    (
 11      plan => 'test_shares',
 12      group_or_subplan => 'SYS_GROUP',
 13      comment => 'Shares for SYS_GROUP',
 14      shares => 4
 15    );
 16    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
 17    (
 18      plan => 'test_shares',
 19      group_or_subplan => 'OTHER_GROUPS',
 20      comment => 'Shares for OTHER_GROUPS',
 21      shares => 8
 22    );
 23    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
 24    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
 25  END;
 26  /

PL/SQL procedure successfully completed.

SQL>
SQL> column plan format a12
SQL> column mgmt_method format a10
SQL> column comments format a25
SQL>
SQL> select plan, mgmt_method, comments from dba_rsrc_plans
  2  where plan = 'TEST_SHARES';

PLAN         MGMT_METHO COMMENTS
------------ ---------- -------------------------
TEST_SHARES  EMPHASIS   it is a test

SQL>
SQL> column group_or_subplan format a15
SQL> column mgmt_p1 format 999
SQL> column status format a8
SQL>
SQL> select plan, group_or_subplan, mgmt_p1, status, comments from dba_rsrc_plan_directives
  2  where plan = 'TEST_SHARES';

PLAN         GROUP_OR_SUBPLA MGMT_P1 STATUS   COMMENTS
------------ --------------- ------- -------- -------------------------
TEST_SHARES  SYS_GROUP             4          Shares for SYS_GROUP
TEST_SHARES  OTHER_GROUPS          8          Shares for OTHER_GROUPS

SQL>
SQL> BEGIN
  2    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  3    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  4    DBMS_RESOURCE_MANAGER.DELETE_PLAN
  5    (
  6      plan => 'test_shares'
  7    );
  8    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  9    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  3    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  4    DBMS_RESOURCE_MANAGER.CREATE_PLAN
  5    (
  6      plan => 'test_shares',
  7      comment => 'it is a test'
  8    );
  9    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
 10    (
 11      plan => 'test_shares',
 12      group_or_subplan => 'SYS_GROUP',
 13      comment => 'Shares for SYS_GROUP',
 14      shares => 40
 15    );
 16    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
 17    (
 18      plan => 'test_shares',
 19      group_or_subplan => 'OTHER_GROUPS',
 20      comment => 'Shares for OTHER_GROUPS',
 21      shares => 80
 22    );
 23    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
 24    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
 25  END;
 26  /
BEGIN
*
ERROR at line 1:
ORA-29382: validation of pending area failed
ORA-29375: sum of values 120 for level 1, plan TEST_SHARES exceeds 100
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3619
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3729
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 658
ORA-06512: at line 23


SQL>
SQL> select plan, mgmt_method, comments from dba_rsrc_plans
  2  where plan = 'TEST_SHARES';

PLAN         MGMT_METHO COMMENTS
------------ ---------- -------------------------
TEST_SHARES  EMPHASIS   it is a test

SQL>
SQL> select plan, group_or_subplan, mgmt_p1, status, comments from dba_rsrc_plan_directives
  2  where plan = 'TEST_SHARES';

PLAN         GROUP_OR_SUBPLA MGMT_P1 STATUS   COMMENTS
------------ --------------- ------- -------- -------------------------
TEST_SHARES  SYS_GROUP            40 PENDING  Shares for SYS_GROUP
TEST_SHARES  OTHER_GROUPS         80 PENDING  Shares for OTHER_GROUPS

As we can see, shares in this case is used for mgmt_p1 so I fail to know for which reason it was added to procedure create_plan_directive(). This is exactly the point in my question.
Connor McDonald
February 22, 2019 - 6:53 am UTC

Just for consistency I imagine. The docs suggest similar

RESMGR_SHARES

In this way, whether its resources on a server, or a pdb allocation, or IO RM in Exadata etc etc...there is a consistent model, that of shares

Michel SALAIS, February 24, 2019 - 12:56 am UTC

Yes it could be ...
The same argument is used everywhere. But we have to be careful because it is not generally the same any way.

Thank you very much
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.