-- Setup
--------
drop table T1 purge;

create table T1(
  id NUMBER not null,
  col1 NUMBER,
  col2 NUMBER
)
tablespace USERS;

declare 
  v_id NUMBER;
  v_col1 NUMBER;
  v_col2 NUMBER;
begin
  for i IN 1..40 loop
    -- id column
    v_id:=i;
    -- col1 column
    if (i between 1 and 15) then v_col1:=mod(i,3); end if;
    if (i between 16 and 40) then v_col1:=i; end if;
    -- col2 column
    if (i between 1 and 30) then v_col2:=mod(i,6); end if;
    if (i between 31 and 40) then v_col2:=999; end if;
    -- insert values
    insert into T1 values (v_id,v_col1,v_col2);
  end loop;
  commit;
end;
/


drop table T2 purge;

create table T2(
  id NUMBER not null,
  col1 NUMBER,
  col2 NUMBER
)
tablespace USERS;

declare 
  v_id NUMBER;
  v_col1 NUMBER;
  v_col2 NUMBER;
begin
  for i IN 1..150 loop
    -- id column
    v_id:=i;
    -- col1 column
    if (i between 1 and 49) then v_col1:=mod(i,7); end if;
    if (i between 50 and 100) then v_col1:=i; end if;
    if (i between 101 and 150) then v_col1:=777; end if;
    -- col2 column
    if (i between 1 and 100) then v_col2:=mod(i,10); end if;
    if (i between 101 and 140) then v_col2:=999; end if;
    if (i between 141 and 150) then v_col2:=i; end if;
    -- insert values
    insert into T2 values (v_id,v_col1,v_col2);
  end loop;
  commit;
end;
/


exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for all columns size 1');
exec dbms_stats.gather_table_stats(user,'T2', method_opt=>'for all columns size 1');


set lines 250 pages 999
alter system flush shared_pool;

drop table Q purge;
create table Q as
select /*+ GATHER_PLAN_STATISTICS */
    T1.ID    id1
  , T2.ID    id2
  , T1.COL1  val
from
    T1,
    T2
where
  T1.COL1=T2.COL1
/

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));


set lines 250 pages 999
alter system flush shared_pool;

drop table Q purge;
create table Q as
select /*+ GATHER_PLAN_STATISTICS */
    T1.ID    id1
  , T2.ID    id2
  , T1.COL1  val
from
    T1,
    T2
where
  T1.COL2=T2.COL2
/

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));


---------------------------------------------------------
set lines 250 pages 999
alter system flush shared_pool;

drop table Q purge;
create table Q as
select /*+ GATHER_PLAN_STATISTICS */
    T1.ID    id1
  , T2.ID    id2
  , T1.COL1  val
from
    T1,
    T2
where
  T1.COL1=T2.COL1 and
  T1.COL2=T2.COL2
/

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));





set lines 250 pages 999
alter system flush shared_pool;

drop table Q purge;
create table Q as
select /*+ GATHER_PLAN_STATISTICS */
    T1.ID    id1
  , T2.ID    id2
  , T1.COL1  val
from
    T1,
    T2
where
  T1.COL1=T2.COL1 or
  T1.COL2=T2.COL2
/

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));

--------------------------------------------------------

set lines 250 pages 999
alter system flush shared_pool;

drop table Q purge;
create table Q as
select /*+ GATHER_PLAN_STATISTICS MONITOR */
*
from
    T2
where
  COL1>=7
/

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES +NOTE'));

set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000

select dbms_perf.report_sql(sql_id=>'cgud94u0jkhjj',outer_start_time=>sysdate-1, outer_end_time=>sysdate, selected_start_time=>sysdate-1, selected_end_time=>sysdate,type=>'TEXT') from dual;

SELECT report_id,PERIOD_START_TIME,PERIOD_END_TIME,GENERATION_TIME FROM dba_hist_reports WHERE component_name = 'sqlmonitor' AND (period_start_time BETWEEN sysdate-1 and sysdate )AND key1 = 'cgud94u0jkhjj';


set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => 145, TYPE => 'text') FROM dual;