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_baseline
2019-09-30
SQL
SQL Server Management Studio (SSMS)
2019-04-25
SQLServerSSM
SAP ABAP SQL的execution plan和cache
2020-03-10
SQL
Python的SQL效能測試
2019-02-16
PythonSQL
Python 的 SQL 效能測試
2018-09-02
PythonSQL
【SQL_PLAN】Oracle 透過檢視sql_plan 格式化執行計劃
2022-09-14
SQLOracle
IBM Engineering Test Management — 需求驅動的測試管理工具
2021-07-28
IBM
Manage SQL Plan Baselines in Oracle 12c
2019-02-15
SQLOracle
Prepared SQL 效能測試
2022-03-20
SQL
SQL MAP 注入測試
2020-04-07
SQL
SQL Access Advisor、SQL Tuning Advisor 測試
2018-06-27
SQL
Jmeter系列（6）- test plan測試計劃詳細講解
2020-04-26
JMeter
測試學習SQL篇
2020-11-13
SQL
Manually Evolve and Accept SQL PLAN BASELINES in Oracle 12c
2019-02-15
SQLOracle
[20181225]12CR2 SQL Plan Directives.txt
2018-12-25
SQL
SQL Server Availability Group Failover 測試
2019-11-01
SQLServerAI
利用SQL Server Management Studio(SSMS)複製資料庫
2019-01-23
SQLServerSSM資料庫
測試人員必會SQL命令
2024-03-29
SQL
使用profiler測試Oracle PL/SQL效能
2019-09-02
OracleSQL
測試雜談——一條SQL引發的思考
2022-03-21
SQL
[20210812]測試sql語句子游標的效能.txt
2021-08-12
SQL
Oracle9i中v$sql、v$sqlarea、v$sqltext、v$sql_plan的聯絡與區別
2018-05-14
OracleSQL
run_stats---sql效率測試工具(轉)
2019-01-09
SQL
[20210816]測試sql語句子游標的效能3.txt
2021-08-27
SQL
[20210818]測試sql語句子游標的效能4.txt
2021-08-27
SQL
openGauss關於PL/SQL匿名塊呼叫測試
2024-04-01
SQL
Customer Management
2018-10-30
"The Alberta Plan for AI Research" - "Research Plan" from Richard Sutton
2024-10-12
AI
測試測試測試測試測試測試
2024-09-03
軟體安全測試之SQL隱碼攻擊
2020-12-09
SQL
[20210419]測試18c SQL Translation Framework.txt
2021-04-19
SQLFramework
4.2.1.1 Plan the PDBs
2020-03-17
Memory Management in Rust
2022-02-17
Rust
Virtualbox host plan
2018-11-06
測試——水杯的測試用例
2018-08-05
功能測試、自動化測試、效能測試的區別
2024-03-06
軟體測試中的功能測試和非功能測試
2022-12-30
小白測試系列:介面測試與效能測試的區別
2020-07-31

SQL PLAN Management的測試

相關文章