This section shows some example SQL that can be used to help find potential issues in the shared pool. The output of these statements should be spooled to a file.
Note: These statements may add to any latch contention as described in "Using V$ Views (V$SQL and V$SQLAREA)" above.

• Finding literal SQL

          SELECT substr(sql_text,1,40) "SQL", 

                 count(*) , 

                 sum(executions) "TotExecs"

            FROM v$sqlarea

           WHERE executions < 5

           GROUP BY substr(sql_text,1,40)

          HAVING count(*) > 30

           ORDER BY 2



          ;

This helps find commonly used literal SQL - See "Eliminating Literal SQL" above.

• Finding the Library Cache hit ratio

        SELECT SUM(PINS) "EXECUTIONS",

        SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"

        FROM V$LIBRARYCACHE;

If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses

• Checking hash chain lengths:

        SELECT hash_value, count(*)

          FROM v$sqlarea 

         GROUP BY hash_value 

        HAVING count(*) > 5

        ;

This should usually return no rows. If there are any HASH_VALUES with high counts (double figures) then you may be seeing the effects of a bug, or an unusual form of literal SQL statement. It is advisable to drill down and list out all the statements mapping to the same HASH_VALUE. Eg:

 SELECT sql_text FROM v$sqlarea WHERE hash_value= <XXX>;

and if these look the same get the full statements from V$SQLTEXT. It is possible for many literals to map to the same hash value. Eg: In 7.3 two statements may have the same hash value if a literal value occurs twice in the statement and there are exactly 32 characters between the occurrences.

• Checking for high version counts:

        SELECT address, hash_value,

                version_count ,

                users_opening ,

                users_executing,

                substr(sql_text,1,40) "SQL"

          FROM v$sqlarea

         WHERE version_count > 10

        ;

"Versions" of a statement occur where the SQL is character for character identical but the underlying objects or binds etc.. are different as described in "Sharable SQL" above. High version counts can occur in various Oracle8i releases due to problems with progression monitoring. This can be disabled by setting _SQLEXEC_PROGRESSION_COST to '0' as described earlier in this note.

• Finding statement/s which use lots of shared pool memory:

        SELECT substr(sql_text,1,40) "Stmt", count(*),

                sum(sharable_mem)    "Mem",

                sum(users_opening)   "Open",

                sum(executions)      "Exec"

          FROM v$sql

         GROUP BY substr(sql_text,1,40)

        HAVING sum(sharable_mem) > <MEMSIZE>

        ;

where MEMSIZE is about 10% of the shared pool size in bytes. This should show if there are similar literal statements, or multiple versions of a statements which account for a large portion of the memory in the shared pool.

• Allocations causing shared pool memory to be 'aged' out

        SELECT * 

          FROM x$ksmlru

        WHERE ksmlrnum>0

        ;

SELECT t.owner,
       t.table_name primary_table,
       c.constraint_name,
       c.table_name second_table,
       acc.column_name
FROM   all_constraints t,
       all_constraints c,
       all_cons_columns acc
WHERE  c.r_constraint_name = t.constraint_name
AND    c.table_name        = acc.table_name
AND    c.constraint_name   = acc.constraint_name
AND    t.owner not in ('SYS', 'SYSTEM','MDSYS','SYSMAN','DBSNMP','OLAPSYS','EXFSYS')
AND    NOT EXISTS (SELECT '1'
                   FROM  all_ind_columns aid
                   WHERE aid.table_name  = acc.table_name
                   AND   aid.column_name = acc.column_name)
ORDER BY c.table_name;

SELECT 'ALTER INDEX '||owner||'.'||index_name|| ' REBUILD TABLESPACE <index_tbs_name>; ' from dba_indexes where owner in ('owner_name') and tablespace_name like '%DAT%';

select * from (
  select opname, target, sofar, totalwork,
         units, elapsed_seconds, message
  from v$session_longops order by start_time desc)
where rownum <=1;

On a busy server one would like to add filter on sid and serial# in the inner query to get the info only about the necessary session and the query is as follows:

select * from (select opname, target, sofar, totalwork,units, elapsed_seconds, message from v$session_longops
where sid = <sid> and serial# = <serial#> order by start_time desc)where rownum <=1;

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE
  '/u01/MJBDB/oradata/undotbs03.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30720M
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

Select 'Control Files: '||value from v$parameter where name= 'control_files';
Select 'Control Files: '||value from v$parameter where name= 'spfile';
select 'Logfile: '||member from v$logfile;
select 'Datafile: '||name from v$datafile;

Check how many redo log files exist.

select member from v$logfile;

 Check whether each group has more than 1 members.

select count(*) from V$LOG where members >1;

show parameter control_files;

-- OR

Select value from v$parameter where name='control_files';

SELECT name
   FROM   stmt_audit_option_map
   WHERE  name not in (select audit_option from dba_stmt_audit_opts)
   AND    name not like 'SELECT%'
   AND    name not like 'INSERT%'
   AND    name not like 'UPDATE%'
   AND    name not like 'DELETE%'
   AND    name not like 'EXECUTE%'
   AND    name not like 'LOCK%'
   AND    name not in
               ('CREATE DIRECTORY','DROP DIRECTORY','GRANT LIBRARY',
                'WRITEDOWN DBLOW','READUP DBHIGH','WRITEUP DBHIGH',
                'WRITEDOWN','READUP','WRITEUP','EXISTS','SUMMARY',
                'NETWORK','DEBUG CONNECT USER','DEBUG CONNECT ANY',
                'CREATE CLUSTER','CREATE DATABASE LINK','CREATE DIMENSION',
                'CREATE LIBRARY','CREATE PROCEDURE','CREATE ROLE',
                'CREATE SEQUENCE','CREATE TABLE','CREATE TRIGGER',
                'CREATE TYPE','CREATE VIEW')
   AND    decode(name, 'TABLESPACE', 3,
                       'PUBLIC DATABASE LINK', 2,
                       'PUBLIC SYNONYM', 2,
                       'ROLLBACK SEGMENT', 3,
                       'TABLESPACE', 3,
                       'USER', 3, 100) >
                   (SELECT COUNT(*)
                    FROM   dba_stmt_audit_opts
                    WHERE  audit_option LIKE '%'||name||'%')
  UNION ALL
    select name from system_privilege_map
                where name not in
                  (select privilege from dba_priv_audit_opts)
               and name not in (select name from stmt_audit_option_map)
                and name not in ('EXTENDS TYPE','ALTER OPERATOR')
                and name not like '%ROLE%'
                and name not like '%SECURITY%'
                and name not like '%EXECUTE%'
                and name not like '%RULE%';

select owner from dba_tables where table_name='AUD$';
select count(*) from ALL_DEF_AUDIT_OPTS where ren='A/A';
select grantee from dba_tab_privs
       where table_name='AUD$'
        and grantee not in ('DELETE_CATALOG_ROLE')
        and grantee not in
         (select grantee from dba_role_privs where granted_role='DBA')
        order by grantee;