开启辅助访问

Bo's Oracle Station

Bo's Oracle Station › 博客首页 ›Oracle技术› SQL Tuning › 查看内容

【博客文章2021】SQL基线常用操作

2021-10-2 10:45| 发布者: admin| 查看: 3350| 评论: 0|原作者: Bo Tang

摘要: SQL基线常用操作：1. 删除和进化基线（需要知道sql_handle和plan_name这两个参数）；2. 固定基线，固定执行计划
【博客文章2021】SQL基线常用操作

Author: Bo Tang

1. SQL Baseline概述：

    SQL Plan Management (SPM)这个功能提供了对SQL执行计划进行可控的进化和稳定化的方法。开启SPM后，Oracle优化器自动管理执行计划并且保证只使用已知的或被验证过的执行计划。当发现了某条SQL语句的新的执行计划时，该执行计划必需被验证为比当前采用的执行计划具有更好的性能后，才会被采用。
    SPM分为以下3大类操作：
    1) 执行计划捕捉（主要有两种：自动执行计划捕捉和批量执行计划捕捉）
    2) 执行计划选择
    3) 执行计划验证

2. 自动执行计划捕捉：

    通过在SQL会话中设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES参数，来打开自动执行计划捕捉功能：任何重复执行的SQL语句都将被自动加载进SPM Repository。
    在会话中设置参数：

SQL> conn sh/oracle_4U
Connected.
SQL> show parameter optimizer_capture_sql_plan
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
 
    在上面的会话中，第1次执行下面的SQL语句：

SQL> set autot on
SQL> set linesize 10000
SQL> SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc;
CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel                        2000-01     15870.34
Saint Marks                    2000-01     55781.37
Cypress Gardens                2000-01      3545.82
......
24 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 429518790

-------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |  1292 | 98192 |    339  (24)| 00:00:01 |        |        |
|   1 |  HASH GROUP BY            |        |  1292 | 98192 |    339  (24)| 00:00:01 |        |        |
|*  2 |   HASH JOIN            |        | 12335 |    915K|    337  (23)| 00:00:01 |        |        |
|*  3 |    TABLE ACCESS INMEMORY FULL    | CUSTOMERS |  2438 | 63388 |     23  (31)| 00:00:01 |        |        |
|*  4 |    HASH JOIN            |        | 35715 |  1743K|    314  (23)| 00:00:01 |        |        |
|   5 |     JOIN FILTER CREATE        | :BF0001   |    227 |  6583 |     12   (0)| 00:00:01 |        |        |
|   6 |      PART JOIN FILTER CREATE    | :BF0000   |    227 |  6583 |     12   (0)| 00:00:01 |        |        |
|   7 |       MERGE JOIN CARTESIAN    |        |    227 |  6583 |     12   (0)| 00:00:01 |        |        |
|*  8 |        TABLE ACCESS FULL    | CHANNELS  |      1 |     13 |      2   (0)| 00:00:01 |        |        |
|   9 |        BUFFER SORT        |        |    227 |  3632 |     10   (0)| 00:00:01 |        |        |
|* 10 |     TABLE ACCESS FULL    | TIMES     |    227 |  3632 |     10   (0)| 00:00:01 |        |        |
|  11 |     JOIN FILTER USE        | :BF0001   |    918K|     18M|    285  (19)| 00:00:01 |        |        |
|  12 |      PARTITION RANGE JOIN-FILTER|        |    918K|     18M|    285  (19)| 00:00:01 |:BF0000|:BF0000|
|* 13 |       TABLE ACCESS INMEMORY FULL| SALES     |    918K|     18M|    285  (19)| 00:00:01 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."CUST_ID"="C"."CUST_ID")
   3 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
       filter("C"."CUST_STATE_PROVINCE"='FL')
   4 - access("S"."TIME_ID"="T"."TIME_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   8 - filter("CH"."CHANNEL_DESC"='Direct Sales')
  10 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
          "T"."CALENDAR_QUARTER_DESC"='2000-02')
  13 - inmemory(SYS_OP_BLOOM_FILTER(:BF0001,"S"."TIME_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0001,"S"."TIME_ID"))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics
----------------------------------------------------------
       2849  recursive calls
    112  db block gets
       4864  consistent gets
    340  physical reads
      21984  redo size
       1535  bytes sent via SQL*Net to client
    812  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
    326  sorts (memory)
      0  sorts (disk)
     24  rows processed

    以sys身份执行下面的SQL语句，以确认上述SQL语句的执行计划是否被捕获进入SPM Repository：

SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
没有数据
 
    由于是首次执行该SQL语句，所以上述SQL语句的执行计划没有被捕获进入SPM Repository。
    在上面的会话中，第2次执行下面的SQL语句：

SQL> SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc;
CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel                        2000-01     15870.34
Saint Marks                    2000-01     55781.37
Cypress Gardens                2000-01      3545.82
......
24 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 429518790

-------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |  1292 | 98192 |    339  (24)| 00:00:01 |        |        |
|   1 |  HASH GROUP BY            |        |  1292 | 98192 |    339  (24)| 00:00:01 |        |        |
|*  2 |   HASH JOIN            |        | 12335 |    915K|    337  (23)| 00:00:01 |        |        |
|*  3 |    TABLE ACCESS INMEMORY FULL    | CUSTOMERS |  2438 | 63388 |     23  (31)| 00:00:01 |        |        |
|*  4 |    HASH JOIN            |        | 35715 |  1743K|    314  (23)| 00:00:01 |        |        |
|   5 |     JOIN FILTER CREATE        | :BF0001   |    227 |  6583 |     12   (0)| 00:00:01 |        |        |
|   6 |      PART JOIN FILTER CREATE    | :BF0000   |    227 |  6583 |     12   (0)| 00:00:01 |        |        |
|   7 |       MERGE JOIN CARTESIAN    |        |    227 |  6583 |     12   (0)| 00:00:01 |        |        |
|*  8 |        TABLE ACCESS FULL    | CHANNELS  |      1 |     13 |      2   (0)| 00:00:01 |        |        |
|   9 |        BUFFER SORT        |        |    227 |  3632 |     10   (0)| 00:00:01 |        |        |
|* 10 |     TABLE ACCESS FULL    | TIMES     |    227 |  3632 |     10   (0)| 00:00:01 |        |        |
|  11 |     JOIN FILTER USE        | :BF0001   |    918K|     18M|    285  (19)| 00:00:01 |        |        |
|  12 |      PARTITION RANGE JOIN-FILTER|        |    918K|     18M|    285  (19)| 00:00:01 |:BF0000|:BF0000|
|* 13 |       TABLE ACCESS INMEMORY FULL| SALES     |    918K|     18M|    285  (19)| 00:00:01 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."CUST_ID"="C"."CUST_ID")
   3 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
       filter("C"."CUST_STATE_PROVINCE"='FL')
   4 - access("S"."TIME_ID"="T"."TIME_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   8 - filter("CH"."CHANNEL_DESC"='Direct Sales')
  10 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
          "T"."CALENDAR_QUARTER_DESC"='2000-02')
  13 - inmemory(SYS_OP_BLOOM_FILTER(:BF0001,"S"."TIME_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0001,"S"."TIME_ID"))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - SQL plan baseline "SQL_PLAN_f2cukwjncj1ua9655907a" used for this statement


Statistics
----------------------------------------------------------
     16  recursive calls
      0  db block gets
     72  consistent gets
     59  physical reads
      0  redo size
       1535  bytes sent via SQL*Net to client
    812  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     24  rows processed

    以sys身份执行下面的SQL语句，以确认上述SQL语句的执行计划是否被捕获进入SPM Repository：

SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
 dba_sql_plan_baselines where sql_text like '%my_auto_capture%';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16227405023310022474    SQL_e13352e468c8874a    " SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc"    SQL_PLAN_f2cukwjncj1ua9655907a    AUTO-CAPTURE    YES    YES    NO    YES
 
    由于是第2次执行该SQL语句，所以上述SQL语句的执行计划被捕获进入SPM Repository。该SQL基线是自动捕获的（ AUTO-CAPTURE  ）、使能的（ENABLED YES）、被接受的（ACCEPTED YES），但是没有被固定（FIXED NO）。注意只有一个SQL Plan：
 “SQL_PLAN_f2cukwjncj1ua9655907a ”存在。
    接着以sys身份，将上述SQL语句打包制作成SQL调优集：

SQL> SELECT sql_id,force_matching_signature, substr(sql_text,1,80), parsing_schema_name,module ,elapsed_time, optimizer_cost
 FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_text like ''%my_auto_capture%'''))
 ORDER BY last_exec_start_time;
--------------------------------------------------------------------------------------------------------------------------------------------
1kzyyqrgh5rc9    4386726710696795349    SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_    SH    SQL*Plus    104602    339
 
SQL> begin
 dbms_sqltune.create_sqlset(sqlset_name=>'my_auto_capture',
                                                             sqlset_owner=>'sys');
end;
PL/SQL procedure successfully completed.
SQL> declare
  sqlset_cur dbms_sqltune.sqlset_cursor;
  bf VARCHAR2(37);
begin
  bf := q'#sql_id='1kzyyqrgh5rc9' #';
  open sqlset_cur for SELECT VALUE(P) FROM TABLE( dbms_sqltune.select_cursor_cache(bf)) P;
  dbms_sqltune.load_sqlset( sqlset_name=>'my_auto_capture', populate_cursor=>sqlset_cur, sqlset_owner=>'SYS');
 END;
/
PL/SQL procedure successfully completed.

    查看my_auto_capture这个SQL 调优集中的SQL语句：

SQL> select  sql_id , sql_text  from dba_sqlset_statements  where sqlset_name='my_auto_capture';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1kzyyqrgh5rc9    " SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc"
 
    将该SQL调优集送进SQL Tuning Advisor进行调优，带上force match并接受生成的SQL Profile（过程略，也可参见本博客SQL Tuning栏目的相关文章）。


    对比SQL Tuning出来的执行计划，如下图所示（原执行计划在上面，其plan hash value是429518790；带上SQL Profile的执行计划在下面，其plan hash value是4042352056）：    


    在上面的会话中，第3次执行同一条SQL语句：

SQL> SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc;
CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel                        2000-01     15870.34
Saint Marks                    2000-01     55781.37
Cypress Gardens                2000-01      3545.82
......
24 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 429518790

-------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |  1292 | 98192 |    343  (25)| 00:00:01 |        |        |
|   1 |  HASH GROUP BY            |        |  1292 | 98192 |    343  (25)| 00:00:01 |        |        |
|*  2 |   HASH JOIN            |        | 34021 |  2524K|    338  (24)| 00:00:01 |        |        |
|*  3 |    TABLE ACCESS INMEMORY FULL    | CUSTOMERS |  2438 | 63388 |     23  (31)| 00:00:01 |        |        |
|*  4 |    HASH JOIN            |        | 98504 |  4809K|    314  (23)| 00:00:01 |        |        |
|   5 |     JOIN FILTER CREATE        | :BF0001   |    227 |  6583 |     12   (0)| 00:00:01 |        |        |
|   6 |      PART JOIN FILTER CREATE    | :BF0000   |    227 |  6583 |     12   (0)| 00:00:01 |        |        |
|   7 |       MERGE JOIN CARTESIAN    |        |    227 |  6583 |     12   (0)| 00:00:01 |        |        |
|*  8 |        TABLE ACCESS FULL    | CHANNELS  |      1 |     13 |      2   (0)| 00:00:01 |        |        |
|   9 |        BUFFER SORT        |        |    227 |  3632 |     10   (0)| 00:00:01 |        |        |
|* 10 |     TABLE ACCESS FULL    | TIMES     |    227 |  3632 |     10   (0)| 00:00:01 |        |        |
|  11 |     JOIN FILTER USE        | :BF0001   |    918K|     18M|    285  (19)| 00:00:01 |        |        |
|  12 |      PARTITION RANGE JOIN-FILTER|        |    918K|     18M|    285  (19)| 00:00:01 |:BF0000|:BF0000|
|* 13 |       TABLE ACCESS INMEMORY FULL| SALES     |    918K|     18M|    285  (19)| 00:00:01 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."CUST_ID"="C"."CUST_ID")
   3 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
       filter("C"."CUST_STATE_PROVINCE"='FL')
   4 - access("S"."TIME_ID"="T"."TIME_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   8 - filter("CH"."CHANNEL_DESC"='Direct Sales')
  10 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
          "T"."CALENDAR_QUARTER_DESC"='2000-02')
  13 - inmemory(SYS_OP_BLOOM_FILTER(:BF0001,"S"."TIME_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0001,"S"."TIME_ID"))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   0 -    STATEMENT
     U -  NO_PARALLEL
     U -  OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block

Note
-----
   - SQL profile "SYS_SQLPROF_0181804ba0c70000" used for this statement
   - SQL plan baseline "SQL_PLAN_f2cukwjncj1ua9655907a" used for this statement


Statistics
----------------------------------------------------------
     19  recursive calls
      4  db block gets
     69  consistent gets
      0  physical reads
      0  redo size
       1535  bytes sent via SQL*Net to client
    813  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     24  rows processed

    以sys身份执行下面的SQL语句，以确认上述SQL语句的执行计划是否被捕获进入SPM Repository：

SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
 dba_sql_plan_baselines where sql_text like '%my_auto_capture%';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13109402004376082309    SQL_b5edf3aa98a15785    "select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%'"    SQL_PLAN_bbvgmpaca2pw509e243f4    AUTO-CAPTURE    YES    YES    NO    YES
13109402004376082309    SQL_b5edf3aa98a15785    "select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%'"    SQL_PLAN_bbvgmpaca2pw5b7054c68    AUTO-CAPTURE    YES    NO    NO    YES
11645540445179873680    SQL_a19d45558c640990    "SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_text like ''%my_auto_capture%'''))
ORDER BY last_exec_start_time"    SQL_PLAN_a37a5aq6682che07fca29    AUTO-CAPTURE    YES    YES    NO    YES
16227405023310022474    SQL_e13352e468c8874a    " SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc"    SQL_PLAN_f2cukwjncj1ua9655907a    AUTO-CAPTURE    YES    YES    NO    YES
16227405023310022474    SQL_e13352e468c8874a    " SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc"    SQL_PLAN_f2cukwjncj1ua8c6f4a2e    AUTO-CAPTURE    YES    NO    NO    YES
   
    现在可以看到：查询出两条基线。这说明新的带SQL Profile的基线被捕获进入了SPM Repository。但请注意：新的带SQL Profile的自动捕获的基线尚未被接受。这一新计划必须先通过验证，然后才能被接受作为有效计划来使用。由于第一条基线的存在，实际上导致SQL Profile根本没有生效。虽然写着“SQL profile "SYS_SQLPROF_0181804ba0c70000" used for this statement”：因为从auto trace跟踪看出来：执行计划依旧是429518790而不是4042352056。
    现在可以在会话中关闭自动捕获SQL基线：

SQL> alter session set optimizer_capture_sql_plan_baselines=false;
Session altered.
   
    删除老的不带SQL Profile的SQL_PLAN_f2cukwjncj1ua9655907a这条基线（删除基线时需要知道sql_handle和plan_name这两个参数）：

SQL> declare
 cnt number;
begin
 cnt := dbms_spm.drop_sql_plan_baseline('SQL_e13352e468c8874a','SQL_PLAN_f2cukwjncj1ua9655907a');
end;
/

    以sys身份执行下面的SQL语句，查看SPM Repository里老的SQL基线是否已经被删除掉了：

SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
 dba_sql_plan_baselines where sql_text like '%my_auto_capture%';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13109402004376082309    SQL_b5edf3aa98a15785    "select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%'"    SQL_PLAN_bbvgmpaca2pw509e243f4    AUTO-CAPTURE    YES    YES    NO    YES
13109402004376082309    SQL_b5edf3aa98a15785    "select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%'"    SQL_PLAN_bbvgmpaca2pw5b7054c68    AUTO-CAPTURE    YES    NO    NO    YES
11645540445179873680    SQL_a19d45558c640990    "SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_text like ''%my_auto_capture%'''))
ORDER BY last_exec_start_time"    SQL_PLAN_a37a5aq6682che07fca29    AUTO-CAPTURE    YES    YES    NO    YES
16227405023310022474    SQL_e13352e468c8874a    " SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc"    SQL_PLAN_f2cukwjncj1ua8c6f4a2e    AUTO-CAPTURE    YES    NO    NO    YES
   
    第4次执行同一条SQL语句：

SQL> SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc;
CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel                        2000-01     15870.34
Saint Marks                    2000-01     55781.37
Cypress Gardens                2000-01      3545.82
......
24 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4042352056

--------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |    1292 | 98192 |     339  (24)| 00:00:01 |         |         |
|   1 |  HASH GROUP BY             |         |    1292 | 98192 |     339  (24)| 00:00:01 |         |         |
|*  2 |   HASH JOIN             |         |    7203 |     534K|     337  (23)| 00:00:01 |         |         |
|   3 |    PART JOIN FILTER CREATE     | :BF0000   |     227 |    3632 |      10   (0)| 00:00:01 |         |         |
|*  4 |     TABLE ACCESS FULL         | TIMES     |     227 |    3632 |      10   (0)| 00:00:01 |         |         |
|*  5 |    HASH JOIN             |         | 46326 |    2714K|     326  (24)| 00:00:01 |         |         |
|   6 |     JOIN FILTER CREATE         | :BF0001   |    2438 | 95082 |      25  (28)| 00:00:01 |         |         |
|   7 |      MERGE JOIN CARTESIAN     |         |    2438 | 95082 |      25  (28)| 00:00:01 |         |         |
|*  8 |       TABLE ACCESS FULL      | CHANNELS  |       1 |      13 |       2   (0)| 00:00:01 |         |         |
|   9 |       BUFFER SORT         |         |    2438 | 63388 |      23  (31)| 00:00:01 |         |         |
|* 10 |        TABLE ACCESS INMEMORY FULL| CUSTOMERS |    2438 | 63388 |      23  (31)| 00:00:01 |         |         |
|  11 |     JOIN FILTER USE         | :BF0001   |     918K|      18M|     285  (19)| 00:00:01 |         |         |
|  12 |      PARTITION RANGE JOIN-FILTER |         |     918K|      18M|     285  (19)| 00:00:01 |:BF0000|:BF0000|
|* 13 |       TABLE ACCESS INMEMORY FULL | SALES     |     918K|      18M|     285  (19)| 00:00:01 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."TIME_ID"="T"."TIME_ID")
   4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
          "T"."CALENDAR_QUARTER_DESC"='2000-02')
   5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   8 - filter("CH"."CHANNEL_DESC"='Direct Sales')
  10 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
       filter("C"."CUST_STATE_PROVINCE"='FL')
  13 - inmemory(SYS_OP_BLOOM_FILTER(:BF0001,"S"."CUST_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0001,"S"."CUST_ID"))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - SQL profile "SYS_SQLPROF_0181804ba0c70000" used for this statement


Statistics
----------------------------------------------------------
      6  recursive calls
      0  db block gets
     64  consistent gets
      0  physical reads
      0  redo size
       1535  bytes sent via SQL*Net to client
    812  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     24  rows processed
   
    “SQL profile "SYS_SQLPROF_0181804ba0c70000" used for this statement”生效了：因为从autot跟踪看出来：执行计划是4042352056。
    下面我们进化验证剩下的这条SQL基线SQL_PLAN_f2cukwjncj1ua8c6f4a2e，使得它变成ACCEPTED：
     以sys身份，为了谨慎起见再次手工验证语句的执行情况：

SQL> set serveroutput on;
SQL> declare
 v_sqltext varchar2(4000);
 v_task varchar2(30);
 v_report clob;
BEGIN
 v_sqltext := q'# SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc
                #';
 v_task := dbms_sqltune.create_tuning_task(sql_text => v_sqltext);
 dbms_sqltune.execute_tuning_task(v_task);
 select dbms_sqltune.report_tuning_task( v_task,'TEXT')  into v_report from dual;
 dbms_output.put_line(v_report);
END;
/
---------------------------------------------------------------------------------------------------------
anonymous block completed
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_5460
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 06/22/2022 10:17:23
Completed at       : 06/22/2022 10:17:40

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 2p80wc5zmr6an
SQL Text   :  SELECT /* my_auto_capture */ c.cust_city,
             t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
              FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
              WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND
             s.channel_id = ch.channel_id
              AND c.cust_state_province = 'FL'
              AND ch.channel_desc = 'Direct Sales'
              AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
             GROUP BY c.cust_city,
              t.calendar_quarter_desc
                            

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "SYS_SQLPROF_0181843cdd110002" exists for this statement and
  was ignored during the tuning process.

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

1- Original
-----------
Plan hash value: 4042352056

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |           |  1292 | 98192 |   364  (21)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                   |           |  1292 | 98192 |   364  (21)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                      |           |  7203 |   534K|   362  (20)| 00:00:01 |       |       |
|   3 |    PART JOIN FILTER CREATE       | :BF0000   |   227 |  3632 |    10   (0)| 00:00:01 |       |       |
|*  4 |     TABLE ACCESS FULL            | TIMES     |   227 |  3632 |    10   (0)| 00:00:01 |       |       |
|*  5 |    HASH JOIN                     |           | 46326 |  2714K|   350  (20)| 00:00:01 |       |       |
|   6 |     JOIN FILTER CREATE           | :BF0001   |  2438 | 95082 |    25  (28)| 00:00:01 |       |       |
|   7 |      MERGE JOIN CARTESIAN        |           |  2438 | 95082 |    25  (28)| 00:00:01 |       |       |
|*  8 |       TABLE ACCESS FULL          | CHANNELS  |     1 |    13 |     2   (0)| 00:00:01 |       |       |
|   9 |       BUFFER SORT                |           |  2438 | 63388 |    23  (31)| 00:00:01 |       |       |
|* 10 |        TABLE ACCESS INMEMORY FULL| CUSTOMERS |  2438 | 63388 |    23  (31)| 00:00:01 |       |       |
|  11 |     JOIN FILTER USE              | :BF0001   |   918K|    18M|   309  (15)| 00:00:01 |       |       |
|  12 |      PARTITION RANGE JOIN-FILTER |           |   918K|    18M|   309  (15)| 00:00:01 |:BF0000|:BF0000|
|* 13 |       TABLE ACCESS INMEMORY FULL | SALES     |   918K|    18M|   309  (15)| 00:00:01 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("S"."TIME_ID"="T"."TIME_ID")
   4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
              "T"."CALENDAR_QUARTER_DESC"='2000-02')
   5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   8 - filter("CH"."CHANNEL_DESC"='Direct Sales')
  10 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
       filter("C"."CUST_STATE_PROVINCE"='FL')
  13 - inmemory(SYS_OP_BLOOM_FILTER(:BF0001,"S"."CUST_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0001,"S"."CUST_ID"))

-------------------------------------------------------------------------------
   
    从以上快速调优的结果看出来：执行计划是没有问题的（没有改变）。接下来，我们进化（自动验证）该基线（有SQL Profile的SQL_e13352e468c8874a，SQL_PLAN_f2cukwjncj1ua8c6f4a2e） ，进而自动接受该基线，最后固定该基线，以免今后的执行计划的变动：

SQL> declare
 v_task clob;
begin
 v_task := dbms_spm.evolve_sql_plan_baseline('SQL_e13352e468c8874a','SQL_PLAN_f2cukwjncj1ua8c6f4a2e');
end;
/

    以sys身份执行下面的SQL语句，查看SPM Repository里的情况：

SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
 dba_sql_plan_baselines where sql_text like '%my_auto_capture%';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16227405023310022474    SQL_e13352e468c8874a    "SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc"    SQL_PLAN_f2cukwjncj1ua8c6f4a2e    AUTO-CAPTURE    YES    YES    NO    YES
13109402004376082309    SQL_b5edf3aa98a15785    "select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%'"    SQL_PLAN_bbvgmpaca2pw509e243f4    AUTO-CAPTURE    YES    YES    NO    YES
1191493243515085968    SQL_108908cacbfea490    "SELECT sql_id,force_matching_signature, substr(sql_text,1,80), parsing_schema_name,module ,elapsed_time, optimizer_cost
 FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_text like ''%my_auto_capture%'''))
 ORDER BY last_exec_start_time"    SQL_PLAN_11288tb5zx94he07fca29    AUTO-CAPTURE    YES    YES    NO    YES
  
    以sys身份执行下面的SQL语句，固定该基线：

SQL> declare 
   cnt  number;
  begin
     cnt := dbms_spm.alter_sql_plan_baseline(plan_name => 'SQL_PLAN_f2cukwjncj1ua8c6f4a2e',
     attribute_name => 'FIXED',attribute_value => 'YES');
  end;
/
SQL> declare
   cnt  number;
  begin
     cnt := dbms_spm.alter_sql_plan_baseline(plan_name => 'SQL_PLAN_f2cukwjncj1ua8c6f4a2e',
     attribute_name => 'AUTOPURGE',attribute_value => 'NO');
  end;
  / 

    以sys身份执行下面的SQL语句，查看SPM Repository里的情况：

SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
 dba_sql_plan_baselines where sql_text like '%my_auto_capture%';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13109402004376082309    SQL_b5edf3aa98a15785    "select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%'"    SQL_PLAN_bbvgmpaca2pw56ded1a00    AUTO-CAPTURE    YES    NO    NO    YES
16227405023310022474    SQL_e13352e468c8874a    "SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc"    SQL_PLAN_f2cukwjncj1ua8c6f4a2e    AUTO-CAPTURE    YES    YES    YES    NO
13109402004376082309    SQL_b5edf3aa98a15785    "select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%'"    SQL_PLAN_bbvgmpaca2pw509e243f4    AUTO-CAPTURE    YES    YES    NO    YES
1191493243515085968    SQL_108908cacbfea490    "SELECT sql_id,force_matching_signature, substr(sql_text,1,80), parsing_schema_name,module ,elapsed_time, optimizer_cost
 FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_text like ''%my_auto_capture%'''))
 ORDER BY last_exec_start_time"    SQL_PLAN_11288tb5zx94he07fca29    AUTO-CAPTURE    YES    YES    NO    YES
 
 
3. 批量执行计划捕捉：

3.1 从SQL调优集来批量加载SQL基线：

SQL> declare
 cnt number;
begin
 cnt := dbms_spm.load_plans_from_sqlset( sqlset_name  => 'my_auto_capture',
                  basic_filter => 'sql_text like ''%my_auto_capture%''');
end;
/

    以sys身份执行下面的SQL语句，查看SPM Repository里的情况：

SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
 dba_sql_plan_baselines where sql_text like '%my_auto_capture%';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13109402004376082309    SQL_b5edf3aa98a15785    "select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%'"    SQL_PLAN_bbvgmpaca2pw56ded1a00    AUTO-CAPTURE    YES    NO    NO    YES
16227405023310022474    SQL_e13352e468c8874a    "SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc"    SQL_PLAN_f2cukwjncj1ua9655907a    MANUAL-LOAD-FROM-STS    YES    YES    NO    YES
16227405023310022474    SQL_e13352e468c8874a    "SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc"    SQL_PLAN_f2cukwjncj1ua8c6f4a2e    AUTO-CAPTURE    YES    YES    YES    NO
13109402004376082309    SQL_b5edf3aa98a15785    "select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from
dba_sql_plan_baselines where sql_text like '%my_auto_capture%'"    SQL_PLAN_bbvgmpaca2pw509e243f4    AUTO-CAPTURE    YES    YES    NO    YES
1191493243515085968    SQL_108908cacbfea490    "SELECT sql_id,force_matching_signature, substr(sql_text,1,80), parsing_schema_name,module ,elapsed_time, optimizer_cost
 FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_text like ''%my_auto_capture%'''))
 ORDER BY last_exec_start_time"    SQL_PLAN_11288tb5zx94he07fca29    AUTO-CAPTURE    YES    YES    NO    YES
3.2 从共享池中的游标来批量加载SQL基线：

SQL> declare
 cnt number;
begin
 cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 某个sql_id);
end;
/

    批量捕获的SQL基线与自动捕获的SQL基线相比具有如下的特征：1. 所有批量加载的基线都是接受的，这点与自动捕获不同。2. origin是MANUAL-LOAD。
   
4. 执行计划选择：

    执行计划选择的优先级如下：
    1. 固定的SQL基线
    2. 普通的SQL基线（必须同时是ACCEPTED和ENABLED）。如果同时有两条以上的基线与该SQL语句相对应，则根据捕获该基线时的环境（比如optimizer_mode）跟当前运行环境匹配程度的来选择（比如A基线是在first_rows_10的时候捕获的，如果当前环境也是first_rows_10，那么A基线就会被选择。B基线是在all_rows下捕获的，B基线就不会被选择。）      
    3. SQL Profile
    4. CBO
    综上所述，由于存在以上的优先级，所以即使删除了SQL Profile，我们的那条语句也会使用4042352056执行计划运行：

SQL>begin
  dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0181804ba0c70000');
end;
/

SQL> SELECT /* my_auto_capture */ c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
 AND c.cust_state_province = 'FL'
 AND ch.channel_desc = 'Direct Sales'
 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
 t.calendar_quarter_desc;
CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel                        2000-01     15870.34
Saint Marks                    2000-01     55781.37
Cypress Gardens                2000-01      3545.82
......
24 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4042352056

--------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |    1292 | 98192 |     365  (21)| 00:00:01 |         |         |
|   1 |  HASH GROUP BY             |         |    1292 | 98192 |     365  (21)| 00:00:01 |         |         |
|*  2 |   HASH JOIN             |         | 12335 |     915K|     362  (20)| 00:00:01 |         |         |
|   3 |    PART JOIN FILTER CREATE     | :BF0000   |     227 |    3632 |      10   (0)| 00:00:01 |         |         |
|*  4 |     TABLE ACCESS FULL         | TIMES     |     227 |    3632 |      10   (0)| 00:00:01 |         |         |
|*  5 |    HASH JOIN             |         | 79336 |    4648K|     350  (20)| 00:00:01 |         |         |
|   6 |     JOIN FILTER CREATE         | :BF0001   |    2438 | 95082 |      25  (28)| 00:00:01 |         |         |
|   7 |      MERGE JOIN CARTESIAN     |         |    2438 | 95082 |      25  (28)| 00:00:01 |         |         |
|*  8 |       TABLE ACCESS FULL      | CHANNELS  |       1 |      13 |       2   (0)| 00:00:01 |         |         |
|   9 |       BUFFER SORT         |         |    2438 | 63388 |      23  (31)| 00:00:01 |         |         |
|* 10 |        TABLE ACCESS INMEMORY FULL| CUSTOMERS |    2438 | 63388 |      23  (31)| 00:00:01 |         |         |
|  11 |     JOIN FILTER USE         | :BF0001   |     918K|      18M|     309  (15)| 00:00:01 |         |         |
|  12 |      PARTITION RANGE JOIN-FILTER |         |     918K|      18M|     309  (15)| 00:00:01 |:BF0000|:BF0000|
|* 13 |       TABLE ACCESS INMEMORY FULL | SALES     |     918K|      18M|     309  (15)| 00:00:01 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."TIME_ID"="T"."TIME_ID")
   4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
          "T"."CALENDAR_QUARTER_DESC"='2000-02')
   5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   8 - filter("CH"."CHANNEL_DESC"='Direct Sales')
  10 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
       filter("C"."CUST_STATE_PROVINCE"='FL')
  13 - inmemory(SYS_OP_BLOOM_FILTER(:BF0001,"S"."CUST_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0001,"S"."CUST_ID"))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   0 -    STATEMENT
     U -  NO_PARALLEL

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


Statistics
----------------------------------------------------------
     43  recursive calls
     14  db block gets
    293  consistent gets
      0  physical reads
       3584  redo size
       1535  bytes sent via SQL*Net to client
    812  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     24  rows processed







路过
雷人
握手
鲜花
鸡蛋
收藏 邀请
上一篇：【博客文章2022】SPA结合SPM进行SQL调优下一篇：【博客文章2022】IO校准和自动SQL并行执行

QQ|手机版|Bo's Oracle Station   

GMT+8, 2026-1-3 22:08 , Processed in 0.016165 second(s), 20 queries .

返回顶部