Library Cache

Library Cache

The library cache stores information about the most recently used SQL and PL/SQL statements. Library cache is very important part of Oracle Sharedpool. Shared Pool controls execution of SQL statements. Shared pool is divided into Data dictionary Cache and Library Cache. In Dedicated server configuration Private SQL area is created in PGA of server process. Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool with in the SGA.

Shared SQL Area

Shared SQL Area contains parse tree and execution plan of SQL cursors and PL/SQL programs. So executable form of SQL statements is available here which ca be reused. When a query is submitted to oracle server for execution, oracle checks if same query has been executed previously. If the parsed execution plan is found then this event is known as Library cache hit or soft parsing. If pared form of the statement is not found in the shared pool then new statement is parsed and its parsed version is stored in Shared SQL area. This is known as hard parse.

Oracle allocates memory from shared pool when a new statement is submitted. If required, oracle may deallocate memory from previous statements. As a result of this, deallocated statements shall require hard parsing when re-submitted. More resources are used to perform a hard parse. So it is very important to keep the size for shared pool large enough to avoid hard parsing.

As Library cache is kept inside Shared Pool so use SHARED_POOL_SIZE initialization parameter to increase the size of Shared Pool. It will indirectly increase memory available for Shared SQL Area.

Private SQL area

Each session issuing a SQL statement has a private SQL area in its PGA (see "Private SQL Area"). Each user that submits the same statement has a private SQL area pointing to the same shared SQL area. Thus, many private SQL areas in separate PGAs can be associated with the same shared SQL area. The database automatically determines when applications submit similar SQL statements. The database considers both SQL statements issued directly by users and applications and recursive SQL statements issued internally by other statements. The location of the private SQL area depends on the connection established for the session. If a session is connected through a shared server, then part of the private SQL area is kept in the SGA.

###DBhtml.sql

SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ;

set pagesize 0 trimspool on feedback off lines 900;

set feedback off;

SET VERIFY OFF;

SET TRIMSPOOL ON ;

set trimspool on;

set pages 1500;

set lines 1000;

set underline off;

set colsep ','

set echo off

spool /u01/healthcheck/DB_NAME-PROD.HTML

select ' ** Database General Information ** '  from dual;

SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME  FROM V$DATABASE;

SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"

FROM   sys.v_$instance;

column "Host Name" format a15;

column "Host Address" format a15;

SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;

SELECT BANNER "VERSION" FROM V$VERSION;

col "Database Size" format a15;

col "Free space" format a15;

select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",

round(free.p / 1024 / 1024/1024) || ' GB' "Free space"

from (select bytes from v$datafile

union all select bytes from v$tempfile

union all select bytes from v$log) used,

(select sum(bytes) as p from dba_free_space) free

group by free.p;

select ' ** Database Physical Size **' from dual;

select sum(bytes/1024/1024/1024) "DB Physical Size(GB)" from dba_data_files;

select ' ** Database Logical Size **' from dual;

select sum(bytes/1024/1024/1024) "DB Actual Size(GB)" from dba_segments;

select ' ** Database Users who are expiring in 15 days **' from dual;

select username , ACCOUNT_STATUS from dba_users where ACCOUNT_STATUS NOT LIKE 'OPEN' and EXPIRY_DATE > sysdate -15;

select ' ** Database SGA Component Size **' from dual;

set line 200;

select    pool, m_bytes from ( select     pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes

    from     v$sgastat

    where     pool is not null   group     by pool

    union

    select     name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes

    from     v$sgastat

    where    pool is null  order     by 2 desc

    ) UNION ALL

    select    'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat;

select ' ** DB Characterset Information **' from dual;

Select * from nls_database_parameters;

col name  format A60 heading "Control Files";

select name from   sys.v_$controlfile;

col member  format A40 heading "Redolog Files";

set line 200;

col archived format a15;

col status format a10;

col first_time format a20;

select a.group#, a.member, b.archived, b.status, b.first_time from v$logfile a, v$log b

where a.group# = b.group# order by a.group#;

select ' ** DB Profile and Default Information ** ' from dual;

set line 200;

col username format a25;

col profile format a20;

col default_tablespace format a25;

col temporary_tablespace format a25;

col EXPIRY_DATE format a20;

col lock_date format a20;

Select username, profile, default_tablespace, temporary_tablespace , EXPIRY_DATE,LOCK_DATE from dba_users;

select ' ** Users Log on Information  ** '  from dual;

set line 200;

col OSUSER format a40;

col STATUS format a15

col MACHINE format a35;

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss') "Logon_Time",osuser,status,machine from v$session where type !='BACKGROUND';

select ' ** Monitoring Schema Growth Rate **'  from dual;

select    obj.owner "Owner",  obj_cnt "Objects",  decode(seg_size, NULL, 0, seg_size) "Size in MB"

from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,

(select owner, ceil(sum(bytes)/1024/1024) seg_size

    from dba_segments group by owner) seg

where obj.owner  = seg.owner(+)

order by 3 desc ,2 desc, 1;

select ' ** Largest object in Database ** '   from dual;

SET LINE 200;

col SEGMENT_NAME format a30;

col SEGMENT_TYPE format a30;

col BYTES format a30;

col TABLESPACE_NAME FORMAT A30;

SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;

select ' ** Monitoring Most resource usnig SQL statements ** 'from dual;

set line 200;

SELECT * FROM   (SELECT Substr(a.sql_text,1,50) sql_text,

               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,

               a.buffer_gets, a.disk_reads, a.executions, a.sorts, a.address

        FROM   v$sqlarea a

        ORDER BY 2 DESC)

WHERE  rownum <= 5;

select ' ** Monitoring Objects Created within 7 days ** ' from dual;

select count(1) from user_objects where CREATED >= sysdate - 7;

select ' ** Counting Invalid object in Database ** '   from dual;

Select owner, object_type,object_name, count(*) from dba_objects where status='INVALID' group by  owner, object_type,object_name;

select ' ** Monitoring Current Running Long Job in DB ** '  from dual;

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) COMPLETE

FROM   V$SESSION_LONGOPS

WHERE TOTALWORK != 0 AND    SOFAR != TOTALWORK order by 1;

select ' ** Monitoring DML Lock **'   from dual;

set line 200;

col username format a30;

col lock_type format a20;

col osuser format a30;

col owner format a25;

col object_name format a50;

SELECT s.sid, s. serial#, s.username, l.lock_type, s.osuser, s.machine,

    o.owner, o.object_name, ROUND(w.seconds_in_wait/60, 2) "Wait_Time"

FROM

     v$session s, dba_locks l, dba_objects o, v$session_wait  w

WHERE   s.sid = l.session_id

  AND l.lock_type IN ('DML','DDL')

  AND l.lock_id1 = o.object_id

  AND l.session_id = w.sid

ORDER BY   s.sid;

select ' ** Track Redolog Generation ** '  from dual;

select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size) / 1024 / 1024)) "REDO PER DAY(MB)"

from v$archived_log

group by trunc(completion_time)

order by 1;

select ' ** Monitor DB Corruption or Need of Recovery ** '  from dual;

set line 200;

SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS,

    r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t

    WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;

select ' **  Tablespace Information  **'  from dual;

col tablespace_name format a15 heading "Tablespace Name"

SELECT Total.name "Tablespace Name",

       nvl(Free_space, 0) Free_space,

       nvl(total_space-Free_space, 0) Used_space,

       total_space

FROM

  (select tablespace_name, sum(bytes/1024/1024) Free_Space

     from sys.dba_free_space

    group by tablespace_name

  ) Free,

  (select b.name,  sum(bytes/1024/1024) TOTAL_SPACE

     from sys.v_$datafile a, sys.v_$tablespace B

    where a.ts# = b.ts#

    group by b.name

  ) Total

WHERE Free.Tablespace_name(+) = Total.name

ORDER BY Total.name

/

select ' ** Shows Used/Free Space Per Datafile **' from dual;

set linesize 200

col file_name format a50 heading "Datafile Name"

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,

         ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))

               used_mb,

         NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb

    FROM v$datafile df, dba_free_space dfs

   WHERE df.file# = dfs.file_id(+)

GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes

ORDER BY file_name;

TTI off

select ' ** Report Tablespace < 10% free space **' from dual;

set pagesize 300;

set linesize 100;

column tablespace_name format a15 heading Tablespace;

column sumb format 999,999,999;

column extents format 9999;

column bytes format 999,999,999,999;

column largest format 999,999,999,999;

column Tot_Size format 999,999 Heading "Total Size(Mb)";

column Tot_Free format 999,999,999 heading "Total Free(Kb)";

column Pct_Free format 999.99 heading "% Free";

column Max_Free format 999,999,999 heading "Max Free(Kb)";

column Min_Add format 999,999,999 heading "Min space add (MB)";

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,

sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,

ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add

from (select tablespace_name,0 tots,sum(bytes) sumb

from sys.dba_free_space a

group by tablespace_name

union

select tablespace_name,sum(bytes) tots,0 from

sys.dba_data_files

group by tablespace_name) a

group by a.tablespace_name

having sum(a.sumb)*100/sum(a.tots) < 10

order by pct_free;

-- Audited Failed Logins ;

--select ' ** Audited Failed Logins ** ' from dual;

--SELECT *  FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0  and     TIMESTAMP >=  Sysdate-1  ORDER BY extended_timestamp desc;

--SELECT USERNAME, OS_USERNAME, USERHOST, EXTENDED_TIMESTAMP FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0 ORDER BY extended_timestamp desc;

--select ' ** Audited Privileges ** ' from dual;

--Audited Privileges

--SELECT PRIV_USED, USERNAME, ACTION_NAME, TIMESTAMP FROM  SYS.DBA_AUDIT_TRAIL dat, SYS.DBA_PRIV_AUDIT_OPTS dpao WHERE

-- dat.PRIV_USED = dpao.PRIVILEGE  and dat.USERNAME = dpao.USER_NAME  and to_char(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') > TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS')

--ORDER BY extended_timestamp desc;

--select ' ** Audited Objects ** ' from dual;

--Audited Objects

--SELECT OWNER, OBJ_NAME, USERNAME, ACTION_NAME, TIMESTAMP FROM SYS.DBA_AUDIT_OBJECT   where   TIMESTAMP >= Sysdate-1 ORDER BY extended_timestamp  desc;

select ' ** File I/O statistics ** ' from dual;

prompt

set linesize 150

col name format a50 heading "Datafile Name"

select name,PHYRDS "Physical Reads",PHYWRTS "Physical Writes",READTIM "Read Time(ms)",WRITETIM "Write Time(ms)",AVGIOTIM "Avg Time" from v$filestat, v$datafile where v$filestat.file#=v$datafile.file#;

set feedback on

prompt

select ' ** Total DB Applied Patches List ** '  from dual;

  SELECT    'Patch '

         || PATCH_ID

         || '     '

         || ': applied on '

         || ACTION_TIME

         || CHR (10)

         || 'Unique Patch ID: '

         || PATCH_UID

         || CHR (10)

         || 'Patch description: "'

         || DESCRIPTION

         || '"'    PATCH_DETAIL

    FROM dba_registry_sqlpatch

ORDER BY action_time

/

select ' ** Monitor ASM Diskgroup Sizes ** '  from dual;

SET LINESIZE  145

SET PAGESIZE  9999

SET VERIFY    off

COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'

COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'

COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'

COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'

COLUMN state                  FORMAT a11           HEAD 'State'

COLUMN type                   FORMAT a6            HEAD 'Type'

COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'

COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'

COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

break on report on disk_group_name skip 1

compute sum label "Grand Total: " of total_mb used_mb on report

SELECT

    name                                     group_name

  , sector_size                              sector_size

  , block_size                               block_size

  , allocation_unit_size                     allocation_unit_size

  , state                                    state

  , type                                     type

  , total_mb                                 total_mb

  , (total_mb - free_mb)                     used_mb

  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used

FROM

    v$asm_diskgroup

ORDER BY

    name

/

select ' ** Monitor RMAN Backup JOB Details ** '  from dual;

select START_TIME,END_TIME INPUT_BYTES,STATUS,INPUT_TYPE from v$rman_backup_job_details

where END_TIME > (sysdate-7);

select ' ** Monitor Flash Recovery Area Usage ** '  from dual;

select name,round(space_limit / 1024 / 1024) space_limit_mb,round(space_used  / 1024 / 1024) space_used_mb,percent_space_used,      percent_space_reclaimable, percent_space_not_reclaimable from v$recovery_file_dest,( select sum(percent_space_reclaimable)  percent_space_reclaimable, sum(percent_space_used) percent_space_used, sum(percent_space_used - percent_space_reclaimable) percent_space_not_reclaimable from  v$recovery_area_usage) order by name;

select ' ** Monitor Standby Database Sequence ** '  from dual;

set line 150

col NAME for a50

set lines 110

select p.prim_max_sequence,

         d.dr_max_archived_sequence,

         ad.dr_max_applied_sequence,

         p.prim_max_sequence-d.dr_max_archived_sequence archive_lag_in_dr,

         p.prim_max_sequence-ad.dr_max_applied_sequence applied_lag_in_dr

from

(select max(sequence#) prim_max_sequence from v$archived_log where dest_id=1 and archived='YES') p,

(select max(sequence#) dr_max_archived_sequence from v$archived_log where standby_dest='YES' and archived='YES') d,

(select max(sequence#) dr_max_applied_sequence from v$archived_log where standby_dest='YES' and archived='YES' and applied='YES') ad;

select ' ** Monitor DB Active Sessions ** '  from dual;

select s.status,count(1),s.username from v$process p,v$session s where paddr(+)=addr group by s.status,s.username order by 1;

select ' ** Monitor Total Open Cursors ** '  from dual;

set lines 333;

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,

s.username, s.machine

from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic#  and s.sid=a.sid

and b.name = 'opened cursors current'

group by s.username, s.machine

order by 1 desc;

select ' ** Monitor Archivelog Generation Details ** '  from dual;

SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))

            "00-01",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))

            "01-02",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))

            "02-03",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))

            "03-04",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))

            "04-05",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))

            "05-06",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))

            "06-07",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))

            "07-08",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))

            "08-09",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))

            "09-10",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))

            "10-11",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))

            "11-12",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))

            "12-13",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))

            "13-14",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))

           "14-15",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))

            "15-16",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))

            "16-17",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))

            "17-18",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))

            "18-19",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))

            "19-20",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))

            "20-21",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))

            "21-22",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))

            "22-23",

         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))

            "23-00",

         COUNT (*) TOTAL

    FROM V$ARCHIVED_LOG

WHERE ARCHIVED='YES' and completion_time > (sysdate-7)

GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')

ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

select ' ** Monitor DB Block Session Details ** '  from dual;

SELECT a.inst_id, a.sid "BLOCKING ID", d.spid "BLOCKING SPID", b.sid "WAITING ID",c.sql_id "BLOCKIN_SQLID" FROM gv$lock a, gv$lock b, gv$session c, gv$process d

WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2 and a.sid=c.sid and c.paddr=d.addr

/

select ' ** Monitor Standby Database GAP ** '  from dual;

Col APPLIED_TIME format a20

Col destination format a20

Col Status format a20

SELECT DB_NAME,destination,  APPLIED_TIME, LOG_APPLIED,LOG_ARCHIVED,

(

  CASE

    WHEN ((APPLIED_TIME            IS NOT NULL    AND (LOG_ARCHIVED-LOG_APPLIED) IS NULL)

    OR (APPLIED_TIME               IS NULL    AND (LOG_ARCHIVED-LOG_APPLIED) IS NOT NULL)

    OR ((LOG_ARCHIVED-LOG_APPLIED)  > 1))

    THEN 'Error! Log Gap is '

    ELSE 'OK!'

  END) Status,

   LOG_ARCHIVED-LOG_APPLIED LOG_GAP

FROM

  ( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE WHERE INST_ID = 1 ),

  (SELECT MAX(SEQUENCE#) LOG_ARCHIVED   FROM V$ARCHIVED_LOG    WHERE DEST_ID=1 ),

   (select applied_seq# as LOG_APPLIED,destination as destination  from v$archive_dest_status WHERE DEST_ID=3 ),

  (SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME  FROM V$ARCHIVED_LOG  WHERE DEST_ID=1 );

select ' ** Monitor DB Corruptions ** '  from dual;

SELECT distinct 'Data Block# '|| block# || ' of Data File '

          || name || ' is corrupted.'

   FROM   v$backup_corruption a, v$datafile b

   WHERE  a.file# = b.file#;

select ' ** Monitor DB Incidents ** '  from dual;

set linesize 170

col RECENT_PROBLEMS for a45

select PROBLEM_KEY RECENT_PROBLEMS_1_WEEK_BACK,to_char(FIRSTINC_TIME,'DD-MON-YY HH24:mi:ss') FIRST_OCCURENCE,to_char(LASTINC_TIME,'DD-MON-YY HH24:mi:ss')

LAST_OCCURENCE FROM V$DIAG_PROBLEM WHERE LASTINC_TIME > SYSDATE -1;

select ' ** Monitor DB Alertlogfile ** '  from dual;

SELECT distinct to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'), message_text FROM x$dbgalertext

where originating_timestamp > sysdate-2 and message_text LIKE 'ORA-%';

select ' ** Current DB Alertlogfile Location ** '  from dual;

col NAME for a15

col VALUE for a60

select name, value from v$diag_info where name = 'Diag Trace';

!df -kh

!uptime

spool off

exit