Oracle Performance Tuning Database Administrator

• Analyze current Oracle 19c database workloads with emphasis on I/O-intensive SQL queries and large tables.
• Review execution plans, table/index statistics, and storage usage.
• Identify performance bottlenecks due to full table scans, large table joins, and high-latency disk operations.

Large Table Review and Partitioning Strategy

• Identify large tables that would benefit from partitioning based on size, access patterns, and business logic.
• Collaborate with the application and development teams to design optimal partitioning schemes (e.g., range, list, interval, composite).
• Implement table partitioning with minimal application impact.
• Update or assist in refactoring queries to leverage partition pruning and parallelism.

Implementation of Oracle Advanced Compression

• Assess current compression usage and storage overhead on target tables and indexes.
• Identify candidate tables and LOBs for Advanced Row, OLTP Table, and Hybrid Columnar Compression (if applicable).
• Implement Oracle Advanced Compression for tables and indexes involved in high I/O activity.
• Validate data integrity, query performance, and space savings.
• Configure compression-aware statistics collection and tuning processes.

Performance Testing and Validation

• Conduct baseline and post-implementation performance testing using representative workloads.
• Use tools like SQL Monitor, AWR, ASH, and OEM to measure improvements in I/O latency, buffer cache usage, and CPU cost.
• Provide performance comparison reports with before/after metrics and analysis.

Application Team Collaboration

• Work directly with application developers and analysts to ensure compatibility of partitioning and compression strategies with existing code and functionality.
• Assist in SQL tuning and refactoring efforts post-implementation.

Documentation and Knowledge Transfer

• Deliver detailed documentation on partitioning and compression configurations.
• Provide guidelines for future partition maintenance, statistics collection, and compression-aware indexing.