02. library_cache_monitoring

----------------------------------------------
-- @name: library_cache_monitoring
-- @author: dion cho
-- @description: test for monitoring library cache
----------------------------------------------

@capture_on

@version

-- caution!!! should do this
set serveroutput on

drop table t1 purge;

create table t1(c1 int, c2 int);


-------------------------------------
-- case1: simple execution
-- execute query
select /*+ lib cache */ * from t1
;

ed sql
/*
col sql_id format a13

-- v$sqlarea
select
  hash_value,
  sql_id,
  version_count,
  loads,
  executions,
  invalidations
from
  v$sqlarea
where
  sql_text like
      'select /*+ lib cache */%'
;

-- v$sql
select
  hash_value,
  sql_id,
  child_number,
  loads,
  executions,
  invalidations
from
  v$sql
where
  sql_text like
      'select /*+ lib cache */%'
;

*/

@sql


-------------------------------------
-- case2: ddl on table
alter table t1 add c3 int;

@sql

-- reexecute the sql
select /*+ lib cache */ * from t1
;

@sql


----------------------------------------
-- case3: gather statistics
exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);

@sql

-- reexecute the sql
select /*+ lib cache */ * from t1
;

@sql

--------------------------------------------
-- case4: recreate the table
drop table t1 purge;

create table t1(c1 int, c2 int);

@sql


select /*+ lib cache */ * from t1
;

@sql


---------------------------------------------
-- case5: same text but different statement
alter session set optimizer_mode = first_rows;

select /*+ lib cache */ * from t1
;

@sql


exec print_table('select * from v$sql_shared_cursor where sql_id = ''&sql_id''');


@capture_off

Comments