(Continued from previous part...)

Oracle DBA Checklist

IV. Monthly Procedures

A. Look for Harmful Growth Rates
1. Review changes in segment growth when compared to previous reports to identify segments with a harmful growth rate.
B. Review Tuning Opportunities
1. Review common Oracle tuning points such as cache hit ratio, latch contention, and other points dealing with memory management. Compare with past reports to identify harmful trends or determine impact of recent tuning adjustments.

C. Look for I/O Contention
1. Review database file activity. Compare to past output to identify trends that could lead to possible contention.

D. Review Fragmentation
1. Investigate fragmentation (e.g. row chaining, etc.).

E. Project Performance into the Future
1. Compare reports on CPU, memory, network, and disk utilization from both Oracle and the operating system to identify trends that could lead to contention for any one of these resources in the near future.
2. Compare performance trends to Service Level Agreement to see when the system will go out of bounds

F. Perform Tuning and Maintenance
1. Make the adjustments necessary to avoid contention for system resources. This may include scheduled down time or request for additional resources.

V. Appendix

A. Daily Procedures
1. Free.sql
--
-- free.sql
--
-- To verify free space in tablespaces
-- Minimum amount of free space
-- document your thresholds:
-- < tablespace_name> = <amount> m
--

SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) / (1024*1024) ) as free_m , max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space
GROUP BY tablespace_name

2. Space.sql
--
-- space.sql
--
-- To check free, pct_free, and allocated space within a tablespace
--

SELECT tablespace_name, largest_free_chunk
, nr_free_chunks, sum_alloc_blocks, sum_free_blocks
, to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%'
AS pct_free
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
FROM dba_data_files
GROUP BY tablespace_name
)
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS largest_free_chunk
, count(blocks) AS nr_free_chunks
, sum(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name

3. analyze5pct.sql
--
-- analyze5pct.sql
--
-- To analyze tables and indexes quickly, using a 5% sample size
-- (do not use this script if you are performing the overnight
-- collection of volumetric data)
--

BEGIN
dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ;
END ;
/

4. nr_extents.sql
--
-- nr_extents.sql
--
-- To find out any object reaching <threshold>
-- extents, and manually upgrade it to allow unlimited
-- max_extents (thus only objects we *expect* to be big
-- are allowed to become big)
--

8 SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents , s.max_extents , to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90') as MB
FROM dba_extents e , dba_segments s
WHERE e.segment_name = s.segment_name
GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents
HAVING count(*) > &THRESHOLD
OR ( ( s.max_extents - count(*) ) < &&THRESHOLD )
ORDER BY count(*) desc

5. spacebound.sql
--
-- spacebound.sql
--
-- To identify space-bound objects. If all is well, no rows are returned.
-- If any space-bound objects are found, look at value of NEXT extent
-- size to figure out what happened.
-- Then use coalesce (alter tablespace coalesce;).
-- Lastly, add another datafile to the tablespace if needed.
--

SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
( SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name
AND a.next_extent > f.big_chunk

(Continued on next part...)