Oracle Scratchpad

November 17, 2019

Library Cache Stats

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 9:36 am GMT Nov 17,2019

In response to a comment that one of my notes references a call to a packate “snap_libcache”, I’ve posted this version of SQL that can be run by SYS to create the package, with a public synonym, and privileges granted to public to execute it. The package doesn’t report the DLM (RAC) related activity, and is suitable only for 11g onwards (older versions require a massive decode to convert indx numbers into names).


rem
rem     Script:         snap_11_libcache.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2001 (updated for 11gR2)
rem     Purpose:        Package to get snapshot start and delta of library cache stats
rem
rem     Notes
rem             Lots of changes needed for 11.2.x.x where x$kglst holds
rem             two types - TYPE (107) and NAMESPACE (84)
rem             
rem             Has to be run by SYS to create the package
rem             No longer needs a complex decode.
rem
rem     Usage:
rem             set serveroutput on size 1000000 format wrapped
rem             set linesize 144
rem             set trimspool on
rem             execute snap_libcache.start_snap
rem             -- do something
rem             execute snap_libcache.end_snap
rem

create or replace package snap_libcache as
        procedure start_snap;
        procedure end_snap;
end;
/

create or replace package body snap_libcache as

        cursor c1 is
                select
                        indx,
                        kglsttyp        lib_type,
                        kglstdsc        name,
                        kglstget        gets,
                        kglstght        get_hits,
                        kglstpin        pins,
                        kglstpht        pin_hits,
                        kglstrld        reloads,
                        kglstinv        invalidations,
                        kglstlrq        dlm_lock_requests,
                        kglstprq        dlm_pin_requests,
--                      kglstprl        dlm_pin_releases,
--                      kglstirq        dlm_invalidation_requests,
                        kglstmiv        dlm_invalidations
                from x$kglst
                ;


        type w_type1 is table of c1%rowtype index by binary_integer;
        w_list1         w_type1;
        w_empty_list    w_type1;

        m_start_time    date;
        m_start_flag    char(1);
        m_end_time      date;

procedure start_snap is
begin

        m_start_time := sysdate;
        m_start_flag := 'U';
        w_list1 := w_empty_list;

        for r in c1 loop
                w_list1(r.indx).gets := r.gets;
                w_list1(r.indx).get_hits := r.get_hits;
                w_list1(r.indx).pins := r.pins;
                w_list1(r.indx).pin_hits := r.pin_hits;
                w_list1(r.indx).reloads := r.reloads;
                w_list1(r.indx).invalidations := r.invalidations;
        end loop;

end start_snap;


procedure end_snap is
begin

        m_end_time := sysdate;

        dbms_output.put_line('---------------------------------');
        dbms_output.put_line('Library Cache - ' || 
                                to_char(m_end_time,'dd-Mon hh24:mi:ss') 
        );

        if m_start_flag = 'U' then
                dbms_output.put_line('Interval:-      '  || 
                                trunc(86400 * (m_end_time - m_start_time)) ||
                                ' seconds'
                );
        else
                dbms_output.put_line('Since Startup:- ' || 
                                to_char(m_start_time,'dd-Mon hh24:mi:ss')
                );
        end if;

        dbms_output.put_line('---------------------------------');

        dbms_output.put_line(
                rpad('Type',10) ||
                rpad('Description',41) ||
                lpad('Gets',12) ||
                lpad('Hits',12) ||
                lpad('Ratio',6) ||
                lpad('Pins',12) ||
                lpad('Hits',12) ||
                lpad('Ratio',6) ||
                lpad('Invalidations',14) ||
                lpad('Reloads',10)
        );

        dbms_output.put_line(
                rpad('-----',10) ||
                rpad('-----',41) ||
                lpad('----',12) ||
                lpad('----',12) ||
                lpad('-----',6) ||
                lpad('----',12) ||
                lpad('----',12) ||
                lpad('-----',6) ||
                lpad('-------------',14) ||
                lpad('------',10)
        );

        for r in c1 loop
                if (not w_list1.exists(r.indx)) then
                    w_list1(r.indx).gets := 0;
                    w_list1(r.indx).get_hits := 0;
                    w_list1(r.indx).pins := 0;
                    w_list1(r.indx).pin_hits := 0;
                    w_list1(r.indx).invalidations := 0;
                    w_list1(r.indx).reloads := 0;
                end if;

                if (
                           (w_list1(r.indx).gets != r.gets)
                        or (w_list1(r.indx).get_hits != r.get_hits)
                        or (w_list1(r.indx).pins != r.pins)
                        or (w_list1(r.indx).pin_hits != r.pin_hits)
                        or (w_list1(r.indx).invalidations != r.invalidations)
                        or (w_list1(r.indx).reloads != r.reloads)
                ) then

                        dbms_output.put(rpad(substr(r.lib_type,1,10),10));
                        dbms_output.put(rpad(substr(r.name,1,41),41));
                        dbms_output.put(to_char( 
                                r.gets - w_list1(r.indx).gets,
                                        '999,999,990')
                        );
                        dbms_output.put(to_char( 
                                r.get_hits - w_list1(r.indx).get_hits,
                                        '999,999,990'));
                        dbms_output.put(to_char( 
                                (r.get_hits - w_list1(r.indx).get_hits)/
                                greatest(
                                        r.gets - w_list1(r.indx).gets,
                                        1
                                ),
                                        '999.0'));
                        dbms_output.put(to_char( 
                                r.pins - w_list1(r.indx).pins,
                                        '999,999,990')
                        );
                        dbms_output.put(to_char( 
                                r.pin_hits - w_list1(r.indx).pin_hits,
                                        '999,999,990'));
                        dbms_output.put(to_char( 
                                (r.pin_hits - w_list1(r.indx).pin_hits)/
                                greatest(
                                        r.pins - w_list1(r.indx).pins,
                                        1
                                ),
                                        '999.0'));
                        dbms_output.put(to_char( 
                                r.invalidations - w_list1(r.indx).invalidations,
                                        '9,999,999,990')
                        );
                        dbms_output.put(to_char( 
                                r.reloads - w_list1(r.indx).reloads,
                                        '9,999,990')
                        );
                        dbms_output.new_line;
                end if;

        end loop;

end end_snap;

begin
        select
                startup_time, 'S'
        into
                m_start_time, m_start_flag
        from
                v$instance;

end snap_libcache;
/


drop public synonym snap_libcache;
create public synonym snap_libcache for snap_libcache;
grant execute on snap_libcache to public;


You’ll note that there are two classes of data, “namespace” and “type”. The dynamic view v$librarycache reports only the namespace rows.

1 Comment »

  1. […] of a couple of little utilities I wrote years ago to take snapshots of my session statistics and the (global) library cache (v$librarycache) stats. I’ve also used my “repetition” framework to execute a basic query 1,000 times. […]

    Pingback by Parse Calls | Oracle Scratchpad — January 13, 2020 @ 11:36 am GMT Jan 13,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.