[20180301]sql profile 非繫結變數.txt
[20180301]sql profile 非繫結變數.txt
http://www.itpub.net/thread-2097379-1-1.html
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t as select rownum id ,rownum||'test' from dual connect by levle<=1e5;
create unique index pk_t on t(id);
select /*+ full(t) */ * from t where id=42;
--//定位sql_id=657njzq307t7y,執行計劃選擇全表掃描,因為提示使用全表掃描.
2.使用sql profile:
--//執行@ &r/sp1 657njzq307t7y,完成sql profile的分析.關於sp1的指令碼可以參考.http://blog.itpub.net/267265/viewspace-1340660/
SCOTT@book> @ &r/sp1 657njzq307t7y
PL/SQL procedure successfully completed.
=================================================================================================================================================
tuning sql_id=657njzq307t7y : report
=================================================================================================================================================
REPORT_TUNING_TASK
-----------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning 657njzq307t7y
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/01/2018 08:48:47
Completed at : 03/01/2018 08:48:48
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 657njzq307t7y
SQL Text : select /*+ full(t) */ * from t where id=42
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."T" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.92%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning
657njzq307t7y', task_owner => 'SCOTT', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .001198 .000028 97.66 %
CPU Time (s): .001199 .0001 91.65 %
User I/O Time (s): 0 0
Buffer Gets: 279 3 98.92 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 81 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 13 | 81 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=42)
2- Using SQL Profile
--------------------
Plan hash value: 2454218153
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_T | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
-------------------------------------------------------------------------------
=================================================================================================================================================
if finished,drop tuning task , run:
execute dbms_sqltune.drop_tuning_task('tuning 657njzq307t7y')
if accept sql profile, run:
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 657njzq307t7y', replace => TRUE ,name=>'tuning 657njzq307t7y');
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 657njzq307t7y', replace => TRUE, name=>'tuning 657njzq307t7y', FORCE_MATCH=>True)
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'tuning 657njzq307t7y')
execute dbms_sqltune.alter_sql_profile(name => 'tuning 657njzq307t7y',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================
3.測試不同的值是否有效:
SCOTT@book> execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 657njzq307t7y', replace => TRUE, name=>'tuning 657njzq307t7y', FORCE_MATCH=>True);
PL/SQL procedure successfully completed.
SCOTT@book> select /*+ full(t) */ * from t where id=41;
ID NAME
---------- --------------------
41 41test
--//使用不同的值測試看看sql profile是否有效.
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 57rqfa0t3p3rn, child number 0
-------------------------------------
select /*+ full(t) */ * from t where id=41
Plan hash value: 2454218153
-------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=41)
Note
-----
- SQL profile tuning 657njzq307t7y used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
32 rows selected.
--//使用sql profile tuning 657njzq307t7y.
--//換各種方式執行:
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select sql_id,sql_text,EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE,sql_profile from v$sql where FORCE_MATCHING_SIGNATURE=8045308685519010163;
SQL_ID SQL_TEXT EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE SQL_PROFILE
------------- ------------------------------------------------------------ ------------------------ ------------------------ ---------------------
5pjxf22kq49cd select /*+ full(t) */ * from t where id=1 9666810874799682817 8045308685519010163 tuning 657njzq307t7y
57rqfa0t3p3rn select /*+ full(t) */ * from t where id=41 7008154288030403093 8045308685519010163 tuning 657njzq307t7y
7grmh3jz0dcyc select /*+ full(t) */ * from t where id= 21 4036573941054301337 8045308685519010163 tuning 657njzq307t7y
agpq3dtz7kpnm Select /*+ full(t) */ * from t where id= 21 4036573941054301337 8045308685519010163 tuning 657njzq307t7y
gukbt51cn7f76 select /*+ full(t) */ * from t where id=21 4036573941054301337 8045308685519010163 tuning 657njzq307t7y
657njzq307t7y select /*+ full(t) */ * from t where id=42 4762379214270820258 8045308685519010163 tuning 657njzq307t7y
0ab2yuh6t6fm8 Select /*+ Full(t) */ * from t where id= 21 4036573941054301337 8045308685519010163 tuning 657njzq307t7y
6 rows selected.
--//可以發現使用非繫結變數,,sql profile 還是選擇正確的執行計劃.只要FORCE_MATCHING_SIGNATURE一樣,都可以選擇正確的執行計劃.
--//只要在接受sql profile時FORCE_MATCH=>True,這樣相關非繫結變數的語句都會使用指定的執行計劃.
--//ZALBB的想法我的理解就是透過sql profile,原來有多個執行計劃的語句,透過這樣的方式選擇正確的執行計劃,也減少了子游標.也減少了硬解析.
--//不知道我的理解是否正確.
--//更正一些錯誤:使用sql profile並不能減少硬解析,要想減少修改語句為繫結變數,或者透過cursor_sharing=force來控制.
--//總之開發還是應該知道合理使用繫結變數的好處.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2151389/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於sql_profile中的繫結變數SQL變數
- SQL Server動態SQL，繫結變數SQLServer變數
- 利用FORCE_MATCHING_SIGNATURE捕獲非繫結變數SQL變數SQL
- SQL Developer中使用繫結變數SQLDeveloper變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- 【SQL 調優】繫結變數窺測SQL變數
- 【sql調優】繫結變數與CBOSQL變數
- 【sql調優】使用繫結變數（二）SQL變數
- 【sql調優】使用繫結變數（一）SQL變數
- 檢視未繫結變數的sql變數SQL
- 獲取sql繫結變數的值SQL變數
- 統計沒有繫結變數SQL變數SQL
- 關於高效捕獲資料庫非繫結變數的SQL語句資料庫變數SQL
- 繫結變數變數
- [20170929]& 代替冒號繫結變數.txt變數
- [20160706]like % 繫結變數.txt變數
- 關於pl/sql中的繫結變數SQL變數
- [SQL]oracle 繫結變數(bind variable)[轉載]SQLOracle變數
- SQL使用繫結變數，測試例項。SQL變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- oracle對非使用繫結變數的語句去重Oracle變數
- [20171231]PLSQL使用繫結變數.txtSQL變數
- 繫結變數在靜態sql和動態sql中變數SQL
- Oracle 繫結變數Oracle變數
- 統計未用繫結變數的sql語句變數SQL
- oracle 查詢未使用繫結變數的sqlOracle變數SQL
- v$sql_bind_capture 獲取繫結變數SQLAPT變數
- [20180930]in list與繫結變數個數.txt變數
- [20180912]PLSLQ與繫結變數.txt變數
- [20180930]in list與繫結變數.txt變數
- SQL*Plus中使用DATE型別的繫結變數SQL型別變數
- v$sql_bind_capture 獲取繫結變數資訊SQLAPT變數
- 查詢沒有使用繫結變數的sql zt變數SQL
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數