-- create object
create type space_usage_object as object 
(
   Segment_owner varchar2(30),
   segment_name varchar2(30),
   segment_type varchar2(30),
   partition_name varchar2(30),
   blocks_unformatted number,
   bytes_unformatted number,
   
   blocks_0_25 number,
   bytes_0_25 number,   

   blocks_25_50 number,
   bytes_25_50 number,   
   
   blocks_50_75 number,
   bytes_50_75 number,   
   
   blocks_75_100 number,
   bytes_75_100 number,            
   
   blocks_full number,
   bytes_full number
);


-- create type
CREATE TYPE space_usage_type AS TABLE OF space_usage_object;   

-- pipelined function
create or replace FUNCTION SPACE_USAGE (
  in_segment_owner        IN  VARCHAR2 ,
  in_segment_name  IN  VARCHAR2 ,
  in_segment_type  IN  VARCHAR2 ,
  in_partition_name       IN  VARCHAR2 := null)
  RETURN space_usage_type PIPELINED AS

 v_unformatted_blocks number;
 v_unformatted_bytes number;
 v_fs1_blocks number;
 v_fs1_bytes number;
 v_fs2_blocks number;
 v_fs2_bytes number;
 v_fs3_blocks number;
 v_fs3_bytes number;
 v_fs4_blocks number;
 v_fs4_bytes number;
 v_full_blocks number;
 v_full_bytes number;
 
 
begin


  dbms_space.space_usage(segment_owner => in_segment_owner,
                        segment_name => in_segment_name,
                        segment_type => in_segment_type,  
                        unformatted_blocks => v_unformatted_blocks, 
                        unformatted_bytes => v_unformatted_bytes,
                        fs1_blocks => v_fs1_blocks, 
                        fs1_bytes => v_fs1_bytes,
                        fs2_blocks => v_fs2_blocks,
                        fs2_bytes => v_fs2_bytes,
                        fs3_blocks => v_fs3_blocks,
                        fs3_bytes => v_fs3_bytes,
                        fs4_blocks => v_fs4_blocks, 
                        fs4_bytes => v_fs4_bytes,
                        full_blocks => v_full_blocks, 
                        full_bytes => v_full_bytes,
                        partition_name =>  in_partition_name);
                        
  pipe row (space_usage_object (in_segment_owner, in_segment_name, in_segment_type, in_partition_name,
                              v_unformatted_blocks , v_unformatted_bytes, 
 v_fs1_blocks, v_fs1_bytes, v_fs2_blocks,  v_fs2_bytes,  v_fs3_blocks, 
 v_fs3_bytes, v_fs4_blocks , v_fs4_bytes , v_full_blocks , v_full_bytes ));

  RETURN ;
END;


-- selecting 
select * from table(space_usage ('OWNER', 'SEGMENT_NAME', 'SEGMENT_TYPE'));

SELECT 'Hazem Ameen' as before_scrambling , TRANSLATE(lower('Hazem Ameen'),
'.@,0123456789abcdefghijklmnopqrstuvxyzابتثجحخدذرزسشصضطظعغفقكلمنوهي', '.@,4214563875qwertyuiop[kjhbvabcdefxzgباتنمكضصثقفغعهخحجدظزوةىرذشسؤ') after_scrambling
FROM dual;

Tuning PL/SQL with Multithreading & DBMS_SCHEDULER

Posted: May 26, 2009 | Author: ksadba | Filed under: Oracle | Tags: dbms_scheduler, Multithreading, PL/SQL | 5 Comments »

We had a piece of PL/SQL code that copies data from remote view to a local table. This code executes once a day and took about 4 hours. As if this is not bad enough, we received a request to run this piece of code 3-4 times daily which will total up to about 16 hours per day.

The original code to copy data from a remote view consists of 3 steps:

1. Copy over employee numbers (one column only) from remote view to local temp table. This step takes few seconds only.
2. Use employee numbers copied locally to do a lookup serially (lookup row by row) from remote view. This step takes about 4 hours to copy 20,000 rows. The reason it takes that long is one column from the view is an actual function. Most of the time is spent executing this function and for so many reasons (not all are technical) we can’t get to function to tune it.
3. Commit one time at the end.

Here how the original code looks like:

– Step 1:
– truncate local temp table so we can copy employee numbers
– copy over employees numbers to local temp table from the remote view

execute immediate 'truncate table temp_emp_no';
execute immediate 'truncate table employees';

insert into temp_emp_no select distinct EMPLOYEE_NUMBER from remote_employee_view@remote_link;
commit;

– Step 2:
– Loop through copied over employee numbers and do a remote lookup 1 row at a time

cursor c1 is
   select emp_no from temp_emp_no;

for i in c1 loop
   exit when c1%notfound;
   insert into employees
   select EMPLOYEE_NUMBER, first_name, last_name, RATE, PERIOD, total_rate
     from remote_employee_view@remote_link;
   where EMPLOYEE_NUMBER = i.emp_no;
end loop;

commit;

At this point we were left with one option only which is run the PL/SQL code in parallel (multithreading), but as you might now, PL/SQL doesn’t support multithreading natively,

To simulate multithreading we need to accomplish 2 steps:

1. Break the job in multiple pieces (threads).
2. Schedule every thread to run concurrently.

Remember first step which is coping employee numbers only from remote view to local table. We hash-partitioned this local table into 4 partitions. Each of these partitions will translate into a thread as you will see.

CREATE TABLE vb_emp_no
   (emp_no  VARCHAR2(30))
    PARTITION BY HASH (EMP_NO)
     PARTITIONS 4
/

This is the thread code (stored procedure) which takes in a partition name as a parameter and copies employee data from remote view for that partition only.

CREATE procedure refresh_employee_data_part (p_name in varchar2 )
  authid definer
is
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  sql_stmt varchar2(2048);
  v_emp_no varchar2(30);

begin
  sql_stmt := 'select emp_no from vb_emp_no partition(' || p_name || ')';
  open v_emp_cursor for sql_stmt ;

loop
  fetch v_emp_cursor into v_emp_no;
  EXIT WHEN v_emp_cursor%NOTFOUND;
  insert into employees
          (EMPLOYEE_NUMBER,
           VACATION_BALANCE,
           RATE, PERIOD, TOTAL_RATE)
  select distinct employee_number, vacation_balance, rate, period, total_rate
    from   remote_employee_view@remote_link;
  where  EMPLOYEE_NUMBER = v_emp_no;
end loop;

commit;
close v_emp_cursor;
end;
/

This procedure glues all parts together. We schedule the previous procedure 4 times and achieve concurrency.

CREATE procedure refresh_employees
  authid definer
is
begin
  execute immediate 'truncate table vb_emp_no';
  insert into vb_emp_no
  select distinct employee_number from remote_view@db_link;
  commit;
  execute immediate 'truncate table employees';

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P1_'),
   job_type => 'PLSQL_BLOCK',
   job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P1''); end;',
   comments => 'Thread 1 to refresh employees',
   enabled => true,
   auto_drop => true);

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P2_'),
   job_type => 'PLSQL_BLOCK',
   job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P2''); end;',
   comments => 'Thread 2 to refresh employees',
   enabled => true,
   auto_drop => true);

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P3_'),
   job_type => 'PLSQL_BLOCK',
   job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P3''); end;',
   comments => 'Thread 3 to refresh employees',
   enabled => true,
   auto_drop = true);

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P4_'),
   job_type => 'PLSQL_BLOCK',
   job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P4''); end;',
   comments => 'Thread 4 to refresh employees,
   enabled => true,
   auto_drop => true);

end;
/

The procedure would finish immediately even if there are errors. To check the execution status look under DBA_SCHEDULER_JOB_RUN_DETAILS to look for errors and running time.

End Result the job finished in 50 minutes more than 4x faster.

Hazem Ameen
Senior Oracle DBA

---

CREATE OR REPLACE
FUNCTION vpd_function (obj_owner IN VARCHAR2, obj_name IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN 'rowid = ''0''';
END vpd_function;
/

BEGIN
DBMS_RLS.ADD_POLICY(object_schema=> 'SCOTT',
object_name=> 'EMP',
policy_name=> 'scott_emp_policy',
function_schema=> 'SYSTEM',
policy_function=> 'vpd_function',
sec_relevant_cols=> 'JOB',
policy_type => DBMS_RLS.SHARED_STATIC,
sec_relevant_cols_opt=> dbms_rls.ALL_ROWS);

END;
/