Effortless Optimization: All about Oracle's Auto Maintenance Tasks

  Auto maintenance tasks are those little helpers that keep your Oracle database running smoothly without any manual intervention. These tasks kick in during maintenance windows, ensuring your database stays healthy and optimized. Here’s a closer look at these automated maintenance superheroes..

Behind-the-Scene Heroes:

Optimizer Statistics Advisor: This advisor reviews how statistics are gathered and suggests tweaks to fine-tune the process. It's like having a personal coach who helps you refine your workout routine for better results.

SQL Plan Management Evolve Advisor: This advisor simplifies plan evolution, eliminating the need for manual intervention. It's like an autopilot for evolving SQL plans, ensuring they stay efficient without you lifting a finger.

Automatic Segment Advisor: This advisor spots fragmented segments and suggests defragmentation. Think of it as someone tidying up your database to keep it organized and efficient.

Automatic Optimizer Statistics Collection: This task collects statistics for schema objects that either lack stats or have outdated stats. Think of it as a librarian updating the catalog to ensure you can always find the right book.

Automatic SQL Tuning Advisor: It analyzes high-load SQL statements and provides tuning advice. Picture a wise mentor who helps you streamline your code for peak performance.

How It All Works:

• When the maintenance window opens, the Oracle database creates a scheduler job for each of these tasks, scheduled to run during the window. 
• Each job gets a runtime name starting with "ORA$AT". Once an automated maintenance job finishes, it’s deleted from the Oracle scheduler job system, but its information remains in the Scheduler Job history for your reference.
• If the maintenance window is long enough, all tasks (except the SQL Tuning Advisor) restart every four hours. 
• By default, maintenance windows run from 10 PM to 2 AM, Monday to Friday. On weekends (Saturday and Sunday), they extend from 6 AM to 2 AM the next day.

Resource Management:

By default, all predefined maintenance windows use the DEFAULT_MAINTENANCE_PLAN resource plan. All auto maintenance tasks operate under its sub-plan ORA$AUTOTASK, which ensures that the total resources are equally divided among the maintenance tasks.

So, next time your database seems to be running seamlessly, remember to thank these unsung heroes working tirelessly behind the scenes.

Stay Informed, Stay Inspired !

Subscribe to my newsletter & never miss an update.