SQL PLAN Management的測試

文件搬運工發表於2018-06-20

參考文件：https://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95105

建立測試用表。並執行查詢語句，並檢視該語句的執行計劃

SYS@test>create table test2 tablespace users as select * from dba_objects;

Table created.

SYS@test>
-- 進行查詢
select * from test2 where object_id<10;

檢視該語句的執行計劃

SYS@test>select sql_id from v$sql where sql_text like 'select * from test2 where object_id%';

SQL_ID
-------------
982wxshw8rbfv

SYS@test>

-- explain 
SYS@test>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 300966803

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("OBJECT_ID"<10)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline "SQL_PLAN_bfxsuw31dur8x99963deb" used for this statement

18 rows selected.

SYS@test>

檢視sqlplan檢視，發現該PLAN是沒有被固定的

SYS@test>select plan_name,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id%';

PLAN_NAME                      ENA ACC FIX
------------------------------ --- --- ---
SQL_PLAN_bfxsuw31dur8x99963deb YES YES NO

SYS@test>

--
SYS@test>SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
FROM   DBA_SQL_PLAN_BASELINES where plan_name='SQL_PLAN_bfxsuw31dur8x99963deb'   2  ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_b7771ae0c2dd5d1d           SQL_PLAN_bfxsuw31dur8x99963deb YES YES NO

SYS@test>

對執行計劃進行固定

DECLARE
  v_cnt PLS_INTEGER;
BEGIN 
  v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(               
                           sql_handle=>'SQL_b7771ae0c2dd5d1d', 
                           attribute_name=>'FIXED', 
                           attribute_value=>'YES');
  DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;


SYS@test>DECLARE
  v_cnt PLS_INTEGER;
BEGIN
  v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
                           sql_handle=>'SQL_b7771ae0c2dd5d1d',
                           attribute_name=>'FIXED',
                           attribute_value=>'YES');
  DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/  2    3    4    5    6    7    8    9   10

PL/SQL procedure successfully completed.

SYS@test>

檢視固定後的結果

SYS@test>SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
FROM   DBA_SQL_PLAN_BASELINES where plan_name='SQL_PLAN_bfxsuw31dur8x99963deb'   2  ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_b7771ae0c2dd5d1d           SQL_PLAN_bfxsuw31dur8x99963deb YES YES YES

SYS@test>

建立索引，並收集統計資訊

create index idx_object_id1 on test2(object_id);
exec dbms_stats.gather_table_stats('SYS','TEST2');

SYS@test>create index idx_object_id1 on test2(object_id);

Index created.

SYS@test>exec dbms_stats.gather_table_stats('SYS','TEST2');

PL/SQL procedure successfully completed.

SYS@test>

檢視執行計劃，發現使用了SQLPLAN，而建立的索引並沒有起作用

SYS@test>explain plan for select * from test2 where object_id<10;

Explained.

SYS@test>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 300966803

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     8 |   784 |   348   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST2 |     8 |   784 |   348   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("OBJECT_ID"<10)

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

17 rows selected.

SYS@test>

如果更改object_id<10 為object_id<20 . 可以看到使用了索引。沒有使用之前固定的執行計劃

SYS@test>explain plan for select * from test2 where object_id<20;

Explained.

SYS@test>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3768730550

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |    17 |  1666 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2          |    17 |  1666 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID1 |    17 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("OBJECT_ID"<20)

14 rows selected.

SYS@test>

上面的演示，通過一個簡單的例子，說明了如何去固定一個SQL執行計劃。

--- 補充內容

1 load sqlplan baseline from cursor

DECLARE  
k1 pls_integer;  
begin  
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (  
sql_id=>'axfpyf7vzw5wq',  
plan_hash_value=>3768730550,  
fixed=>'NO',  
enabled=>'NO');  
end;  

--
SYS@test>DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id=>'axfpyf7vzw5wq',
plan_hash_value=>3768730550,
fixed=>'NO',
enabled=>'NO');
end;    2    3    4    5    6    7    8    9
 10  /

PL/SQL procedure successfully completed.

SYS@test>

2 修改SQLPLAN baseline的屬性

DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_b7771ae0c2dd5d1d',
plan_name=>'SQL_PLAN_bfxsuw31dur8x99963deb',
--attribute_name=>'ENABLED',
--attribute_value=>'NO',
--attribute_name=>'FIXED',
--attribute_value=>'NO',
attribute_name=>'autopurge',
attribute_value=>'NO'
);
end;

-- 修改固定為NO
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_b7771ae0c2dd5d1d',
plan_name=>'SQL_PLAN_bfxsuw31dur8x99963deb',
--attribute_name=>'ENABLED',
--attribute_value=>'NO',
attribute_name=>'FIXED',
attribute_value=>'NO'
--attribute_name=>'autopurge',
--attribute_value=>'NO'
);
end;

-- 修改已接受為NO
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_b7771ae0c2dd5d1d',
plan_name=>'SQL_PLAN_bfxsuw31dur8x99963deb',
attribute_name=>'ENABLED',
attribute_value=>'NO'
--attribute_name=>'FIXED',
--attribute_value=>'NO'
--attribute_name=>'autopurge',
--attribute_value=>'NO'
);
end;

3 直接通過sqlid 進行load，然後再固定執行計劃

DECLARE
  my_plans PLS_INTEGER;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '6pum75s6s9060');
END;
/
SYS@test>DECLARE
  my_plans PLS_INTEGER;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '6pum75s6s9060');
END;  2    3    4    5
  6  /

PL/SQL procedure successfully completed.

SYS@test>

DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_72dffdbeb0c6d1ca',
plan_name=>'SQL_PLAN_75rzxruscdnfa5f3bf724',
attribute_name=>'FIXED',
attribute_value=>'YES'
);
end;

4 一個演進的測試，然後進行固定執行計劃

SYS@test>drop index SYS.IDX_OBJECT_ID1;

Index dropped.

SYS@test>
-- 執行語句
SYS@test>select * from test2 where object_id<5;
-- 查詢sqlid
SYS@test>select sql_id from v$sql where sql_text='select * from test2 where object_id<5';

SQL_ID
-------------
1b5utgagagrhx

SYS@test>

-- load到sql Plan baseline 中 
DECLARE
  my_plans PLS_INTEGER;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '1b5utgagagrhx');
END;
/
SYS@test>DECLARE
  my_plans PLS_INTEGER;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '1b5utgagagrhx');
END;  2    3    4    5
  6  /

PL/SQL procedure successfully completed.

SYS@test>

-- 建立索引 
create index idx_object_id2 on test2(object_id) tablespace users;
exec dbms_stats.gather_table_stats('SYS','test2');
-- 再次查詢，
select * from test2 where object_id<5;
-- 進行演進
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
    report clob;
BEGIN
    report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
                  sql_handle => 'SQL_30f4ab054111c786');
    DBMS_OUTPUT.PUT_LINE(report);
END;
/

SYS@test>SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
    report clob;
BEGIN
    report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
                  sql_handle => 'SQL_30f4ab054111c786');
    DBMS_OUTPUT.PUT_LINE(report);
END;SYS@test>SYS@test>  2    3    4    5    6    7
  8  /

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_30f4ab054111c786

PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan:
SQL_PLAN_31x5b0p0j3jw66f7568ca
------------------------------------
  Plan was verified: Time used .06 seconds.
  Plan passed performance
criterion: 416.83 times better than baseline plan.
  Plan was changed to an accepted plan.

                            Baseline Plan
Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:
COMPLETE       COMPLETE
  Rows Processed:                       3              3
  Elapsed Time(ms):                  5.21           .009
578.89
  CPU Time(ms):                     5.044              0
  Buffer Gets:                       1250              3            416.67

Physical Read Requests:               0              0
  Physical Write Requests:              0              0
  Physical Read Bytes:
0              0
  Physical Write Bytes:                 0              0
  Executions:                           1
1

-------------------------------------------------------------------------------
                                 Report
Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans
accepted: 1


PL/SQL procedure successfully completed.

SYS@test>

-- 然後fix
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_30f4ab054111c786',
plan_name=>'SQL_PLAN_31x5b0p0j3jw66f7568ca',
attribute_name=>'FIXED',
attribute_value=>'YES'
);
end;

SYS@test>DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_30f4ab054111c786',
plan_name=>'SQL_PLAN_31x5b0p0j3jw66f7568ca',
attribute_name=>'FIXED',
attribute_value=>'YES'
);
end;  2    3    4    5    6    7    8    9   10
 11  /

PL/SQL procedure successfully completed.

SYS@test>
-- 查詢結果 。 ok 了 
SYS@test>select enabled,accepted,fixed,reproduced from dba_sql_plan_baselines where sql_handle='SQL_30f4ab054111c786' and PLan_name='SQL_PLAN_31x5b0p0j3jw66f7568ca';

ENA ACC FIX REP
--- --- --- ---
YES YES YES YES

SYS@test>

5 檢視SQLPLAN baseline

SYS@test>select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_15fcb21aeb77f6f7',format=>'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_15fcb21aeb77f6f7
SQL text: select * from test2 where object_id<25
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_1bz5k3bprgxrr5f3bf724         Plan id: 1597765412
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4066171248

------------------------------------------------------
| Id  | Operation                   | Name           |
------------------------------------------------------
|   0 | SELECT STATEMENT            |                |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2          |
|   2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID2 |
------------------------------------------------------

20 rows selected.

SYS@test>

6 檢視SQLPLAN baseline的引數。預設是保留53周，佔用的sysaux的空間限制是10%

SYS@test>SELECT PARAMETER_NAME, PARAMETER_VALUE FROM  DBA_SQL_MANAGEMENT_CONFIG;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        10
PLAN_RETENTION_WEEKS                        53

SYS@test>

-- 修改sql plan baseline的保留時間為105周
BEGIN
  DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);
END;

修改，及檢視修改後的引數

BEGIN
  DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/
SYS@test>SELECT PARAMETER_NAME, PARAMETER_VALUE FROM  DBA_SQL_MANAGEMENT_CONFIG;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        30
PLAN_RETENTION_WEEKS                       105

SYS@test>

END

SQL Plan Management
2016-05-27
SQL
SQL Plan Management(SPM)
2012-12-25
SQL
SQL Plan Management介紹
2014-07-22
SQL
11g-sql plan management
2014-06-04
SQL
Oracle11g中SQL Plan Management （一）
2011-10-31
OracleSQL
Oracle11g中SQL Plan Management （二）
2011-11-03
OracleSQL
Oracle11g中SQL Plan Management （三）
2011-11-06
OracleSQL
Oracle 11g SQL效能的新特性（三）- SQL Plan Management
2015-02-09
OracleSQL
Oracle 11g 新特性 -- SQL Plan Management 示例
2014-02-18
OracleSQL
【DBA】Oracle 11g 針對SQL效能的新特性（三）- SQL Plan Management
2017-07-01
OracleSQL
oracle11g中SQL最佳化（SQL TUNING）新特性之SQL Plan Management(SPM)
2016-06-11
OracleSQL
SQL management base
2014-03-02
SQL
sql tuning gather_plan_statistics與filter和執行計劃一點測試
2015-06-27
SQLFilter
SQL PROFILE 測試
2014-07-24
SQL
使用Trace Management Object監測和診斷SQL Server（一）
2009-05-27
ObjectSQLServer
使用Trace Management Object監測和診斷SQL Server（二）
2009-05-27
ObjectSQLServer
Python的SQL效能測試
2019-02-16
PythonSQL
Python 的 SQL 效能測試
2018-09-02
PythonSQL
SAP ABAP SQL的execution plan和cache
2020-03-10
SQL
SQL MAP 注入測試
2020-04-07
SQL
Prepared SQL 效能測試
2022-03-20
SQL
sql_plan_baseline
2019-09-30
SQL
WRH$_SQL_PLAN　被鎖
2014-02-25
SQL
sql plan baselines（一）
2013-01-20
SQL
sql plan baseline（二）
2013-01-21
SQL
SQL Server Management Studio (SSMS)
2019-04-25
SQLServerSSM
Shared SQL Dependency Management (249)
2007-11-25
SQL
IBM Engineering Test Management — 需求驅動的測試管理工具
2021-07-28
IBM
【SQL_PLAN】Oracle 透過檢視sql_plan 格式化執行計劃
2022-09-14
SQLOracle
測試學習SQL篇
2020-11-13
SQL
sql trace 簡單測試
2015-07-04
SQL
使用V$SQL_PLAN檢視
2016-10-11
SQL
v$sql_plan 檢視解析
2012-11-13
SQL
用EXPLAIN PLAN 分析SQL語句
2006-08-16
AISQL
pl/sql中的forall簡單測試
2015-06-10
SQL
oracle sql_not exists與null的測試
2012-12-14
OracleSQLNull
【Explain Plan】檢視SQL的執行計劃
2009-10-07
AISQL
Sql Server生成測試資料
2015-04-23
SQLServer

SQL PLAN Management的測試

相關文章