Online Contents

Paperless Oracle‎ > ‎Cursor‎ > ‎

03. session cursor sharing.sql

--------------------------------------------------------------------------------
-- @name: session cursor sharing
-- @author: dion cho
-- @note: session cursor sharing
--------------------------------------------------------------------------------

drop table t1 purge;

create table t1(c1 number);

insert into t1 select level from dual connect by level <= 10
;

commit;

-- session #1
select /* session_cursor_sharing */ * from t1;

-- session #2
@lco % 'select /* session_cursor_sharing */%'

@lock_internal 'select sid from v$session' 'select /* session_cursor_sharing */%'

-- weird?
-- session #1
select * from dual;

-- session #2
@lco % 'select /* session_cursor_sharing */%'

@lock_internal 'select sid from v$session' 'select /* session_cursor_sharing */%'


-- session #1 - second execution
select /* session_cursor_sharing */ * from t1;
select * from dual;


-- session #2
@lco % 'select /* session_cursor_sharing */%'

@lock_internal 'select sid from v$session' 'select /* session_cursor_sharing */%'


-- session #1 - third execution
select /* session_cursor_sharing */ * from t1;
select * from dual;


-- session #2
@lco % 'select /* session_cursor_sharing */%'

@lock_internal 'select sid from v$session' 'select /* session_cursor_sharing */%'

-- library cache dump
@ukja102
alter session set events 'immediate trace name library_cache level 10';
@trace_file
ho copy &trace_file trc1.trc


-- what if we have the shared pool flushed out
alter system flush shared_pool;

@lco % 'select /* session_cursor_sharing */%'

@lock_internal 'select sid from v$session' 'select /* session_cursor_sharing */%'

-- library cache dump?
@ukja102
alter session set events 'immediate trace name library_cache level 10';
@trace_file
ho copy &trace_file trc2.trc

-- compare it!
ed trc1.trc
ed trc2.trc



-- when session being terminated
-- session #1
exit

-- session #2
@lco % 'select /* session_cursor_sharing */%'

@lock_internal 'select sid from v$session' 'select /* session_cursor_sharing */%'




Comments