..
set echo on
set linesize 121
drop table t;

clear screen
create table t as select * from all_objects;
create index t_idx on t(object_id);
begin
dbms_stats.gather_table_stats
( user, 'T',
method_opt=> 'for columns REPEAT',
cascade => TRUE );
end;
/
pause


clear screen
set autotrace traceonly explain
select * from t where object_id = 55;
pause

clear screen
begin
dbms_stats.gather_table_stats
( user, 'T',
cascade => TRUE );
end;
/
pause

clear screen
select * from t where object_id = 55;
pause
set autotrace off

clear screen
set echo off
prompt
prompt
prompt Next we'll look at autotrace statistics -- showing in this case the effect
prompt of arraysize on consistent gets (and hence latching for example)
prompt start by looking at the number of blocks in T
prompt
prompt
column blocks new_val BLOCKS
column rpb new_val RPB
set echo on

select blocks, ceil(num_rows/blocks) rpb
from user_tables
where table_name='T';
pause

set autotrace traceonly statistics
clear screen
set arraysize 10
select * from t;
exec dbms_output.put_line( round( (1+&RPB/10)*&BLOCKS ) )
pause

clear screen
set arraysize 100
select * from t;
exec dbms_output.put_line( round( (1+&RPB/100)*&BLOCKS ) )
pause

clear screen
set arraysize 500
select * from t;
exec dbms_output.put_line( round( (1+&RPB/500)*&BLOCKS ) )
pause


set autotrace off
set feedback off

clear screen
drop table emp;
drop table dept;
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
alter table emp add constraint emp_pk primary key(empno);
create index emp_ename_idx on emp(ename);
alter table dept add constraint dept_pk primary key(deptno);
exec dbms_stats.gather_table_stats( user, 'EMP', cascade=>true);
exec dbms_stats.gather_table_stats( user, 'DEPT', cascade=>true);
pause

clear screen
delete from plan_table;
explain plan for
select *
from emp, dept
where emp.deptno = dept.deptno
and mgr = (select empno from emp where ename = 'KING');
pause
set echo off
clear screen
prompt
prompt
prompt Make sure screen is 12 point........
prompt else it will not fit....
prompt
pause
set echo on

clear screen

select plan_table_output
from table(dbms_xplan.display);
pause

clear screen
delete from plan_table;
variable deptno varchar2(20)
explain plan for
select *
from dept
where deptno = :deptno;
pause
select plan_table_output
from table(dbms_xplan.display);