Oracle has introduced a new concept of Performance Profiles with 12c Release 2.
Now, What is Performance Profile?
Performance Profiles are new concept in Resource Manager. We can fairly distribute resources among pdbs as per their workload.
By creating a performance profile you can set the following resource limit
- SHARES
- UTILIZATION_LIMIT
- PARALLEL_SERVER_LIMIT
Let us understand this with an example :
Step 1: Create CDB_PLAN procedure of the DBMS_RESOURCE_MANAGER package in CDB$ROOT. In the following example, I have created two performance profile prf_1 and prf_2.
PRF_1 is created with resource_limit shares=2 and utilization limit to 50% and parallel server limit to 50%.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL>
DECLARE
plan_name VARCHAR2(30) := 'Pdb_performance_profile';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.create_cdb_plan(
plan => plan_name,
comment => 'Resource plan using profiles');
DBMS_RESOURCE_MANAGER.create_cdb_profile_directive(
plan => plan_name,
profile => 'prf_1',
shares => 2,
utilization_limit => 50,
parallel_server_limit => 50);
SQL>
DBMS_RESOURCE_MANAGER.create_cdb_profile_directive(
plan => plan_name,
profile => 'prf_2',
shares => 3,
utilization_limit => 33,
parallel_server_limit => 33);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
PL/SQL procedure successfully completed.
SQL>
Step 2: We can check the created performance profile from dba_cdb_rsrc_plans view.
SQL> col plan for a20
SQL> col comments for a20
SQL> col status for a20
SQL> set lines 10000
SQL>
SELECT plan_id, plan, comments, status, mandatory
FROM dba_cdb_rsrc_plans
WHERE plan ='PDB_PERFORMANCE_PROFILE';SQL> 2 3
PLAN_ID PLAN COMMENTS STATUS MAN
---------- -------------------- -------------------- -------------------- ---
73979 PDB_PERFORMANCE_PROF Resource plan using NO
ILE profiles
Step 3: Lets set created performance profile in pdb
SQL> alter session set container=pdb1;
Session altered.
SQL> ALTER SYSTEM SET DB_PERFORMANCE_PROFILE='PRF_1';
ALTER SYSTEM SET DB_PERFORMANCE_PROFILE='PRF_1'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
We can see here DB_PERFORMANCE_PROFILE parameter is not modifiable with memory level. We must bounce the database to set DB_PERFORMANCE_PROFILe.
SQL> ALTER SYSTEM SET DB_PERFORMANCE_PROFILE='PRF_1' scope=spfile; System altered. SQL> alter pluggable database close immediate; Pluggable database altered. SQL> alter pluggable database open; Pluggable database altered. SQL> show parameter db_performance_profile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_performance_profile string PRF_1 SQL>
Step 4: View details of performance profile from dba_cdb_rsrc_directives
SQL> COLUMN plan FORMAT A30
SQL> COLUMN pluggable_database FORMAT A25
COLUMN profile FORMAT A25
SET LINESIZE 150 VERIFY OFF
SELECT plan,
pluggable_database,
profile,
shares,
utilization_limit ,
parallel_server_limit AS parallel
FROM dba_cdb_rsrc_plan_directives
WHERE plan ='PDB_PERFORMANCE_PROFILE';
ORDER BY plan, pluggable_database, profile;SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8
PLAN PLUGGABLE_DATABASE PROFILE SHARES UTILIZATION_LIMIT PARALLEL
------------------------------ ------------------------- ------------------------- ---------- ----------------- ----------
PDB_PERFORMANCE_PROFILE ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
PDB_PERFORMANCE_PROFILE ORA$AUTOTASK 90 100
PDB_PERFORMANCE_PROFILE PRF_1 2 50 50
PDB_PERFORMANCE_PROFILE PRF_2 3 33 33
SQL>
If you want to be updated with all our articles send us an Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
