SQL execution plans come in the form of SQL plan baselines for comparing and testing for regressions in SQL statements. SQL plans are captured, and with 23c, plan changes are detected at parse time and validated if the plan has changed. If you think machine learning is taking away the DBA’s performance tuning job, you are wrong; it is actually making it better.
There is reduced risk in plans changing, and with all of the statements running against the database, it great to leverage the real-time SQL plan management in 23c. Errors can be intercepted and changed with alternative solutions that will return the expected results without issues in the application.
The parameters OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES and OPTIMIZER_USE_ SQL_PLAN_BASELINES control the SQL plan capture and baselines.
Execution plans can be captured automatically on a running system, but you can also import a plan from a staging table or a tuning set. It helps with application changes, upgrades, and migrations to be able to use consistent plans.
Here is an example to load from a SQL tuning set:
SQL> declare v_result pls_integer; beginv_result := dbms_spm.load_plans_from_sqlset(sqlset_name => ‘MM_STS’, basic filter => ‘sql text like ”select%orders%”’);end; /
If there are no SQL plan regressions when a plan changes, the plan can evolve and improve the plan. This can be done automatically or after review by the DBA.
The capture is done by turning on the parameter, and when the capture is completed, it can be set to false:
SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;
After capturing information and researching the resource-intensive SQL statements, you can use the SQL Plan Management Evolve Advisor to see different plans.
Note oracle 23c has automatic indexing, partitioning, and materialized views to improve statements and allow for the sQl plan to evolve and improve performance.
The following will test alternative plans of SQL you want to repair:
SQL> exec dbms_spm.set_evolve_task_parameter( task_name => ‘mmsqltask’, parameter => ‘ALTERNATE_PLAN_SOURCE’, value => ‘CURSOR_CACHE+AUTOMATIC_ WORKOAD_REPOSITORY+SQL_TUNING_SET’);
SQL Plan Management, along with real-time statistics, allows DBAs to effectively tune repeatable SQL statements in the database.
Leave a Reply