Adaptive SQL Plan Management (SPM)

DB架构

于 2023-02-13 16:24:22 发布

阅读量428

点赞数

分类专栏： DBA 文章标签： sql 数据库 java

本文链接：https://blog.csdn.net/u011868279/article/details/129010164

版权

DBA 专栏收录该内容

183 篇文章 13 订阅

订阅专栏

文章介绍了Oracle12c中的SQLPlanManagement(SPM)功能，特别是SYS_AUTO_SPM_EVOLVE_TASK顾问任务，用于自动进化SQL计划基线。内容包括如何设置和调整参数以控制基线进化，以及手动和自动执行SQL计划基线的步骤，强调了ACCEPT_PLANS参数的作用。此外，还展示了如何使用DBMS_SPM包的函数来创建、执行、报告和实施SQL计划基线的进化任务。

摘要由CSDN通过智能技术生成

SQL Plan Management (SPM) provide a "conservative plan selection strategy" for the optimizer.

SYS_AUTO_SPM_EVOLVE_TASK

In Oracle database 12c the evolution of existing baselines is automated as an advisor task called SYS_AUTO_SPM_EVOLVE_TASK, triggered by the existing "sql tuning advisor" client under the automated database maintenance tasks.

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 13 15:36:25 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> show user;
USER is "SYS"
SQL> 
SQL> conn sys@PDB1 AS SYSDBA
Enter password: 
Connected.
SQL> show user;
USER is "SYS"
SQL> COLUMN client_name FORMAT A35
SQL> COLUMN task_name FORMAT a30
SQL> SELECT client_name, task_name
  2  FROM   dba_autotask_task;

CLIENT_NAME                         TASK_NAME
----------------------------------- ------------------------------
auto optimizer stats collection     gather_stats_prog
sql tuning advisor                  AUTO_SQL_TUNING_PROG
auto space advisor                  auto_space_advisor_prog

SQL>

You shouldn't alter the "sql tuning advisor" client directly to control baseline evolution. Instead, amend the parameters of the SYS_AUTO_SPM_EVOLVE_TASK advisor task.

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 13 15:36:25 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> show user;
USER is "SYS"
SQL> 
SQL> conn sys@PDB1 AS SYSDBA
Enter password: 
Connected.
SQL> show user;
USER is "SYS"
SQL> COLUMN client_name FORMAT A35
SQL> COLUMN task_name FORMAT a30
SQL> SELECT client_name, task_name
  2  FROM   dba_autotask_task;

CLIENT_NAME                         TASK_NAME
----------------------------------- ------------------------------
auto optimizer stats collection     gather_stats_prog
sql tuning advisor                  AUTO_SQL_TUNING_PROG
auto space advisor                  auto_space_advisor_prog

SQL> 
SQL> 
SQL> 
SQL> show user;
USER is "SYS"
SQL> COLUMN parameter_name FORMAT A25
SQL> COLUMN parameter_value FORMAT a15
SQL> 
SQL> SELECT parameter_name, parameter_value
  2  FROM   dba_advisor_parameters
  3  WHERE  task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
  4  AND    parameter_value != 'UNUSED'
  5  ORDER BY parameter_name;

PARAMETER_NAME            PARAMETER_VALUE
------------------------- ---------------
ACCEPT_PLANS              TRUE
ALTERNATE_PLAN_BASELINE   AUTO
ALTERNATE_PLAN_LIMIT      UNLIMITED
ALTERNATE_PLAN_SOURCE     AUTO
DAYS_TO_EXPIRE            UNLIMITED
DEFAULT_EXECUTION_TYPE    SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE  30
JOURNALING                INFORMATION
MODE                      COMPREHENSIVE
TARGET_OBJECTS            1
TIME_LIMIT                3600

11 rows selected.

SQL>

If you don't wish existing baselines to be evolved automatically, set the ACCEPT_PLANS parameter to FALSE.

SQL> 
SQL> 
SQL> BEGIN
  2    DBMS_SPM.set_evolve_task_parameter(
  3      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
  4      parameter => 'ACCEPT_PLANS',
  5      value     => 'FALSE');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

Typically, the ACCEPT_PLANS and TIME_LIMIT parameters will be the only ones you will interact with. The rest of this article assumes you have the default settings for these parameters. If you have modified them, switch them back to the default values using the following code.

SQL> 
SQL> BEGIN
  2    DBMS_SPM.set_evolve_task_parameter(
  3      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
  4      parameter => 'ACCEPT_PLANS',
  5      value     => 'TRUE');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_SPM.set_evolve_task_parameter(
  3      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
  4      parameter => 'TIME_LIMIT',
  5      value     => 3600);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

The DBMS_SPM package has a function called REPORT_AUTO_EVOLVE_TASK to display information about the the actions taken by the automatic evolve task. With no parameters specified it produces a text report for the latest run of the task.

SQL> 
SQL> SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SQL> SELECT DBMS_SPM.report_auto_evolve_task
  2  FROM   dual;

REPORT_AUTO_EVOLVE_TASK
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-----------------------------------------------------------------------
----------------------

 Task Information:
 ---------------------------------------------
 Task Name            : SYS_AUTO_SPM_EVOLVE_TASK
 Task Owner           : SYS
 Description          : Automatic SPM Evolve Task
 Execution Name       : EXEC_391
 Execution Type       : SPM EVOLVE
 Scope                : COMPREHENSIVE
 Status               : COMPLETED
 Started              : 02/13/2023 14:01:13
 Finished             : 02/13/2023 14:01:14
 Last Updated         : 02/13/2023 14:01:14
 Global Time Limit    : 3600
 Per-Plan Time Limit  : UNUSED
 Number of Errors     : 0
---------------------------------------------------------------------------
------------------

SUMMARY SECTION
---------------------------------------------------------------------------------------------
  Number of plans processed  : 0
  Number of findings         : 0
  Number of recommendations  : 0
  Number of errors           : 0
---------------------------------------------------------------------------------------------



SQL>

Manually Evolving SQL Plan Baselines

In previous releases, evolving SQL plan baselines was done using the EVOLVE_SQL_PLAN_BASELINE function. In 12c this has been replaced by a task-based approach, which typically involves the following steps.

CREATE_EVOLVE_TASK
EXECUTE_EVOLVE_TASK
REPORT_EVOLVE_TASK
IMPLEMENT_EVOLVE_TASK

In addition, the following routines can interact with an evolve task.

CANCEL_EVOLVE_TASK
RESUME_EVOLVE_TASK
RESET_EVOLVE_TASK

Create and populate a test table

SQL> 
SQL> 
SQL> show user;
USER is "SYS"
SQL> 
SQL> 
SQL> conn maxwellpan/maxwellpan@PDB1;
Connected.
SQL> DROP TABLE spm_test_tab PURGE;
DROP TABLE spm_test_tab PURGE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE spm_test_tab (
  2    id           NUMBER,
  3    description  VARCHAR2(50)
  4  );

Table created.

SQL> INSERT /*+ APPEND */ INTO spm_test_tab
  2  SELECT level,
  3         'Description for ' || level
  4  FROM   dual
  5  CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL>

Query the table using an unindexed column, which results in a full table scan.

SQL> 
SQL> 
SQL> SET AUTOTRACE TRACE
SQL> SELECT description
  2  FROM   spm_test_tab
  3  WHERE  id = 99;


Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |    25 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    14   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=99)


Statistics
----------------------------------------------------------
         99  recursive calls
         12  db block gets
        148  consistent gets
         42  physical reads
       2212  redo size
        569  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Identify the SQL_ID of the SQL statement by querying the V$SQL view.

SQL> 
SQL> 
SQL> conn sys@PDB1 AS SYSDBA
Enter password: 
Connected.
SQL> show user;
USER is "SYS"
SQL> SELECT sql_id
  2  FROM   v$sql
  3  WHERE  plan_hash_value = 1107868462
  4  AND    sql_text NOT LIKE 'EXPLAIN%';

SQL_ID
---------------------------------------
gat6z1bc6nc2d

SQL>

Use this SQL_ID to manually load the SQL plan baseline.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_plans_loaded  PLS_INTEGER;
  3  BEGIN
  4    l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
  5      sql_id => 'gat6z1bc6nc2d');
  6    DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
  7  END;
  8  /
Plans Loaded: 1

PL/SQL procedure successfully completed.

SQL>

The DBA_SQL_PLAN_BASELINES view provides information about the SQL plan baselines. We can see there is a single plan associated with our baseline, which is both enabled and accepted.

SQL> 
SQL> COLUMN sql_handle FORMAT A20
SQL> COLUMN plan_name FORMAT A30
SQL> SELECT sql_handle, plan_name, enabled, accepted 
  2  FROM   dba_sql_plan_baselines
  3  WHERE  sql_text LIKE '%spm_test_tab%'
  4  AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE           PLAN_NAME                      ENABLED   ACCEPTED
-------------------- ------------------------------ --------- ---------
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES       YES

SQL>

Flush the shared pool to force another hard parse, create an index on the ID column, then repeat the query to see the affect on the execution plan.

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 13 16:05:15 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> conn sys@PDB1 AS SYSDBA
Enter password: 
Connected.
SQL> show user;
USER is "SYS"
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> conn maxwellpan/maxwellpan@PDB1;
Connected.
SQL> 
SQL> CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> 
SQL> SET AUTOTRACE TRACE
SQL> 
SQL> SELECT description
  2  FROM   spm_test_tab
  3  WHERE  id = 99;


Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462

--------------------------------------------------------------------------------
--

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
 |

--------------------------------------------------------------------------------
--

|   0 | SELECT STATEMENT  |              |     1 |    25 |    14   (0)| 00:00:01
 |

|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    14   (0)| 00:00:01
 |

--------------------------------------------------------------------------------
--


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=99)

Note
-----
   - SQL plan baseline "SQL_PLAN_7qxjk7bch8h5tb65c37c8" used for this statement


Statistics
----------------------------------------------------------
        958  recursive calls
         41  db block gets
       1089  consistent gets
          0  physical reads
      10964  redo size
        569  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         64  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Notice the query doesn't use the newly created index, even though we forced a hard parse. The note explains the SQL plan baseline is used. Looking at the DBA_SQL_PLAN_BASELINES view we can see why.

SQL> 
SQL> conn sys@PDB1 AS SYSDBA
Enter password: 
Connected.
SQL> set pagesize 200 linesize 200
SQL> col sql_handle format a20
SQL> col sql_handle format a30
SQL> col plan_name format a20
SQL> SELECT sql_handle, plan_name, enabled, accepted 
  2  FROM   dba_sql_plan_baselines
  3  WHERE  sql_handle = 'SQL_7b76323ad90440b9';

SQL_HANDLE                     PLAN_NAME            ENABLED   ACCEPTED
------------------------------ -------------------- --------- ---------
SQL_7b76323ad90440b9           SQL_PLAN_7qxjk7bch8h YES       NO
                               5t3652c362

SQL_7b76323ad90440b9           SQL_PLAN_7qxjk7bch8h YES       YES
                               5tb65c37c8


SQL>

The SQL plan baseline now contains a second plan, but it has not yet been accepted.

If you don't see the new row in the DBA_SQL_PLAN_BASELINES view go back and rerun the query from "spm_test_tab" until you do. It sometimes takes the server a few attempts before it notices the need for additional plans.

For the new plan to be used we need to wait for the maintenance window or manually evolve the SQL plan baseline. Create a new evolve task for this baseline.

SQL> 
SQL> SET SERVEROUTPUT ON
DECLARE
SQL>   2    l_return VARCHAR2(32767);
  3  BEGIN
  4    l_return := DBMS_SPM.create_evolve_task(sql_handle => 'SQL_7b76323ad90440b9');
  5    DBMS_OUTPUT.put_line('Task Name: ' || l_return);
  6  END;
  7  /
Task Name: TASK_21

PL/SQL procedure successfully completed.

SQL>

Execute the evolve task.

SQL> 
SQL> SET SERVEROUTPUT ON
SQL> 
SQL> DECLARE
  2    l_return VARCHAR2(32767);
  3  BEGIN
  4    l_return := DBMS_SPM.execute_evolve_task(task_name => 'TASK_21');
  5    DBMS_OUTPUT.put_line('Execution Name: ' || l_return);
  6  END;
  7  /
Execution Name: EXEC_411

PL/SQL procedure successfully completed.

SQL>

Report on the result of the evolve task.

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100

SELECT DBMS_SPM.report_evolve_task(task_name => 'TASK_21', execution_name => 'EXEC_21') AS output
FROM   dual;

OUTPUT
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------

 Task Information:
 ---------------------------------------------
 Task Name            : TASK_21
 Task Owner           : SYS
 Execution Name       : EXEC_21
 Execution Type       : SPM EVOLVE
 Scope                : COMPREHENSIVE
 Status               : COMPLETED
 Started              : 02/18/2015 08:37:41
 Finished             : 02/18/2015 08:37:41
 Last Updated         : 02/18/2015 08:37:41
 Global Time Limit    : 2147483646
 Per-Plan Time Limit  : UNUSED
 Number of Errors     : 0
---------------------------------------------------------------------------------------------

SUMMARY SECTION
---------------------------------------------------------------------------------------------
  Number of plans processed  : 1
  Number of findings         : 1
  Number of recommendations  : 1
  Number of errors           : 0
---------------------------------------------------------------------------------------------

DETAILS SECTION
---------------------------------------------------------------------------------------------
 Object ID          : 2
 Test Plan Name     : SQL_PLAN_7qxjk7bch8h5t3652c362
 Base Plan Name     : SQL_PLAN_7qxjk7bch8h5tb65c37c8
 SQL Handle         : SQL_7b76323ad90440b9
 Parsing Schema     : TEST
 Test Plan Creator  : TEST
 SQL Text           : SELECT description FROM spm_test_tab WHERE id = 99

Execution Statistics:
-----------------------------
                    Base Plan                     Test Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  .000019                       .000005
 CPU Time (s):      .000022                       0
 Buffer Gets:       4                             0
 Optimizer Cost:    14                            2
 Disk Reads:        0                             0
 Direct Writes:     0                             0
 Rows Processed:    0                             0
 Executions:        10                            10


FINDINGS SECTION
---------------------------------------------------------------------------------------------

Findings (1):
-----------------------------
 1. The plan was verified in 0.02000 seconds. It passed the benefit criterion
    because its verified performance was 15.00740 times better than that of the
    baseline plan.

Recommendation:
-----------------------------
 Consider accepting the plan. Execute
 dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_21', object_id => 2,
 task_owner => 'SYS');


EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------

Baseline Plan
-----------------------------
 Plan Id          : 101
 Plan Hash Value  : 3059496904

-----------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |    1 |    25 |   14 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | SPM_TEST_TAB |    1 |    25 |   14 | 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=99)


Test Plan
-----------------------------
 Plan Id          : 102
 Plan Hash Value  : 911393634

---------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |    1 |    25 |    2 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | SPM_TEST_TAB     |    1 |    25 |    2 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SPM_TEST_TAB_IDX |    1 |       |    1 | 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=99)

---------------------------------------------------------------------------------------------

SQL>

If the evolve task has completed and has reported recommendations, implement them. The recommendations suggests using ACCEPT_SQL_PLAN_BASELINE, but you should really use IMPLEMENT_EVOLVE_TASK.

SET SERVEROUTPUT ON
DECLARE
  l_return NUMBER;
BEGIN
  l_return := DBMS_SPM.implement_evolve_task(task_name => 'TASK_21');
  DBMS_OUTPUT.put_line('Plans Accepted: ' || l_return);
END;
/
Plans Accepted: 1

PL/SQL procedure successfully completed.

SQL>

The DBA_SQL_PLAN_BASELINES view shows the second plan as been accepted.

CONN sys/pdb1 AS SYSDBA

SELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_handle = 'SQL_7b76323ad90440b9';

SQL_HANDLE           PLAN_NAME                      ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES YES
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES

SQL

Repeating the earlier test shows the more efficient plan is now available for use.

CONN maxwellpan/maxwellpan@pdb1

SET AUTOTRACE TRACE LINESIZE 130

SELECT description
FROM   spm_test_tab
WHERE  id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 2338891031

--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST_TAB     |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SPM_TEST_TAB_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=99)

Note
-----
   - SQL plan baseline "SQL_PLAN_7qxjk7bch8h5t3652c362" used for this statement

If you want to remove the plans, drop them using the DROP_SQL_PLAN_BASELINE function.

CONN sys@pdb1 AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_7b76323ad90440b9');
  DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped);
END;
/
Plans Dropped: 2

PL/SQL procedure successfully completed.

SQL>