oracle 11g sql plan baseline(5)baseline的問題和補充
補充sql plan baseline的 其他知識及其問題
SQL> desc dba_sql_plan_baselines;
Name Null? Type
----------------------------------------- -------- ----------------------------
SIGNATURE NOT NULL NUMBER
SQL_HANDLE NOT NULL VARCHAR2(30)
SQL_TEXT NOT NULL CLOB
PLAN_NAME NOT NULL VARCHAR2(30)
CREATOR VARCHAR2(30)
ORIGIN VARCHAR2(14)
PARSING_SCHEMA_NAME VARCHAR2(30)
DESCRIPTION VARCHAR2(500)
VERSION VARCHAR2(64)
CREATED NOT NULL TIMESTAMP(6)
LAST_MODIFIED TIMESTAMP(6)
LAST_EXECUTED TIMESTAMP(6)
LAST_VERIFIED TIMESTAMP(6)
ENABLED VARCHAR2(3)
ACCEPTED VARCHAR2(3)
FIXED VARCHAR2(3)
AUTOPURGE VARCHAR2(3)
OPTIMIZER_COST NUMBER
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
EXECUTIONS NUMBER
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
END_OF_FETCH_COUNT NUMBER
有一個問題 就是用sqltext生成的簽名，如果我有2個schma 下面都有t4這個表，相同的sql語句查詢不同的schema下相同名字的物件（前提sql text一樣，那麼baselines會共享，這也許不是我們
想要的）
實驗user sys建立t4 table
SQL> show user;
USER is "SYS"
SQL> create table t4 (a int);
Table created.
SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into t4 values(i);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats('SYS','T4');
PL/SQL procedure successfully completed.
SQL> select * from t4 where a=4;
A
----------
4
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3d72zm2kf8pus, child number 0
-------------------------------------
select * from t4 where a=4
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=4)
18 rows selected.
SQL> DECLARE
2 ret PLS_INTEGER;
3 BEGIN
4 ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id',
5 plan_hash_value => NULL);
6 dbms_output.put_line(ret || ' SQL plan baseline(s) created');
7 END;
8 /
Enter value for sql_id: 3d72zm2kf8pus
old 4: ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id',
new 4: ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '3d72zm2kf8pus',
PL/SQL procedure successfully completed.
SQL> select * from t4 where a=4;
A
----------
4
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3d72zm2kf8pus, child number 1
-------------------------------------
select * from t4 where a=4
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=4)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_3a0873a3cda915e5 used for this statement
22 rows selected.
上面的問題是 我們sys schema下製作了一個基於t4的 sqlplan baselines
現在在schema xh下
SQL> conn xh/a831115
Connected.
SQL> drop table t4;
drop table t4
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table t4 (a int);
Table created.
SQL> declare
2 begin
3 for i in 1..500 loop
4 insert into t4 values(i);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> create index t4_id on t4(a);
Index created.
SQL> show user
USER is "XH"
SQL> execute dbms_stats.gather_table_stats('XH','T4');
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ENA ACC
--- ---
SYS_SQL_0b32b5423a0873a3 ~~~~~~~~~~~~~~~~~~~~由於xh的plan
select * from t4 where a=4
YES NO
SYS_SQL_0b32b5423a0873a3
select * from t4 where a=4
YES YES
SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ENA ACC
--- ---
SQL> select * from t4 where a=4;
A
----------
4
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3d72zm2kf8pus, child number 2
-------------------------------------
select * from t4 where a=4
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 3 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=4)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_3a0873a3cda915e5 used for this statement
22 rows selected.
可以看到 由於sql text一樣 但查詢的物件不一樣 ，還是使用了為sys.t4 sql_text統計資訊製作的sql plan baselines
SQL> alter system set optimizer_use_sql_plan_baselines=false;關閉使用sql plan baseline
System altered.
SQL> select * from t4 where a=4;
A
----------
4
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3d72zm2kf8pus, child number 0
-------------------------------------
select * from t4 where a=4
Plan hash value: 3313391582
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| T4_ID | 1 | 3 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"=4)
18 rows selected.
可以看到 查詢xh.t4 （雖然sql text相同，但走index是很好的）
保留期，如果一個sql plan baseline autopurge屬性為yes(default)在保留期中一直未使用，且過了保留期 將被清除
SQL> select parameter_value from dba_sql_management_config where parameter_name='PLAN_RETENTION_WEEKS';
PARAMETER_VALUE
---------------
53 ~~~預設保留期是53天
SQL> execute dbms_spm.configure(parameter_name=>'PLAN_RETENTION_WEEKS',parameter_value=>10);
PL/SQL procedure successfully completed.
SQL> select parameter_value from dba_sql_management_config where parameter_name='PLAN_RETENTION_WEEKS';
PARAMETER_VALUE
---------------
10~~~~~~~~修改為10天
sql plan baseline預設儲存在sysaux，佔sysaux10%,超過後alert中回有告警
SQL> select parameter_value from dba_sql_management_config where parameter_name='SPACE_BUDGET_PERCENT';
PARAMETER_VALUE
---------------
10~~~default 10%
SQL> execute dbms_spm.configure(parameter_name=>'SPACE_BUDGET_PERCENT',parameter_value=>5);
PL/SQL procedure successfully completed.
SQL> select parameter_value from dba_sql_management_config where parameter_name='SPACE_BUDGET_PERCENT';
PARAMETER_VALUE
---------------
5~~~~手動修改佔sysaux 百分比
SQL> execute dbms_spm.configure(parameter_name=>'SPACE_BUDGET_PERCENT',parameter_value=>null);
PL/SQL procedure successfully completed.~~~~引數值為努null，就設定為default值
SQL> select parameter_value from dba_sql_management_config where parameter_name='SPACE_BUDGET_PERCENT';
PARAMETER_VALUE
---------------
10
這是執行dbms_spm所要的系統許可權
SQL> select * from session_privs where privilege like '%ADMINISTER SQL MAN%';
PRIVILEGE
----------------------------------------
ADMINISTER SQL MANAGEMENT OBJECT
*************
刪除outlines
SQL> DECLARE
2 ret PLS_INTEGER;
3 BEGIN
4 FOR c IN (SELECT DISTINCT sql_handle
5 FROM dba_sql_plan_baselines
6 WHERE creator = user
7 AND created > systimestamp - to_dsinterval('0 00:15:00'))
8 LOOP
ret := dbms_spm.drop_sql_plan_baseline(c.sql_handle);
9 10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
********************
顯示sql plan base line
先建立一個base line
SQL> SQL> create table t1 (a int);
Table created.
SQL> insert into t1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
Session altered.
SQL> select count(*) from t1;
COUNT(*)
----------
1
SQL> select count(*) from t1;
COUNT(*)
----------
1
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;
Session altered.
SQL> select count(*) from t1;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)
8 rows selected.
SQL> select count(*) from t1;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)
8 rows selected.
SQL> set serveroutput off
SQL> select count(*) from t1;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5bc0v4my7dvr5, child number 1
-------------------------------------
select count(*) from t1
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| T1 | 1 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
- SQL plan baseline SYS_SQL_PLAN_b98b6a04616acf47 used for this statement
19 rows selected.
#看viwe 能看基本資訊dba_sql_plan_baselines
SQL> set linesize 1000
SQL> select * from dba_sql_plan_baselines where plan_name='SYS_SQL_PLAN_b98b6a04616acf47';
SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME CREATOR ORIGIN PARSING_SCHEMA_NAME DESCRIPTION VERSION CREATED LAST_MODIFIED
---------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ -------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
LAST_EXECUTED LAST_VERIFIED ENA ACC FIX AUT OPTIMIZER_COST MODULE ACTION EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES END_OF_FETCH_COUNT
--------------------------------------------------------------------------- --------------------------------------------------------------------------- --- --- --- --- -------------- ------------------------------------------------ -------------------------------- ---------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ------------------
1.6287E+19 SYS_SQL_e208a16bb98b6a04 select count(*) from t1 SYS_SQL_PLAN_b98b6a04616acf47 XH AUTO-CAPTURE XH 11.1.0.6.0 23-FEB-10 10.57.21.000000 AM 23-FEB-10 10.57.21.000000 AM
23-FEB-10 10.57.27.000000 AM YES YES NO YES 3 SQL*Plus 0 0 0 0 0 0 0 0 0
#用函式看更詳細
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_e208a16bb98b6a04'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_e208a16bb98b6a04
SQL text: select count(*) from t1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_b98b6a04616acf47
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 82 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------
20 rows selected.
#檢視baseline的 所用到的hints
SQL> SELECT extractValue(value(h),'.') AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
4 WHERE so.name = 'SYS_SQL_PLAN_8fb2691f3fdbb376'
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8 AND so.plan_id = od.plan_id;
HINT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_××DED_HINTS
6 rows selected.
SQL>
SQL> col occupant_name for a15
SQL> col move_procedure for a20
SQL> select occupant_name ,move_procedure from v$sysaux_occupants;
OCCUPANT_NAME MOVE_PROCEDURE
--------------- --------------------
LOGMNR SYS.DBMS_LOGMNR_D.SE
T_TABLESPACE
LOGSTDBY SYS.DBMS_LOGSTDBY.SE
T_TABLESPACE
SMON_SCN_TIME
PL/SCOPE
STREAMS
XDB XDB.DBMS_XDB.MOVEXDB
_TABLESPACE
OCCUPANT_NAME MOVE_PROCEDURE
--------------- --------------------
AO DBMS_AW.MOVE_AWMETA
XSOQHIST DBMS_XSOQ.OlapiMoveP
roc
XSAMD DBMS_AMD.Move_OLAP_C
atalog
SM/AWR
SM/ADVISOR
SM/OPTSTAT
OCCUPANT_NAME MOVE_PROCEDURE
--------------- --------------------
SM/OTHER
STATSPACK
SDO MDSYS.MOVE_SDO
WM DBMS_WM.move_proc
ORDIM
ORDIM/PLUGINS
ORDIM/SQLMM
EM emd_maintenance.move
_em_tblspc
TEXT DRI_MOVE_CTXSYS
OCCUPANT_NAME MOVE_PROCEDURE
--------------- --------------------
ULTRASEARCH MOVE_WK
ULTRASEARCH_DEM MOVE_WK
O_USER
EXPRESSION_FILT
ER
EM_MONITORING_U
SER
TSM
OCCUPANT_NAME MOVE_PROCEDURE
--------------- --------------------
SQL_MANAGEMENT_ ~~~~~~~~~~~~~~~~沒有工具可以將sql plan baseline儲存在sysaux之外
BASE
AUTO_TASK
JOB_SCHEDULER
29 rows selected.
查到了存在sysaux中的元件
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-629039/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 11g sql plan baseline(3)演化baselineOracleSQL
- oracle 11g sql plan baseline(4)使用baseline覆蓋hintOracleSQL
- oracle 11g sql plan baseline(1)基本使用OracleSQL
- Oracle SQL Plan Baseline 學習OracleSQL
- sql_plan_baselineSQL
- sql plan baseline（二）SQL
- sql plan baseline使用心得SQL
- oracle 11g sql plan baseline(2)調整baselines屬性OracleSQL
- Oracle SQL baselineOracleSQL
- Oracle OCP 1Z0-053 Q252(SQL Plan Baseline)OracleSQL
- zt_oracle11g sql baseline與sql execution plan執行計劃OracleSQL
- SQL Plan Baseline與Shared Cursor機制研究（一）SQL
- SQL Plan Baseline與Shared Cursor機制研究（二）SQL
- SQL Plan Baseline與Shared Cursor機制研究（三）SQL
- sql pan baselineSQL
- sql profile和baseline的協作關係SQL
- oracle baseline基線_awrOracle
- benchmark和baseline的區別
- 關於Oracle baseline的幾點Oracle
- 關於Oracle Baseline和DBMS_SQLTUNE工具OracleSQL
- baseline固定SQL執行計劃SQL
- Oracle OCP 1Z0 053 Q201(DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE)OracleSQL
- baseline依賴SQL文字還是SQL ID?SQL
- 11203測試sql baselineSQL
- SQL BASELINE修改固定執行計劃SQL
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL
- 終於搞定了vertical-align：baseline對齊的問題
- Oracle 11g SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- Oracle 11g 新特性 -- SQL Plan Management 示例OracleSQL
- align-items:baseline 作用
- 深度剖析Baseline設計原理
- 理解awr中的基準線(baseline)
- 【DBA】Oracle 11g 針對SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- zt_sql baseline_sql profile_sql outline修改固定執行計劃SQL
- SQL SERVER和ORACLE的排序問題SQLServerOracle排序
- 布匹缺陷檢測baseline提升過程
- 控制執行計劃之-SPM BASELINE（六）
- 控制執行計劃之-SPM BASELINE（五）