10 Replies Latest reply: Apr 25, 2013 7:00 AM by oralicious RSS

    simulate latch : cache buffer chains

    user12288492
      I want to simulate latch : cache buffer chains wait event due to use of nested loop join for lookup tables

      This is what a tried :

      -- create parent / child tables

      SQL>drop table emp1 purge;
      drop table dept1 purge;

      create table dept1 (dept_id number primary key,
      dept_name char(30));

      create table emp1 (id number primary key,
      sal number,
      dept_id number references dept1(dept_id));

      begin
      for i in 1..1000 loop
      insert into dept1 values (i, 'dept'||i);
      end loop;
      end;
      /

      drop sequence test_seq;
      create sequence test_seq start with 1 increment by 1 cache 2000;

      begin
      for i in 1..1000 loop
      for j in 1..1000 loop
      insert into emp1 values (test_seq.nextval, i*10, i);
      commit;
      end loop;
      end loop;
      end;
      /




      exec dbms_stats.gather_table_stats(USER, 'EMP1', CASCADE=>true);
      exec dbms_stats.gather_table_stats(USER, 'DEPT1', CASCADE=>true);


      I traced many queries like the one given below (dept_id between 1 and n where n varied from 10 to 1000) but they always result in hash join


      1* select d.dept_name, e.id from sys.dept1 d, sys.emp1 e where d.dept_id = e.dept_id and e.dept_id < 1000


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 619452140

      ----------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ----------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 998K| 41M| 680 (2)| 00:00:09 |
      |* 1 | HASH JOIN | | 998K| 41M| 680 (2)| 00:00:09 |
      |* 2 | TABLE ACCESS FULL| DEPT1 | 999 | 34965 | 4 (0)| 00:00:01 |
      |* 3 | TABLE ACCESS FULL| EMP1 | 999K| 8780K| 672 (2)| 00:00:09 |
      ----------------------------------------------------------------------------

      what can I do to get a nested loop join to simulate latch : cache buffer chains?

      Regards