Inspired by Sue Lee’s presentation at Enkitec’s E4 conference I decided to re-run my test suite to work out how you can use Database and I/O Resource Manager in Oracle 188.8.131.52.2 to help consolidating databases in the new Multi-Tenant architecture. I should point out briefly that the use of Multi-Tenant as demonstrated in this article requires you to have a license. Tuning tools shown will also require you to be appropriately licensed…
I have created a Container Database (CDB), named MBACHMT (MBACH – Multi-Tenant), in which I want to run Swingbench to experiment with I/O Resource Manager. The same Pluggable Databases (PDBs) are subject to the new CDB-(Database) Resource Manager testing. In order to simplify the task I’ll just create a single PDB for now, install Swingbench’s Order Entry schema, and clone the PDB twice. The CDB is created using dbca on an Exadata X2-2 quarter rack. Once the CDB was in place I could create the first PDB. I don’t think the steps need a lot of explanation, so here they go without much commenting. First the PDB must be created-based on the SEED database, then I create a tablespace and a user account to host the actual data.
SYS:MBACHMT1> create pluggable database swingbench0 admin user admin identified by secret roles=(DBA); Pluggable database created. SYS:MBACHMT1> alter pluggable database swingbench0 open instance=all; Pluggable database altered. SYS:MBACHMT1> alter session set container = swingbench0; Session altered. SYS:MBACHMT1> create tablespace soe datafile size 5g; Tablespace created. SYS:MBACHMT1> create user soe identified by soe default tablespace soe; User created. SYS:MBACHMT1> grant connect to soe; Grant succeeded. SYS:MBACHMT1> grant execute on dbms_lock to soe; Grant succeeded.
One thing requires an explanation, and that’s the “alter session set container = swingbench0” command. As you can see the SQLPROMPT is comprised of username – colon – container name. The container named does not change when using the SYS account to switch the context from CDB$ROOT to a PDB, hence you continue to see the MBACHMT1 prefix when in fact I am executing commands on the PDB level.
oewizard -scale 1 -dbap supersecret -u soe -p soe -cl -cs //enkscan2/swingbench0 -ts SOE -create SwingBench Wizard Author : Dominic Giles Version : 184.108.40.2069 Running in Lights Out Mode using config file : oewizard.xml ============================================ | Datagenerator Run Stats | ============================================ Connection Time 0:00:00.005 Data Generation Time 0:00:21.380 DDL Creation Time 0:00:52.221 Total Run Time 0:01:13.609 Rows Inserted per sec 566,237 Data Generated (MB) per sec 46.1 Actual Rows Generated 13,009,500 Post Creation Validation Report =============================== The schema appears to have been created successfully. Valid Objects ============= Valid Tables : 'ORDERS','ORDER_ITEMS','CUSTOMERS','WAREHOUSES','ORDERENTRY_METADATA','INVENTORIES','PRODUCT_INFORMATION', 'PRODUCT_DESCRIPTIONS','ADDRESSES','CARD_DETAILS' Valid Indexes : 'PRD_DESC_PK','PROD_NAME_IX','PRODUCT_INFORMATION_PK','PROD_SUPPLIER_IX','PROD_CATEGORY_IX','INVENTORY_PK', 'INV_PRODUCT_IX','INV_WAREHOUSE_IX','ORDER_PK','ORD_SALES_REP_IX','ORD_CUSTOMER_IX','ORD_ORDER_DATE_IX', 'ORD_WAREHOUSE_IX','ORDER_ITEMS_PK','ITEM_ORDER_IX','ITEM_PRODUCT_IX','WAREHOUSES_PK','WHS_LOCATION_IX', 'CUSTOMERS_PK','CUST_EMAIL_IX','CUST_ACCOUNT_MANAGER_IX','CUST_FUNC_LOWER_NAME_IX','ADDRESS_PK','ADDRESS_CUST_IX', 'CARD_DETAILS_PK','CARDDETAILS_CUST_IX' Valid Views : 'PRODUCTS','PRODUCT_PRICES' Valid Sequences : 'CUSTOMER_SEQ','ORDERS_SEQ','ADDRESS_SEQ','LOGON_SEQ','CARD_DETAILS_SEQ' Valid Code : 'ORDERENTRY' Schema Created
With the schema in place I need a PDB Resource Plan, or in other words, enable a resource manager plan on PDB-level. The PDB Resource Plan is almost identical to non-CDB DBRM plans, with a few restrictions mentioned in the Admin Guide chapter 44, just below figure 44-4. The one that affects me is the lack of multi-level resource plans. For this reason I’m going to use a simple plan based on the RATIO mgmt_mth of dbms_resource_manager.create_plan. Not having multi-level resource plans at your disposal might actually prevent incredibly complex plans from being created, that are beautifully architected but equally difficult to understand for me at least.
To keep it simple, my PDBs just have 1 purpose: execute Swingbench. As such there won’t be an additional application user, all I care about is the SOE account. I want it to be eligible for the lion share of CPU, so here’s the plan. You must make sure that your execution context is the new PDB (swingbench0). You can make sure by selecting “sys_context(‘userenv’,’con_name’) from dual.
begin dbms_resource_manager.clear_pending_area; dbms_resource_manager.create_pending_area; -- create a new resource consumer group to which we will later on add plan directives -- a consumer group is a logical construct grouping sessions to a similar/identical workload dbms_resource_manager.create_consumer_group('SWINGBENCH_GROUP', 'for swingbench processing'); dbms_resource_manager.validate_pending_area(); dbms_resource_manager.submit_pending_area(); end; / begin dbms_resource_manager.create_pending_area(); -- when logging in as oracle user "SOE", map this session to the SWINGBENCH_GROUP dbms_resource_manager.set_consumer_group_mapping( dbms_resource_manager.oracle_user, 'SOE', 'SWINGBENCH_GROUP'); dbms_resource_manager.submit_pending_area(); end; / begin -- must allow the SOE user to switch from OTHERS_GROUP to SWINGBENCH_GROUP. Forgetting this step -- is a common reason for DBRM not to work as expected dbms_resource_manager_privs.grant_switch_consumer_group('SOE','SWINGBENCH_GROUP', true); end; / BEGIN dbms_resource_manager.clear_pending_area(); dbms_resource_manager.create_pending_area(); -- new create the plan in the first step. Note the mgmt_mth which essentially requires you -- to think of CPU shares, not percentages. Also enforces the requirement not to use -- multi-level plans -- thanks for @fritshoogland for making this obvious to me dbms_resource_manager.create_plan( plan => 'ENKITEC_SWINGBENCH_PDB_PLAN', mgmt_mth => 'RATIO', comment => 'sample DBRM plan for swingbench' ); -- now define what the plan is about. Give the SYS_GROUP 3 shares dbms_resource_manager.create_plan_directive( plan => 'ENKITEC_SWINGBENCH_PDB_PLAN', comment => 'sys_group is level 1', group_or_subplan => 'SYS_GROUP', mgmt_p1 => 3); -- the SWINGBENCH user gets 7 shares dbms_resource_manager.create_plan_directive( plan => 'ENKITEC_SWINGBENCH_PDB_PLAN', group_or_subplan => 'SWINGBENCH_GROUP', comment => 'us before anyone else', mgmt_p1 => 7 ); -- finally anyone not in a previous consumer group will be mapped to the -- OTHER_GROUPS and get 1 share. dbms_resource_manager.create_plan_directive( plan => 'ENKITEC_SWINGBENCH_PDB_PLAN', group_or_subplan => 'OTHER_GROUPS', comment => 'all the rest', mgmt_p1 => 1 ); dbms_resource_manager.validate_pending_area(); dbms_resource_manager.submit_pending_area(); end; /
If you didn’t get any errors you can enable the plan in the PDB using the familiar “alter system set resource_manager_plan = ENKITEC_SWINGBENCH_PDB_PLAN;” command.
The PDB is now ready for cloning, which requires it to be open read only. Once swingbench0 is in the correct open mode, clone it using “create pluggable database swingbench1 from swingbench0;” and “create pluggable database swingbench2 from swingbench0;”. Nice-no “RMAN> duplicate database to swingbench1 from active database” and all this … just a one-liner. Once the cloning is done, open the PDBs.
The CDB plan
With the PDBs all registered I am now able to define a CDB resource plan in CDB$ROOT. Again, check using “select sys_context(‘userenv’,’con_name’) from dual” that you are in the root, not a PDB. Here is the plan:
begin dbms_resource_manager.clear_pending_area; dbms_resource_manager.create_pending_area; dbms_resource_manager.create_cdb_plan( plan => 'ENKITC_CDB_PLAN', comment => 'A CDB plan for 12c' ); dbms_resource_manager.create_cdb_plan_directive( plan => 'ENKITC_CDB_PLAN', pluggable_database => 'swingbench0', shares => 5, utilization_limit => 100); dbms_resource_manager.create_cdb_plan_directive( plan => 'ENKITC_CDB_PLAN', pluggable_database => 'swingbench1', shares => 3, utilization_limit => 50); dbms_resource_manager.create_cdb_plan_directive( plan => 'ENKITC_CDB_PLAN', pluggable_database => 'swingbench2', shares => 1, utilization_limit => 30); dbms_resource_manager.validate_pending_area; dbms_resource_manager.submit_pending_area; end; / SQL> alter system set RESOURCE_MANAGER_PLAN = 'FORCE:ENKITC_CDB_PLAN' scope=both sid='*'; System altered.
With this CDB plan I defined CPU shares and utilisation limits. There are other items worth investigating, refer to the documentation for those. Please take note that except for swingbench0 I capped the maximum utilisation of 50 and 30 percent respectively. This will be interesting later.
No workload is 100% CPU-bound and I tried a few iterations before coming up with a suitable model to view the CDB Resource Plan in action. In the end all I needed was CPU burning nonsense, and I have found one way of burning CPU by calculating millions of square roots. I have written a small “launcher” script that can execute a SQL script against a (Pluggable) database x-many times. When exceeding the CPU capacity on the system I should be able to see the effect. So I launched 20 sessions of my CPU burning for-loops against each of the PDBs in instance two and connected against CDB$ROOT to see the effect:
SYS:MBACHMT2> select count(*), con_id, inst_id, event from gv$session where username = 'SOE' group by con_id, inst_id, event; COUNT(*) CON_ID INST_ID EVENT ----------- ----------- ----------- ---------------------------------------------------------------- 20 3 2 resmgr:cpu quantum 20 4 2 resmgr:cpu quantum 20 5 2 resmgr:cpu quantum
And yes, that looks like it ;)
The timings for the executions were:
- 232 seconds for swingbench0
- 318 seconds for swingbench1
- 509 seconds for swingbench2
A little bit of OEM Express
OEM Express was running at the time and it allows you to see the utilisation of your PDBs:
You can see the 3 PDBs working along. On the CPU bars to the right you can see the number of running sessions (green) and those waiting (beige). You can also see the entitlement as per shares (black vertical bar in “CPU Resource Limits”) and the utilisation limit (red vertical bar) The output almost perfectly matched the configuration.