General Oracle DB Check and Recommendations

 

Database Configuration Analysis

1. Optimize Redo Logs

Command:

SELECT * FROM v$log;

Recommendation & Improvement:
Redo logs should be properly sized and optimized to minimize log switches and ensure good performance. This improves database transaction management and recovery performance. Too many log switches or small log file sizes can negatively impact system performance.

---

2. Table Partitioning for Big Tables

Recommendation & Improvement:
Partitioning large tables can improve query performance, maintenance, and availability. Tables that grow too large can cause slower queries, especially full table scans. By partitioning, queries can target specific partitions, thus reducing I/O and improving performance.

Database Performance Analysis.

3. Check Stale Statistics for Tables

Command:

SELECT owner, table_name, last_analyzed 
FROM dba_tables 
WHERE last_analyzed IS NULL OR last_analyzed < SYSDATE - 30;

Recommendation & Improvement:
Stale statistics can cause poor query execution plans, leading to suboptimal performance. Regularly updating statistics helps the optimizer make the best decisions, improving query performance.

---

4. Check Fragmented Tables/Indexes

Command:

SELECT table_name, chain_cnt 
FROM dba_tables 
WHERE chain_cnt > 0;

Recommendation & Improvement:
Fragmentation can cause performance issues by increasing the time it takes to scan or retrieve data. Rebuilding fragmented tables and indexes improves I/O efficiency.

---

5. Check Statistics for SYS Tables

Recommendation & Improvement:
Statistics for system tables (SYS schema) are important for the optimizer to choose the best execution plan. Outdated statistics can lead to inefficient query plans, which can degrade performance.

---

6. Check User Objects in SYSTEM Tablespace

Command:

SELECT owner, segment_name, tablespace_name 
FROM dba_segments 
WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYS', 'SYSTEM');

Recommendation & Improvement:
Placing user objects in the SYSTEM tablespace can cause fragmentation and performance issues. User data should be stored in dedicated tablespaces for better performance and easier management.

---

7. Check Un-indexed Foreign Keys

Command:

SELECT * FROM (
  SELECT ct.table_name, co.column_name, co.position column_position
  FROM user_constraints ct, user_cons_columns co
  WHERE ct.constraint_name = co.constraint_name
  AND ct.constraint_type = 'R'
  MINUS
  SELECT ui.table_name, uic.column_name, uic.column_position
  FROM user_indexes ui, user_ind_columns uic
  WHERE ui.index_name = uic.index_name
);

Recommendation & Improvement:
Foreign keys should be indexed for performance reasons. Missing indexes can result in slower queries, especially on large tables, since the database has to perform full table scans instead of using the index.

Best Practices

8. Use Switchover Verify During Switch Over

Recommendation & Improvement:
Always verify switchover operations in Data Guard environments before switching production to the standby. This ensures that all configurations are correct and prevents downtime due to configuration errors.

---

9. Check Temp File Mismatch on Nodes

Recommendation & Improvement:
In a RAC environment, temporary files should be consistent across nodes. Mismatched temporary files can cause performance issues or errors when the database tries to perform certain operations like sorting.

---

10. OS Watcher Need to be Installed on Each Node

Recommendation & Improvement:
OS Watcher helps monitor OS-level metrics, providing early warnings of resource bottlenecks that can affect Oracle performance. It allows proactive resolution of potential hardware or system-related issues.

---

11. Backup Table Deletion from Database

Recommendation & Improvement:
Tables used for backup purposes should be deleted after use to avoid unnecessary space consumption and to improve performance.

---

12. Block Corruption Check

Command:

RMAN> validate database;

Recommendation & Improvement:
Corruption can go unnoticed and degrade database integrity. Running block corruption checks regularly can help prevent or resolve data corruption issues before they affect production workloads.

---

13. Purge Recyclebin Regularly

Command:

PURGE DBA_RECYCLEBIN;

Recommendation & Improvement:
The recyclebin can consume valuable space. Regular purging ensures that space is reclaimed, avoiding unnecessary storage use.

---

14. Check Hard Parsing for SQL

Recommendation & Improvement:
Frequent hard parsing indicates inefficient use of SQL. Using bind variables and cached execution plans can reduce hard parsing, improving overall performance.

Other Recommendation

15. Check Compatibility Parameter

Command:

SHOW PARAMETER compatibility;

Recommendation & Improvement:
Ensure the compatibility parameter is set according to the Oracle version to avoid compatibility issues and to utilize the latest features and optimizations.

---

16. Check Sequence Cache

Recommendation & Improvement:
The sequence cache helps improve performance by reducing the need for database round trips. If cache values are too low, it can result in performance degradation. Adjust the cache size for better performance.

---

17. DOP Removal from Object

Recommendation & Improvement:
Setting a fixed degree of parallelism (DOP) at the object level can lead to suboptimal resource usage. Removing DOP settings and letting the system decide helps balance workload distribution.

---

18. Check Invalid Objects

Command:

SELECT object_name, object_type 
FROM dba_objects 
WHERE status = 'INVALID';

Recommendation & Improvement:
Invalid objects can cause runtime errors and application failures. Regularly checking for and recompiling invalid objects ensures system stability.

---

19. Check AWR Retention

Command:

SELECT retention FROM dba_hist_wr_control;

Recommendation & Improvement:
The AWR retention period should be adjusted to meet business requirements. Too short a retention can result in loss of important historical data, while too long may cause unnecessary overhead.

---

20. Check Datafiles Count in Each Tablespace

Recommendation & Improvement:
Minimizing the number of data files per tablespace simplifies management. Avoid excessive datafiles that make backups and space management more complex.

---

21. Tablespace Usages

Recommendation & Improvement:
Monitor tablespace usage to ensure no tablespace runs out of space. This is critical to prevent database downtime due to lack of storage.

---

22. Undo TS Mismatch on Each Node

Recommendation & Improvement:
In a RAC environment, ensure all nodes have the same undo tablespace configuration. Mismatches can lead to errors or performance degradation.

---

23. Purging for Large Tables

Recommendation & Improvement:
Large tables should be purged in batches or during off-peak hours to avoid full table scans and to keep the database performance optimal.

---

24. ASM Space Usages

Recommendation & Improvement:
Monitor ASM space usage to avoid running out of space, which could lead to downtime or poor performance.

---

25. Unusable Indexes

Command:

SELECT index_name, table_name 
FROM dba_indexes 
WHERE status = 'UNUSABLE';

Recommendation & Improvement:
Unusable indexes should be rebuilt or dropped. They consume resources without providing benefits, leading to performance inefficiencies.

---

26. Invisible Indexes

Recommendation & Improvement:
Invisible indexes can be used for testing purposes but should not be used in production unless necessary. Regularly review and make them visible if required.

---

27. Check Default Degree for Tables

Recommendation & Improvement:
Ensure that tables do not have a default degree of parallelism unless necessary. The default DOP should be set based on system and workload requirements to optimize resource usage.

---

28. Check ORA-Errors in Alert Log

Recommendation & Improvement:
Regularly review the alert log for ORA-errors, which can indicate underlying issues that need to be resolved to avoid performance degradation or downtime.

---

30. Check Auto Extensible for Datafiles

Recommendation & Improvement:
Auto-extension should be enabled for datafiles to prevent them from running out of space. However, it should be configured with limits to avoid uncontrolled growth.

---

31. Check Redo Transport Destination

Recommendation & Improvement:
Ensure that redo transport destinations are correctly configured in Data Guard environments to avoid lag or data loss.

---

32. Set Hidden _use_single_log_writer to True If Not Set

Recommendation & Improvement:
Setting _use_single_log_writer to true can improve performance in some RAC configurations by consolidating log writer processes, reducing overhead.

---

33. Check archive_lag_target Parameter

Recommendation & Improvement:
This parameter defines the maximum acceptable lag for redo transport in Data Guard environments. Proper configuration ensures data consistency and prevents delays in log shipping.

---

By addressing each of these points, you can optimize Oracle database performance, reliability, and manageability.