Tag Archives: update

Managed Resource Manager in Oracle

Managed Resource Manager in Oracle

Resource Manager is used for resource allocation among the many concurrent database sessions as needed. Different job need different resource allocation like ETL job, OLTP server etc
You can set at session level when particular job begins.

Terms used in Resource Manager Configuration
RESOURCE CONSUMER GROUP: Collection of user sessions that are grouped together based on resource requirements. It allocate resources on Resources consumer group not individual session. Default group present in database : SYS_GROUP (session started by SYS or SYSTEM user), DEFAULT_CONSUMER_GROUP (sessions started by user accounts), OTHER_GROUPS (sessions that belong to a consumer group that is not part of active plan)
RESOURCE PLAN: Resource Manage allocate resources to resource plan. One resource plan active at a time. child resource plan directives controls resource allocation for a different consumer group. You can create your own resource plan.
RESOURCE PLAN DIRECTIVE: Resource Manager allocates resources to consumer groups according to the set of resource plan directives.A directive can limit resource allocation to consumer group like CPU, session etc. Each directive references one consumer group.

Example of Resource plan as SALES_PLAN
One active plan SALES_PLAN, its directive having 75% CPU for OLTP Consumer group, 20% for reporting consumer group and 5% CPU for others.

Resouceplanexplain

Handle Privileges for Resource Manager

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(GRANTEE_NAME => 'SCOTT', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', ADMIN_OPTION => FALSE);

Creating a Resource plan

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'SIMPLE_PLAN1',
CONSUMER_GROUP1 => 'MYGROUP1', GROUP1_PERCENT => 75,
CONSUMER_GROUP2 => 'MYGROUP2', GROUP2_PERCENT => 25);
END;
/

It will create the following resource plan:

Consumer Group	Level 1	Level 2	Level 3
--------------  ------- ------- -------
SYS_GROUP	    100%	-	    -
MYGROUP1	    -	    75%	    -
MYGROUP2	    -	    25%	    -
OTHER_GROUPS	-	    -	    100%

Check the current resource plan

SQL> show parameter resource

                                                                                                                       
NAME                                 TYPE        VALUE                                                                      
------------------------------------ ----------- ------------------------------                                             
resource_limit                       boolean     TRUE                                                                       
resource_manager_cpu_allocation      integer     6                                                                          
resource_manager_plan                string      SCHEDULER[0x4446]:DEFAULT_MAINTENANCE_PLAN

Force fully use your Resource plan until you manually change it not effected by scheduler

--Disabling Plan Switches by Oracle Scheduler Windows use force keyword it will not effected by scheduler until you manually change it.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';

Check list and meaning of jobs

SELECT plan,plan_id,status,comments from DBA_RSRC_PLANS;

View displays the currently active resource plan and its subplans.

SELECT name, is_top_plan FROM v$rsrc_plan;

NAME                             IS_TO 
-------------------------------- ----- 
DEFAULT_MAINTENANCE_PLAN         TRUE  

Check timing from the history of Resource plan

SELECT sequence# seq, name plan_name,
to_char(start_time, 'DD-MON-YY HH24:MM') start_time,
to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name
FROM v$rsrc_plan_history;

SEQ PLAN_NAME                START_TIME      END_TIME        WINDOW_NAME        
--- ------------------------ --------------- --------------- -------------------
  1 DEFAULT_PLAN             02-AUG-18 02:08 02-AUG-18 22:08                                                                                                                            
  2 DEFAULT_MAINTENANCE_PLAN 02-AUG-18 22:08                 THURSDAY_WINDOW

Check the running status with SID and resource consumer group

SELECT se.sid sess_id, co.name consumer_group,
se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
FROM v$rsrc_session_info se, v$rsrc_consumer_group co
WHERE se.current_consumer_group_id = co.id;

Check which directives are defined for plans in your database

select plan, group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3
from DBA_RSRC_PLAN_DIRECTIVES
where plan like ('DEFAULT_%')
order by plan, mgmt_p1 desc, mgmt_p2 desc, mgmt_p3 desc;

PLAN                     GROUP_OR_SUBPLAN      MGMT_P1 MGMT_P2 MGMT_P3
------------------------ --------------------- ------- ------- -------
DEFAULT_MAINTENANCE_PLAN SYS_GROUP             75      0       0
DEFAULT_MAINTENANCE_PLAN OTHER_GROUPS          0       50      0
DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK_SUB_PLAN 0       25      0
DEFAULT_MAINTENANCE_PLAN ORA$DIAGNOSTICS       0       25      0
DEFAULT_PLAN             SYS_GROUP             75      0       0
DEFAULT_PLAN             OTHER_GROUPS          0       90      0
DEFAULT_PLAN             ORA$AUTOTASK_SUB_PLAN 0       5       0
DEFAULT_PLAN             ORA$DIAGNOSTICS       0       5       0


Note: DEFAULT_PLAN they are allowed 90% and 5% respectively & DEFAULT_MAINTENANCE_PLAN allowed 75% and 25%

Check in Resource plan Scheduler

select window_name,RESOURCE_PLAN from DBA_SCHEDULER_WINDOWS;

Update a Resource Manager PLAN

Exec DBMS_RESOURCE_MANAGER.UPDATE_PLAN(PLAN => 'DAYTIME',NEW_COMMENT => '50% more CPU resources');

Delete a Resource manager plan

EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'OLTP_PLAN')

Disable the resource manager

1. Set the current resource manager plan to null

alter system set resource_manager_plan='' scope=both

2. Change the active windows to use the null resource manager plan

execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

Advertisements

Implementing Parallel DML Operations in Database

Implementing Parallel DML Operations in Database

Parallel DML Operation is disable by default. For enable fire the following command:

ALTER SESSION ENABLE PARALLEL DML;

to force parallel behaviour, regardless of the parallel degree you have placed on an object:

ALTER SESSION FORCE PARALLEL DML;

For session to run parallel DML operations:
You can run parallel session by specifying in hints or table have degree value more than 1. You can use parallel operation on all DML Statements INSERT, UPDATE & DELETE

1. Enable the session for Parallel DML operations:

ALTER SESSION ENABLE PARALLEL DML;

2. Run the parallel operation by specifying HInts in the statements:

INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT /*+ PARALLEL(DEPT_COPY,4) */ * FROM DEPT_COPY;

UPDATE /*+ PARALLEL(EMP,4) */ EMP SET SAL = SAL*1.01 WHERE DEPTNO=10;

DELETE /*+ PARALLEL(EMP,4) */ FROM EMP WHERE DEPTNO=10;

If you want to delete or update large amount of data in bigger table in Enterprise edition then you can speed up with Parallel hints for the operations.

Note: Insert parallel is not work on single insert statement
Table having the trigger is not worked with parallel hints

Select, Update, Insert AND Delete Operations on a Table

SELECT STATEMENT:
SELECTING ALL COLUMNS OF THE TABLE:
A ‘SELECT’ statement is used as a DATA RETRIEVAL statement i.e. It retrieves information from the database.

SYNTAX:
SQL> SELECT * FROM TABLE NAME;
• SELECT identifies WHAT COLUMNS.
• FROM identifies WHICH TABLE.
Simply, SELECT clause specify which column is to be displayed & FROM clause specify the table containing the columns listed in the SELECT clause.
Here, ‘*’ is used to select all columns.

SELECTING SPECIFIC COLUMNS OF THE TABLE:
SYNTAX:
SQL> SELECT ENAME,JOB FROM EMP
 
SELECTING DISTINCT ELEMENTS FROM THE TABLE:
SYNTAX:
SQL> SELECT DISTINCT ENAME,JOB FROM EMP
The SELECT DISTINCT statement is used to return only distinct (different) values.It is used to remove duplicate values.

SQL> select * from empxyz;

NAME                   AGE
----------------------- ------
anju                    23
jkg                     34
anju                    23

SQL> select distinct name,age from empxyz;

NAME                    AGE
----------------------- -------
anju                    23
jkg                     34


INSERT STATEMENT:
SYNTAX:
SQL> INSERT into CSE(student,rollno) VALUES ('MONIKA',651);
INSERT statement is used to ADD NEW ROW TO A TABLE.
Using INSERT We can only insert on row at a time. As shown in above example,
In above example CSE is the name of the TABLE & STUDENT, ROLLNO are its two ATTRIBUTES.
Enclose CHARACTER & DATE values within a SINGLE QUOTATION MARKS.
 
DELETE STATEMENT:
SYNTAX:
SQL> DELETE from CSE where rollno BETWEEN 605 AND 630;
i.e. DELETE FROM table [WHERE condition];
If we OMIT WHERE CLAUSE then ALL ROWS OF THE COLUMN ARE DELETED.

UPDATE STATEMENT:
SYNTAX:
SQL> UPDATE cse SET rollno=21 WHERE student='ITIKA';
Here, If we do not use WHERE clause then ALL ROWS OF THE TABLE ARE UPDATED.
SPCIFIED ROW or ROWS are modified if we specify the WHERE clause

Download latest Opatch (patch 6880880) from oracle support

Download latest Opatch (patch 6880880) from oracle support.

1. Login to oracle support site https://support.oracle.com

2. Go to tab    —> patches and updates

3. Go to patches search window and type patch number : 6880880

4. select platform type like windows or linux

5. Press the search button.

patchsearch

6. After search button you will found the list of all patches available for different version of oracle. Please download as required.

Patchresult

msdtc.exe is causing lock/active on windows system

msdtc.exe is causing lock/active on windows system.

Issue: If you need to update any application on windows sometime we face the issue with active/locked file by windows services or process.

Solution:

C:\windows>tasklist /m oci*

Image Name     PID      Modules
============   ======   ========
explorer.exe   3136     oci.dll
vmtoolsd.exe   36108    oci.dll
msdtc.exe      6932     oci.dll


If you kill the PID process for msdtc.exe , it open again as shown below:

taskkill /pid 6932 /F
SUCCESS: The process with PID 6932 has been terminated.

C:\windows>tasklist /m oci*

Image Name     PID     Modules
============   =====   ========
explorer.exe   3136    oci.dll
vmtoolsd.exe   36108   oci.dll
msdtc.exe      23492   oci.dll


So, for fix it we need to stop the services for msdtc.exe process and disable it for the duration of our activity and then start it. Open the Services.msc of microsoft windows and find the following service and stopped or disabled it for during the activity, then start it.

Service name is Distributed Transaction Coordinator

6.JPG