"ORACLE" SPM

The primary role of SPM (SQL Plan Management) is to improve and stabilize system performance by managing SQL execution plans.

Application: Database upgrade, uninterrupted system data change, new module development of business system

System structure

The management process for SQL plan baseline:

The first step: Capture execution Plan (main role: Detection of execution plan and record changes and recorded in baseline, there are two ways to capture: 1, automatic capture 2, manual load)

Manual load execution plan->from cursor cache

DECLARE My_plans Pls_integer; BEGIN My_plans: = dbms_spm. Load_plans_from_cursor_cache (sql_id = ' 99twu5t2dn5xd '); END; /

Manual load Execution Plan->from tunning set

DECLARE My_plans Pls_integer; BEGIN My_plans: = dbms_spm. Load_plans_from_sqlset (sqlset_name = ' tset1 '); END; /

Step Two: Choose a performance-good execution plan

Step Three: Evolution (two ways: 1, through packet Evolution dbms_spm.evolve_sql_plan_baseline 2, manual load)

Set Serveroutput on set LONG 10000 DECLARE report CLOB; BEGIN Report: = DBMS_SPM. Evolve_sql_plan_baseline (sql_handle = ' sys_sql_593bc74fca8e6738 '); Dbms_output. Put_Line (report); END; /

SQL Plan baseline can be queried by querying the SELECT statement

SELECT sql_handle, Plan_name, ENABLED, ACCEPTED, FIXED from Dba_sql_plan_baselines;

The fixed execution plan in baseline has a higher priority than the no fixed execution plan, and you can set the fixed property by changing the baseline property.

"ORACLE" SPM