在Oracle中，如何使用STA来生成SQL Profile？
♣答案部分
利用STA对语句进行优化后，STA会对语句进行分析，采用最优的优化策略，并给出优化后的查询计划。可以按照STA给出的建议重写语句。但是，有些情况下，你可能无法重写语句（比如在生产环境中，SQL语句又在一个包中）。这个时候就可以利用Sql Profile，将优化策略存储在Profile中，Oracle在构建这条语句的查询计划时，就不会使用已有相关统计数据，而使用Profile的策略，生成新的查询计划。
第一步：给用户赋权限
1[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba 2 3SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016 4 5Copyright (c) 1982, 2013, Oracle. All rights reserved. 6 7 8Connected to: 9Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 10With the Partitioning, Real Application Clusters, OLAP, Data Mining 11and Real Application Testing options 12 13SYS@dlhr> 14SYS@dlhr> 15SYS@dlhr> 16SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR; 17 18Grant succeeded. 19 20SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR; 21 22Grant succeeded. 23 24SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR; 25 26Grant succeeded. 27 28SYS@dlhr> conn lhr/lhr 29Connected. 30LHR@dlhr> 31LHR@dlhr> select * from v$version; 32 33BANNER 34-------------------------------------------------------------------------------- 35Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 36PL/SQL Release 11.2.0.4.0 - Production 37CORE 11.2.0.4.0 Production 38TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production 39NLSRTL Version 11.2.0.4.0 - Production 40 41LHR@dlhr> create table lhr.TB_LHR_20160525_01 as select * from dba_objects; 42 43Table created. 44 45LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id); 46 47Index created. 48 49LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4); 50 51PL/SQL procedure successfully completed. 52 53LHR@dlhr> set autot on 54LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; 55 56 COUNT(*) 57---------- 58 1 59 60 61Execution Plan 62---------------------------------------------------------- 63Plan hash value: 3612989399 64 65----------------------------------------------------------------------------------------- 66| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 67----------------------------------------------------------------------------------------- 68| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 | 69| 1 | SORT AGGREGATE | | 1 | 5 | | | 70|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 | 71----------------------------------------------------------------------------------------- 72 73Predicate Information (identified by operation id): 74--------------------------------------------------- 75 76 2 - filter("OBJECT_ID"=100) 77 78 79Statistics 80---------------------------------------------------------- 81 1 recursive calls 82 0 db block gets 83 1249 consistent gets 84 0 physical reads 85 0 redo size 86 526 bytes sent via SQL*Net to client 87 520 bytes received via SQL*Net from client 88 2 SQL*Net roundtrips to/from client 89 0 sorts (memory) 90 0 sorts (disk) 91 1 rows processed 92LHR@dlhr> set autot off 93LHR@dlhr> SELECT v.SQL_ID, v.SQL_TEXT FROM v$sql v WHERE v.SQL_TEXT like '%no_index(TB_LHR_20160525_01%' and v.SQL_TEXT not like '%v$sql%' ; 94 95SQL_ID 96------------- 97SQL_TEXT 98------------------------------------------------------------------------------------------------------------------------------------ 997jt1btjkcczb8100select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 1001011027suktf0w95cry103EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L104HR_20160525_01 where object_id = 100 2 3SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016 4 5Copyright (c) 1982, 2013, Oracle. All rights reserved. 6 7 8Connected to: 9Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 10With the Partitioning, Real Application Clusters, OLAP, Data Mining 11and Real Application Testing options 12 13SYS@dlhr> 14SYS@dlhr> 15SYS@dlhr> 16SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR; 17 18Grant succeeded. 19 20SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR; 21 22Grant succeeded. 23 24SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR; 25 26Grant succeeded. 27 28SYS@dlhr> conn lhr/lhr 29Connected. 30LHR@dlhr> 31LHR@dlhr> select * from v$version; 32 33BANNER 34-------------------------------------------------------------------------------- 35Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 36PL/SQL Release 11.2.0.4.0 - Production 37CORE 11.2.0.4.0 Production 38TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production 39NLSRTL Version 11.2.0.4.0 - Production 40 41LHR@dlhr> create table lhr.TB_LHR_20160525_01 as select * from dba_objects; 42 43Table created. 44 45LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id); 46 47Index created. 48 49LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4); 50 51PL/SQL procedure successfully completed. 52 53LHR@dlhr> set autot on 54LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; 55 56 COUNT(*) 57---------- 58 1 59 60 61Execution Plan 62---------------------------------------------------------- 63Plan hash value: 3612989399 64 65----------------------------------------------------------------------------------------- 66| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 67----------------------------------------------------------------------------------------- 68| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 | 69| 1 | SORT AGGREGATE | | 1 | 5 | | | 70|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 | 71----------------------------------------------------------------------------------------- 72 73Predicate Information (identified by operation id): 74--------------------------------------------------- 75 76 2 - filter("OBJECT_ID"=100) 77 78 79Statistics 80---------------------------------------------------------- 81 1 recursive calls 82 0 db block gets 83 1249 consistent gets 84 0 physical reads 85 0 redo size 86 526 bytes sent via SQL*Net to client 87 520 bytes received via SQL*Net from client 88 2 SQL*Net roundtrips to/from client 89 0 sorts (memory) 90 0 sorts (disk) 91 1 rows processed 92LHR@dlhr> set autot off 93LHR@dlhr> SELECT v.SQL_ID, v.SQL_TEXT FROM v$sql v WHERE v.SQL_TEXT like '%no_index(TB_LHR_20160525_01%' and v.SQL_TEXT not like '%v$sql%' ; 94 95SQL_ID 96------------- 97SQL_TEXT 98------------------------------------------------------------------------------------------------------------------------------------ 997jt1btjkcczb8 100select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 101 1027suktf0w95cry 103EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L 104HR_20160525_01 where object_id = 100
第二步：创建、执行优化任务
1LHR@dlhr> DECLARE 2 2 my_task_name VARCHAR2(30); 3 3 my_sqltext CLOB; 4 4 BEGIN 5 5 my_sqltext := 'select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100'; 6 6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 7 sql_text => my_sqltext, 8 8 user_name => 'LHR', 9 9 scope => 'COMPREHENSIVE',10 10 time_limit => 60,11 11 task_name => 'sql_profile_test',12 12 description => 'Task to tune a query on a specified table');13 13 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');14 14 END;15 15 /1617PL/SQL procedure successfully completed.DECLARE 2 2 my_task_name VARCHAR2(30); 3 3 my_sqltext CLOB; 4 4 BEGIN 5 5 my_sqltext := 'select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100'; 6 6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 7 sql_text => my_sqltext, 8 8 user_name => 'LHR', 9 9 scope => 'COMPREHENSIVE', 10 10 time_limit => 60, 11 11 task_name => 'sql_profile_test', 12 12 description => 'Task to tune a query on a specified table'); 13 13 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test'); 14 14 END; 15 15 / 16 17PL/SQL procedure successfully completed.
或者也可以使用sqlid来生成优化任务，如下：
1LHR@dlhr> DECLARE 2 2 a_tuning_task VARCHAR2(30); 3 3 BEGIN 4 4 a_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '7jt1btjkcczb8', 5 5 task_name => 'sql_profile_test_SQLID'); 6 6 dbms_sqltune.execute_tuning_task(a_tuning_task); 7 7 END; 8 8 / 910PL/SQL procedure successfully completed.DECLARE 2 2 a_tuning_task VARCHAR2(30); 3 3 BEGIN 4 4 a_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '7jt1btjkcczb8', 5 5 task_name => 'sql_profile_test_SQLID'); 6 6 dbms_sqltune.execute_tuning_task(a_tuning_task); 7 7 END; 8 8 / 9 10PL/SQL procedure successfully completed.
第三步：查看优化建议
1LHR@dlhr> set autot off 2LHR@dlhr> set long 10000 3LHR@dlhr> set longchunksize 1000 4LHR@dlhr> set linesize 100 5LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL; 6 7DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') 8---------------------------------------------------------------------------------------------------- 9GENERAL INFORMATION SECTION 10------------------------------------------------------------------------------- 11Tuning Task Name : sql_profile_test 12Tuning Task Owner : LHR 13Workload Type : Single SQL Statement 14Scope : COMPREHENSIVE 15Time Limit(seconds): 60 16Completion Status : COMPLETED 17Started at : 05/25/2016 16:58:31 18Completed at : 05/25/2016 16:58:32 19 20------------------------------------------------------------------------------- 21Schema Name: LHR 22SQL ID : 9kzm8scz6t92z 23SQL Text : select /*+no_index(TB_LHR_20160525_01 24 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 25 where object_id = 100 26 27------------------------------------------------------------------------------- 28FINDINGS SECTION (1 finding) 29------------------------------------------------------------------------------- 30 311- SQL Profile Finding (see explain plans section below) 32-------------------------------------------------------- 33 A potentially better execution plan was found for this statement. 34 35 Recommendation (estimated benefit: 99.83%) 36 ------------------------------------------ 37 - Consider accepting the recommended SQL profile. 38 execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test', 39 task_owner => 'LHR', replace => TRUE); 40 41 Validation results 42 ------------------ 43 The SQL profile was tested by executing both its plan and the original plan 44 and measuring their respective execution statistics. A plan may have been 45 only partially executed if the other could be run to completion in less time. 46 47 Original Plan With SQL Profile % Improved 48 ------------- ---------------- ---------- 49 Completion Status: COMPLETE COMPLETE 50 Elapsed Time (s): .006278 .00004 99.36 % 51 CPU Time (s): .003397 .000021 99.38 % 52 User I/O Time (s): 0 0 53 Buffer Gets: 1249 2 99.83 % 54 Physical Read Requests: 0 0 55 Physical Write Requests: 0 0 56 57DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') 58---------------------------------------------------------------------------------------------------- 59 Physical Read Bytes: 0 0 60 Physical Write Bytes: 0 0 61 Rows Processed: 1 1 62 Fetches: 1 1 63 Executions: 1 1 64 65 Notes 66 ----- 67 1. Statistics for the original plan were averaged over 10 executions. 68 2. Statistics for the SQL profile plan were averaged over 10 executions. 69 70------------------------------------------------------------------------------- 71EXPLAIN PLANS SECTION 72------------------------------------------------------------------------------- 73 741- Original With Adjusted Cost 75------------------------------ 76Plan hash value: 3612989399 77 78----------------------------------------------------------------------------------------- 79| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 80----------------------------------------------------------------------------------------- 81| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 | 82| 1 | SORT AGGREGATE | | 1 | 5 | | | 83|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 | 84----------------------------------------------------------------------------------------- 85 86Predicate Information (identified by operation id): 87--------------------------------------------------- 88 89 2 - filter("OBJECT_ID"=100) 90 912- Using SQL Profile 92-------------------- 93Plan hash value: 661515879 94 95-------------------------------------------------------------------------------------------- 96| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 97-------------------------------------------------------------------------------------------- 98| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | 99| 1 | SORT AGGREGATE | | 1 | 5 | | |100|* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 |101--------------------------------------------------------------------------------------------102103Predicate Information (identified by operation id):104---------------------------------------------------105106107DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')108----------------------------------------------------------------------------------------------------109 2 - access("OBJECT_ID"=100)110111-------------------------------------------------------------------------------set autot off 2LHR@dlhr> set long 10000 3LHR@dlhr> set longchunksize 1000 4LHR@dlhr> set linesize 100 5LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL; 6 7DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') 8---------------------------------------------------------------------------------------------------- 9GENERAL INFORMATION SECTION 10------------------------------------------------------------------------------- 11Tuning Task Name : sql_profile_test 12Tuning Task Owner : LHR 13Workload Type : Single SQL Statement 14Scope : COMPREHENSIVE 15Time Limit(seconds): 60 16Completion Status : COMPLETED 17Started at : 05/25/2016 16:58:31 18Completed at : 05/25/2016 16:58:32 19 20------------------------------------------------------------------------------- 21Schema Name: LHR 22SQL ID : 9kzm8scz6t92z 23SQL Text : select /*+no_index(TB_LHR_20160525_01 24 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 25 where object_id = 100 26 27------------------------------------------------------------------------------- 28FINDINGS SECTION (1 finding) 29------------------------------------------------------------------------------- 30 311- SQL Profile Finding (see explain plans section below) 32-------------------------------------------------------- 33 A potentially better execution plan was found for this statement. 34 35 Recommendation (estimated benefit: 99.83%) 36 ------------------------------------------ 37 - Consider accepting the recommended SQL profile. 38 execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test', 39 task_owner => 'LHR', replace => TRUE); 40 41 Validation results 42 ------------------ 43 The SQL profile was tested by executing both its plan and the original plan 44 and measuring their respective execution statistics. A plan may have been 45 only partially executed if the other could be run to completion in less time. 46 47 Original Plan With SQL Profile % Improved 48 ------------- ---------------- ---------- 49 Completion Status: COMPLETE COMPLETE 50 Elapsed Time (s): .006278 .00004 99.36 % 51 CPU Time (s): .003397 .000021 99.38 % 52 User I/O Time (s): 0 0 53 Buffer Gets: 1249 2 99.83 % 54 Physical Read Requests: 0 0 55 Physical Write Requests: 0 0 56 57DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') 58---------------------------------------------------------------------------------------------------- 59 Physical Read Bytes: 0 0 60 Physical Write Bytes: 0 0 61 Rows Processed: 1 1 62 Fetches: 1 1 63 Executions: 1 1 64 65 Notes 66 ----- 67 1. Statistics for the original plan were averaged over 10 executions. 68 2. Statistics for the SQL profile plan were averaged over 10 executions. 69 70------------------------------------------------------------------------------- 71EXPLAIN PLANS SECTION 72------------------------------------------------------------------------------- 73 741- Original With Adjusted Cost 75------------------------------ 76Plan hash value: 3612989399 77 78----------------------------------------------------------------------------------------- 79| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 80----------------------------------------------------------------------------------------- 81| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 | 82| 1 | SORT AGGREGATE | | 1 | 5 | | | 83|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 | 84----------------------------------------------------------------------------------------- 85 86Predicate Information (identified by operation id): 87--------------------------------------------------- 88 89 2 - filter("OBJECT_ID"=100) 90 912- Using SQL Profile 92-------------------- 93Plan hash value: 661515879 94 95-------------------------------------------------------------------------------------------- 96| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 97-------------------------------------------------------------------------------------------- 98| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | 99| 1 | SORT AGGREGATE | | 1 | 5 | | | 100|* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 | 101-------------------------------------------------------------------------------------------- 102 103Predicate Information (identified by operation id): 104--------------------------------------------------- 105 106 107DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') 108---------------------------------------------------------------------------------------------------- 109 2 - access("OBJECT_ID"=100) 110 111-------------------------------------------------------------------------------
这里可以看到，在优化建议中给出了新的查询计划。现在，我们决定接受这个建议，并且不重写语句。
第四步：接受profile
1LHR@dlhr> set autot on 2LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; 3 4 COUNT(*) 5---------- 6 1 7 8 9Execution Plan10----------------------------------------------------------1112Plan hash value: 36129893991314-----------------------------------------------------------------------------------------15| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |16-----------------------------------------------------------------------------------------17| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 |18| 1 | SORT AGGREGATE | | 1 | 5 | | |19|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 |20-----------------------------------------------------------------------------------------2122Predicate Information (identified by operation id):23---------------------------------------------------2425 2 - filter("OBJECT_ID"=100)262728Statistics29----------------------------------------------------------30 0 recursive calls31 0 db block gets32 1249 consistent gets33 0 physical reads34 0 redo size35 526 bytes sent via SQL*Net to client36 520 bytes received via SQL*Net from client37 2 SQL*Net roundtrips to/from client38 0 sorts (memory)39 0 sorts (disk)40 1 rows processed41LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE);4243PL/SQL procedure successfully completed.4445LHR@dlhr> set autot off46LHR@dlhr> SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints47 2 FROM dba_sql_profiles d,48 3 dba_advisor_tasks e,49 4 SYS.SQLOBJ$DATA A,50 5 SYS.SQLOBJ$ B,51 6 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),52 7 '/outline_data/hint'))) h53 8 where a.signature = b.signature54 9 and a.category = b.category55 10 and a.obj_type = b.obj_type56 11 and a.plan_id = b.plan_id57 12 and a.signature = d.signature 58 13 and d.task_id=e.task_id59 14 and d.name = 'SYS_SQLPROF_0154e728ad3f0000'60 15 ;6162TASK_NAME NAME63------------------------------ ------------------------------64SQL_TEXT65----------------------------------------------------------------------------------------------------66HINTS67----------------------------------------------------------------------------------------------------68sql_profile_test SYS_SQLPROF_0154e728ad3f000069select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_0170where object_id = 10071OPTIMIZER_FEATURES_ENABLE(default)7273sql_profile_test SYS_SQLPROF_0154e728ad3f000074select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_0175where object_id = 10076IGNORE_OPTIM_EMBEDDED_HINTSset autot on 2LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; 3 4 COUNT(*) 5---------- 6 1 7 8 9Execution Plan 10---------------------------------------------------------- 11 12Plan hash value: 3612989399 13 14----------------------------------------------------------------------------------------- 15| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 16----------------------------------------------------------------------------------------- 17| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 | 18| 1 | SORT AGGREGATE | | 1 | 5 | | | 19|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 | 20----------------------------------------------------------------------------------------- 21 22Predicate Information (identified by operation id): 23--------------------------------------------------- 24 25 2 - filter("OBJECT_ID"=100) 26 27 28Statistics 29---------------------------------------------------------- 30 0 recursive calls 31 0 db block gets 32 1249 consistent gets 33 0 physical reads 34 0 redo size 35 526 bytes sent via SQL*Net to client 36 520 bytes received via SQL*Net from client 37 2 SQL*Net roundtrips to/from client 38 0 sorts (memory) 39 0 sorts (disk) 40 1 rows processed 41LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE); 42 43PL/SQL procedure successfully completed. 44 45LHR@dlhr> set autot off 46LHR@dlhr> SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints 47 2 FROM dba_sql_profiles d, 48 3 dba_advisor_tasks e, 49 4 SYS.SQLOBJ$DATA A, 50 5 SYS.SQLOBJ$ B, 51 6 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA), 52 7 '/outline_data/hint'))) h 53 8 where a.signature = b.signature 54 9 and a.category = b.category 55 10 and a.obj_type = b.obj_type 56 11 and a.plan_id = b.plan_id 57 12 and a.signature = d.signature 58 13 and d.task_id=e.task_id 59 14 and d.name = 'SYS_SQLPROF_0154e728ad3f0000' 60 15 ; 61 62TASK_NAME NAME 63------------------------------ ------------------------------ 64SQL_TEXT 65---------------------------------------------------------------------------------------------------- 66HINTS 67---------------------------------------------------------------------------------------------------- 68sql_profile_test SYS_SQLPROF_0154e728ad3f0000 69select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 70where object_id = 100 71OPTIMIZER_FEATURES_ENABLE(default) 72 73sql_profile_test SYS_SQLPROF_0154e728ad3f0000 74select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 75where object_id = 100 76IGNORE_OPTIM_EMBEDDED_HINTS
在这里用了包DBMS_SQLTUNE的另一个函数：ACCEPT_SQL_PROFILE。其中，参数task_name即我们创建的优化建议任务的名称，name是profile的名字，可以是任意合法名称。此外这个函数还有其他一些函数，下面是这个函数的原型：
1DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 2 task_name IN VARCHAR2, 3 object_id IN NUMBER := NULL, 4 name IN VARCHAR2 := NULL, 5 description IN VARCHAR2 := NULL, 6 category IN VARCHAR2 := NULL; 7 task_owner IN VARCHAR2 := NULL, 8 replace IN BOOLEAN := FALSE, 9 force_match IN BOOLEAN := FALSE)10 RETURN VARCHAR2; 2 task_name IN VARCHAR2, 3 object_id IN NUMBER := NULL, 4 name IN VARCHAR2 := NULL, 5 description IN VARCHAR2 := NULL, 6 category IN VARCHAR2 := NULL; 7 task_owner IN VARCHAR2 := NULL, 8 replace IN BOOLEAN := FALSE, 9 force_match IN BOOLEAN := FALSE) 10 RETURN VARCHAR2;
Description是profile的描述信息；task_owner是优化建议任务的所有者；replace为TRUE时，如果这个profile已经存在，就代替它；force_match为TURE时，表示与语句强制匹配，即强制使用绑定变量，和系统参数cursor_sharing设置为FORCE时类似，为FALSE时，与cursor_sharing设置为EXACT时类似，即完全匹配。
这里要特别提到的是category这个参数，你可以通过设置这个参数，制定特定会话使用这个profile。在10g中，每个会话都有一个新参数SQLTUNE_CATEGORY，他的默认值是DEFAULT。而我们在调用这个函数时，如果没有指定这个参数，那它的值也是DEFAULT，而如果我们给这个profile指定了一个其它的CATEGORY值，如FOR_TUNING，那么只有会话参SQLTUNE_CATEGORY也为FOR_TUNING时，才会使用这个porfile。为什么说这个参数很有用呢？试想一个这样的环境：你在一个生产系统上利用STA调优一条语句，STA已经给出了优化建议，但是你又不敢贸然实施它给出的建议（毕竟它只是机器嘛，不能完全信任），你就可以创建一个有特殊CATEGORY的profile，然后在你自己的会话中制定SQLTUNE_CATEGORY为这个特殊的CATEGORY，那就既可以看优化建议的实际效果又不影响生产环境。
此外可以通过视图DBA_SQL_PROFILES来查看已经创建的profile。
第五步：查看profile的效果
1LHR@dlhr> set autot on 2LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; 3 4 COUNT(*) 5---------- 6 1 7 8 9Execution Plan10----------------------------------------------------------11Plan hash value: 6615158791213--------------------------------------------------------------------------------------------14| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |15--------------------------------------------------------------------------------------------16| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |17| 1 | SORT AGGREGATE | | 1 | 5 | | |18|* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 |19--------------------------------------------------------------------------------------------2021Predicate Information (identified by operation id):22---------------------------------------------------2324 2 - access("OBJECT_ID"=100)2526Note27-----28 - SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement293031Statistics32----------------------------------------------------------33 1 recursive calls34 0 db block gets35 2 consistent gets36 0 physical reads37 0 redo size38 526 bytes sent via SQL*Net to client39 520 bytes received via SQL*Net from client40 2 SQL*Net roundtrips to/from client41 0 sorts (memory)42 0 sorts (disk)43 1 rows processedset autot on 2LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; 3 4 COUNT(*) 5---------- 6 1 7 8 9Execution Plan 10---------------------------------------------------------- 11Plan hash value: 661515879 12 13-------------------------------------------------------------------------------------------- 14| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 15-------------------------------------------------------------------------------------------- 16| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | 17| 1 | SORT AGGREGATE | | 1 | 5 | | | 18|* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 | 19-------------------------------------------------------------------------------------------- 20 21Predicate Information (identified by operation id): 22--------------------------------------------------- 23 24 2 - access("OBJECT_ID"=100) 25 26Note 27----- 28 - SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement 29 30 31Statistics 32---------------------------------------------------------- 33 1 recursive calls 34 0 db block gets 35 2 consistent gets 36 0 physical reads 37 0 redo size 38 526 bytes sent via SQL*Net to client 39 520 bytes received via SQL*Net from client 40 2 SQL*Net roundtrips to/from client 41 0 sorts (memory) 42 0 sorts (disk) 43 1 rows processed
从NOTE部分可以看到，语句采用了profile中的数据，创建了新的查询计划。并且在查询计划中还有一些附加信息，表明这个语句是采用了“SYS_SQLPROF_0154e728ad3f0000”这个Profile，而不是根据对象上面的统计数据来生成的查询计划。
但上述方法主要是依赖sql tuning advisor,如果它无法生成你想要的执行计划.你还可以通过手动的方式,通过sql profile把hint加进去.复杂的SQL的hint可以采用脚本coe_xfr_sql_profile.sql来产生原语句的outline data和加hint语句的outline data，然后替换对应的SYS.SQLPROF_ATTR，最后执行生成的sql就可以了。
使用PLSQL DEVELOPER 11查看执行计划，如下图，新版本的好处：
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧
