[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来控制.
--//总之开发还是应该知道合理使用绑定变量的好处.
