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.
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.
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
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.
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.
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.
SELECT * FROM x$ksmlru WHERE ksmlrnum>0 ;
FROM all_constraints t,
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
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;
Select value from v$parameter where name='control_files';
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',
'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,
'USER', 3, 100) >
WHERE audit_option LIKE '%'||name||'%')
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
and grantee not in ('DELETE_CATALOG_ROLE')
and grantee not in
(select grantee from dba_role_privs where granted_role='DBA')
order by grantee;
1-10 of 62