♣
题目部分
在Oracle中,SPM的使用有哪些步骤?
♣
答案部分
取消自动捕获,也可以不取消自动捕捉:
show parameter baselines
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;
执行:
[ZHLHRSPMDB2:oracle]:/oracle>ORACLE_SID=dlhr
[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 15:47:55 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@dlhr> conn lhr/lhr
Connected.
LHR@dlhr>
LHR@dlhr>
LHR@dlhr>
LHR@dlhr> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
LHR@dlhr> show parameter baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean TRUE
LHR@dlhr> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;
System altered.
--创建表并插入数据,脚本:
CREATE TABLE tb_spm_test_lhr (
id NUMBER,
description VARCHAR2(50)
);
DECLARE
TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;
l_tab t_tab := t_TAB();
BEGIN
FOR i IN 1 .. 10000 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO tb_spm_test_lhr VALUES l_tab(i);
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'tb_spm_test_lhr', cascade=>TRUE);
set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
开始执行:
LHR@dlhr> CREATE TABLE tb_spm_test_lhr (
2 id NUMBER,
3 description VARCHAR2(50)
4 );
Table created.
LHR@dlhr>
LHR@dlhr> DECLARE
2 TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;
3 l_tab t_tab := t_TAB();
4 BEGIN
5 FOR i IN 1 .. 10000 LOOP
6 l_tab.extend;
7 l_tab(l_tab.last).id := i;
8 l_tab(l_tab.last).description := 'Description for ' || i;
9 END LOOP;
10
11 FORALL i IN l_tab.first .. l_tab.last
12 INSERT INTO tb_spm_test_lhr VALUES l_tab(i);
13
14 COMMIT;
15 END;
16 /
PL/SQL procedure successfully completed.
LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 40 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
94 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
获取刚才查询的SQL_ID:
set autot off
col SQL_TEXT format a100
select distinct a.SQL_ID,a.SQL_TEXT from v$sql a
WHERE a.SQL_TEXT like '%SELECT description FROM tb_spm_test_lhr WHERE id = 100%'
and a.SQL_TEXT not like '%v$sql%'
AND sql_text NOT LIKE '%EXPLAIN%';
LHR@dlhr> set autot off
LHR@dlhr> col SQL_TEXT format a100
LHR@dlhr> select distinct a.SQL_ID,a.SQL_TEXT from v$sql a
2 WHERE a.SQL_TEXT like '%SELECT description FROM tb_spm_test_lhr WHERE id = 100%'
3 and a.SQL_TEXT not like '%v$sql%'
4 AND sql_text NOT LIKE '%EXPLAIN%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
garkwg3yy2ram SELECT description FROM tb_spm_test_lhr WHERE id = 100
----使用SQL_ID 从cursor cache中手工捕获执行计划:
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '&sql_id');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
-- --使用DBA_SQL_PLAN_BASELINES视图查看SPM 信息:
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%tb_spm_test_lhr%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
--刷新Share Pool,使下次SQL 执行时必须进行硬解析:
ALTER SYSTEM FLUSH SHARED_POOL;
LHR@dlhr> SET SERVEROUTPUT ON
LHR@dlhr> DECLARE
2 l_plans_loaded PLS_INTEGER;
3 BEGIN
4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
5 sql_id => '&sql_id');
6 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
7 END;
8 /
Enter value for sql_id: garkwg3yy2ram
old 5: sql_id => '&sql_id');
new 5: sql_id => 'garkwg3yy2ram');
Plans Loaded: 1
PL/SQL procedure successfully completed.
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES
LHR@dlhr> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
LHR@dlhr> set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
LHR@dlhr>
Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 40 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement
Statistics
----------------------------------------------------------
555 recursive calls
16 db block gets
667 consistent gets
0 physical reads
3056 redo size
546 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
32 sorts (memory)
0 sorts (disk)
1 rows processed
--创建索引,收集统计信息,并查询相同的SQL:
CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);
EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);
set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
LHR@dlhr> CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);
Index created.
LHR@dlhr> EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);
PL/SQL procedure successfully completed.
LHR@dlhr>
LHR@dlhr>
LHR@dlhr>
LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement
Statistics
----------------------------------------------------------
640 recursive calls
39 db block gets
493 consistent gets
2 physical reads
12268 redo size
546 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
--这里我们创建了索引,但是这里还是走的全表扫描,这里使用索引明显才是最优的方案。
--查看SPM 视图:
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%tb_spm_test_lhr%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
LHR@dlhr> set autot off
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 YES NO
--通过BASELINES查询的结果,可以看到SQL产生了两条执行计划,但是最优的执行计划并没有被标记为ACCEPT,所以没有使用。
--下边我们演化执行计划:演化就是将cost低的执行计划标记为accept:
LHR@dlhr> SET LONG 10000
LHR@dlhr> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual;
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual
new 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_4f19d3cf57be7303') FROM dual
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_4F19D3CF57BE7303')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_4f19d3cf57be7303
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_4y6fmtxbvwws38b725570
------------------------------------
Plan was verified: Time used .018 seconds.
Plan passed performance criterion: 15 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): .308 .025 12.32
CPU Time(ms): .164 .015 10.93
Buffer Gets: 45 3 15
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
--再次查看DBA_SQL_PLAN_BASELINES视图:
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%tb_spm_test_lhr%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
LHR@dlhr> set autot off
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 YES YES
--再次执行SQL:
set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2587945646
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Note
-----
- SQL plan baseline "SQL_PLAN_4y6fmtxbvwws38b725570" used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
14 db block gets
18 consistent gets
0 physical reads
3048 redo size
553 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这次正确的使用了索引。因为只有标记为ENABLE和ACCEPT的plan才可以被使用。
下面示例将我们的第一个走全表扫描的执行计划标记为fixed。标记为fixed的执行计划会被优先使用。FIXED表示优化程序仅考虑标记为FIXED的计划,而不考虑其它计划。例如,如果有10个基线计划,其中的三个计划被标记为FIXED,则优化程序将仅使用这三个计划中的最佳计划,而忽略其它所有计划。如果某个SQL计划基线至少包含一个已启用的已修复计划,则该SQL计划基线就是FIXED的。如果在修复的SQL计划基线中添加了新计划,则在手动将这些新计划声明为FIXED之前,无法使用这些新计划。
set autot off
select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => '&sql_handle',
plan_name => '&plan_name',
attribute_name => 'fixed',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
LHR@dlhr> SET SERVEROUTPUT ON
LHR@dlhr> DECLARE
2 l_plans_altered PLS_INTEGER;
3 BEGIN
4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
5 sql_handle => '&sql_handle',
6 plan_name => '&plan_name',
7 attribute_name => 'fixed',
8 attribute_value => 'YES');
9
10 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
11 END;
12 /
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 5: sql_handle => '&sql_handle',
new 5: sql_handle => 'SQL_4f19d3cf57be7303',
Enter value for plan_name: SQL_PLAN_4y6fmtxbvwws3184920d2
old 6: plan_name => '&plan_name',
new 6: plan_name => 'SQL_PLAN_4y6fmtxbvwws3184920d2',
Plans Altered: 1
PL/SQL procedure successfully completed.
--验证:
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%tb_spm_test_lhr%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
LHR@dlhr> set autot off
LHR@dlhr> select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'))
new 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => 'SQL_4f19d3cf57be7303', format => 'basic'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_4f19d3cf57be7303
SQL text: SELECT description FROM tb_spm_test_lhr WHERE id = 100
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4y6fmtxbvwws3184920d2 Plan id: 407445714
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2196561629
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR |
---------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4y6fmtxbvwws38b725570 Plan id: 2339526000
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2587945646
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR |
| 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX |
--------------------------------------------------------
34 rows selected.
LHR@dlhr> set autot off
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX
----------------------------------- ----------------------------------- -------------- --- --- ---
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 MANUAL-LOAD YES YES YES
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 AUTO-CAPTURE YES YES NO
--再次查看我们之前的SQL:
set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement
Statistics
----------------------------------------------------------
6 recursive calls
8 db block gets
46 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这里已经走了全表扫描,根据前边的示例,我们知道这里走索引会更优,但因为我们将走全表扫描的执行计划设置为fixed,所以优先使用这个执行计划。
& 说明:
有关固定执行计划的具体过程及更多的知识可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2107604/
本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。