dba view study 1

SELECT * FROM v$sort_usage

Select * from v$sgastat order by bytes desc

Select Pool, Name, Bytes, (Bytes/1000000) MB from v$sgastat where name like '%cache%' order by bytes desc

Select * from v$librarycache

Select sga.*, lbc.* 

  from v$sgastat sga,

       (select NameSpace, Gets, GetHits, GetHitRatio, Pins, PinHits, PinHitRatio, Reloads, Invalidations from v$librarycache) lbc 

 Where Upper(sga.Name) = lbc.namespace(+) order by sga.bytes desc

Select * from v$sqlarea

Select ADDR, Latch#, Level#, Name, Hash, Gets, Misses, Sleeps, Wait_Time from v$latch Where Name ='shared pool'

Select * from v$latch_children Where Name ='shared pool'

Select l.ADDR, l.Latch#, l.Level#, l.Name, l.Hash, l.Gets, l.Misses, l.Sleeps, l.Wait_Time , lc.* 

  from v$latch l, v$latch_children lc Where l.Name ='shared pool' and l.Latch# = lc.Latch# order by lc.child# 

Select l.ADDR, l.Latch#, l.Level#, l.Name, l.Hash, l.Gets, l.Misses, l.Sleeps, l.Wait_Time , lc.* 

  from v$latch l, v$latch_children lc Where l.Name ='library cache' and l.Latch# = lc.Latch# order by lc.child# 

Select * from v$db_object_cache

/* 

   Library cache locks and pins are externalized in three x$ tables. 

    x$kgllk is externalizing all locking structures on an object. 

    Entries in x$kglob acts as a resource structure. 

    x$kglpn is externalizing all library cache pins.

    

    x$kglob.kglhdadr acts as a pointer to the resource structure. 

    Presumably, kglhdadr stands KGL handle address. 

    x$kgllk acts as a lock structure and x$kgllk.kgllkhdl points to x$kglob.kglhdadr. Also, 

    x$kglpn acts as a pin stucture and x$kglpn.kglpnhdl points to x$kglob.kglhdadr to pin a resource. 

    To give an analogy between object locking scenarios, 

    x$kglob acts as resource structure and 

    x$kgllk acts as lock structures for library cache locks. 

    For library cache pins, 

    x$kglpn acts as pin structure. 

    x$kglpn also pins that resource using kglpnhdl. This might be clear after reviewing the example below.

*/

Select x.*  from sys.x$kglob x -- This view shous the acutal running sql, procedures, functions ..

Select User_Name, kglnaobj, x.* from sys.x$kgllk x where kglnaobj like '%from%'  --***

Select x.* from sys.x$kglpn x 

################  Test case Start  At https://orainternals.wordpress.com/tag/xkglob/  ##################

We will create a simple test case to create library cache locks and pin waits

    create or replace procedure backup.test_kgllk (l_sleep in boolean , l_compile in boolean)

    as

     begin

      if (l_sleep ) then

        sys.dbms_lock.sleep(60);

      elsif (l_compile )  then

          execute immediate 'alter procedure test_kgllk compile';

      end if;

     end;

    /

In this test case above, we create a procedure and it accepts two boolean parameters: sleep and compile. 

Passing true to first argument will enable the procedure to sleep for a minute 

and passing true for the second argument will enable the procedure to recompile itself.

Let’s create two sessions in the database and then execute them as below.

Session #1: exec test_kgllk ( true, false); - Sleep for 1 minutes and no compile

Session #2: exec test_kgllk ( false, true); - No sleep,but compile..

At this point both sessions are waiting. Following SQL can be used to print session wait details.

select

 distinct

   ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,ses.ksuseunm machine,

   ob.kglnaown obj_owner, ob.kglnaobj obj_name

   ,pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req

   , w.state, w.event, w.wait_Time, w.seconds_in_Wait

   -- lk.kglnaobj, lk.user_name, lk.kgllksnm,

   --,lk.kgllkhdl,lk.kglhdpar

   --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,

   --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl

 from

  x$kglpn pn,  x$kglob ob,x$ksuse ses

   , v$session_wait w

where pn.kglpnhdl in

(select kglpnhdl from x$kglpn where kglpnreq >0 )

and ob.kglhdadr = pn.kglpnhdl

and pn.kglpnuse = ses.addr

and w.sid = ses.indx

order by seconds_in_wait desc

/

Output of above SQL is:

                                                                pin  pin  pin                                 wait seconds

  SID   SERIAL# USERNAME     MACHINE   OBJ_OWNER  OBJ_NAME      cnt  mode req  STATE      EVENT               time in_wait

----- --------- ------------ --------- ---------- ------------- ---- ---- ---- ---------- ------------------- ----- -------

  268     12409 SYS          orap      SYS        TEST_KGLLK    3    2    0    WAITING    PL/SQL lock timer       0       7

  313     45572 SYS          orap      SYS        TEST_KGLLK    0    0    3    WAITING    library cache pin       0       3

  313     45572 SYS          orap      SYS        TEST_KGLLK    3    2    0    WAITING    library cache pin       0       3

Session 268 (session #1) is sleeping while holding library cache pin on test_kgllk object (waiting on PL/SQL lock timer more accurately).

Session 313 is holding library cache pin in mode 2 and waiting for library cache pin in mode 3.

Obviously, session 313 is waiting for session 268 to release library cache pins. 

Since session 268 is executing, 

session 313 should not be allowed to modify test_kgllk library cache object. 

That’s exactly why library cache pins are needed.

################  Test case  End  ##################

 

Select * from resource_group_mapping$

Select * from fet$

Select * from ts$

Select * from view$

Select * from icol$

Select D_OBJ#, ORDER#, TYPES, COLUMNS  from access$

Select * from sys.all_synonym

Select * from fixed_obj$ fo order by fo.timestamp desc

Library cache pin holders/waiters

---------------------------------

                                                                                   pin  pin  pin                                            wait seconds

  SID   SERIAL# USERNAME     MACHINE              OBJ_OWNER  OBJ_NAME              cnt mode  req STATE      EVENT                           time in_wait

----- --------- ------------ -------------------- ---------- -------------------- ---- ---- ---- ---------- ------------------------------ ----- -------

  313     45572 SYS          oraperf              SYS        TEST_KGLLK              0    0    3 WAITING    library cache pin                  0      26

  313     45572 SYS          oraperf              SYS        TEST_KGLLK              3    2    0 WAITING    library cache pin                  0      26

Wait, what happened to session #2? It is not visible in x$kglpn. Querying v$session_wait shows that Session #2 is waiting for library cache lock. We will ru