SQL调优基于优化器，Cost Based Optimizer (CBO)。CBO基于统计信息给出执行路径。 两个建议器与SQL性能相关，均属于诊断包：
SQL Tuning Advisor - 查看语句如何运行SQL Access Advisor - 查看运行基于的segment结构调优的唯一目的是减少最终用户的响应时间。业务用户只关心时间，而不是SGA如何配置，索引如何设置等。因此调优实际是解决业务问题，最终用户的问题必须量化才能调优。 考虑以下典型的应用开发生命周期：
业务分析 - 定义业务流程，即需要做什么系统分析 - 业务流程建模，ER图，数据流图等系统设计 - 应用运行的环境，如Oracle数据库，HDFS等应用设计 - 写SQL，写应用实现 - 创建数据库等，部署应用维护 - 监控并在使用中调整 早期的设计必须正确，最初就必须考虑性能，例如正确的schema设计，逆范式化等。否则后期很难调整，正所谓预防重于治疗。因此调优方法论包含两个重要特征：
自顶而下 - 不过由于前期设计不当，DBA通常被迫采用自底而上的方法，即被动方法针对业务需求Oracle收集了大量运行和性能数据，存于V$视图中，并定期存入AWR中。 两个术语，统计值(statistics) 和度量(metrics):
统计值 - 绝对值，自描述的两个或多个相关的统计值，而且通常与时间关联，如本周与上周的事务/每秒 统计到度量转换通常由DBA完成。操作系统的I/O子系统在定位和交付数据块时，会话可能等待，这称为wait event。 wait event不可能完全消除，当应尽量减少。典型的wait event如db file scattered read，发生在全表扫描时的读取数据块。free buffer wait 表示寻找可用的buffer来放置数据块。buffer busy wait表示数据块已在buffer中，但由于其它会话在使用因此无法访问。 一些重要的性能监控视图如下：
V$STATNAME - 所有统计值名称，并归类V$SYSSTAT - 实例启动后的，整个实例的统计值V$SESSTAT - 会话启动后，所有已登录会话的统计值V$MYSTAT - 当前会话的统计值V$EVENT_NAME - 所有wait event名称，并归类V$SYSTEM_EVENT - 实例启动后，wait event的次数，时间V$SESSION_EVENT - 会话启动后，wait event的次数，时间有限的性能信息。 运行以下语句，此语句居然执行40分钟都没结束：
select count(*) from (select a.*, b.* from all_objects a, all_objects b);然后再Database Express中查看。 以下是首页信息： 以下是performance hub的信息，注意最上方图的滑动时间窗： 相关查看和设置EM配置的语句：
select dbms_xdb_config.gethttpport from dual; select dbms_xdb_config.gethttpsport from dual; exec dbms_xdb_config.sethttpport(5500); exec dbms_xdb_config.sethttpsport(5501); lsnrctl statusPGA从9i起可自动管理，SGA从10g起可自动管理。Oracle的内存使用是指虚拟内存，因为其没法区分是RAM还是swap。
与服务器进程关联的不可共享的内存称为PGA，用以存放会话相关数据。SQL执行必需SGA及少量PGA。 通常，内存分配分为3级，即：
optimal - 全部读入内存中执行，最理想情况one-pass - 分批次读入内存中执行multipass - 依赖磁盘执行，尽量避免 例如排序操作，如果总共10G数据，此时optimal就不现实，one-pass是最佳选择。 PGA建议自动管理，只需设定一个目标值，即一个总的值，供所有会话使用。PGA自动管理依赖3个参数： • WORKAREA_SIZE_POLICY - 默认AUTO • PGA_AGGREGATE_TARGET - 软限制, MAX(10MB, 0.2SGA)。尽管是软限制，也应合理设置 • PGA_AGGREGATE_LIMIT - 硬限制，MAX(2GB, PGA_AGGREGATE_TARGET2, 3MB * PROCESSES) orclpdb1> show parameter WORKAREA_SIZE_POLICY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ workarea_size_policy string AUTO orclpdb1> show parameter PGA_AGGREGATE_TARGET NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 384M orclpdb1> show parameter PGA_AGGREGATE_LIMIT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 2G orclpdb1> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ processes integer 300SGA中以下的内存结构可以单独调节：
The shared poolThe database buffer cache (default pool)The log bufferThe large poolThe streams poolThe Java pool 其中后3个设小了报错，设大了性能不会提升。 前3个，如果设小了，不会报错，但影响性能。shared pool 和log buffer 设置过大会影响性能。 SGA可自动管理，设置一个总的值，然后分摊(apportion)到各内存结构，并自动调节。log buffer例外，在实例启动时保持不变。 orclpdb1> show parameter log_buffer NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_buffer big integer 1680K可以手工调节的SGA内存参数如下：
SHARED_POOL_SIZEDB_CACHE_SIZELARGE_POOL_SIZESTREAMS_POOL_SIZEJAVA_POOL_SIZE如果使用自动内存管理（ASMM），只需将以上参数设为默认或0，然后设置SGA_TARGET（可动态调大调小），至多再设置一个SGA_MAX_SIZE（静态参数）。
AMM只需设置一个参数MEMORY_TARGET（动态参数，最大值受后面参数限制），至多再设置MEMORY_MAX_TARGET（静态参数）。然后可自动设置SGA和PGA。 最佳实践为只设置MEMORY_TARGET，然后将MEMORY_MAX_TARGET 设置为其1.2倍。其余参数一概不设。 AMM可简化管理，并提升性能，例如日间和晚间工作负载不同时。 启用AMM后，仍可设置PGA_AGGREGATE_TARGET 和 SGA_TARGET，这表示最小必须保证的值。 除非工作负载发生变化，负载稳定后内存分配也趋于稳定。 内存管理的后台进程为MMON和MMAN.g 内存分配可通过以下视图监控：
V$MEMORY_DYNAMIC_COMPONENTSV$MEMORY_RESIZE_OPS 实验: alter system set db_cache_size=0 scope=memory; alter system set shared_pool_size=0 scope=memory; alter system set large_pool_size=0 scope=memory; alter system set java_pool_size=0 scope=memory; -- 禁用AMM alter system set memory_target=0 scope=memory; -- 分别设置SGA和PGA alter system set pga_aggregate_target=10m scope=memory; alter system set sga_target=800m scope=memory; -- 查看实际的PGA大小 ORCLCDB> select sum(value) from v$sesstat natural join v$statname where name='session pga memory'; SUM(VALUE) ---------- 8038192 -- 查看实际的SGA大小 ORCLCDB> select sum(bytes) from v$sgastat; SUM(BYTES) ---------- 721416288Memory Advisor在自动内存管理中实际已用到，对DBA也是可见的：
-- DBaaS on OCI, 19c -- PGA advisor SQL> select pga_target_for_estimate, pga_target_factor, estd_extra_bytes_rw from v$pga_target_advice; PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ESTD_EXTRA_BYTES_RW ----------------------- ----------------- ------------------- 1006632960 .125 1831027712 2013265920 .25 0 4026531840 .5 0 6039797760 .75 0 8053063680 1 0 9663676416 1.2 0 1.1274E+10 1.4 0 1.2885E+10 1.6 0 1.4496E+10 1.8 0 1.6106E+10 2 0 2.4159E+10 3 0 3.2212E+10 4 0 4.8318E+10 6 0 6.4425E+10 8 0 14 rows selected. -- 上图中，第二列表示与实际值的比例，1就是当前值。可以看到PGA设大了。设成0.25时就可以了，因为第三列表示额外的I/O -- SGA Advisor，这回对应的不是I/O而是DB_TIME。因为减少DB_TIME是目标。 SQL> select sga_size, sga_size_factor, estd_db_time from v$sga_target_advice; SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ---------- --------------- ------------ 3840 .25 17205 5760 .375 17143 7680 .5 17138 9600 .625 17138 11520 .75 17138 13440 .875 17138 15360 1 17138 17280 1.125 17138 19200 1.25 17138 21120 1.375 17138 23040 1.5 17138 24960 1.625 17138 26880 1.75 17138 28800 1.875 17138 30720 2 17138 15 rows selected. -- 因为AMM未激活，所以无数据 SQL> select memory_size, memory_size_factor, estd_db_time from v$memory_target_advice; no rows selected总共有7个Memory Advisor：
Advisorv$ ViewPGAv$pga_target_adviceSGAv$sga_target_adviceMemoryv$memory_target_adviceDB Cachev$db_cache_adviceJava Poolv$java_pool_adviceStreams Poolv$db_cache_adviceShared Poolv$shared_pool_adviceSQL可以多种方式执行，执行方式称为执行计划，执行计划由优化器产生，优化器依赖统计信息，因此统计信息必需准。最重要的统计信息是对象统计信息。 统计信息只与SQL有关，与PL/SQL无关。但PL/SQL中如果有SQL语句也会有关系。
部分统计信息存于DBA_TABLES中，如行数，行宽度，空间情况。 关于列的统计信息存于DBA_TAB_COLUMNS中，如唯一值数量，最小最大值，空值数量，平均列宽。 统计信息存于DBA_INDEXES，包括索引树深度等。 对象统计信息是静态的，必需重新收集才能更新。 由于对象或数据会发生变化，以及为避免影响性能，统计信息使用采样方式搜集，因此统计信息是不完美的。
比较以下语句：
select FIRST_NAME, LAST_NAME from hr.employees where salary > 10000; select FIRST_NAME, LAST_NAME from hr.employees where salary > 0;第一个适合用索引，第二个适合全表扫描。 优化器的判断可能不准或错误，因为统计信息会过时或采样不够。有两个补救方法： SQL plan directives 或 adaptive execution plans. directive指示优化器搜集额外的信息，也称为动态采样。如果优化器的估计与实际情况差异较大，就需要directive。directive存于AWR，与表关联而不是语句。 adaptive execution plan每次影响一个语句，如超过多少行用A计划，少于则使用B计划。在实际执行时还可以调整。
对象统计信息是静态的，不是实时的。手工收集可使用DBMS_STATS 包，如下例：
SQL> execute dbms_stats.gather_table_stats('HR', 'REGIONS', estimate_percent=>100); PL/SQL procedure successfully completed. SQL> select num_rows from dba_tables where owner='HR' and table_name='REGIONS'; NUM_ROWS ---------- 4 SQL> insert into hr.regions values(99, 'UK'); 1 row created. SQL> select num_rows from dba_tables where owner='HR' and table_name='REGIONS'; NUM_ROWS ---------- 4 SQL> execute dbms_stats.gather_table_stats('HR', 'REGIONS', estimate_percent=>dbms_stats.auto_sample_size); PL/SQL procedure successfully completed. SQL> select num_rows from dba_tables where owner='HR' and table_name='REGIONS'; NUM_ROWS ---------- 5搜集统计信息有利于性能，但在搜集时会影响性能，因此需要决定搜集的频率，每次采样的比率。 对象统计信息可从多个层级搜集，DBMS_STATS包中相关过程如下：
gather_database_stats 整个数据库gather_schema_stats schema中所有对象gather_table_stats 单表gather_index_stats 单索引 例如： execute dbms_stats.gather_schema_stats(- ownname=>'HR',- cascade=>dbms_stats.auto_cascade,- estimate_percent=>dbms_stats.auto_sample_size,- degree=>dbms_stats.auto_degree,- no_invalidate=>dbms_stats.auto_invalidate,- granularity=>'auto',- method_opt=>'for all columns size auto',- options=>'gather');以上，cascade表示搜集索引。estimate_percent表示采样多少数据。degree表示并行。 自动统计信息搜集任务在维护窗口每日运行一次，维护窗口工作日为4小时，晚10点开始，周末为20小时，早6点开始。
SQL Tunning Advisor分析SQL语句并给出建议，如搜集统计信息，建索引，修改SQL语句等。 此Advisor在维护窗口自动运行，分析最耗资源的语句，可选择自动实施建议。Advisor也可手动执行。
Advisor的能力包括：
统计信息分析：是否缺，是否过时，建议刷新或重建SQL profiling：搜集SQL语句的辅助统计信息及执行统计信息，并存于SQL ProfileAccess Path：评估建立新索引的影响Structure analysis：重构SQL语句，看是否可更好执行 Advisor只对单个SQL进行建议，并不考虑全局。因此SELECT语句调好了，DML语句有可能变慢。管理任务相关的：
CREATE_TUNING_TASKEXECUTE_TUNING_TASKREPORT_TUNING_TASK 相关的数据字典中视图：DBA_ADVISOR_LOGDBA_ADVISOR_TASKSV$ADVISOR_PROGRESS示例：
SQL> create user user13 identified by Oracle#123; User created. SQL> grant dba to user13; Grant succeeded. SQL> connect user13/Oracle#123@orclpdb1; Connected. SQL> create table object_analysis as select * from all_objects; Table created. SQL> variable vtask varchar2(100); SQL> execute :vtask := dbms_sqltune.create_tuning_task(- sql_text=>'select max(object_id) from object_analysis'); PL/SQL procedure successfully completed. SQL> execute dbms_sqltune.execute_tuning_task(:vtask); PL/SQL procedure successfully completed. SQL> set lines 160 SQL> set long 10000 SQL> set longchunksize 10000 SQL> set pages 999 SQL> select dbms_sqltune.report_tuning_task(:vtask) from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK(:VTASK) ---------------------------------------------------------------------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : TASK_22 Tuning Task Owner : USER13 Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 11/06/2019 13:02:59 Completed at : 11/06/2019 13:02:59 ------------------------------------------------------------------------------- Schema Name : USER13 Container Name: ORCLPDB1 SQL ID : 6v5yv15r6nckd SQL Text : select max(object_id) from object_analysis ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- Index Finding (see explain plans section below) -------------------------------------------------- The execution plan of this statement can be improved by creating one or more indices. Recommendation (estimated benefit: 99.46%) ------------------------------------------ - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index USER13.IDX$$_00160001 on USER13.OBJECT_ANALYSIS("OBJECT_ID"); Rationale --------- Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 4144131271 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 374 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | TABLE ACCESS FULL| OBJECT_ANALYSIS | 68190 | 332K| 374 (1)| 00:00:01 | -------------------------------------------------------------------------------------- 2- Using New Indices -------------------- Plan hash value: 1085109013 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| IDX$$_00160001 | 1 | 5 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- SQL>指定工作负载的分析，针对segment结构。
负载可以为单个SQL，SQL tuning set(存储的一系列语句)，SQL Cache内容。 建议可包括索引，分区，物化视图。 建议有可能对某些有利而对其它一些不利。 建议通常是好的，单可以再进一步优化，变得更好。 此建议器通过Enterprise Manager或DBMS_ADVISOR调用
DBMS_ADVISOR中的过程较复杂，简单些的可使用DBMS_ADVISOR.QUICK_TUNE。
SQL> host pwd /home/oracle SQL> create directory tune_dir as '/home/oracle/tune_dir'; Directory created. SQL> host mkdir tune_dir; SQL> execute dbms_advisor.quick_tune(- dbms_advisor.sqlaccess_advisor,- 'task1',- 'select distinct object_id from object_analysis'- ) SQL> begin dbms_advisor.create_file( dbms_advisor.get_task_script('task1'), 'TUNE_DIR', 'tune_fts.sql' ); end; /查看文件：
$ cat tune_dir/tune_fts.sql Rem SQL Access Advisor: Version 19.0.0.0.0 - Production Rem Rem Username: USER13 Rem Task: task1 Rem Execution date: Rem CREATE MATERIALIZED VIEW LOG ON "USER13"."OBJECT_ANALYSIS" WITH ROWID, SEQUENCE("OBJECT_ID") INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW "USER13"."MV$$_001E0000" REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT "USER13"."OBJECT_ANALYSIS"."OBJECT_ID" C1, COUNT(*) M1 FROM "USER13"."OBJECT_ANALYSIS" GROUP BY "USER13"."OBJECT_ANALYSIS"."OBJECT_ID"; begin dbms_stats.gather_table_stats('"USER13"','"MV$$_001E0000"',NULL,dbms_stats.auto_sample_size); end; /以下是在OCI上的DBaaS（单实例数据库）的内存设定，主机为2 OCPU，30G内存。 PGA内存设定：
SQL> show parameter WORKAREA_SIZE_POLICY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ workarea_size_policy string AUTO SQL> show parameter PGA_AGGREGATE_TARGET NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 7680M SQL> show parameter PGA_AGGREGATE_LIMIT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 15GSGA内存设定：
SQL> show parameter log_buffer NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_buffer big integer 16M SQL> show parameter SHARED_POOL_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_pool_size big integer 0 SQL> show parameter DB_CACHE_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 0 SQL> show parameter LARGE_POOL_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ large_pool_size big integer 0 SQL> show parameter STREAMS_POOL_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 0 SQL> show parameter JAVA_POOL_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_pool_size big integer 0 SQL> show parameter SGA_TARGET NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 15G SQL> show parameter SGA_MAX_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_max_size big integer 15GAMM设定：
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 0 SQL> show parameter MEMORY_MAX_TARGET NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_max_target big integer 0