Oracle 19c OCM课程:应用SQL执行计划基线的案例

2023年 10月 23日 27.5k 0

修改配置,采集SQL执行计划基线

Oracle 19c与SQL执行计划基线相关的默认参数值如下:

    SQL> show parameter baseline


    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_capture_sql_plan_baselines boolean FALSE
    optimizer_use_sql_plan_baselines boolean TRUE

    使用存储过程DBMS_SPM.CONFIGURE修改配置,自动捕捉TPCC用户执行的SQL,并创建基线:

      EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','TPCC',true);
      alter system set optimizer_capture_sql_plan_baselines=true;

      修改后的参数存放在视图DBA_SQL_MANAGEMENT_CONFIG中,检查一下:

        COL PARAMETER_NAME FORMAT a32
        COL PARAMETER_VALUE FORMAT a32


        SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG ;


        PARAMETER_NAME PARAMETER_VALUE
        -------------------------------- --------------------------------
        AUTO_CAPTURE_ACTION
        AUTO_CAPTURE_MODULE
        AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (TPCC)
        AUTO_CAPTURE_SQL_TEXT
        AUTO_SPM_EVOLVE_TASK OFF
        AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 --
        AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
        PLAN_RETENTION_WEEKS 53 -- 53不用的计划会被清理
        SPACE_BUDGET_PERCENT 10 -- 占用SYSAUX的空间不超过10%,超过在alert中报警


        9 rows selected.

        02

        查看SQL执行计划基线的应用

        先将一个索引改成不可见:

          SQL> alter index tpcc.CUSTOMER_I1 invisible;


          Index altered.

          应用运行一段时间后,检查已经创建的SQL基线:

            SQL> select count(distinct sql_handle),count(distinct plan_name),count(distinct SIGNATURE) from DBA_SQL_PLAN_BASELINES;


            COUNT(DISTINCTSQL_HANDLE) COUNT(DISTINCTPLAN_NAME) COUNT(DISTINCTSIGNATURE)
            ------------------------- ------------------------ ------------------------
            30 30 30

            可以看到为30个SQL建立了基线,都是ACCEPTED,因为每个SQL只有一个执行计划。

            检查与这个索引相关的SQL的执行情况:

              SQL> select SQL_ID,EXECUTIONS,OPTIMIZER_COST,SQL_PLAN_BASELINE from v$sql where sql_id='arykx3hpq9xsa';




              SQL_ID EXECUTIONS OPTIMIZER_COST SQL_PLAN_BASELINE
              ------------- ---------- -------------- ------------------------------
              arykx3hpq9xsa 5102 1945 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea

              可以看到它用到了一个SQL基线的执行计划,成本是1945,查看这个SQL基线的执行计划:

                SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_2d858eea22dc0f79','SQL_PLAN_2v1cfx8jds3vt3a6ea7ea','basic') );


                PLAN_TABLE_OUTPUT
                --------------------------------------------------------------------------------
                SQL handle: SQL_2d858eea22dc0f79
                SQL text: UPDATE CUSTOMER SET C_BALANCE = C_BALANCE + :B1 WHERE C_W_ID = :B4
                AND C_D_ID = :B3 AND C_ID = :B2
                --------------------------------------------------------------------------------


                --------------------------------------------------------------------------------
                Plan name: SQL_PLAN_2v1cfx8jds3vt3a6ea7ea Plan id: 980330474
                Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
                Plan rows: From dictionary
                --------------------------------------------------------------------------------


                Plan hash value: 3529770744


                ----------------------------------------
                | Id | Operation | Name |
                ----------------------------------------
                | 0 | UPDATE STATEMENT | |
                | 1 | UPDATE | CUSTOMER |
                | 2 | INDEX SKIP SCAN| CUSTOMER_I2 |
                ----------------------------------------


                22 rows selected.

                可以看到这个SQL执行中使用了CUSTOMER_I2 索引,没有使用CUSTOMER_I1索引,因为CUSTOMER_I1这个索引被修改成了不可见。这种检查SQL执行计划的方法和在游标中查询SQL执行计划的方法得到同样的结果:

                  set pagesize 200
                  SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('arykx3hpq9xsa'));

                  将这个索引改成可见:

                    alter index tpcc.CUSTOMER_I1 visible;

                    第二次执行应用程序,然后再检查这个SQL的执行情况:

                      SQL> select SQL_ID,EXECUTIONS,OPTIMIZER_COST,SQL_PLAN_BASELINE from v$sql where sql_id='arykx3hpq9xsa';


                      SQL_ID EXECUTIONS OPTIMIZER_COST SQL_PLAN_BASELINE
                      ------------- ---------- -------------- ------------------------------
                      arykx3hpq9xsa 2376 1945 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea

                      发现这个SQL的执行成本和使用执行计划基线仍然没有发生变化,检查这个SQL对应的执行计划基线:

                        col plan_name form a30
                        col signature forma 99999999999999999999999
                        select sql_handle,plan_name,signature,accepted,optimizer_cost from DBA_SQL_PLAN_BASELINES
                        where SIGNATURE=(select EXACT_MATCHING_SIGNATURE from v$sql where sql_id='arykx3hpq9xsa');


                        SQL_HANDLE PLAN_NAME SIGNATURE ACC OPTIMIZER_COST
                        ------------------------------ ------------------------------ ------------------------ --- --------------
                        SQL_2d858eea22dc0f79 SQL_PLAN_2v1cfx8jds3vt341d91fc 3280185039867613049 NO 3
                        SQL_2d858eea22dc0f79 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea 3280185039867613049 YES 1945

                        发现这个SQL对应了两个基线,期中成本小到3的基线居然是没有被接受的!

                        03

                        手工进化基线

                        因为SQL基线的进化任务要到晚上维护窗口时才会执行,新的基线没有进化成可接受的,所以SQL执行时不会选择这个基线,我们可以手工对这个SQL基线进行进化:

                          VARIABLE cnt NUMBER
                          VARIABLE tk_name VARCHAR2(50)
                          VARIABLE exe_name VARCHAR2(50)
                          VARIABLE evol_out CLOB


                          begin
                          :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK(
                          sql_handle => 'SQL_2d858eea22dc0f79',
                          plan_name => 'SQL_PLAN_2v1cfx8jds3vt341d91fc');
                          end;
                          /

                          SELECT :tk_name FROM DUAL;
                          SQL> SELECT :tk_name FROM DUAL;


                          :TK_NAME
                          -----------------------
                          TASK_1551


                          EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name);
                          SELECT :exe_name FROM DUAL;
                          :EXE_NAME
                          --------------------------------
                          EXEC_3452

                          检查这个进化计划的执行报告:

                            EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
                            SELECT :evol_out FROM DUAL;
                            SQL> SELECT :evol_out FROM DUAL;


                            :EVOL_OUT
                            --------------------------------------------------------------------------------
                            GENERAL INFORMATION SECTION
                            ----------------------------------------------------


                            Task Information:
                            ---------------------------------------------
                            Task Name : TASK_1551


                            Task Owner : SYS
                            Execution Name : EXEC_3452
                            Execution Type : SPM EVOLVE
                            Scope : COMPREHENSIVE


                            Status : COMPLETED
                            Started : 09/08/2023 15:21:50
                            Finished : 09/08/2023 15:21:50
                            Last Updated : 09/08/2023 15:21:50
                            Global Time Limit : 2147483646
                            Per-Plan Time Limit : UNUSED
                            Number of Errors : 0
                            -----------------------------------------
                            SUMMARY SECTION
                            --------------------------------------------------
                            7 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_2v1cfx8jds3vt341d91fc
                            Base Plan Name : SQL_PLAN_2v1cfx8jds3vt3a6ea7ea
                            SQL Handle : SQL_2d858eea22dc0f79


                            Parsing Schema : TPCC


                            Test Plan Creator : SYS


                            SQL Text : UPDATE CUSTOMER SET C_BALANCE = C_BALANCE + :B1 WHERE
                            C_W_ID = :B4 AND C_D_ID= :B3 AND C_ID = :B2


                            Bind Variables:
                            -----------------------------
                            2 - (NUMBER): 3


                            3 - (NUMBER): 1


                            4 - (NUMBER): 2285




                            Execution Statistics:
                            -----------------------------
                            Base Plan
                            Test Plan
                            --------------------
                            -------- ----------------------------
                            Elapsed Time (s): .00064 .000002
                            CPU Time (s): .000638 .000002
                            Buffer Gets: 117 0
                            Optimizer Cost: 1945 3
                            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.12000 seconds. It passed the benefit criterion
                            because its verified performance was 390.90001 times better than that of
                            the baseline plan.


                            Recommendation:
                            -----------------------------
                            Consider accepting the plan. Execute


                            dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1551', object_id => 2,
                            task_owner => 'SYS');


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


                            Baseline Plan
                            -----------------------------
                            Plan Id : 9217
                            Plan Hash Value : 980330474


                            -------------------------------------------
                            | Id | Operation | Name | Rows | Bytes | Cost | Time |
                            ---------------------------------------------------
                            ------------------------
                            | 0 | UPDATE STATEMENT | | 1 | 15 | 1945 | 00:00:01 |
                            | 1 | UPDATE | CUSTOMER | | | | |
                            | * 2 | INDEX SKIP SCAN | CUSTOMER_I2 | 1 | 15 | 1944 | 00:00:01 |
                            -----------------------------------------------


                            Predicate Information (identified by operation id)
                            :
                            ------------------------------------------
                            * 2 - access("C_W_ID"=:B4 AND "C_D_ID"=:B3 AND "C_ID"=:B2)
                            * 2 - filter("C_ID"=:B2 AND "C_W_ID"=:B4 AND "C_D_ID"=:B3)




                            Test Plan
                            -----------------------------
                            Plan Id : 9218
                            Plan Hash Value : 874353148


                            ----------------------------------------------------
                            | Id | Operation | Name | Rows | Bytes | Cost | Time |
                            --------------------------------------------------------
                            | 0 | UPDATE STATEMENT | | 1 | 15| 3 | 00:00:01 |
                            | 1 | UPDATE | CUSTOMER | | | | |
                            | * 2 | INDEX UNIQUE SCAN | CUSTOMER_I1 | 1 | 15 | 2 | 00:00:01 |
                            --------------------------------------------


                            Predicate Information (identified by operation id):
                            ------------------------------------------
                            * 2 - access("C_W_ID"=:B4 AND "C_D_ID"=:B3 AND "C_ID"=:B2)


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



                            根据报告中的建议,执行下面的SQL接受第二个基线:

                              SQL> exec dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1551', object_id => 2, task_owner => 'SYS');


                              PL/SQL procedure successfully completed.

                              再次检查这个SQL对应的执行计划基线:

                                col plan_name form a30
                                col signature forma 99999999999999999999999
                                select sql_handle,plan_name,signature,accepted,optimizer_cost from DBA_SQL_PLAN_BASELINES
                                2 where SIGNATURE=(select EXACT_MATCHING_SIGNATURE from v$sql where sql_id='arykx3hpq9xsa');


                                SQL_HANDLE PLAN_NAME SIGNATURE ACC OPTIMIZER_COST
                                ------------------------------ ------------------------------ ------------------------ --- --------------
                                SQL_2d858eea22dc0f79 SQL_PLAN_2v1cfx8jds3vt341d91fc 3280185039867613049 YES 3
                                SQL_2d858eea22dc0f79 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea 3280185039867613049 YES 1945

                                发现两个基线都是接受的。

                                第三次执行应用后,然后检查SQL的执行情况:

                                  SQL> select SQL_ID,EXECUTIONS,OPTIMIZER_COST,SQL_PLAN_BASELINE from v$sql where sql_id='arykx3hpq9xsa';


                                  SQL_ID EXECUTIONS OPTIMIZER_COST SQL_PLAN_BASELINE
                                  ------------- ---------- -------------- ------------------------------
                                  arykx3hpq9xsa 6849 3 SQL_PLAN_2v1cfx8jds3vt341d91fc

                                  发现应用的SQL采用了第二个基线,执行成本从1945降低到3。

                                  04

                                  删除SQL基线

                                  使用dbms_spm.drop_sql_plan_baseline可以删除SQL基线,但不能一次删除所有的SQL基线,如果要删除所有的SQL基线,需要用游标进行循环删除  ,相关程序如下(参见Doc ID 790039.1)

                                    declare
                                    pgn number;
                                    sqlhdl varchar2(30);
                                    cursor hdl_cur is
                                    select distinct sql_handle from dba_sql_plan_baselines;
                                    begin
                                    open hdl_cur;
                                    loop
                                    fetch hdl_cur into sqlhdl;
                                    exit when hdl_cur%NOTFOUND;
                                    pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl);
                                    end loop;
                                    close hdl_cur;
                                    commit;
                                    end;
                                    /


                                    PL/SQL procedure successfully completed.


                                    SQL> select count(*) from dba_sql_plan_baselines;


                                    COUNT(*)
                                    ----------
                                    0

                                    相关文章

                                    Oracle如何使用授予和撤销权限的语法和示例
                                    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
                                    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
                                    社区版oceanbase安装
                                    Oracle 导出CSV工具-sqluldr2
                                    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

                                    发布评论