SQL Profile 實驗03
實驗說明：手動固定執行計劃
實驗描述：讓走索引的執行計劃，透過手動固定Profile走全表掃描
##刪除原來的執行計劃
exec dbms_sqltune.drop_sql_profile('coe_0gmwrzua0usax_463314188');
##正常執行計劃，走索引T3_INDEX
SQL> select count(*) from t3 where object_id<=5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 129501593
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| T3_INDEX | 2245 | 29185 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<=5000)
Note
-----
- dynamic sampling used for this statement (level=2)
##增加hint，強制走全表掃描
SQL> select /*+full(t3)*/ count(*) from t3 where object_id<=5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 277 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T3 | 2245 | 29185 | 277 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=5000)
Note
-----
- dynamic sampling used for this statement (level=2)
##檢視SQL_ID等資訊
col sql_id for a20;
col sql_text for a100;
set linesize 200;
set pagesize 20000;
conn /as sysdba;
SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%count(*)%t3%where%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
-------------------- --------------- ---------------------------------------------------------------------------------------------------- <=5000
0gmwrzua0usax 129501593 select count(*) from t3 where object_id<=5000
fdv618tykuv26 463314188 select /*+full(t3)*/ count(*) from t3 where object_id<=500
##手動收集B語句（走hint的SQL）outline資訊
col PLAN_TABLE_OUTPUT for a100;
SQL> select * from table(dbms_xplan.display_cursor('fdv618tykuv26',null,'outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID fdv618tykuv26, child number 0
-------------------------------------
select /*+full(t3)*/ count(*) from t3 where object_id<=5000
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 277 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T3 | 2245 | 29185 | 277 (1)| 00:00:04 |
---------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T3"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=5000)
Note
-----
- dynamic sampling used for this statement (level=2)
37 rows selected.
##建立Profile並匯入
declare
h sys.sqlprof_attr;
sql_txt clob;
begin
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', ##此處需要注意單引號，可以透過q'[]'或者兩個單引號轉義''11.2.0.4''（此處為四個單引號）
q'[DB_VERSION('11.2.0.4')]', ##此處需要注意單引號，可以透過q'[]'或者兩個單引號轉義''11.2.0.4''（此處為四個單引號）
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "T3"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
select sql_fulltext into sql_txt from v$sql where sql_id='0gmwrzua0usax';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => sql_txt,
profile => h,
name => 'SQLPROF_Manual',
description => 'Manual SQLProfile',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE);
end;
/
##重新檢視執行計劃
SQL> select count(*) from t3 where object_id<=5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 277 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T3 | 3606 | 18030 | 277 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=5000)
Note
-----
- SQL profile "SQLPROF_Manual" used for this statement
##附錄資訊
##重新收集統計資訊
SQL> exec dbms_stats.gather_table_stats('scott','T3',cascade=>true);
##dbms_sqltune.accept_sql_profile 中force_match的作用 ，force_match的預設值為false，表示只有在sql文字完全一致的情況下才會應用sql_profile，這種情況下只要目標sql的sql文字發生一點改動，原來的profile將失去作用，即變數值更改影響SQL執行計劃 。force_match的改為true，就相當於目標sql的where條件中的具體的輸入值用繫結變數替換了，即變數值更改不影響SQL執行計劃 。
##oracle本身預設的是單引號，但是在大家寫儲存過程或者寫SQL語句時，有時候需要拼SQL或者是SQL的值裡需要傳入含單引號的值，此時就需要使用兩個單引號"''"來進行轉義，其實oracle本身提供了這種轉換預設單引號為其他標識的方法那就是——"q" 語法為q [Oracle's quote operator]
declare
h sys.sqlprof_attr;
sql_txt clob;
begin
h := SYS.SQLPROF_ATTR(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')',
'DB_VERSION(''11.2.0.4'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "T3"@"SEL$1")',
'END_OUTLINE_DATA');
select sql_fulltext into sql_txt from v$sql where sql_id='0gmwrzua0usax';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => sql_txt,
profile => h,
name => 'SQLPROF_Manual',
description => 'Manual SQLProfile',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE);
end;
/
實驗描述：讓走索引的執行計劃，透過手動固定Profile走全表掃描
##刪除原來的執行計劃
exec dbms_sqltune.drop_sql_profile('coe_0gmwrzua0usax_463314188');
##正常執行計劃，走索引T3_INDEX
SQL> select count(*) from t3 where object_id<=5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 129501593
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| T3_INDEX | 2245 | 29185 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<=5000)
Note
-----
- dynamic sampling used for this statement (level=2)
##增加hint，強制走全表掃描
SQL> select /*+full(t3)*/ count(*) from t3 where object_id<=5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 277 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T3 | 2245 | 29185 | 277 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=5000)
Note
-----
- dynamic sampling used for this statement (level=2)
##檢視SQL_ID等資訊
col sql_id for a20;
col sql_text for a100;
set linesize 200;
set pagesize 20000;
conn /as sysdba;
SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%count(*)%t3%where%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
-------------------- --------------- ---------------------------------------------------------------------------------------------------- <=5000
0gmwrzua0usax 129501593 select count(*) from t3 where object_id<=5000
fdv618tykuv26 463314188 select /*+full(t3)*/ count(*) from t3 where object_id<=500
##手動收集B語句（走hint的SQL）outline資訊
col PLAN_TABLE_OUTPUT for a100;
SQL> select * from table(dbms_xplan.display_cursor('fdv618tykuv26',null,'outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID fdv618tykuv26, child number 0
-------------------------------------
select /*+full(t3)*/ count(*) from t3 where object_id<=5000
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 277 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T3 | 2245 | 29185 | 277 (1)| 00:00:04 |
---------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T3"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=5000)
Note
-----
- dynamic sampling used for this statement (level=2)
37 rows selected.
##建立Profile並匯入
declare
h sys.sqlprof_attr;
sql_txt clob;
begin
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', ##此處需要注意單引號，可以透過q'[]'或者兩個單引號轉義''11.2.0.4''（此處為四個單引號）
q'[DB_VERSION('11.2.0.4')]', ##此處需要注意單引號，可以透過q'[]'或者兩個單引號轉義''11.2.0.4''（此處為四個單引號）
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "T3"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
select sql_fulltext into sql_txt from v$sql where sql_id='0gmwrzua0usax';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => sql_txt,
profile => h,
name => 'SQLPROF_Manual',
description => 'Manual SQLProfile',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE);
end;
/
##重新檢視執行計劃
SQL> select count(*) from t3 where object_id<=5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 277 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T3 | 3606 | 18030 | 277 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=5000)
Note
-----
- SQL profile "SQLPROF_Manual" used for this statement
##附錄資訊
##重新收集統計資訊
SQL> exec dbms_stats.gather_table_stats('scott','T3',cascade=>true);
##dbms_sqltune.accept_sql_profile 中force_match的作用 ，force_match的預設值為false，表示只有在sql文字完全一致的情況下才會應用sql_profile，這種情況下只要目標sql的sql文字發生一點改動，原來的profile將失去作用，即變數值更改影響SQL執行計劃 。force_match的改為true，就相當於目標sql的where條件中的具體的輸入值用繫結變數替換了，即變數值更改不影響SQL執行計劃 。
##oracle本身預設的是單引號，但是在大家寫儲存過程或者寫SQL語句時，有時候需要拼SQL或者是SQL的值裡需要傳入含單引號的值，此時就需要使用兩個單引號"''"來進行轉義，其實oracle本身提供了這種轉換預設單引號為其他標識的方法那就是——"q" 語法為q [Oracle's quote operator]
declare
h sys.sqlprof_attr;
sql_txt clob;
begin
h := SYS.SQLPROF_ATTR(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')',
'DB_VERSION(''11.2.0.4'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "T3"@"SEL$1")',
'END_OUTLINE_DATA');
select sql_fulltext into sql_txt from v$sql where sql_id='0gmwrzua0usax';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => sql_txt,
profile => h,
name => 'SQLPROF_Manual',
description => 'Manual SQLProfile',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE);
end;
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27067062/viewspace-2075677/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL Profile 實驗02SQL
- SQL Profile 實驗01SQL
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- sql profileSQL
- sql profile使用SQL
- Oracle實驗(03)：number的使用Oracle
- oracle profile 試驗Oracle
- ORACLE SQL PROFILE使用OracleSQL
- SQL PROFILE 測試SQL
- sql profile的使用SQL
- sql tuning task和sql profileSQL
- 使用sql profile實現outline的功能SQL
- SQL Server profile使用技巧SQLServer
- 自動/手動型別sql_profile(dbms_sqltune/coe_xfr_sql_profile.sql)型別SQL
- SQL Profile（第四篇）SQL
- SQL Profile（第三篇）SQL
- SQL Profile（第二篇）SQL
- sql_profile的使用(一)SQL
- 使用SQL Profile進行SQL優化案例SQL優化
- 【實驗】使用PRODUCT_USER_PROFILE禁止特定使用者在SQL*Plus中使用delete語句SQLdelete
- sql monitoring實驗SQL
- 1223 result cache,sql profile,sql patchSQL
- 使用SQL Profile進行SQL最佳化案例SQL
- SQL Profile（第一篇）SQL
- 檢視SQL PROFILE使用的HINTSQL
- sql最佳化：使用sql profile最佳化sql語句SQL
- oracle 通過sql profile為sql語句加hintOracleSQL
- 通過SQL PROFILE自動優化SQL語句SQL優化
- SQL PROFILE修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- PL/SQL 03 流程控制SQL
- sql tuning advisor(STA) 建議 建立sql profileSQL
- oracle實用sql(11)--收集一週各時段的load profileOracleSQL
- MySQL Profile檢視SQL的資源使用MySql
- 控制執行計劃之-SQL Profile（一）SQL
- DBMS_SQLTUNE詳解(2)---sql_profileSQL
- 用sql profile來固定執行計劃SQL
- 03-25實驗一、命令解釋程式的編寫