credspp数据库修正_已经几乎不需要修正应用了！划时代的SQL调优方法-CSDN博客

本文链接：https://blog.csdn.net/weixin_30446393/article/details/112875859

以前的SQL调优

SQL Profile是什么

调优执行顺序

经常被问到的问题

总结

＜一般而言SQL的调优流程＞

诊断/指定瓶颈

执行合适的调优

检查效果

根据需要进一步调优

・Automatic Database DiagnosticMonitor(ADDM)

ADDM是监视/诊断数据库性能的功能。

发现内存不足以及I/O问题、性能较差的SQL、RealApplicationClusters(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(salescust_id_indx) */sales_date,sales_amount

FROM sales WHEREcustomer_id=‘ABE’;

SELECT/*+ USE_HASH(s c) LEADING(c s) */*

FROM sales s , customers c

WHEREs.customer_id=c.customer_idANDs.sales_amount> 1000;

以前的SQL调优的缺点

HINT提示的缺点

每个SQL都需要自定义

要求高度的知识存量与技巧

性能恶化时，需要修正应用

某些SQL语句可以高速嵌入提示，但这就不得不对应用进行修正。

在package应用中无法使用提示！

SQL Profile是什么

每个SQL中取得的固有的辅助性的统计信息

SQL Profile直到被删除或者被重新制作为止，都在数据库中保持原样

优化需要用到SQL Profile以及现存的优化统计两方面才能制成执行计划

Oracle Database 10g 开始，追加 SQL调优建议，从这里开始生成

简而言之

制成服从建议的SQL profile的话，执行计划也会最优化。 =》原因是数据库有如提示一样可以改善执行计划的统计信息！=》

如果使用SQL profile的话、、就可以不修正应用，但又能实现与提示同样的效果！！

SQLprofile的制成与使用

(实际应用SQLprofile之后，会说明直到Active session降低为止所需要等待的时间)

SQL profile是指SQLTuningAdvisor，收集的辅助统计信息。

通过接受被提示的SQL profile，就可以不加工SQL就控制执行计划。

＜追加＞

SQLprofile是在10g中得到强化的功能之一，特征是不全面变更应用就可以进行调优。

SQL调优建议

在高负荷背景下诊断有问题的SQL语句以及执行计划

诊断结果是?

寻找SQL语句的问题点，SQL语句的修正方法

建议制成必要的索引

制成SQL profile

一般而言，SQL调优建议等一系列的自动调优功能是从GUI的管理工具

Oracle Enterprise Manager 10g开始执行的。

SQL profile：经常出现的问题

Q1. 制作一个SQL profile的话、能够加速多个SQL吗？

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的话就可以。

＜顺序＞

・在开发环境中、

执行 dbms_sqltune.create_stgtab_sqlprof，然后制成暂时储存SQL profile Staging表。

执行dbms_sqltune.pack_stgtab_sqlprof，将SQL profile储存在Staging表中。

使用 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