How to find the dependencies for PL/SQL objects

column name format a20
column referenced_owner format a15 heading R_OWNER
column referenced_name format a15 heading R_NAME
column referenced_type format a12 heading R_TYPE
select name, type, referenced_owner, referenced_name,referenced_type
from user_dependencies
order by type, name;

Finding Invalid Objects (procedure, package, trigger...)

Developers often change a small section of PL/SQL code that fails to compile upon execution, forcing an application failure. A simple query, reviewed daily, will help you spot these failures before the end user does:
col "Owner" format a12
col "Object" format a20
col "OType" format a12
col "Change DTE" format a20
select substr(owner,1,12) "Owner", substr(object_name,1,20) "Object", object_type "OType", to_char(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') "Change Date"
from dba_objects
where status <> 'VALID'
order by 1, 2;


The preceding example will display any objects that are INVALID, meaning they were never
compiled successfully or changes in dependent objects have caused them to become INVALID.
If we had a procedure PROCESS_DATE, for example, found to be INVALID, we could manually
recompile this procedure with the following command:
alter procedure PROCESS_DATE compile;
Once this command is executed and the PROCESS_DATE passes the recompile, the procedure
would be changed by Oracle automatically from INVALID to VALID. Another manual method
that exists is to call the DBMS_UTILITY.COMPILE_SCHEMA package procedure as outlined next
to recompile all stored procedures, functions, and packages for a given schema:
begin
dbms_utility.compile_schema('USERA');
end;
/



To find the state of all PL/SQL objects for your schema, execute the following:
column object_name format a20
column last_ddl_time heading 'last ddl time'
select object_type, object_name, status, created, last_ddl_time
from user_objects
where object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER');


Note:  the above was copied from "Oracle Database 10g Performance Tuning Tips and Techniques" book.

Query the V$DB_OBJECT_CACHE table to find objects that are not pinned and are also large enough to potentially cause problems

You can query the V$DB_OBJECT_CACHE view to determine PL/SQL that is both large and currently not marked “kept.” These are objects that may cause problems (due to their size and need for a large amount of contiguous memory) if they need to be reloaded at a later time. This will only show the current statements in the cache.
The example that follows searches for those objects requiring greater than 100K.
select name, sharable_mem
from v$db_object_cache
where sharable_mem > 100000
and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION',
'PROCEDURE')
and kept = 'NO';

Note: the above is copied from "Oracle Database 10g Performance Tuning Tips and Techniques" book.

Monitor Index Usage

1 Enable index monitoring
ALTER INDEX my_index_i MONITORING USAGE;


2 Disable index monitoring
ALTER INDEX my_index_i NOMONITORING USAGE;


3 Information about the index usage can be displayed using the V$OBJECT_USAGE view

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  index_name = 'MY_INDEX_I'
ORDER BY index_name;

4 Script to generate index monitoring command

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql

SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" MONITORING USAGE;'
FROM   dba_indexes i
WHERE  owner      = UPPER('&1')
AND    table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

SPOOL OFF

SET PAGESIZE 18
SET FEEDBACK ON

5 Script to generate disabling index monitoring command

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql


SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" NOMONITORING USAGE;'
FROM   dba_indexes i
WHERE  owner      = UPPER('&1')
AND    table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));


SPOOL OFF


SET PAGESIZE 18
SET FEEDBACK ON