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

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

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

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

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

Author: Bo Tang

1. 删除和进化基线（需要知道sql_handle和plan_name这两个参数）：

select * from dba_sql_plan_baselines spb
where spb.sql_text like '%/*LOAD_AUTO*/%';
--获取到了sql_handle和plan_name

验证删除了SQL基线后，auto trace 看有没有用基线：

SQL> @query1
SQL> set echo on
SQL>
SQL> select /*LOAD_AUTO*/ * from sh.sales where quantity_sold > 40 order by prod_id;

no rows selected

SQL>
SQL> show parameter optimizer

NAME                                  TYPE       VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean       TRUE
optimizer_dynamic_sampling          integer       2
optimizer_features_enable          string       11.2.0.1
optimizer_index_caching             integer       0
optimizer_index_cost_adj          integer       100
optimizer_mode                            string       ALL_ROWS
optimizer_secure_view_merging          boolean       TRUE
optimizer_use_invisible_indexes    boolean       FALSE
optimizer_use_pending_statistics    boolean       FALSE
optimizer_use_sql_plan_baselines    boolean       TRUE
SQL> alter session set optimizer_mode=first_rows;

Session altered.

SQL> @query1
SQL> set echo on
SQL>
SQL> select /*LOAD_AUTO*/ * from sh.sales where quantity_sold > 40 order by prod_id;

no rows selected

SQL>
SQL> alter system set optimizer_mode=first_rows;

System altered.

SQL> set autot on
SQL> @query1
SQL> set echo on
SQL>
SQL> select /*LOAD_AUTO*/ * from sh.sales where quantity_sold > 40 order by prod_id;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 899219946

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

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

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

| 0 | SELECT STATEMENT                   |                   |    1 | 29 |
2883       (1)| 00:00:35 |    |    |

| 1 |  SORT ORDER BY                         |                   |    1 | 29 |
2883       (1)| 00:00:35 |    |    |

| 2 | PARTITION RANGE ALL                   |                   |    1 | 29 |
2882       (1)| 00:00:35 |    1 | 28 |

|*  3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |    1 | 29 |
2882       (1)| 00:00:35 |    1 | 28 |

| 4 |    BITMAP CONVERSION TO ROWIDS    |                   |    |    |
         |             |    |    |

| 5 |    BITMAP INDEX FULL SCAN          | SALES_PROMO_BIX |    |    |
         |             |    1 | 28 |

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

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

3 - filter("QUANTITY_SOLD">40)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   2030  consistent gets
      0  physical reads
      0  redo size
      667  bytes sent via SQL*Net to client
      404  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      0  rows processed

SQL>
SQL> SQL> set linesize 10000
SQL> @query1
SQL> set echo on
SQL>
SQL> select /*LOAD_AUTO*/ * from sh.sales where quantity_sold > 40 order by prod_id;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 899219946

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                   |                   |    1 | 29 |  2883       (1)| 00:00:35 |    |    |
| 1 |  SORT ORDER BY                         |                   |    1 | 29 |  2883       (1)| 00:00:35 |    |    |
| 2 | PARTITION RANGE ALL                   |                   |    1 | 29 |  2882       (1)| 00:00:35 |    1 | 28 |
|*  3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |    1 | 29 |  2882       (1)| 00:00:35 |    1 | 28 |
| 4 |    BITMAP CONVERSION TO ROWIDS    |                   |    |    |          |             |    |    |
| 5 |    BITMAP INDEX FULL SCAN          | SALES_PROMO_BIX |    |    |          |             |    1 | 28 |
-----------------------------------------------------------------------------------------------------------------------

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

3 - filter("QUANTITY_SOLD">40)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   2030  consistent gets
      0  physical reads
      0  redo size
      667  bytes sent via SQL*Net to client
      404  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      0  rows processed

SQL>
SQL> @query1
SQL> set echo on
SQL>
SQL> select /*LOAD_AUTO*/ * from sh.sales where quantity_sold > 40 order by prod_id;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 899219946

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                   |                   |    1 | 29 |  2883       (1)| 00:00:35 |    |    |
| 1 |  SORT ORDER BY                         |                   |    1 | 29 |  2883       (1)| 00:00:35 |    |    |
| 2 | PARTITION RANGE ALL                   |                   |    1 | 29 |  2882       (1)| 00:00:35 |    1 | 28 |
|*  3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |    1 | 29 |  2882       (1)| 00:00:35 |    1 | 28 |
| 4 |    BITMAP CONVERSION TO ROWIDS    |                   |    |    |          |             |    |    |
| 5 |    BITMAP INDEX FULL SCAN          | SALES_PROMO_BIX |    |    |          |             |    1 | 28 |
-----------------------------------------------------------------------------------------------------------------------

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

3 - filter("QUANTITY_SOLD">40)

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

Statistics
----------------------------------------------------------
      15  recursive calls
      13  db block gets
   2037  consistent gets
      0  physical reads
   3040  redo size
      667  bytes sent via SQL*Net to client
      404  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      0  rows processed

SQL>
SQL>

2. 固定基线，固定执行计划：

select  * from dba_sql_plan_baselines  spb where spb.sql_text
like '%/*LOAD_STS*/%';

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

declare
  v1 number;
  begin
   v1 := dbms_spm.alter_sql_plan_baseline(plan_name => 'SQL_PLAN_ahstbv1bu98ky11df68d0',
   attribute_name => 'FIXED',attribute_value => 'YES');
  end;

SQL> show parameter optimizer

NAME                                  TYPE       VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean       FALSE
optimizer_dynamic_sampling          integer       2
optimizer_features_enable          string       11.2.0.1
optimizer_index_caching             integer       0
optimizer_index_cost_adj          integer       100
optimizer_mode                            string       ALL_ROWS
optimizer_secure_view_merging          boolean       TRUE
optimizer_use_invisible_indexes    boolean       FALSE
optimizer_use_pending_statistics    boolean       FALSE
optimizer_use_sql_plan_baselines    boolean       TRUE
SQL> set autot on
SQL> @query2
SQL> set echo on
SQL>
SQL> select /*LOAD_STS*/ * from sh.sales where quantity_sold > 40 order by prod_id;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3803407550

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |          |          1 |       29 |       491 (3)| 00:00:06 |          |          |
| 1 |  SORT ORDER BY          |          |          1 |       29 |       491 (3)| 00:00:06 |          |          |
| 2 | PARTITION RANGE ALL|          |          1 |       29 |       490 (2)| 00:00:06 |          1 |       28 |
|*  3 | TABLE ACCESS FULL | SALES |          1 |       29 |       490 (2)| 00:00:06 |          1 |       28 |
----------------------------------------------------------------------------------------------

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

3 - filter("QUANTITY_SOLD">40)

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

Statistics
----------------------------------------------------------
      22  recursive calls
      13  db block gets
   1728  consistent gets
      0  physical reads
   3048  redo size
      667  bytes sent via SQL*Net to client
      404  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      0  rows processed

SQL>
SQL> @query2
SQL> set echo on
SQL>
SQL> select /*LOAD_STS*/ * from sh.sales where quantity_sold > 40 order by prod_id;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 899219946

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                   |                   |    1 | 29 |  2883       (1)| 00:00:35 |    |    |
| 1 |  SORT ORDER BY                         |                   |    1 | 29 |  2883       (1)| 00:00:35 |    |    |
| 2 | PARTITION RANGE ALL                   |                   |    1 | 29 |  2882       (1)| 00:00:35 |    1 | 28 |
|*  3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |    1 | 29 |  2882       (1)| 00:00:35 |    1 | 28 |
| 4 |    BITMAP CONVERSION TO ROWIDS    |                   |    |    |          |             |    |    |
| 5 |    BITMAP INDEX FULL SCAN          | SALES_PROMO_BIX |    |    |          |             |    1 | 28 |
-----------------------------------------------------------------------------------------------------------------------

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

3 - filter("QUANTITY_SOLD">40)

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

Statistics
----------------------------------------------------------
      28  recursive calls
      21  db block gets
   2050  consistent gets
      2  physical reads
   3056  redo size
      667  bytes sent via SQL*Net to client
      404  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      0  rows processed

SQL>
SQL>

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2022】SPA结合SPM进行SQL调优下一篇：【博客文章2022】IO校准和自动SQL并行执行