/* runstat를 위한 설정 */
grant select on v_$statname to test;

grant select on v_$mystat to test;

grant select on v_$latch to test;

grant select on v_$timer to test;

create global temporary table run_stats
( runid varchar2(15),
  name varchar2(80),
  value int )
on commit preserve rows;

create or replace view stats
as select 'STAT...' || a.name name, b.value
	      from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
    union all
    select 'LATCH.' || name,  gets
      from v$latch
	union all
	select 'STAT...Elapsed Time', hsecs from v$timer;

create or replace package runstats_pkg
as
    procedure rs_start;
    procedure rs_middle;
    procedure rs_stop( p_difference_threshold in number default 0 );
end;

create or replace package body runstats_pkg
as
g_start number;
g_run1  number;
g_run2  number;

procedure rs_start
is 
begin
    delete from run_stats;

    insert into run_stats 
    select 'before', stats.* from stats;
        
    g_start := dbms_utility.get_time;
end;

/* runstat설정 끝 */

/* debuf.f를 사용하기 위해서는 utl_file_dir를 설정해야 한다. */ 
alter system set utl_file_dir='C:\debug_test' scope=spfile

drop table t1;

drop table t2;

create table t1 as select level id, abs(dbms_random.random) val from dual connect by level <= 100000;

create table t2 as select level id, abs(dbms_random.random) val from dual connect by level <= 15000;

alter table t1 add constraints t1_pk primary key (id);

alter table t2 add constraints t2_pk primary key (id);

analyze table t1 compute statistics for table for all indexes for all indexed columns;

analyze table t2 compute statistics for table for all indexes for all indexed columns;

create or replace function get_data(p_id in number) 
return number is
l_val t2.val%type;
begin
  select val into l_val
    from t2
   where id = p_id;
   
  return l_val;
exception
  when no_data_found then
    return null; 	
end; 
/

/* 아우터 조인과 함수를 이용한 조인의 성능을 비교하는 테스트 */
create or replace
PROCEDURE ps_test IS
ls_ip4 varchar2(20);
BEGIN
  select SYS_CONTEXT('USERENV','HOST') 
    into ls_ip4
    from dual;
  dbms_application_info.set_module('ps_test','start');
  dbms_application_info.set_client_info('connect host : ' || ls_ip4 );
  
  runstats_pkg.rs_start;
  debug.f('entering procedure runstat stat : "%s"', 'Start');
  dbms_application_info.set_action('select outer join');
  
  for x in (select a.id
                 , a.val val_a
				 , b.val val_b
			  from t1 a, t2 b
			 where a.id = b.id(+) )
  loop
    null;
  end loop;

  runstats_pkg.rs_middle;
  debug.f('entering procedure runstat stat : "%s"', 'Middle'); 
  dbms_application_info.set_action('select function join');
  
  for x in (select id
                 , val val_a
				 , get_data(id) val_b 
			  from t1)
  loop
    null;
  end loop;
  
  runstats_pkg.rs_stop;
  debug.f('entering procedure runstat stat : "%s"', 'End');  
  
  dbms_application_info.set_action('update v$session_longops');
  update t1
     set val = val / 4;
  
  dbms_application_info.set_module('','');
  dbms_application_info.set_action('');
  dbms_application_info.set_client_info('');  
END ps_test;
/

/* debug.f 기록을 위한 파일을 open */
exec debug.init;

set serveroutput on														  			   

alter session set sql_trace=true;

begin
ps_test;
end;

alter session set sql_trace=false;


/* 다른 세션에서 v$session정보를 확인한다 */
col module format a15 
col action format a15
col client_info format a15
select module, action, client_info from v$session  where username = 'TEST';

/* 다른 세션에서 v$session_longops정보를 확인한다 */
select * from v$session_longops
 where (sid, serial#) in (select sid, serial# from v$session  where username = 'TEST');

/* debug f 기록을 확인한다. */