https://www.askmaclean.com/archives/sqltuning-sql-profile.html ‎  
  • 以前的SQL调优
  • SQL Profile是什么
  • 调优执行顺序
  • 经常被问到的问题
  • 总结
  ＜一般而言SQL的调优流程＞
  1. 诊断/指定瓶颈
  2. 执行合适的调优
  3. 检查效果　　
  4. 根据需要进一步调优
・Automatic Database Diagnostic Monitor（ADDM） ADDM是监视/诊断数据库性能的功能。 发现内存不足以及I/O问题、性能较差的SQL、Real Application Clusters（RAC）相关的问题等等各种问题，给数据库管理者提供建议。 那时，会为数据库管理者提供解决问题所需要进行的操作提出建议，数据库管理者就可以高效解决问题。 ADDM与其他建议相同都可以手动启动来诊断数据库，但一般而言是定期性地自动启动，监视数据库是否有性能问题。 ADDM自动启动的时机是在取得AWR的snapshot时，通过取得了snapshot的MMON进程来自动启动 手动启动ADDM的情况下，可以诊断过去任一时间点的数据库。 ADDM是从两个snapshot中取得数据库的负荷信息，进行诊断。 自动启动的情况下，使用最新取得的snapshot以及1个之前的snapshot，手动启动的情况下，用户可以指定任意两个snapshot。 要使用这个功能的话，需要将STATISTICS_LEVEL 初始化参数设定为TYPICAL（默认）或者ALL。  

SQL较慢的时候，首先要寻找原因！

  以前的SQL调优 ＜一般而言SQL的调优流程＞
  • 诊断/指定瓶颈
  • STATSPACK： 与DB整体的统计一起收集SQL统计
  • EXPLAIN PLAN： 在每个SQL中表示执行计划
  • SQL TRACE与TKPROF： 收集以session等单位来执行的SQL执行的统计信息，并且
总结结果报告。
  • SQL*Plus的AUTOTRACE機能： 在每个SQL中表示执行计划以及性能统计
  2.执行合适的调优
  • 优化的选择＜到Oracle 9i 为止＞
  • RBO （ Rule-based optimizer ）＜到Oracle 9i 为止＞
  • 重新检查物理設計（Index相关）
  • SQL的改写　　　　　　　　　　 etc..
  • CBO （ cost-based optimizer ）
  • 取得合适的对象的统计信息
  • 使用优化提示   不知所措时一定要使用这个功能！
  • 检查Partitioning导入
  • 并行处理的检验 　　etc..
3.检查效果　　=》 根据需要进一步调优    

优化提示

  • 通过使用提示、可以指示优化使用特定的访问路径
　　　　　　＜例＞
  • 指定使用合适的index
  • 指定合适的表结合方法与结合顺序
  • 指定CBO的目标（重视吞吐量 or面向 OLTP 等） etc.
  • 在使用RULE提示以外的情况下，就会自动执行CBP的最优化
  • 提示的使用例子（ /*+ 与*/ 之间指定提示、直接嵌入到SQL中 ）
  • 使其使用附着与sales表的customer_id列的cust_id_indx这个index。
SELECT  /*+ INDEX(sales cust_id_indx) */  sales_date , sales_amount FROM sales WHERE customer_id=‘ABE’;   SELECT  /*+ USE_HASH(s c) LEADING(c s) */  * FROM sales s , customers c WHERE s.customer_id=c.customer_id AND s.sales_amount > 1000;   以前的SQL调优的缺点 HINT提示的缺点
  • 每个SQL都需要自定义
  • 要求高度的知识存量与技巧
  • 性能恶化时，需要修正应用
某些SQL语句可以高速嵌入提示，但这就不得不对应用进行修正。 在package应用中无法使用提示！  

SQL Profile是什么

每个SQL中取得的固有的辅助性的统计信息 SQL Profile直到被删除或者被重新制作为止，都在数据库中保持原样 优化需要用到SQL Profile以及现存的优化统计两方面才能制成执行计划 Oracle Database 10g 开始，追加 SQL调优建议，从这里开始生成   简而言之  制成服从建议的SQL profile的话，执行计划也会最优化。 =》 原因是 数据库有如提示一样可以改善执行计划的统计信息！=》 如果使用SQL profile的话、、就可以不修正应用，但又能实现与提示同样的效果！！  

SQL profile的制成与使用

（实际应用SQL profile之后，会说明直到Active session降低为止所需要等待的时间） SQL profile是指SQLTuning Advisor，收集的辅助统计信息。 通过接受被提示的SQL profile，就可以不加工SQL就控制执行计划。 ＜追加＞ SQL profile是在10g中得到强化的功能之一，特征是不全面变更应用就可以进行调优。   SQL profile的制成与使用 SQL调优建议
  • 在高负荷背景下诊断有问题的SQL语句以及执行计划
  • 诊断结果是?
  • 寻找SQL语句的问题点，SQL语句的修正方法
  • 建议制成必要的索引
  • 制成SQL profile
一般而言，SQL调优建议等一系列的自动调优功能是从GUI的管理工具 Oracle Enterprise Manager 10g开始执行的。   sql_tuning_z1 SQL profile：经常出现的问题 Q1. 制作一个SQL profile的话、能够加速多个SQL吗？ SQL_PROFILE1 Q2. 执行SQL调优建议时，可以明确指定制成SQLprofile吗？ A2. 不可以。 SQL调优建议是在分析SQL的时候，为了改善性能，做出最合适的建议的功能。在手动调优中，比起使用提示进行调优，制成index的效果更好的情况下，请选择制成index。与此相同在只要制成index就可以明显改善性能的情况下，不会建议制成SQL profile。 Q3. 可以确认SQL profile的内容吗？ A3. 无法确认SQL profile的内容。（SQL profile的列表可以通过搜索DBA_SQL_PROFILES视图来进行确认。） Q4. SQL profile是随着数据量的增加而动态更新吗？ A4.因为SQLprofile是静态的信息，所以不会自动更新。 因此，使用时，                因为数据量的变化，SQL profile就会变旧，就可能使得性能恶化。 （已经产生恶化的情况下，可以再次制成SQL profile） Q5. 我们已经明白了SQL profile的效果有多好了，那要制成性能更好的执     行计划的话，为什么不使用默认的优化呢？ A5. 制成SQL profile比一般的CBO需要花费的时间更多。如果，默认中，对于所有SQL，都检验SQL profile的话，就可能影响到整体的性能，所以现实中不采用那种架构。   Q6. 能将开发环境下制成的SQL profile移动到正式环境中使用吗？ A6. 如果是Oracle Database 10g R2的话就可以。 ＜顺序＞ ・在开发环境中、
  1. 执行 dbms_sqltune.create_stgtab_sqlprof，然后制成暂时储存SQL profile Staging表。
  2. 执行dbms_sqltune.pack_stgtab_sqlprof，将SQL profile储存在Staging表中。
  3. 使用 DataPump以及Export，在dump文件中取出Staging表。
・在正式环境中 4.使用在开发环境中制成的dump文件使用Data Pump进行import。 5. 执行dbms_sqltune.unpack_stgtab_sqlprof，从Staging表表中取出SQL profile，反应在正式环境中 Oracle Database 10g R1中无法将SQL profile移动到其他数据库中　        （比如，制成index时，在开发环境下，可以将通过建议制成的项目用Export / Import 等移动到正式环境中，SQL profile不需要在正式环境中用别的途径来制成）   Q7. 有确认是否使用了 SQL profile的方法吗？ A7. 确认有两种方法。 ①灵活使用SQL*Plus的Autotrace功能 1. 设定Autotrace为ON SQL> set autotrace on 2. 每次执行SQL语句时，以下的note就都会被表示出来，使用了SQL profile的情况下， 是表示出SQLprofile的名字 Note ----- - SQL profile "SYS_SQLPROF_014564deb351c000" used for this　statement ②灵活使用Explain Plan 1. 对于SQL执行Explain Plan （例） SQL> EXPLAIN PLAN FOR SELECT * FROM emp; 2. 执行下述SQL的话，就会得到与①同样的结果 SQL> select plan_table_output from table(dbms_xplan.display());

要使用SQL调优建议需要什么!?

  • 仅限Oracle Database 10g Enterprise Edition或以上版本
  • 需要购买以下两个Option
Database Diagnostics Pack
  • 可以使用的功能
  • AWR (Automatic Workload Repository)
  • ADDM (Automatic Database Diagnostic Monitor）
  • 性能监视（数据库以及主机）
  • 项目通知: 通知method、规定、以及日程
  • 项目历史以及Metric历史 （数据库以及主机）
Database Tuning Pack
  • 可以使用的功能
  • SQL Access Advisor
  • SQL Tuning Advisor
  • SQL Tuning Sets
  • 重新编辑对象
  总结
  • SQL profile非常有效！
  • 克服了一直以来提示调优的弱点！
  • 不需要修正应用就可以完成类似于提示的调优！
  • 总结了US oracle的咨询的调优与窍门
  • 即使没有技能与经验也能完成高度的SQL调优！
灵活使用Oracle Database 10g Enterprise Edition+ Option 非常重要！ 灵活使用SQL调优建议 由建议开始制成SQL profile