Oracle Database AI-Driven and Automatic Plan Optimization
Oracle Database has consistently had one of the strongest query optimization engines. With each new version, they’ve added more and more automatic features to ensure that queries sent to the database choose the fastest possible method for retrieving or updating data. The latest 23ai version introduces even more features that make the database appear to take over the job of a SQL tuning expert powered by AI.
One of the most frustrating incidents in normal applications is when one or more SQL statements suddenly perform poorly. This leads to two immediate issues: the application stops functioning correctly, waiting for the poor SQL to finish, and the poor SQL begins using more system resources, dragging down the performance of other SQL statements. The typical response is to escalate the issue to a database administrator or system engineer, who must determine two things: what needs to be changed to restore the poor SQL to normal performance and why it changed in the first place to prevent it from happening again. None of this is particularly easy.
Automatic SQL Tuning Sets and Real-Time Plan Management
AI comes to the rescue by eliminating manual steps. 23ai provides exactly that in the form of two new internal features: Automatic SQL Tuning Sets (ASTS) and Real-Time SQL Plan Management (Real-Time SPM). These features enable the Optimizer to identify SQL statements where the execution plan that was previously running efficiently (fast) changes to one that is now running poorly (slow). When such a change is detected, the Optimizer automatically switches to the previously faster execution plan and verifies that it indeed performs faster.
Let’s delve deeper into how all this works. First, we have Automatic SQL Tuning Sets (ASTS). With 23ai, the database now records a history of repeatable SQL statements executed in the system. Alongside each SQL statement, the execution plan is stored, which is crucial when things change, and we need to know the execution plan that previously ran well. The ASTS is stored in the SYSAUX tablespace at the PDB level, like existing SQL Tuning Sets (STS). However, the STS called “SYS_AUTO_STS” is automatically managed by Oracle and used in the real-time optimization of SQL statements. You can view the statements and execution plans from this set just like any other tuning set by querying the DBA_SQLSET and DBA_SQLSET_STATEMENTS views. Additionally, you can view the historical execution plans using DBMS_XPLAIN tools.
Now that there’s a history of statements, Real-Time SPM can come into play. This is somewhat an extension of the existing SQL Plan Management (SPM), which creates SQL baselines or saved execution plans. These plans are used by the Optimizer to prioritize a specific way to execute a SQL statement. Traditionally, a DBA or system engineer would have to identify that an SQL statement was regressing in performance, determine what a better execution plan would look like, and create a baseline to instruct the Optimizer to run the statement in a more performant manner. Often, finding and verifying the optimal execution plan is extremely challenging.
How Real-Time SPM Works in 23ai
The unique real-time feature introduced in 23ai moves the previously manual labor to the Optimizer, which detects after an SQL statement has finished that its execution was substandard. On the next execution of the statement, the Optimizer searches the ASTS for a better execution plan and then runs the statement with that previously known execution plan.
If the performance improves during the next execution, the Optimizer will create a new SQL baseline to maintain consistent performance in the future. The system-generated baselines are stored in the DBA_SQL_PLAN_BASEILNE view, just like previously. The baselines generated by the Real-Time SPM feature will be marked with an origin of “FOREGROUND-CAPTURE,” indicating that the Optimizer created them while executing a statement. However, to help prevent future performance regresses again, the Optimizer will review the existing baselines over time to determine if they need to be disabled or a new baseline created.
For more information on this feature and other 23ai offerings, follow Viscosity’s series of blog posts. Additionally, look for myself and other Viscosity speakers at your local user groups, where we will present on this and other topics.
23ai introduces several remarkable new features, including AI for data, AI for development, and AI for database administration. Oracle database stands out as years ahead of its competitors.
Happy Holidays! 🎄
SUBMIT YOUR COMMENT