Shared Pool Utilization

 
SET  LONG   10000000 ;
 SET LONGC 10000000 ;
 SET LINESIZE 32767 ;
 SET PAGESIZE 0 ;
 SET TRIMSPOOL ON ;
SPOOL sahred_pool.html
SELECT DBMS_PERF.REPORT_PERFHUB FROM DUAL;
SPOOL OFF;


 
SELECT 1                                                                   NOPR,
       TO_CHAR (A.INST_ID)                                                 INST_ID,
       A.USERS                                                             USERS,
       TO_CHAR (A.GARBAGE, '9,999,999,999')                                GARBAGE,
       TO_CHAR (B.GOOD, '9,999,999,999')                                   GOOD,
       TO_CHAR ((B.GOOD / (B.GOOD + A.GARBAGE)) * 100, '9,999,999.999')    GOOD_PERCENT
  FROM (  SELECT A.INST_ID,
                 B.USERNAME                                  USERS,
                 SUM (A.SHARABLE_MEM + A.PERSISTENT_MEM)     GARBAGE,
                 TO_NUMBER (NULL)                            GOOD
            FROM SYS.GV_$SQLAREA A, DBA_USERS B
           WHERE (A.PARSING_USER_ID = B.USER_ID AND A.EXECUTIONS <= 1)
        GROUP BY A.INST_ID, B.USERNAME
        UNION
          SELECT DISTINCT C.INST_ID,
                          B.USERNAME                                  USERS,
                          TO_NUMBER (NULL)                            GARBAGE,
                          SUM (C.SHARABLE_MEM + C.PERSISTENT_MEM)     GOOD
            FROM DBA_USERS B, SYS.GV_$SQLAREA C
           WHERE (B.USER_ID = C.PARSING_USER_ID AND C.EXECUTIONS > 1)
        GROUP BY C.INST_ID, B.USERNAME) A,
       (  SELECT A.INST_ID,
                 B.USERNAME                                  USERS,
                 SUM (A.SHARABLE_MEM + A.PERSISTENT_MEM)     GARBAGE,
                 TO_NUMBER (NULL)                            GOOD
            FROM SYS.GV_$SQLAREA A, DBA_USERS B
           WHERE (A.PARSING_USER_ID = B.USER_ID AND A.EXECUTIONS <= 1)
        GROUP BY A.INST_ID, B.USERNAME
        UNION
          SELECT DISTINCT C.INST_ID,
                          B.USERNAME                                  USERS,
                          TO_NUMBER (NULL)                            GARBAGE,
                          SUM (C.SHARABLE_MEM + C.PERSISTENT_MEM)     GOOD
            FROM DBA_USERS B, SYS.GV_$SQLAREA C
           WHERE (B.USER_ID = C.PARSING_USER_ID AND C.EXECUTIONS > 1)
        GROUP BY C.INST_ID, B.USERNAME) B
 WHERE     A.USERS = B.USERS
       AND A.INST_ID = B.INST_ID
       AND A.GARBAGE IS NOT NULL
       AND B.GOOD IS NOT NULL
UNION
SELECT 2                    NOPR,
       '-------'            INST_ID,
       '-------------'      USERS,
       '--------------'     GARBAGE,
       '--------------'     GOOD,
       '--------------'     GOOD_PERCENT
  FROM DUAL
UNION
  SELECT 3                                             NOPR,
         TO_CHAR (A.INST_ID, '999999'),
         TO_CHAR (COUNT (A.USERS))                     USERS,
         TO_CHAR (SUM (A.GARBAGE), '9,999,999,999')    GARBAGE,
         TO_CHAR (SUM (B.GOOD), '9,999,999,999')       GOOD,
         TO_CHAR (((SUM (B.GOOD) / (SUM (B.GOOD) + SUM (A.GARBAGE))) * 100),
                  '9,999,999.999')                     GOOD_PERCENT
    FROM (  SELECT A.INST_ID,
                   B.USERNAME                                  USERS,
                   SUM (A.SHARABLE_MEM + A.PERSISTENT_MEM)     GARBAGE,
                   TO_NUMBER (NULL)                            GOOD
              FROM SYS.GV_$SQLAREA A, DBA_USERS B
             WHERE (A.PARSING_USER_ID = B.USER_ID AND A.EXECUTIONS <= 1)
          GROUP BY A.INST_ID, B.USERNAME
          UNION
            SELECT DISTINCT C.INST_ID,
                            B.USERNAME                                  USERS,
                            TO_NUMBER (NULL)                            GARBAGE,
                            SUM (C.SHARABLE_MEM + C.PERSISTENT_MEM)     GOOD
              FROM DBA_USERS B, SYS.GV_$SQLAREA C
             WHERE (B.USER_ID = C.PARSING_USER_ID AND C.EXECUTIONS > 1)
          GROUP BY C.INST_ID, B.USERNAME) A,
         (  SELECT A.INST_ID,
                   B.USERNAME                                  USERS,
                   SUM (A.SHARABLE_MEM + A.PERSISTENT_MEM)     GARBAGE,
                   TO_NUMBER (NULL)                            GOOD
              FROM SYS.GV_$SQLAREA A, DBA_USERS B
             WHERE (A.PARSING_USER_ID = B.USER_ID AND A.EXECUTIONS <= 1)
          GROUP BY A.INST_ID, B.USERNAME
          UNION
            SELECT DISTINCT C.INST_ID,
                            B.USERNAME                                  USERS,
                            TO_NUMBER (NULL)                            GARBAGE,
                            SUM (C.SHARABLE_MEM + C.PERSISTENT_MEM)     GOOD
              FROM DBA_USERS B, SYS.GV_$SQLAREA C
             WHERE (B.USER_ID = C.PARSING_USER_ID AND C.EXECUTIONS > 1)
          GROUP BY C.INST_ID, B.USERNAME) B
   WHERE     A.USERS = B.USERS
         AND A.INST_ID = B.INST_ID
         AND A.GARBAGE IS NOT NULL
         AND B.GOOD IS NOT NULL
GROUP BY A.INST_ID
ORDER BY 1, 2 DESC;




 
SET serveroutput on;

DECLARE
   object_mem       NUMBER;
   shared_sql       NUMBER;
   cursor_mem       NUMBER;
   mts_mem          NUMBER;
   used_pool_size   NUMBER;
   free_mem         NUMBER;
   pool_size        VARCHAR2 (512);                     -- Now from V$SGASTAT
BEGIN
   -- Stored objects (packages, views)
   SELECT SUM (sharable_mem)
     INTO object_mem
     FROM v$db_object_cache;

   -- Shared SQL -- need to have additional memory if dynamic SQL used
   SELECT SUM (sharable_mem)
     INTO shared_sql
     FROM v$sqlarea;

   -- User Cursor Usage -- run this during peak usage.
   --  assumes 250 bytes per open cursor, for each concurrent user.
   SELECT SUM (250 * users_opening)
     INTO cursor_mem
     FROM v$sqlarea;

   -- For a test system -- get usage for one user, multiply by # users
   -- select (250 * value) bytes_per_user
   -- from v$sesstat s, v$statname n
   -- where s.statistic# = n.statistic#
   -- and n.name = 'opened cursors current'
   -- and s.sid = 25;  -- where 25 is the sid of the process
   -- MTS memory needed to hold session information for shared server users
   -- This query computes a total for all currently logged on users (run
   --  multiply by # users.
   SELECT SUM (VALUE)
     INTO mts_mem
     FROM v$sesstat s, v$statname n
    WHERE s.statistic# = n.statistic# AND n.NAME = 'session uga memory max';

   -- Free (unused) memory in the SGA: gives an indication of how much memory
   -- is being wasted out of the total allocated.
   SELECT BYTES
     INTO free_mem
     FROM v$sgastat
    WHERE NAME = 'free memory' AND pool = 'shared pool';

   -- For non-MTS add up object, shared sql, cursors and 20% overhead.
   used_pool_size := ROUND (1.2 * (object_mem + shared_sql + cursor_mem));

   -- For MTS mts contribution needs to be included (comment out previous line)
   -- used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));
   SELECT SUM (BYTES)
     INTO pool_size
     FROM v$sgastat
    WHERE pool = 'shared pool';

   -- Display results
   DBMS_OUTPUT.put_line ('Shared Pool Memory Utilization Report');
   DBMS_OUTPUT.put_line ('Obj mem:  ' || TO_CHAR (object_mem) || ' bytes');
   DBMS_OUTPUT.put_line ('Shared sql:  ' || TO_CHAR (shared_sql) || ' bytes');
   DBMS_OUTPUT.put_line ('Cursors:  ' || TO_CHAR (cursor_mem) || ' bytes');
   -- dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' bytes');
   DBMS_OUTPUT.put_line (   'Free memory: '
                         || TO_CHAR (free_mem)
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (free_mem / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Shared pool utilization (total):  '
                         || TO_CHAR (used_pool_size)
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (used_pool_size / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Shared pool allocation (actual):  '
                         || pool_size
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (pool_size / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Percentage Utilized:  '
                         || TO_CHAR (ROUND (used_pool_size / pool_size * 100))
                        );
END;
/









–SHARED POOL QUICK CHECK NOTES:

select 'You may need to increase the SHARED_POOL_RESERVED_SIZE' Description,
'Request Failures = '||REQUEST_FAILURES Logic
from v$shared_pool_reserved
where REQUEST_FAILURES > 0
and 0 != (select to_number(VALUE) from v$parameter where NAME = 'shared_pool_reserved_size')
union
select 'You may be able to decrease the SHARED_POOL_RESERVED_SIZE' Description,
'Request Failures = '||REQUEST_FAILURES Logic
from v$shared_pool_reserved where REQUEST_FAILURES < 5
and 0 != ( select to_number(VALUE) from v$parameter where NAME = 'shared_pool_reserved_size')
–SHARED POOL MEMORY USAGE NOTES:>
V$DB_OBJECT_CACHE
This view displays database objects that are cached in the library cache. Objects include tables, indexes,
clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

select OWNER, NAME||' - '||TYPE object, SHARABLE_MEM
from v$db_object_cache
where SHARABLE_MEM > 10000
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order by SHARABLE_MEM desc
–LOADS INTO SHARED POOL NOTES:
select OWNER, NAME||’ – ‘||TYPE object, LOADS
from v$db_object_cache
where LOADS > 3
and type in (‘PACKAGE’,’PACKAGE BODY’,’FUNCTION’,’PROCEDURE’)
order by LOADS desc

–SHARED POOL EXECUTION NOTES:
select OWNER, NAME||’ – ‘||TYPE object, EXECUTIONS
from v$db_object_cache
where EXECUTIONS > 100
and type in (‘PACKAGE’,’PACKAGE BODY’,’FUNCTION’,’PROCEDURE’)
order by EXECUTIONS desc

 

–SHARED POOL DETAIL NOTES:
select OWNER, NAME, DB_LINK, NAMESPACE, TYPE, SHARABLE_MEM,
LOADS, EXECUTIONS, LOCKS, PINS
from v$db_object_cache
order by OWNER, NAME

–SHARED POOL V$LIBRARYCACHE STATISTIC NOTES:
select NAMESPACE, GETS, GETHITS, round(GETHITRATIO*100,2) gethit_ratio,
PINS, PINHITS, round(PINHITRATIO*100,2) pinhit_ratio, RELOADS, INVALIDATIONS
from v$librarycache

–SHARED POOL RESERVED SIZE NOTES:
select NAME, VALUE
from v$parameter
where NAME like ‘%reser%’

–PINNED OBJECT NOTES:
select NAME,TYPE,KEPT
from v$db_object_cache
where KEPT = ‘YES’

 




REM LOCATION:   Database TuningShared Pool Reports
REM FUNCTION:   Estimates shared pool utilization
REM TESTED ON:  7.3.3.5, 8.0.4.1, 8.1.5, 8.1.7, 9.0.1, 10.2.0.3, 11.1.0.6
REM PLATFORM:   non-specific
REM REQUIRES:   v$db_object_cache, v$sqlarea, v$sesstat, v$statname,
REM             v$sgastat, v$parameter
REM
REM  This is a part of the Knowledge Xpert for Oracle Administration library.
REM  Copyright (C) 2008 Quest Software
REM  All rights reserved.
REM
REM******************** Knowledge Xpert for Oracle Administration ********************
REM
REM NOTES:     Based on current database usage. This should be
REM            run during peak operation, after all stored
REM            objects i.e. packages, views have been loaded.
REM
REM 08/02/08 Robert Freeman - Modified to use v$sgastat instead v$parameter for
REM                           shared pool size.
REM***********************************************************************************
REM

REM If running Shared Server uncomment the mts calculation and output commands.
SET serveroutput on;

DECLARE
   object_mem       NUMBER;
   shared_sql       NUMBER;
   cursor_mem       NUMBER;
   mts_mem          NUMBER;
   used_pool_size   NUMBER;
   free_mem         NUMBER;
   pool_size        VARCHAR2 (512);                     -- Now from V$SGASTAT
BEGIN
   -- Stored objects (packages, views)
   SELECT SUM (sharable_mem)
     INTO object_mem
     FROM v$db_object_cache;

   -- Shared SQL -- need to have additional memory if dynamic SQL used
   SELECT SUM (sharable_mem)
     INTO shared_sql
     FROM v$sqlarea;

   -- User Cursor Usage -- run this during peak usage.
   --  assumes 250 bytes per open cursor, for each concurrent user.
   SELECT SUM (250 * users_opening)
     INTO cursor_mem
     FROM v$sqlarea;

   -- For a test system -- get usage for one user, multiply by # users
   -- select (250 * value) bytes_per_user
   -- from v$sesstat s, v$statname n
   -- where s.statistic# = n.statistic#
   -- and n.name = 'opened cursors current'
   -- and s.sid = 25;  -- where 25 is the sid of the process
   -- MTS memory needed to hold session information for shared server users
   -- This query computes a total for all currently logged on users (run
   --  multiply by # users.
   SELECT SUM (VALUE)
     INTO mts_mem
     FROM v$sesstat s, v$statname n
    WHERE s.statistic# = n.statistic# AND n.NAME = 'session uga memory max';

   -- Free (unused) memory in the SGA: gives an indication of how much memory
   -- is being wasted out of the total allocated.
   SELECT BYTES
     INTO free_mem
     FROM v$sgastat
    WHERE NAME = 'free memory' AND pool = 'shared pool';

   -- For non-MTS add up object, shared sql, cursors and 20% overhead.
   used_pool_size := ROUND (1.2 * (object_mem + shared_sql + cursor_mem));

   -- For MTS mts contribution needs to be included (comment out previous line)
   -- used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));
   SELECT SUM (BYTES)
     INTO pool_size
     FROM v$sgastat
    WHERE pool = 'shared pool';

   -- Display results
   DBMS_OUTPUT.put_line ('Shared Pool Memory Utilization Report');
   DBMS_OUTPUT.put_line ('Obj mem:  ' || TO_CHAR (object_mem) || ' bytes');
   DBMS_OUTPUT.put_line ('Shared sql:  ' || TO_CHAR (shared_sql) || ' bytes');
   DBMS_OUTPUT.put_line ('Cursors:  ' || TO_CHAR (cursor_mem) || ' bytes');
   -- dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' bytes');
   DBMS_OUTPUT.put_line (   'Free memory: '
                         || TO_CHAR (free_mem)
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (free_mem / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Shared pool utilization (total):  '
                         || TO_CHAR (used_pool_size)
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (used_pool_size / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Shared pool allocation (actual):  '
                         || pool_size
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (pool_size / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Percentage Utilized:  '
                         || TO_CHAR (ROUND (used_pool_size / pool_size * 100))
                        );
END;
/




Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more