This discussion is archived
10 Replies Latest reply: Apr 25, 2013 5:00 AM by User477708-OC RSS

simulate latch : cache buffer chains

user12288492 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points