Creating SQL Baselines in Oracle 19c

Learn how to effectively create SQL baselines in Oracle Database 19c to stabilize performance and optimize query execution plans. This step-by-step guide covers the process of creating SQL baselines, providing insights into Oracle's performance tuning capabilities.
---
Disclaimer/Disclosure: Some of the content was synthetically produced using various Generative AI (artificial intelligence) tools; so, there may be inaccuracies or misleading information present in the video. Please consider this before relying on the content to make any decisions or take any actions etc. If you still have any concerns, please feel free to write them in a comment. Thank you.
---
In Oracle Database 19c, SQL baselines serve as a vital tool for stabilizing performance and optimizing query execution plans. By capturing and preserving efficient execution plans for SQL statements, baselines help maintain consistent performance even after database upgrades or changes in optimizer statistics. Let's delve into the step-by-step process of creating SQL baselines in Oracle 19c:

Identify SQL Statements:
Before creating SQL baselines, identify the SQL statements that require performance stabilization or optimization. These could be frequently executed queries or those experiencing performance regressions.

Enable SQL Plan Management (SPM):
Ensure that SQL Plan Management (SPM) is enabled at the database level. SPM enables the capture and evolution of SQL execution plans over time, facilitating the creation and management of SQL baselines.

[[See Video to Reveal this Text or Code Snippet]]

Capture SQL Statements:
Execute the SQL statements you've identified to capture their execution plans. Oracle automatically captures the plans and stores them in the SQL Management Base (SMB) as SQL plan baselines.

Verify Captured Plans:
Confirm that the SQL plan baselines have been captured for the identified SQL statements by querying the DBA_SQL_PLAN_BASELINES view.

[[See Video to Reveal this Text or Code Snippet]]

Evolve Baselines (Optional):
If necessary, you can manually evolve SQL plan baselines to adapt to changing execution environments or optimizer statistics. This involves accepting new execution plans or manually creating alternative plans.

[[See Video to Reveal this Text or Code Snippet]]

Monitor Baseline Performance:
Regularly monitor the performance of SQL statements with SQL plan baselines in place. Use Oracle's performance monitoring tools to ensure that the baseline plans continue to deliver optimal performance.

Disable Automatic Plan Capture (Optional):
If desired, you can disable automatic plan capture to prevent new SQL plan baselines from being created automatically. This may be useful in environments where manual intervention is preferred for SQL tuning.

[[See Video to Reveal this Text or Code Snippet]]

By following these steps, you can effectively create SQL baselines in Oracle Database 19c to stabilize performance and optimize query execution plans, ensuring consistent and efficient operation even in dynamic database environments.