\nSQL> startup force\nORACLE instance started.\n\nSQL> show spparameter resource_manager_plan\n\nSID NAME TYPE VALUE\n-------- ----------------------------- ----------- ----------------------------\n* resource_manager_plan string\n\nSQL> show spparameter cpu_count\n\nSID NAME TYPE VALUE\n-------- ----------------------------- ----------- ----------------------------\n* cpu_count integer\n\nSQL> show parameter resource_manager_plan\n\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\nresource_manager_plan string\n\nSQL> show parameter cpu_count\n\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\ncpu_count integer 16\n\n<\/code><\/pre>\nI have a VM with 16 CPU threads, no “cpu_count” or “resource_manager_plan” are set in SPFILE. I restarted the instance (it is a lab) to be sure that nothing is set on scope=memory.<\/p>\n
sqlplus \/ as sysdba @ tmp.sql \/dev\/null\nfor i in {1..16} ; do echo \"exec loop null; end loop;\" | sqlplus -s \"c##franck\/c##franck\" & done >\/dev\/null\nsleep 10\n( cd \/tmp && git clone https:\/\/github.com\/tanelpoder\/tpt-oracle.git )\nsqlplus \/ as sysdba @ \/tmp\/tpt-oracle\/snapper ash=event+username 30 1 all < \/dev\/null\npkill -f oracleCDB1A<\/code><\/pre>\nI run 32 sessions working in memory (simple PL\/SQL loops) and look at the sessions with Tanel Poder’s snapper<\/a> in order to show whether I am ON CPU or in Resource Manager wait. And then kill my sessions in a very hugly fashion (this is a lab)<\/p>\nNothing set, all defaults: no instance caging<\/h3>\n-- Session Snapper v4.31 - by Tanel Poder ( http:\/\/blog.tanelpoder.com\/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)\n\n-------------------------------------------------------------------------------\n ActSes %Thread | EVENT | USERNAME\n-------------------------------------------------------------------------------\n 16.00 (1600%) | ON CPU | C##FRANCK\n 16.00 (1600%) | ON CPU | SYS\n\n-- End of ASH snap 1, end=2021-03-16 17:38:43, seconds=30, samples_taken=96, AAS=32<\/code><\/pre>\nOn my CPU_COUNT=16 (default but not set) instance, I have 32 sessions ON CPU -> no instance caging<\/p>\n
Only CPU_COUNT set, no resource manager plan: no instance caging<\/h3>\n\nSQL> alter system set cpu_count=16 scope=memory;\nSystem altered.\n<\/code><\/pre>\nI have set CPU_COUNT explicitely to 16 (just checking because this is where I always have a doubt)<\/p>\n
-- Session Snapper v4.31 - by Tanel Poder ( http:\/\/blog.tanelpoder.com\/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)\n\n-------------------------------------------------------------------------------\n ActSes %Thread | EVENT | USERNAME\n-------------------------------------------------------------------------------\n 16.00 (1600%) | ON CPU | C##FRANCK\n 16.00 (1600%) | ON CPU | SYS\n .03 (3%) | ON CPU |\n\n-- End of ASH snap 1, end=2021-03-16 18:03:50, seconds=5, samples_taken=37, AAS=32\n<\/code><\/pre>\nSetting CPU_COUNT manually doesn’t change anything here.<\/p>\n
\nSQL> startup force\nORACLE instance started.\n<\/code><\/pre>\nFor the next test I reset to the default to show that CPU_COUNT doesn’t have to be set explicitely in order to enable instance caging.<\/p>\n
Resource manager set to DEFAULT_CDB_PLAN with default CPU_COUNT: instance caging<\/h3>\nSQL> alter system set resource_manager_plan=DEFAULT_CDB_PLAN scope=memory;\n\nSystem altered.\n\n<\/code><\/pre>\nI have set the default resource manager plan (I’m in multitenant and running from the CDB)<\/p>\n
\n-- Session Snapper v4.31 - by Tanel Poder ( http:\/\/blog.tanelpoder.com\/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)\n\n-------------------------------------------------------------------------------\n ActSes %Thread | EVENT | USERNAME\n-------------------------------------------------------------------------------\n 13.07 (1307%) | ON CPU | SYS\n 12.20 (1220%) | resmgr:cpu quantum | C##FRANCK\n 3.80 (380%) | ON CPU | C##FRANCK\n 2.93 (293%) | resmgr:cpu quantum | SYS\n\n-- End of ASH snap 1, end=2021-03-16 18:21:24, seconds=30, samples_taken=94, AAS=32\n<\/code><\/pre>\nHere only 16 sessions on average are ON CPU and the others are scheduled out by Resource Manager. Note that there’s a higher priority for SYS than for my user.<\/p>\n
Resource manager set to DEFAULT_MAINTENANCE_PLAN with default CPU_COUNT: instance caging<\/h3>\nSQL> alter system set resource_manager_plan=DEFAULT_MAINTENANCE_PLAN scope=memory;\n\nSystem altered.\n\n<\/code><\/pre>\nI have set the default resource manager plan (I’m in multitenant and running from the CDB)<\/p>\n
\n-- Session Snapper v4.31 - by Tanel Poder ( http:\/\/blog.tanelpoder.com\/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)\n\n-------------------------------------------------------------------------------\n ActSes %Thread | EVENT | USERNAME\n-------------------------------------------------------------------------------\n 13.22 (1322%) | ON CPU | SYS\n 12.31 (1231%) | resmgr:cpu quantum | C##FRANCK\n 3.69 (369%) | ON CPU | C##FRANCK\n 2.78 (278%) | resmgr:cpu quantum | SYS\n .07 (7%) | ON CPU |\n .04 (4%) | resmgr:cpu quantum |\n\n-- End of ASH snap 1, end=2021-03-16 18:29:31, seconds=30, samples_taken=95, AAS=32.1\n<\/code><\/pre>\nHere only 16 sessions on average are ON CPU and the others are scheduled out by Resource Manager. Again, there’s a higher priority for SYS than for my user.<\/p>\n
Same in a PDB<\/h3>\n\nfor i in {1..16} ; do echo \"exec loop null; end loop;\" | ORACLE_PDB_SID=PDB1 sqlplus -s \/ as sysdba & done >\/dev\/null\nfor i in {1..16} ; do echo \"exec loop null; end loop;\" | sqlplus -s \"c##franck\/c##franck\"@\/\/localhost\/PDB1 & done >\/dev\/null\n<\/code><\/pre>\nI’ve changed my connections to connect to the PDB<\/p>\n
\n-- Session Snapper v4.31 - by Tanel Poder ( http:\/\/blog.tanelpoder.com\/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)\n\n\n----------------------------------------------------------------------------------------\n ActSes %Thread | EVENT | USERNAME | CON_ID\n----------------------------------------------------------------------------------------\n 14.95 (1495%) | ON CPU | SYS | 3\n 14.27 (1427%) | resmgr:cpu quantum | C##FRANCK | 3\n 1.73 (173%) | ON CPU | C##FRANCK | 3\n 1.05 (105%) | resmgr:cpu quantum | SYS | 3\n .01 (1%) | LGWR all worker groups | | 0\n .01 (1%) | ON CPU | | 0\n\n-- End of ASH snap 1, end=2021-03-16 19:14:52, seconds=30, samples_taken=93, AAS=32\n<\/code><\/pre>\nI check the CON_ID to verify that I run in the PDB and here, with the CDB resource manager plan DEFAULT_MAINTENANCE_PLAN the SYS_GROUP (SYSDBA and SYSTEM) can take 90% of CPU. It is the same with DEFAULT_CDB_PLAN.<\/p>\n
Adding a Simple Plan<\/h3>\n\nSQL> alter session set container=PDB1;\n\nSession altered.\n\nSQL> BEGIN\n 2 DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'SIMPLE_PLAN1',\n 3 CONSUMER_GROUP1 => 'MYGROUP1', GROUP1_PERCENT => 80,\n 4 CONSUMER_GROUP2 => 'MYGROUP2', GROUP2_PERCENT => 20);\n 5 END;\n 6 \/\n\nPL\/SQL procedure successfully completed.\n\nSQL> alter system set resource_manager_plan=SIMPLE_PLAN1 scope=memory;\n\nSystem altered.\nThis is the simple plan example from the documentation (or here<\/a>). \n<\/code><\/pre>\n\n-- Session Snapper v4.31 - by Tanel Poder ( http:\/\/blog.tanelpoder.com\/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)\n\n----------------------------------------------------------------------------------------\n ActSes %Thread | EVENT | USERNAME | CON_ID\n----------------------------------------------------------------------------------------\n 8.59 (859%) | ON CPU | SYS | 3\n 8.51 (851%) | ON CPU | C##FRANCK | 3\n 7.49 (749%) | resmgr:cpu quantum | C##FRANCK | 3\n 7.41 (741%) | resmgr:cpu quantum | SYS | 3\n .04 (4%) | ON CPU | | 0\n\n-- End of ASH snap 1, end=2021-03-16 19:29:54, seconds=30, samples_taken=92, AAS=32\n<\/code><\/pre>\nNow, with this simple plan, everything changed. The level 1 gives 100% to SYS_GROUP but it actually got 50%. Level 2 gives 80% and 20% to groups that are not used there. And level 3 gives 100% to OTHER_GROUPS. But those are the levels documented in pre-multitenant.<\/p>\n
Mapping my user to one simple plan group<\/h3>\n\nSQL> BEGIN\n 2 DBMS_RESOURCE_MANAGER.create_pending_area;\n 3 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING\n 4 (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'C##FRANCK', 'MYGROUP2');\n 5 DBMS_RESOURCE_MANAGER.validate_pending_area;\n 6 DBMS_RESOURCE_MANAGER.submit_pending_area;\n 7 END;\n 8 \/\n\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nI’ve assigned my C##FRANCK user which gets 20% at level 2<\/p>\n
\n-- Session Snapper v4.31 - by Tanel Poder ( http:\/\/blog.tanelpoder.com\/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)\n\n----------------------------------------------------------------------------------------\n ActSes %Thread | EVENT | USERNAME | CON_ID\n----------------------------------------------------------------------------------------\n 13.20 (1320%) | ON CPU | C##FRANCK | 3\n 12.78 (1278%) | resmgr:cpu quantum | SYS | 3\n 3.22 (322%) | ON CPU | SYS | 3\n 2.80 (280%) | resmgr:cpu quantum | C##FRANCK | 3\n .02 (2%) | resmgr:cpu quantum | | 0\n .01 (1%) | log file parallel write | | 0\n .01 (1%) | ON CPU | | 0\n\n-- End of ASH snap 1, end=2021-03-16 19:45:52, seconds=30, samples_taken=96, AAS=32\n<\/code><\/pre>\nNow my user got 80% of the CPU resource and SYS only 20%<\/p>\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2021-03-16-205636.jpg\")
<\/a><\/p>\n