--
-- Set vs Row Processing - Which is better?
-- 
-- Author: Craig A. Shallahamer, craig@orapub.com
-- 29-May-2012
--
-- Use at your own risk, not OraPub's
--

set echo on feedback on verify on

-- Disable experiment killing stuff...
set tab off
-- disable AWR collection or your numbers can be strangely skewed.
execute dbms_workload_repository.modify_snapshot_settings(interval => 0);
-- disable resource manager to ensure Oracle CPU activity is not throttled down. If this occurs
-- you will see a quantum related wait event.
alter system set resource_manager_plan = '';
show parameter resource_manager_plan

set serveroutput on

def maxCustomers=500   -- number of customers
def totOrders=1000000     -- total number of orders; e.g., 1000
def orderSets=10        -- number of order sets; e.g., 3 : 300 orders, 600 orders, 900 orders
def maxSamples=12       -- number of timed samples for each order set
def chill=4            -- seconds to pause before gathering timed samples

prompt .
prompt Creating objects
prompt .

create or replace function op_get_interval_s(i_intrvl interval day to second) return number is
begin
  return extract(day from i_intrvl) * 86400 + 
         extract(hour from i_intrvl) * 3600 + 
         extract(minute from i_intrvl) * 60 + 
         extract(second from i_intrvl);
  exception
    when others then begin return null; 
  end;
end;
/

create or replace function op_get_normal(avg_in number, stdev_in number) return number is
begin
declare
  sample_value number;
  z            number;               -- standardized value
  m            number  := avg_in ;   -- average
  s            number  := stdev_in ; -- standard deviation
  x            number;               -- un-standardized value
begin
    z := dbms_random.normal;
    x := z * s + m ;
    return x ;
end;
end;
/

create or replace function op_get_cust_id(min_in number, avg_in number, max_in number) return number is
begin
declare
  cust_id_v number;
begin
 loop
    cust_id_v := trunc(op_get_normal(avg_in,max_in/3));
    exit when (cust_id_v < max_in) and (cust_id_v > min_in) ;
 end loop;
 return cust_id_v ;
end;
end;
/


drop table op_results;
create table op_results (
  number_of_orders    number,
  sample_no           number,
  proc_type           varchar2(5), -- row or set
  time_proc_s         number,
  create_date         date,
  order_sum_amount    number
);

drop table op_customer;
create table op_customer (
  id     number,
  name	 varchar2(200),
  bogus1 varchar2(200),
  bogus2 varchar2(200),
  status varchar2(5)
);
create index op_customer_id on op_customer (id,status);

drop sequence op_seq;
create sequence op_seq;

declare
  i_v             number;
  cust_id_v       number;
  cust_name_v     varchar2(200);
  cust_bogus1_v   varchar2(200);
  cust_bogus2_v   varchar2(200);
  cust_status_v   varchar2(5);
begin
  for i_v in 1..&maxCustomers 
  loop
    cust_id_v     := i_v ;
    cust_name_v   := dbms_random.string('A', trunc(dbms_random.value(100,200)));
    cust_bogus1_v := dbms_random.string('A', trunc(dbms_random.value(100,200)));
    cust_bogus2_v := dbms_random.string('A', trunc(dbms_random.value(100,200)));
    cust_status_v := trunc(dbms_random.value(1,3));

    insert into op_customer values (cust_id_v, cust_name_v, cust_bogus1_v, cust_bogus2_v, cust_status_v);
  end loop;
end;
/
commit;

drop table op_interim;
create table op_interim (
  cust_id       number,
  cust_status   varchar2(5),
  cust_name     varchar2(200),
  count_orders  number,
  sum_amount    number
);

drop table op_order;
create table op_order (
  cust_id         number,
  id              number,
  description     varchar2(200),
  amount          number,
  bogus1	  varchar2(200)
);
create index op_order_pk on op_order (cust_id,id);

prompt .
prompt Run experiment
prompt .

declare
  type cv_typ is ref cursor;
  cust_cur         cv_typ;
  order_cur        cv_typ;

  order_sets_v     number;
  order_id_v       number;
  number_of_orders_v number;

  cust_id_v          number;
  order_id_v         number;
  description_v      varchar2(200);
  amount_v           number;
  bogus1_v           varchar2(200);
  sum_amount_v       number;
  count_orders_v     number;
  cust_status_v      varchar2(5);
  cust_name_v        varchar2(200);

  samples_no_v       number;

  time_t0_v          timestamp;
  time_t1_v          timestamp;
  elapsed_s_v        number;

  cust_cur_id_v      number;
  cust_cur_status_v  varchar2(5);
  cust_cur_name_v    varchar2(200);
  order_cur_id_v     number;
  order_cur_cust_id_v number;
  order_cur_amount_v  number;

  order_count_v       number;
  order_sum_amount_v  number;
  order_sum_check_v   number;
begin

  for order_sets_v in 1..&orderSets
  loop
    --
    -- Load data into orders table (this can take awhile...)
    --
    execute immediate('truncate table op_order');
    number_of_orders_v := order_sets_v * trunc( &totOrders / &orderSets ) ;

    for order_id_v in 1..number_of_orders_v
    loop
      cust_id_v     := op_get_cust_id(1,&maxCustomers/2,&maxCustomers);
      description_v := dbms_random.string('A', trunc(dbms_random.value(100,200)));
      amount_v      := dbms_random.value(250,1000000);
      bogus1_v      := dbms_random.string('A', trunc(dbms_random.value(100,200)));

      insert into op_order values (cust_id_v, order_id_v, description_v, amount_v, bogus1_v);
      commit write batch nowait;
    end loop; 
    commit;

    execute immediate('truncate table op_interim');
    dbms_lock.sleep(&chill);
    --
    -- Collect experimental samples
    --
    for sample_no_v in 1..&maxSamples
    loop
      --
      -- set processing
      --
      execute immediate('truncate table op_interim');
      dbms_lock.sleep(&chill);
      select current_timestamp into time_t0_v from dual;

      insert into op_interim
      select c.id, c.status, c.name, count(o.amount), sum(o.amount)
      from   op_customer c,
             op_order o
      where  c.status = '1'
        and  c.id = o.cust_id (+)
      group by c.id, c.status, c.name;
      commit;

      select current_timestamp into time_t1_v from dual;
      select op_get_interval_s(time_t1_v-time_t0_v) into elapsed_s_v from dual;

      select sum(sum_amount) into order_sum_check_v from op_interim;

      insert into op_results values (number_of_orders_v, sample_no_v, 'set', elapsed_s_v, 
        sysdate, order_sum_check_v);
      commit;
    end loop;

    for sample_no_v in 1..&maxSamples
    loop
      --
      -- row processing
      --
      execute immediate('truncate table op_interim');
      dbms_lock.sleep(&chill);
      select current_timestamp into time_t0_v from dual;

      order_sum_check_v := 0;

      open cust_cur for
        select id, status, name
        from   op_customer
        order by id;
      loop
        fetch cust_cur into cust_cur_id_v, cust_cur_status_v, cust_cur_name_v ;
        exit when cust_cur%NOTFOUND;

        order_count_v      := 0 ;
        order_sum_amount_v := 0 ;

        open order_cur for
          select id, cust_id, amount
          from   op_order
          order by cust_id, id;
        loop
          fetch order_cur into order_cur_id_v, order_cur_cust_id_v, order_cur_amount_v ;     
          exit when order_cur%NOTFOUND;

          if (cust_cur_status_v = '1') AND (cust_cur_id_v = order_cur_cust_id_v) then
            order_count_v      := order_count_v + 1 ;
            order_sum_amount_v := order_sum_amount_v + order_cur_amount_v ;
            order_sum_check_v  := order_sum_check_v + order_cur_amount_v ;
          end if;
        end loop;
        close order_cur;

        insert into op_interim values 
          (cust_cur_id_v, cust_cur_status_v, cust_cur_name_v, order_count_v, order_sum_amount_v);
      end loop;
      close cust_cur;

      select current_timestamp into time_t1_v from dual;
      select op_get_interval_s(time_t1_v-time_t0_v) into elapsed_s_v from dual;

      insert into op_results values (number_of_orders_v, sample_no_v, 'row', elapsed_s_v, 
        sysdate, order_sum_check_v);
      commit;
    end loop; 

  end loop; -- order_sets_v loop
end;
/


----------------------------
-- Reporting SQL
----------------------------
set tab off

-- Full Data Dump

select * from op_results;

col time_proc_s format 999990.0000
select number_of_orders,
       sample_no,
       proc_type,
       time_proc_s,
       to_char(create_date,'DD-Mon-YYYY HH24:MI:SS') date_stamp,
       order_sum_amount
from   op_results
order  by 5;

-- Q: Is there a sig elapsed time diff between set and row processing for each number of orders?
-- I graphedI chose the what is cleary (by visual observation) the most closest call.
--
select time_proc_s||',' from op_results where number_of_orders=100000 and proc_type='set';
select time_proc_s||',' from op_results where number_of_orders=100000 and proc_type='row';

-- Q: Is there is a sig diff between throughput; set vs row
--
col np noprint
col avg_throughput format 999999990.0000
select number_of_orders np, 
       '{'||number_of_orders||',' , avg(number_of_orders/time_proc_s), '},' avg_throughput 
from   op_results
where  proc_type = 'set'
group by number_of_orders
order by number_of_orders;

select number_of_orders np, 
       '{'||number_of_orders||',' , avg(number_of_orders/time_proc_s), '},' avg_throughput 
from   op_results
where  proc_type = 'row'
group by number_of_orders
order by number_of_orders;


-- Q: Does processing for set processing scale better compared to row processing?
col np noprint
select number_of_orders np,'{'||number_of_orders||',' , avg(time_proc_s), '},' y
from   op_results 
where  proc_type = 'set' 
group by number_of_orders, '{'||number_of_orders||','
order by number_of_orders;
select number_of_orders np,'{'||number_of_orders||',' , avg(time_proc_s), '},' y
from   op_results 
where  proc_type = 'row' 
group by number_of_orders, '{'||number_of_orders||','
order by number_of_orders;

-- END