Oracle Database Performance Tuning
大约 132 分钟
Oracle Database Performance Tuning
Overview
简而言之，性能问题通常是由于资源争用或者资源耗尽导致，这里的资源从物理层面就是 CPU、Memory、Disk、Network，从逻辑层面就是 Lock、Latch、Mutex。
那么解决性能问题就可以从如下两个方面入手：
- 增加资源
- 减少对资源的使用
我们往往需要在不增加资源（成本）的前提下，提高性能或者保持性能稳定，那么在系统上线前，需要做如下检查：
- When you create the control file for the production database, allow for growth by setting
MAXINSTANCES,MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS, andMAXLOGHISTORYto values higher than what you anticipate for the rollout. This technique results in more disk space usage and larger control files, but saves time later should these need extension in an emergency.- Set block size to the value used to develop the application. Export the schema statistics from the development or test environment to the production database if the testing was done on representative data volumes and the current SQL execution plans are correct.
- Set the minimal number of initialization parameters. Ideally, most other parameters should be left at default. If there is more tuning to perform, then this appears when the system is under load.
- Be prepared to manage block contention by setting storage options of database objects. Tables and indexes that experience high
INSERT/UPDATE/DELETErates should be created with automatic segment space management. To avoid contention of rollback segments, use automatic undo management.- All SQL statements should be verified to be optimal and their resource usage understood.
- Validate that middleware and programs that connect to the database are efficient in their connection management and do not logon or logoff repeatedly.
- Validate that the SQL statements use cursors efficiently. The database should parse each SQL statement once and then execute it multiple times. The most common reason this does not happen is because bind variables are not used properly and
WHEREclause predicates are sent as string literals. If you use precompilers to develop the application, then make sure to reset the parametersMAXOPENCURSORS,HOLD_CURSOR, andRELEASE_CURSORfrom the default values before precompiling the application.- Validate that all schema objects have been correctly migrated from the development environment to the production database. This includes tables, indexes, sequences, triggers, packages, procedures, functions, Java objects, synonyms, grants, and views. Ensure that any modifications made in testing are made to the production system.
- As soon as the system is rolled out, establish a baseline set of statistics from the database and operating system. This first set of statistics validates or corrects any assumptions made in the design and rollout process.
- Start anticipating the first bottleneck (which is inevitable) and follow the Oracle performance method to make performance improvement.
提升性能的步骤：
- Perform the following initial standard checks:
- Get candid feedback from users. Determine the performance project's scope and subsequent performance goals, and performance goals for the future. This process is key in future capacity planning.
- Get a full set of operating system, database, and application statistics from the system when the performance is both good and bad. If these are not available, then get whatever is available. Missing statistics are analogous to missing evidence at a crime scene: They make detectives work harder and it is more time-consuming.
- Sanity-check the operating systems of all computers involved with user performance. By sanity-checking the operating system, you look for hardware or operating system resources that are fully utilized. List any over-used resources as symptoms for analysis later. In addition, check that all hardware shows no errors or diagnostics.
- Check for the top ten most common mistakes with Oracle Database, and determine if any of these are likely to be the problem. List these as symptoms for later analysis. These are included because they represent the most likely problems. ADDM automatically detects and reports nine of these top ten issues.
- Build a conceptual model of what is happening on the system using the symptoms as clues to understand what caused the performance problems.
- Propose a series of remedy actions and the anticipated behavior to the system, then apply them in the order that can benefit the application the most. ADDM produces recommendations each with an expected benefit. A golden rule in performance work is that you only change one thing at a time and then measure the differences. Unfortunately, system downtime requirements might prohibit such a rigorous investigation method. If multiple changes are applied at the same time, then try to ensure that they are isolated so that the effects of each change can be independently validated.
- Validate that the changes made have had the desired effect, and see if the user's perception of performance has improved. Otherwise, look for more bottlenecks, and continue refining the conceptual model until your understanding of the application becomes more accurate.
- Repeat the last three steps until performance goals are met or become impossible due to other constraints.
最常见的10个错误：
Bad connection management
The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and is totally unscalable.
Bad use of cursors and the shared pool
Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times. Be suspicious of applications generating dynamic SQL.
Bad SQL
Bad SQL is SQL that uses more resources than appropriate for the application requirement. This can be a decision support systems (DSS) query that runs for more than 24 hours, or a query from an online application that takes more than a minute. You should investigate SQL that consumes significant system resources for potential improvement. ADDM identifies high load SQL. SQL Tuning Advisor can provide recommendations for improvement.
Use of nonstandard initialization parameters
These might have been implemented based on poor advice or incorrect assumptions. Most databases provide acceptable performance using only the set of basic parameters. In particular, parameters associated with
SPIN_COUNTon latches and undocumented optimizer features can cause a great deal of problems that can require considerable investigation.Likewise, optimizer parameters set in the initialization parameter file can override proven optimal execution plans. For these reasons, schemas, schema statistics, and optimizer settings should be managed as a group to ensure consistency of performance.
Getting database I/O wrong
Many sites lay out their databases poorly over the available disks. Other sites specify the number of disks incorrectly, because they configure disks by disk space and not I/O bandwidth.
Online redo log setup problems
Many sites run with too few online redo log files and files that are too small. Small redo log files cause system checkpoints to continuously put a high load on the buffer cache and I/O system. If too few redo log files exist, then the archive cannot keep up, and the database must wait for the archiver to catch up.
Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (
INITRANS), or shortage of rollback segments.This is particularly common on
INSERT-heavy applications, in applications that have raised the block size above 8K, or in applications with large numbers of active users and few rollback segments. Use automatic segment-space management (ASSM) and automatic undo management to solve this problem.Long full table scans
Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable.
High amounts of recursive (
SYS) SQLLarge amounts of recursive SQL executed by
SYScould indicate space management activities, such as extent allocations, taking place. This is unscalable and impacts user response time. Use locally managed tablespaces to reduce recursive SQL due to extent allocation. Recursive SQL executed under another user ID is probably SQL and PL/SQL, and this is not a problem.Deployment and migration errors
In many cases, an application uses too many resources because the schema owning the tables has not been successfully migrated from the development environment or from an older implementation. Examples of this are missing indexes or incorrect statistics. These errors can lead to sub-optimal execution plans and poor interactive user performance. When migrating applications of known performance, export the schema statistics to maintain plan stability using the
DBMS_STATSpackage.
紧急性能问题解决步骤：
Survey the performance problem and collect the symptoms of the performance problem. This process should include the following:
- User feedback on how the system is underperforming. Is the problem throughput or response time?
- Ask the question, "What has changed since we last had good performance?" This answer can give clues to the problem. However, getting unbiased answers in an escalated situation can be difficult. Try to locate some reference points, such as collected statistics or log files, that were taken before and after the problem.
- Use automatic tuning features to diagnose and monitor the problem.
Sanity-check the hardware utilization of all components of the application system. Check where the highest CPU utilization is, and check the disk, memory usage, and network performance on all the system components. This quick process identifies which tier is causing the problem. If the problem is in the application, then shift analysis to application debugging. Otherwise, move on to database server analysis.
Determine if the database server is constrained on CPU or if it is spending time waiting on wait events. If the database server is CPU-constrained, then investigate the following:
- Sessions that are consuming large amounts of CPU at the operating system level and database; check
V$SESS_TIME_MODELfor database CPU usage- Sessions or statements that perform many buffer gets at the database level; check
V$SESSTATandV$SQLSTATS- Execution plan changes causing sub-optimal SQL execution; these can be difficult to locate
- Incorrect setting of initialization parameters
- Algorithmic issues caused by code changes or upgrades of all components
If the database sessions are waiting on events, then follow the wait events listed in
V$SESSION_WAITto determine what is causing serialization. TheV$ACTIVE_SESSION_HISTORYview contains a sampled history of session activity which you can use to perform diagnosis even after an incident has ended and the system has returned to normal operation. In cases of massive contention for the library cache, it might not be possible to logon or submit SQL to the database. In this case, use historical data to determine why there is suddenly contention on this latch. If most waits are for I/O, then examineV$ACTIVE_SESSION_HISTORYto determine the SQL being run by the sessions that are performing all of the inputs and outputs.Apply emergency action to stabilize the system. This could involve actions that take parts of the application off-line or restrict the workload that can be applied to the system. It could also involve a system restart or the termination of job in process. These naturally have service level implications.
Validate that the system is stable. Having made changes and restrictions to the system, validate that the system is now stable, and collect a reference set of statistics for the database. Now follow the rigorous performance method described earlier in this book to bring back all functionality and users to the system. This process may require significant application re-engineering before it is complete.
Instance Tuning
Initial Instance Configuration
Initialization Parameters
对性能无影响的初始化参数：
| Parameter | Description |
|---|---|
DB_NAME | Name of the database. This should match the ORACLE_SID environment variable. |
DB_DOMAIN | Location of the database in Internet dot notation. |
OPEN_CURSORS | Limit on the maximum number of cursors (active SQL statements) for each session. The setting is application-dependent; 500 is recommended. |
CONTROL_FILES | Set to contain at least two files on different disk drives to prevent failures from control file loss. |
DB_FILES | Set to the maximum number of files that can assigned to the database. |
对性能有影响的初始化参数：
| Parameter | Description |
|---|---|
COMPATIBLE | Specifies the release with which the Oracle database must maintain compatibility. It lets you take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality in your environment. If your application was designed for a specific release of Oracle Database, and you are actually installing a later release, then you might want to set this parameter to the version of the previous release. |
DB_BLOCK_SIZE | Sets the size of the Oracle database blocks stored in the database files and cached in the SGA. The range of values depends on the operating system, but it is typically 8192 for transaction processing systems and higher values for database warehouse systems. |
SGA_TARGET | Specifies the total size of all SGA components. If SGA_TARGET is specified, then the buffer cache (DB_CACHE_SIZE), Java pool (JAVA_POOL_SIZE), large pool (LARGE_POOL_SIZE), and shared pool (SHARED_POOL_SIZE) memory pools are automatically sized. |
PGA_AGGREGATE_TARGET | Specifies the target aggregate PGA memory available to all server processes attached to the instance. |
PROCESSES | Sets the maximum number of processes that can be started by that instance. This is the most important primary parameter to set, because many other parameter values are deduced from this. |
SESSIONS | This is set by default from the value of processes. However, if you are using the shared server, then the deduced value is likely to be insufficient. |
UNDO_MANAGEMENT | Specifies the undo space management mode used by the database. The default is AUTO. If unspecified, the database uses AUTO.See "UNDO". |
UNDO_TABLESPACE | Specifies the undo tablespace to be used when an instance starts. |
Redo Log Files
联机重做日志文件的大小会影响数据库的性能。通常，增大联机重做日志文件可以获得更好的性能。根据日志生成量确定日志文件的大小，Oracle 建议以 20 分钟的日志生成量作为日志文件的大小。在实际工作中，随着系统越来越繁忙，日志生成也越来越快，为减少调整日志文件大小的频率，一般可以在创建数据库的时候设置日志文件大小为 2 G，5 个日志组，适合大多数数据库。
如果在创建数据库的时候没有配置，则需要在数据库创建完成后进行配置。
例子：RAC 环境配置联机重做日志文件
SQL> select l.group#,l.thread#,l.bytes/1024/1024/1024,f.member from v$log l join v$logfile f on l.group#=f.group## order by 1;
GROUP## THREAD## L.BYTES/1024/1024/1024 MEMBER
---------- ---------- ---------------------- --------------------------------------------------
1 1 .048828125 +ARCHDG/test/onlinelog/group_1.257.916070395
1 1 .048828125 +DATADG/test/onlinelog/group_1.261.916070395
2 1 .048828125 +ARCHDG/test/onlinelog/group_2.258.916070395
2 1 .048828125 +DATADG/test/onlinelog/group_2.262.916070395
3 2 .048828125 +DATADG/test/onlinelog/group_3.265.916070611
3 2 .048828125 +ARCHDG/test/onlinelog/group_3.259.916070611
4 2 .048828125 +DATADG/test/onlinelog/group_4.266.916070611
4 2 .048828125 +ARCHDG/test/onlinelog/group_4.260.916070611
8 rows selected.
每个实例2组，每组2个日志文件，每个日志文件50M，共8个日志文件。修改日志位置为+REDODG，每个实例5组，每组1个日志文件，每个日志文件2G，共10个日志文件。
SQL> alter system set db_create_online_log_dest_1='+REDODG';
System altered.
增加日志组：
SQL> alter database add logfile thread 1 group 11 size 2G;
Database altered.
SQL> alter database add logfile thread 1 group 12 size 2G;
Database altered.
SQL> alter database add logfile thread 1 group 13 size 2G;
Database altered.
SQL> alter database add logfile thread 1 group 14 size 2G;
Database altered.
SQL> alter database add logfile thread 1 group 15 size 2G;
Database altered.
SQL> alter database add logfile thread 2 group 16 size 2G;
Database altered.
SQL> alter database add logfile thread 2 group 17 size 2G;
Database altered.
SQL> alter database add logfile thread 2 group 18 size 2G;
Database altered.
SQL> alter database add logfile thread 2 group 19 size 2G;
Database altered.
SQL> alter database add logfile thread 2 group 20 size 2G;
Database altered.
然后切换到增加的日志组，使以前的日志组处于inactive状态：
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
然后删除默认的重做日志文件：
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
最终结果如下：
SQL> select l.group#,l.thread#,l.bytes/1024/1024/1024 Gb,l.archived,l.status,f.member from v$log l join v$logfile f on l.group#=f.group## order by 1;
GROUP## THREAD## GB ARC STATUS MEMBER
---------- ---------- ---------- --- ---------------- --------------------------------------------------
11 1 2 NO CURRENT +REDODG/test/onlinelog/group_11.256.971775807
12 1 2 YES UNUSED +REDODG/test/onlinelog/group_12.257.971775855
13 1 2 YES UNUSED +REDODG/test/onlinelog/group_13.258.971775869
14 1 2 YES UNUSED +REDODG/test/onlinelog/group_14.259.971775881
15 1 2 YES UNUSED +REDODG/test/onlinelog/group_15.260.971775895
16 2 2 NO CURRENT +REDODG/test/onlinelog/group_16.261.971775921
17 2 2 YES UNUSED +REDODG/test/onlinelog/group_17.262.971775941
18 2 2 YES UNUSED +REDODG/test/onlinelog/group_18.263.971775957
19 2 2 YES UNUSED +REDODG/test/onlinelog/group_19.264.971775973
20 2 2 YES UNUSED +REDODG/test/onlinelog/group_20.265.971776001
10 rows selected.
SQL> select group#,thread#,members,bytes/1024/1024/1024 Gb from v$log;
GROUP## THREAD## MEMBERS GB
---------- ---------- ---------- ----------
11 1 1 2
12 1 1 2
13 1 1 2
14 1 1 2
15 1 1 2
16 2 1 2
17 2 1 2
18 2 1 2
19 2 1 2
20 2 1 2
10 rows selected.
UNDO
对于 UNDO，除了确保初始化参数 UNDO_MANAGEMENT 设置为 AUTO 外，还需要为 UNDO 表空间的数据文件指定合适的大小，并限制其自动扩展。
例子：调整 UNDO 表空间数据文件为 10 G，不自动扩展
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,AUTOEXTENSIBLE from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_h1txxkxs_.dbf .068359375 31.9999847 YES
SQL> alter database datafile '/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_h1txxkxs_.dbf' resize 10G;
Database altered.
SQL> alter database datafile '/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_h1txxkxs_.dbf' autoextend off;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,AUTOEXTENSIBLE from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_h1txxkxs_.dbf 10 0 NO
Temporary Tablespaces
合适的临时表空间大小可以提高排序性能。创建数据库后，建议为临时文件指定合适的大小，并限制其自动扩展。
例子：调整临时文件大小为 10 G，不自动扩展
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_temp_files order by 1;
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
TEMP /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_hv3oym1t_.tmp .028320313 31.9999847 Y
SQL> alter database tempfile '/u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_hv3oym1t_.tmp' resize 10G;
Database altered.
SQL> alter database tempfile '/u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_hv3oym1t_.tmp' autoextend off;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_temp_files order by 1;
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
TEMP /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_hv3oym1t_.tmp 10 0 NO
ASH
Oracle 会自动收集系统的统计信息用于性能问题的诊断。对于活动会话每秒进行采样收集到 V$ACTIVE_SESSION_HISTORY 视图中，并将其历史数据放入 DBA_HIST_ACTIVE_SESS_HISTORY 视图，我们可以基于它们生成 ASH 报告，用于在较短时间内发生的瞬时性能问题的分析。
例子：生成 ASH 报告
SQL> @?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2419524882 TEST 1 test
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2419524882 1 TEST test test
Defaults to current database
Using database id: 2419524882
Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Using instance number(s): 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available: 21-Nov-22 22:04:27 [ 8 mins in the past]
Latest ASH sample available: 21-Nov-22 22:11:13 [ 2 mins in the past]
Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
-- Valid input formats:
-- To specify absolute begin time:
-- [MM/DD[/YY]] HH24:MI[:SS]
-- Examples: 02/23/03 14:30:15
-- 02/23 14:30:15
-- 14:30:15
-- 14:30
-- To specify relative begin time: (start with '-' sign)
-- -[HH24:]MI
-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
-- -25 (SYSDATE - 25 Mins)
Defaults to -15 mins
Enter value for begin_time: -5
Report begin time specified: -5
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration:
Report duration specified:
Using 21-Nov-22 22:08:09 as report begin time
Using 21-Nov-22 22:13:15 as report end time
Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- In the 'Activity Over Time' section of the ASH report,
-- the analysis period is divided into smaller slots
-- and top wait events are reported in each of those slots.
-- Default:
-- The analysis period will be automatically split upto 10 slots
-- complying to a minimum slot width of
-- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or
-- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.
Specify Slot Width in seconds to use in the 'Activity Over Time' section:
Defaults to a value as explained above:
Slot Width specified:
Specify Report Targets (using ashrpti.sql) to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- ASH Report can accept "Report Targets",
-- like a particular SQL statement, or a particular SESSION,
-- to generate the report on. If one or more report targets are
-- specified, then the data used to generate the report will only be
-- the ASH samples that pertain to ALL the specified report targets.
-- Default:
-- If none of the report targets are specified,
-- then the target defaults to all activity in the database instance.
Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
SESSION report target specified:
Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
SQL report target specified:
Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
WAIT_CLASS report target specified:
Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
SERVICE report target specified:
Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
MODULE report target specified:
Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
ACTION report target specified:
Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
CLIENT_ID report target specified:
Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:
Defaults to NULL: (% and _ wildcards allowed)
PLSQL_ENTRY report target specified:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_1121_2213.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name ashrpt_1_1121_2213.html
Summary of All User Input
-------------------------
Format : HTML
DB Id : 2419524882
Inst num : 1
Begin time : 21-Nov-22 22:08:09
End time : 21-Nov-22 22:13:15
Slot width : Default
Report targets : 0
Report name : ashrpt_1_1121_2213.html
AWR
使用 AWR（Automatic Workload Repository）可以获取数据库在一段时间内的整体运行情况及性能瓶颈。AWR 从内存（动态性能视图）和数据库（数据字典视图）中收集数据保存到 SYSAUX 表空间，我们可以查看 AWR 相关视图或者使用AWR脚本生成报告来查看这些数据。
AWR 收集的数据有：
- 段对象统计数据
- 在
V$SYS_TIME_MODEL和V$SESS_TIME_MODEL的时间模型统计数据 - 在
V$SYSSTAT和V$SESSSTAT的系统和会话统计数据 - SQL
- ASH
初始化参数 STATISTICS_LEVEL 控制统计数据收集的级别，只有设置为 TYPICAL（默认）或者 ALL 才会启用 AWR。
SQL> show parameter statistics_level;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> select statistics_name,activation_level,statistics_view_name from v$statistics_level;
STATISTICS_NAME ACTIVAT STATISTICS_VIEW_NAME
---------------------------------------- ------- ------------------------------
Buffer Cache Advice TYPICAL V$DB_CACHE_ADVICE
MTTR Advice TYPICAL V$MTTR_TARGET_ADVICE
Timed Statistics TYPICAL
Timed OS Statistics ALL
Segment Level Statistics TYPICAL V$SEGSTAT
PGA Advice TYPICAL V$PGA_TARGET_ADVICE
Plan Execution Statistics ALL V$SQL_PLAN_STATISTICS
Shared Pool Advice TYPICAL V$SHARED_POOL_ADVICE
Modification Monitoring TYPICAL
Longops Statistics TYPICAL V$SESSION_LONGOPS
Bind Data Capture TYPICAL V$SQL_BIND_CAPTURE
STATISTICS_NAME ACTIVAT STATISTICS_VIEW_NAME
---------------------------------------- ------- ------------------------------
Ultrafast Latch Statistics TYPICAL
Threshold-based Alerts TYPICAL
Global Cache Statistics TYPICAL
Global Cache CPU Statistics ALL
Active Session History TYPICAL V$ACTIVE_SESSION_HISTORY
Undo Advisor, Alerts and Fast Ramp up TYPICAL V$UNDOSTAT
Streams Pool Advice TYPICAL V$STREAMS_POOL_ADVICE
Time Model Events TYPICAL V$SESS_TIME_MODEL
Plan Execution Sampling TYPICAL V$ACTIVE_SESSION_HISTORY
Automated Maintenance Tasks TYPICAL
SQL Monitoring TYPICAL V$SQL_MONITORING
STATISTICS_NAME ACTIVAT STATISTICS_VIEW_NAME
---------------------------------------- ------- ------------------------------
Adaptive Thresholds Enabled TYPICAL
V$IOSTAT_* statistics TYPICAL
24 rows selected.
Snapshots
快照是某个时间段的历史数据集。默认情况下，Oracle 数据库每小时自动生成一次性能统计数据的快照，并保留 8 天，也可以根据需要在某个时间点手动创建快照。
例子：创建快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
PL/SQL procedure successfully completed.
例子：使用 DBA_HIST_SNAPSHOT 视图查看快照信息
SQL> select snap_id,dbid,begin_interval_time,end_interval_time from dba_hist_snapshot order by snap_id;
SNAP_ID DBID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ---------- ------------------------------ ------------------------------
1 2419524882 21-NOV-22 10.04.29.000 PM 21-NOV-22 11.00.29.105 PM
2 2419524882 21-NOV-22 11.00.29.105 PM 22-NOV-22 12.00.30.551 AM
3 2419524882 22-NOV-22 12.00.30.551 AM 22-NOV-22 01.00.31.975 AM
4 2419524882 22-NOV-22 01.00.31.975 AM 22-NOV-22 02.00.33.450 AM
5 2419524882 22-NOV-22 02.00.33.450 AM 22-NOV-22 03.00.34.924 AM
6 2419524882 22-NOV-22 03.00.34.924 AM 22-NOV-22 04.00.36.358 AM
7 2419524882 22-NOV-22 04.00.36.358 AM 22-NOV-22 05.00.37.810 AM
8 2419524882 22-NOV-22 05.00.37.810 AM 22-NOV-22 06.00.39.364 AM
9 2419524882 22-NOV-22 06.00.39.364 AM 22-NOV-22 07.00.40.907 AM
10 2419524882 22-NOV-22 07.00.40.907 AM 22-NOV-22 08.00.42.328 AM
11 2419524882 22-NOV-22 08.00.42.328 AM 22-NOV-22 09.00.43.752 AM
12 2419524882 22-NOV-22 09.00.43.752 AM 22-NOV-22 10.00.45.164 AM
13 2419524882 22-NOV-22 10.00.45.164 AM 22-NOV-22 11.00.46.676 AM
14 2419524882 22-NOV-22 11.00.46.676 AM 22-NOV-22 12.00.48.421 PM
15 2419524882 22-NOV-22 12.00.48.421 PM 22-NOV-22 01.00.49.872 PM
16 2419524882 22-NOV-22 01.00.49.872 PM 22-NOV-22 02.00.51.304 PM
17 2419524882 22-NOV-22 02.00.51.304 PM 22-NOV-22 03.00.52.757 PM
18 2419524882 22-NOV-22 03.00.52.757 PM 22-NOV-22 04.00.54.218 PM
19 2419524882 22-NOV-22 04.00.54.218 PM 22-NOV-22 05.00.55.675 PM
20 2419524882 22-NOV-22 05.00.55.675 PM 22-NOV-22 06.00.57.120 PM
21 2419524882 22-NOV-22 06.00.57.120 PM 22-NOV-22 07.00.58.559 PM
22 2419524882 22-NOV-22 07.00.58.559 PM 22-NOV-22 08.01.00.001 PM
23 2419524882 22-NOV-22 08.01.00.001 PM 22-NOV-22 09.00.01.484 PM
24 2419524882 22-NOV-22 09.00.01.484 PM 22-NOV-22 09.18.46.239 PM
24 rows selected.
例子：删除快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 24,high_snap_id => 24);
PL/SQL procedure successfully completed.
例子：查看并调整快照设置，包括收集间隔 INTERVAL（分钟），保留时间 RETENTION（分钟）以及 TOP SQL 数量
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
2419524882 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 12960,interval => 30, topnsql => 50);
PL/SQL procedure successfully completed.
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
2419524882 +00000 00:30:00.0 +00009 00:00:00.0 50
例子：收集指定 SQL
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('4xamnunv51w9j');
PL/SQL procedure successfully completed.
AWR Reports
我们可以指定起始快照和结束快照从而获取这两个快照之间所有收集的数据来生成 AWR Report。
例子：生成 AWR 报告
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2419524882 TEST 1 test
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2419524882 1 TEST test test
Using 2419524882 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
test TEST 2 22 Nov 2022 00:00 1
3 22 Nov 2022 01:00 1
4 22 Nov 2022 02:00 1
5 22 Nov 2022 03:00 1
6 22 Nov 2022 04:00 1
7 22 Nov 2022 05:00 1
8 22 Nov 2022 06:00 1
9 22 Nov 2022 07:00 1
10 22 Nov 2022 08:00 1
11 22 Nov 2022 09:00 1
12 22 Nov 2022 10:00 1
13 22 Nov 2022 11:00 1
14 22 Nov 2022 12:00 1
15 22 Nov 2022 13:00 1
16 22 Nov 2022 14:00 1
17 22 Nov 2022 15:00 1
18 22 Nov 2022 16:00 1
19 22 Nov 2022 17:00 1
20 22 Nov 2022 18:00 1
21 22 Nov 2022 19:00 1
22 22 Nov 2022 20:01 1
23 22 Nov 2022 21:00 1
25 22 Nov 2022 22:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 23
Begin Snapshot Id specified: 23
Enter value for end_snap: 25
End Snapshot Id specified: 25
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_23_25.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_23_25.html
例子：使用脚本定时自动生成 AWR
[oracle@test ~]$ vim /home/oracle/scripts/autoawr.sh
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export AWR_HOME=/home/oracle/awrrpt
export NLS_DATE_FORMAT='yyyymmdd hh24:mi:ss'
RETENTION=28
m=(`sqlplus -S "sys/123456@192.168.8.131:1521/test as sysdba" << EOF
set pagesize 0 feedback off verify off heading off echo off
select max(snap_id) from dba_hist_snapshot where begin_interval_time>to_date(to_char(sysdate,'YYYY-MM-DD')||' 06
','YYYY-MM-DD HH24') and end_interval_time<=to_date(to_char(sysdate,'YYYY-MM-DD')||' 20','YYYY-MM-DD HH24');
EOF`)
n=(`sqlplus -S "sys/123456@192.168.8.131:1521/test as sysdba" << EOF
set pagesize 0 feedback off verify off heading off echo off
select min(snap_id) from dba_hist_snapshot where begin_interval_time>to_date(to_char(sysdate,'YYYY-MM-DD')||' 06
','YYYY-MM-DD HH24') and end_interval_time<=to_date(to_char(sysdate,'YYYY-MM-DD')||' 20','YYYY-MM-DD HH24');
EOF`)
name=(`sqlplus -S "sys/123456@192.168.8.131:1521/test as sysdba" << EOF
set pagesize 0 feedback off verify off heading off echo off
select instance_name from v\\\$instance;
EOF`)
date=(`sqlplus -S "sys/123456@192.168.8.131:1521/test as sysdba" << EOF
set pagesize 0 feedback off verify off heading off echo off
select to_char(sysdate,'yyyymmddhh24') from dual;
EOF`)
sqlplus -S "sys/123456@192.168.8.131:1521/test as sysdba" << EOF
@$ORACLE_HOME/rdbms/admin/awrrpt.sql;
html
1
$n
$m
$AWR_HOME/awrrpt_1_${name}_${date}.html
EOF
find $AWR_HOME -name "awrrpt_1_*.html" -mtime +$RETENTION -exec rm {} \;
[oracle@test ~]$ chmod a+x /home/oracle/scripts/autoawr.sh
[oracle@test ~]$ crontab -l
39 8 * * 4 /home/oracle/scripts/autoawr.sh > /home/oracle/scripts/autoawr.log 2>&1
生成 AWR Report 的脚本如下：
| Item | Script |
|---|---|
| Generating an AWR Report | @$ORACLE_HOME/rdbms/admin/awrrpt.sql |
| Generating an Oracle RAC AWR Report | @$ORACLE_HOME/rdbms/admin/awrgrpt.sql |
| Generating an AWR Report on a Specific Database Instance | @$ORACLE_HOME/rdbms/admin/awrrpti.sql |
| Generating an Oracle RAC AWR Report on Specific Database Instances | @$ORACLE_HOME/rdbms/admin/awrgrpti.sql |
| Generating an AWR Report for a SQL Statement | @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql |
| Generating an AWR Report for a SQL Statement on a Specific Database Instance | @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql |
| Generating an AWR Compare Periods Report | @$ORACLE_HOME/rdbms/admin/awrddrpt.sql |
| Generating an Oracle RAC AWR Compare Periods Report | @$ORACLE_HOME/rdbms/admin/awrgdrpt.sql |
| Generating an AWR Compare Periods Report on a Specific Database Instance | @$ORACLE_HOME/rdbms/admin/awrddrpi.sql |
| Generating an Oracle RAC AWR Compare Periods Report on Specific Database Instances | @$ORACLE_HOME/rdbms/admin/awrgdrpi.sql |
Wait Events
等待事件是由服务器进程或线程递增的统计信息, 用于表示在能够继续处理某一事件之前必须等待其他事件完成。等待事件数据显示可能影响性能的各种问题的症状, 如闩锁争用、缓冲区争用和I/O争用。
等待事件只是问题的症状，而不是真正的原因。
等待事件被分为很多类，包括：管理（Administrative）、应用程序（Application）、集群（Cluster）、提交（Commit）、并发（Concurrency）、配置（Configuration）、空闲（Idle）、网络（Network）、其他（Other）、调度（Scheduler）、系统 I/O（System I/O）和用户 I/O（User I/O）。
相关视图：
| 视图 | 描述 |
|---|---|
v$event_name | Oracle 所定义的所有等待事件。 |
v$system_event | 实例启动以来所有会话上发生的等待事件的累积信息，用于判断实例整体的稳定性。 |
v$session_event | 当前已连接的各个会话的等待事件累积信息，用于判断会话的整体稳定性。 使用 total_waits，total_timeouts 和 time_waited 列掌握等待现况。 |
v$session_wait | 会话当前等待事件的详细信息，通过 P1、P2、P3、wait_time 列观察等待现象。v$session_wait 的信息不是累积信息，是实时信息，需要反复查询才能得到所需信息。 WAIT_TIME_MICRO：等待时间的总计。如果会话在正在等待，那么值就是当前等待花费的时间，如果会话不在等待，那么这个值就是上次等待时间总和。 TIME_REMAINING_MICRO：有4个值，>0，0，-1，NULL。 >0 当前等待需要的时间，0 等待超时，-1 当前等待无限期，NULL 会话没有在等待。 TIME_SINCE_LAST_WAIT_MICRO：上次等待结束后流逝的时间，如果会话正在等待，则值为 0。 |
v$session_wait_history | 显示每个活动会话的最后 10 个等待事件 |
v$system_wait_class | 实例级别等待类的等待时间总计 |
v$session_wait_class | 会话级别等待类的等待时间总计 |
v$event_histogram | 等待事件在各个时间段的等待次数 |
buffer busy waits
产生原因
当会话想要访问缓冲区高速缓存中的数据库块但由于缓冲区“忙”时而不能访问会发生这种等待。可能发生这种情况的两个主要原因是：
- 另一个会话是将块读入缓冲区
- 另一个会话将缓冲区保持在与我们的请求不兼容的模式中
- Inefficient SQL statements read more blocks than necessary. If there are many sessions running these statements, they will attempt to read the same blocks and possibly wait on this event.
- If the FREELISTS parameter for a table is too low, multiple sessions that are attempting to insert rows in the same table and end up waiting for freelists. This problem shows up as contention for the segment header of the table.
- Multiple sessions are attempting to change an index block (possibly do to an insert).
- The INITRANS parameters is too low for a segment. Any DML operation that needs to go into the block needs to lock an Interested Transaction List (ITL). If INITRANS parameter is set too low, then there will be less number of ITLs allocated originally. Oracle can only allocate more ITLs if there is space in PCTFREE area of the block. If, however, there is no space to increase the ITL, the transactions that cannot lock ITLs will have to wait until the previous transactions have completed operations on the block. The waiter will register ‘buffer busy wait’ for the block
模拟等待
会话1
SQL> create table tb1 (id int ,name varchar2(10));
Table created.
SQL> insert into tb1 values (1,'scott');
1 row created.
SQL> insert into tb1 values (2,'tom');
1 row created.
SQL> commit;
Commit complete.
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
1
会话2
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
27
会话1
SQL> begin
for i in 1..100000 loop
update tb1 set name='rose' where id=2;
commit;
end loop;
end;
/
会话2
SQL> begin
for i in 1..100000 loop
update tb1 set name='john' where id=1;
commit;
end loop;
end;
/
会话3
SQL> SELECT g.inst_id,g.sid,g.serial#,g.event,g.username,g.sql_hash_value,s.sql_fulltext
FROM gv$session g,v$sql s
WHERE g.sql_hash_value = s.hash_value and username='HR' and event='buffer busy waits';
INST_ID SID SERIAL## EVENT USERNAME SQL_HASH_VALUE SQL_FULLTEXT
---------- ---------- ---------- ------------------ ---------- -------------- --------------------------------------
1 1 7 buffer busy waits HR 401484711 UPDATE TB1 SET NAME='rose' WHERE ID=2
1 27 49 buffer busy waits HR 2040921087 UPDATE TB1 SET NAME='john' WHERE ID=1
SQL> select event,sid,p1,p2,p3 from v$session_wait_history where event='buffer busy waits'
EVENT SID P1 P2 P3
-------------------- ---------- ---------- ---------- ----------
buffer busy waits 1 4 5471 1
buffer busy waits 1 4 5471 1
buffer busy waits 1 4 5471 1
buffer busy waits 1 4 5471 1
buffer busy waits 1 4 5471 1
buffer busy waits 27 4 5471 1
buffer busy waits 27 4 5471 1
buffer busy waits 27 4 5471 1
buffer busy waits 27 4 5471 1
buffer busy waits 27 4 5471 1
10 rows selected.
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME FROM DBA_EXTENTS A WHERE FILE_ID = 4 AND 5471 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME PARTITION_NAME
---------- -------------------- ------------------ ----------------- ---------------
HR TB1 TABLE USERS
定位问题
定位到热点块：
select event,sid,p1,p2,p3 from v$session_wait_history where event='buffer busy waits';
定位到块所属的段：
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME FROM DBA_EXTENTS A WHERE FILE_ID = 4 AND 5471 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
定位到造成等待的SQL（需要在等待出现的时候才会有结果）：
SELECT g.inst_id,g.sid,g.serial#,g.event,g.username,g.sql_hash_value,s.sql_fulltext
FROM gv$session g,v$sql s
WHERE g.sql_hash_value = s.hash_value and username='HR' and event='buffer busy waits';
解决方法
As buffer busy waits are due to contention for particular blocks then you cannot take any action until you know which blocks are being competed for and why. Eliminating the cause of the contention is the best option. Note that "buffer busy waits" for data blocks are often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index) - the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk - the other sessions (scanning the same index) quickly 'catch up' and want the block which is currently being read from disk - they wait for the buffer as someone is already reading the block in.
The following hints may be useful for particular types of contention - these are things that MAY reduce contention for particular situations:
| Block Type | Possible Actions |
|---|---|
| data blocks | Eliminate HOT blocks from the application. Check for repeatedly scanned / unselective indexes. Change PCTFREE and/or PCTUSED. Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes). Increase INITRANS. Reduce the number of rows per block. |
| segment header | Increase of number of FREELISTs. Use FREELIST GROUPs (even in single instance this can make a difference). |
| freelist blocks | Add more FREELISTS. In case of Parallel Server make sure that each instance has its own FREELIST GROUP(s). |
| undo header | Add more rollback segments. |
db file sequential read
产生原因
读取数据到连续的内存，通常为单块读，也有可能读取了多个块或者文件头，通常是使用了 selectivity 不高的索引从而导致访问了过多不必要的索引块或者使用了错误的索引。
定位原因
使用 AWR 查看 SQL ordered by Reads 找到物理读最多的 SQL。
解决方法
如果是由于 SQL 导致，则：
- 如果 SQL 使用了 Index Range Scan，很可能该索引的选择性不高从而导致访问了很多块，可以通过强制使用选择性高的索引来减少物理 IO。
- 如果索引碎片程度高，则可以重建索引。
- 如果索引聚族因子很大，则可以按照索引列的顺序重建表来减少聚族因子，从而减少回表的IO。
- 使用分区及其分区修剪来减少访问的索引块以及表块。
如果不是由于 SQL 导致，则：
通过 “File I/O Statistics” 定位到热点磁盘，再平衡 IO。
Oracle 9.2 后，使用 V$SEGMENT_STATISTICS 视图可以找到最多物理读的段。
如果不是由于 SQL 导致，且磁盘 IO 分布均匀，响应时间差不多，则可以增大 Buffer Cache。
清理历史数据，减少访问数据量。
db file scattered read
产生原因
当数据库在进行 FULL TABLE SCAN 或者 INDEX FAST FULL SCAN 时，可能会从磁盘多块读取数据（一次读取 DB_FILE_MULTIBLOCK_READ_COUNT 块）到非连续的缓冲区缓存，则会出现该等待事件。
定位问题
找到全表扫描的 SQL 和索引快速全扫描的 SQL
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL'
order by p.hash_value, t.piece;
找到索引快速全扫描的SQL
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='INDEX'
and p.options='FULL SCAN'
order by p.hash_value, t.piece;
解决方法
调优 SQL。
考虑分区。
将查询分流到 ADG。
对象数据量少却占用了大量空间，考虑收缩段。
考虑压缩。
对历史数据进行分离。
cursor: pin S wait on X
产生原因
游标等待与某种形式的解析相关联。当一个会话尝试以共享模式下获取 mutex pin 时，另一个会话正在将该 mutex pin 以排他模式保持在相同的游标对象上，会话就可能会等待此事件。通常 "cursor: pin S wait on X" 是一种症状，而不是原因。需要定位到问题进行调优。
产生 "cursor: pin S wait on X" 的原因：
共享池配置不合适。如果共享池太小，则会经常出现该等待。
频繁的硬解析（没有使用绑定变量，统计信息过时）。
解析版本太多，导致搜索解析链表耗时长而导致该等待。
解析错误。
Bug。
定位问题
在 11g 以后，持有 mutex 的会话显示在视图 V$SESSION 的 BLOCKING_SESSION 列中。
SELECT SQL_ID, ACTION, BLOCKING_SESSION, BLOCKING_SESSION_STATUS
FROM v$session
WHERE SID=&SID_OF_WAITING_SESSION;
在 11g 和 12g 中，可以使用以下 sql 直接找到阻塞会话：
select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT
from v$session where event ='cursor：pin S wait on X';
解决方法
- 收集相关对象的统计信息。
- 调优解析量高的 SQL，硬解析较多考虑使用绑定变量，软解析比例高则查看应用是否使用了 shareable SQLs，做到一次解析多次执行。
- 解决解析高版本问题，通过 V$SQL_SHARED_CURSOR 查看高版本问题的原因，或者高版本相关的 bug。使用 hint 注释 SQL 使 SQL 映射到不同的 hash_value 和父游标。
- 通过使用 hint，outlines，SPM 等固化执行计划，减少解析。
- 如果是解析错误，可以使用 10035 事件进行跟踪，找到问题 SQL。
alter system set events '10035 trace name context forever,level 5';
alter system set events '10035 trace name context off';
latch: cache buffers chains
产生原因
当一个数据块读入到 sga 中时，该块的块头（buffer header）会放置在一个 hash bucket 的链表（hash chain）中。该内存结构由一系列 cache buffers chains 子 latch 保护（又名 hash latch 或者 cbc latch）。对Buffer cache 中的块，要 select 或者 update、insert、delete 等，都得先获得 cache buffers chains 子 latch，以保证对 chain 的排他访问。若在过程中发生争用，就会等待 latch: cache buffers chains 事件。
发生 latch: cache buffers chains 等待的情况：
- 同一个 cache buffers chains 下不同 block 被频繁访问，称为 hot chains
- 同一个 cache buffers chains 下同一个块被频繁访问，称为 hot block
产生的原因：
低效率的 SQL 语句（主要体现在逻辑读过高）
buffer cache 太少
Hot Block
解决方法
Look for SQL that accesses the blocks in question and determine if the repeated reads are necessary. This may be within a single session or across multiple sessions.
Check for suboptimal SQL(this is the most common cause of the events) - look at the execution plan for the SQL being run and try to reduce the gets per executions which will minimize the number of blocks being accessed and therefore reduce the chances of multiple sessions contending for the same block.
enq: TM - contention
产生原因
- 子表外键无索引
- 外键没有索引，确实可能导致子表产生表锁，但是有前提：
- 子表有删改操作。
- 主表有删操作，或者更新主键的操作。
- 外键不建索引，则删除主表记录或主子表关联查询，都会进行子表的全表扫描。
- 主子表任何插入操作，无论顺序，不会产生锁
并行 DML
模拟等待
模拟子表外键无索引：
创建父表并插入数据
SQL> create table dept as select department_id,department_name from departments where 1=0;
SQL> alter table dept add constraint pk_dept_department_id primary key(department_id);
SQL> insert into dept values(1,'aa');
SQL> insert into dept values(2,'bb');
SQL> insert into dept values(3,'cc');
SQL> commit;
创建子表并插入数据
SQL> create table emp as select employee_id,department_id from employees where 1=0;
SQL> alter table emp add constraint fk_emp_department_id foreign key(department_id) references dept(department_id) on delete cascade;
SQL> insert into emp values(1,1);
SQL> insert into emp values(2,1);
SQL> insert into emp values(3,2);
SQL> commit;
SQL> select * from dept;
DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
1 aa
2 bb
3 cc
SQL> select * from emp;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
2 1
3 2
父表的 insert 阻塞父表的 delete：
| 会话1 | 会话2 |
|---|---|
| SQL> insert into dept values(4,'dd'); 1 row created. | |
| SQL> delete from dept where department_id=1; |
父表的 delete 阻塞父表的 delete：
| 会话1 | 会话2 |
|---|---|
| SQL> delete from dept where department_id=1; 1 row deleted. | |
| SQL> delete from dept where department_id=2; |
子表的 delete 阻塞父表的 delete：
| 会话1 | 会话2 |
|---|---|
| SQL> delete emp where department_id=1; 2 rows deleted. | |
| SQL> delete from dept where department_id=2; |
子表的 update 阻塞父表的 delete：
| 会话1 | 会话2 |
|---|---|
| SQL> update emp set department_id=3 where employee_id=3; 1 row updated. | |
| SQL> delete from dept where department_id=1; |
父表对父表的阻塞：
| 发起的会话 | Insert | Delete | Update |
|---|---|---|---|
| Insert | N | Y | N |
| Delete | N | Y | N |
| Update | N | N | N |
子表对父表的阻塞：
| 发起的会话 | Insert | Delete | Update |
|---|---|---|---|
| Insert | N | Y | N |
| Delete | N | Y | N |
| Update | N | Y | N |
以上为子表外键无索引导致的阻塞。如果在子表上创建了索引，则相应的阻塞就消失了。
定位原因
检查没有索引的外键
SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name, column_position;
定位阻塞者和等待者
select /*+rule*/inst_id,decode(request, 0, 'holder', 'waiter') role,sid,type,id1,id2,lmode,request,ctime,block
from gv$lock
where (id1, id2, type) in (select id1, id2, type from gv$lock where request >0)
order by ctime desc ,role;
INST_ID ROLE SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ------ ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
1 holder 1 TM 88802 0 3 0 204 1
1 waiter 22 TM 88802 0 0 5 198 0
定位等待者会话及 SQL
SELECT g.inst_id,g.sid,g.serial#,g.event,g.username,g.blocking_session,g.sql_hash_value,s.sql_fulltext
FROM gv$session g,v$sql s
WHERE g.sql_hash_value = s.hash_value and username='HR';
INST_ID SID SERIAL## EVENT USERNAME BLOCKING_SESSION SQL_HASH_VALUE SQL_FULLTEXT
------- ---- ------- --------------------- -------- ---------------- -------------- --------------------------------------
1 22 37 enq: TM - contention HR 1 2415659695 delete from dept where department_id=1
解决方法
在子表外键上面创建索引。使用以下脚本获取创建脚本。
SELECT 'CREATE INDEX '
|| OWNER
|| '.'
|| REPLACE(CONSTRAINT_NAME,'FK_','IX_')
|| ' ON '
|| OWNER
|| '.'
|| TABLE_NAME
|| ' ('
|| FK_COLUMNS
||') TABLESPACE '
||
(
SELECT TABLESPACE_NAME
FROM DBA_TABLES
WHERE OWNER= CON.OWNER
AND TABLE_NAME= CON.TABLE_NAME) CREATE_INDEXES_ON_FOREIGN_KEY
FROM (
SELECT CC.OWNER,
CC.TABLE_NAME,
CC.CONSTRAINT_NAME,
LISTAGG(CC.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY CC.POSITION) FK_COLUMNS
FROM DBA_CONS_COLUMNS CC,
DBA_CONSTRAINTS DC
WHERE CC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME
AND DC.CONSTRAINT_TYPE = 'R'
AND CC.OWNER = DC.OWNER
AND DC.OWNER NOT IN ( 'SYS',
'SYSTEM',
'OLAPSYS',
'SYSMAN',
'MDSYS',
'ADMIN' )
GROUP BY CC.OWNER,
CC.TABLE_NAME,
CC.CONSTRAINT_NAME) CON
WHERE NOT EXISTS
(
SELECT 1
FROM (
SELECT TABLE_OWNER,
TABLE_NAME,
INDEX_NAME,
LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_POSITION) FK_COLUMNS
FROM DBA_IND_COLUMNS
WHERE INDEX_OWNER NOT IN ( 'SYS',
'SYSTEM',
'OLAPSYS',
'SYSMAN',
'MDSYS',
'ADMIN' )
GROUP BY TABLE_OWNER,
TABLE_NAME ,INDEX_NAME) COL
WHERE CON.OWNER = COL.TABLE_OWNER
AND CON.TABLE_NAME = COL.TABLE_NAME
AND CON.FK_COLUMNS = SUBSTR(COL.FK_COLUMNS, 1, LENGTH(CON.FK_COLUMNS)) )
ORDER BY 1;
enq: TX - allocate ITL entry
产生原因
默认情况下，表的 INITRANS 值为 1，索引的值为 2。这定义了一个称为相关事务列表（ITL）的内部块结构。为了修改块中的数据，进程需要使用空的 ITL 槽来记录修改块中数据的事物信息。如果没有足够的空闲 ITL 插槽，则将在块中保留的空闲空间中获取新的插槽。如果用完空闲空间并且有太多并发 DML 事务竞争相同的数据块，则会出现等待事件 "enq: TX - allocate ITL entry"。
模拟等待
会话1
SQL> create table t_itl_lhr(a int) pctfree 0 initrans 1;
Table created.
SQL> begin
for i in 1..2000 loop
insert into t_itl_lhr values(i);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> SELECT F, B, COUNT(*)
FROM (SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) F,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) B
FROM T_ITL_LHR)
GROUP BY F, B
ORDER BY F,B;
F B COUNT(*)
---------- ---------- ----------
4 5459 733
4 5460 534
4 5463 733
SQL> UPDATE T_ITL_LHR SET A=A WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=5459 AND DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=1;
1 row updated.
会话2
SQL> UPDATE T_ITL_LHR SET A=A WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=5459 AND DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=2;
1 row updated.
会话3
SQL> UPDATE T_ITL_LHR SET A=A WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=5459 AND DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=3;
定位问题
定位当前阻塞者，等待者以及请求模式
select /*+rule*/inst_id,decode(request, 0, 'holder', 'waiter') role,sid,type,id1,id2,lmode,request,ctime,block
from gv$lock
where (id1, id2, type) in (select id1, id2, type from gv$lock where request >0)
order by ctime desc ,role;
INST_ID ROLE SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ------ ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
1 holder 32 TX 262165 846 6 0 558 1
1 waiter 36 TX 262165 846 0 4 232 0
定位等待者的 SQL
SELECT g.inst_id,g.sid,g.serial#,g.event,g.username,g.sql_hash_value,s.sql_fulltext
FROM gv$session g,v$sql s
WHERE g.sql_hash_value = s.hash_value and username='HR';
INST_ID SID SERIAL## EVENT USERNAME SQL_HASH_VALUE SQL_FULLTEXT
------- ---- ------- ---------------------------- ---------- -------------- --------------------------------------------------------------------------------
1 36 13 enq: TX - allocate ITL entry HR 3669332419 UPDATE T_ITL_LHR SET A=A WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)=5459 AND DBM
使用 AWR 在 "Top 5 Timed Foreground Events" 查看是否有 "enq: TX - allocate ITL entry "。在 "Segments by ITL Waits" 查看等待对象。
解决方法
enq: TX – allocate ITL entry 为分配 ITL 条目的等待，因为 PCTFREE 不足，BLOCK 中没有足够空间分配 ITL，ORACLE 只能重用 ITL，但是这个时候由于没有 COMMIT，无法重用 ITL，所以会出现 allocate ITL 等待事件。要解决此类问题，我们可以考虑增加 PCTFREE 和 INITRANS 大小（ALTER TABLE tablename PCTFREE 20 INITRANS 16 ），需要注意该修改只能对于新 block 生效，已经存放数据的 block 不会发生改变。另外可以考虑修改业务逻辑，减少频繁访问。
enq: TX - row lock contention
产生原因
Waits due to Row being locked by an active Transaction：等待者请求模式为 6，不同的会话同时更新或删除同一个记录。
Waits due to Unique or Primary Key Constraint Enforcement：等待者请求模式为 4，表上存在主键或唯一索引，会话 1 插入一个值（未提交），会话 2 同时或随后也插入同样的值。会话 1 提交后，enq: TX - row lock contention 消失。
Waits due to Foreign Key Constraint Enforcement：等待者请求模式为 4，表存在主外键读情况，主表不提交，那么子表必须进行等待。
Waits due to Insufficient 'ITL' slots in a Block：等待者请求模式为 4
Waits due to rows being covered by the same BITMAP index fragment：等待者请求模式为 4，更新位图索引的一个键值，会指向多行记录，所以更新一行就会把该键值指向的所有行锁定。
模拟等待
Waits due to Row being locked by an active Transaction：等待者请求模式为 6
会话1
SQL> update emp set last_name='aaa' where employee_id=100;
1 row updated.
会话2
SQL> update emp set last_name='aaa' where employee_id=100;
Waits due to Unique or Primary Key Constraint Enforcement：等待者请求模式为 4
会话1
SQL> create table emp as select employee_id,department_id from employees;
Table created.
SQL> alter table emp modify employee_id primary key;
Table altered.
SQL> insert into emp values(207,80);
1 row created.
会话2
SQL> insert into emp values(207,80);
Waits due to Foreign Key Constraint Enforcement：等待者请求模式为 4
会话1
SQL> create table dept as select department_id,department_name from departments;
Table created.
SQL> alter table dept modify department_id primary key;
Table altered.
SQL> alter table emp add constraint fx_emp_deptid foreign key(department_id) references dept(department_id);
Table altered.
SQL> insert into dept values(300,'aaa');
1 row created.
会话2
SQL> insert into emp values(207,300);
Waits due to Insufficient 'ITL' slots in a Block：等待者请求模式为 4
- Oracle 会记录哪些行被每个数据块头区域中的哪个事务锁定，称为 “相关事务列表”。对象中任何块中的 ITL 槽数由 INITRANS 和 MAXTRANS 属性控制。INITRANS 是首次使用时在块中最初创建的事物槽数，而 MAXTRANS 设置上限。每个想要修改块的事务都需要块中此 “ITL” 列表中的一个槽。
- MAXTRANS 对并发事务的数量设置上限，这些事务可以在块内的任何单个时间点处于活动状态。
- INITRANS 提供最低保证的 “每块” 并发性。
- 如果超过 INITRANS 但小于 MAXTRANS 的事物想要在同一个区块内同时处于活动状态，则 ITL 列表将被扩展，前提是块内有空闲空间。
- 如果没有空闲的 “ITL”，则请求会话将在模式 4 中的一个活动事务锁上等待。
- 在早期版本的 Oracle 数据库中，MAXTRANS 参数限制了可以同时使用数据块中数据的事务条目数。此参数已在 10g 及更高版本中弃用。Oracle 数据库现在自动允许任何数据块最多 255 个并发更新事务，具体取决于块中的可用空间。
Waits due to rows being covered by the same BITMAP index fragment：等待者请求模式为 4
会话1
SQL> create table emp(id number,sex varchar2(10));
Table created.
SQL> create bitmap index idx_emp_sex on emp(sex);
Index created.
SQL> insert into emp values(1,'male');
1 row created.
SQL> insert into emp values(2,'male');
1 row created.
SQL> commit;
Commit complete.
SQL> update emp set sex='female' where id=1;
1 row updated.
会话2
SQL> update emp set sex='female' where id=2;
定位问题
定位当前阻塞者，等待者以及请求模式
SQL> SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX';
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
33 TX 196631 1022 0 6
32 TX 196631 1022 6 0
SQL> select sid,serial#,action,schemaname,blocking_session,blocking_session_status from v$session where schemaname='HR' and blocking_session is not null;
SID SERIAL## ACTION SCHEMANAME BLOCKING_SESSION BLOCKING_SE
---------- ---------- ---------- -------------------- ---------------- -----------
33 37 HR 32 VALID
会话32阻塞了会话33
SQL> select /*+rule*/inst_id,decode(request, 0, 'holder', 'waiter') role,sid,type,id1,id2,lmode,request,ctime,block
from gv$lock
where (id1, id2, type) in (select id1, id2, type from gv$lock where request >0)
order by ctime desc ,role;
INST_ID ROLE SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ------ ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
1 holder 32 TX 196631 1022 6 0 758 1
1 waiter 33 TX 196631 1022 0 6 720 0
定位等待者的 SQL
SQL> SELECT g.inst_id,g.sid,g.serial#,g.event,g.username,g.sql_hash_value,s.sql_fulltext
FROM gv$session g,v$sql s
WHERE g.wait_class = 'Application' AND g.sql_hash_value = s.hash_value;
INST_ID SID SERIAL## EVENT USERNAME SQL_HASH_VALUE SQL_FULLTEXT
------- ---- ------- ----------------------------- ---------- -------------- --------------------------------------------------
1 33 35 enq: TX - row lock contention HR 205260137 update emp set last_name='aaa' where employee_id=100
定位等待的对象、文件、块和行
SQL> SELECT row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
FROM v$session
WHERE event='enq: TX - row lock contention'
AND state='WAITING';
ROW_WAIT_OBJ## ROW_WAIT_FILE## ROW_WAIT_BLOCK## ROW_WAIT_ROW#
------------- -------------- --------------- -------------
88725 4 3447 9
SQL> select owner, object_name from dba_objects where object_id=88725;
OWNER OBJECT_NAME
---------- ---------------
HR EMP
使用 AWR 在 "Top 5 Timed Foreground Events" 查看是否有 "enq: TX - row lock contention"。在 ”Segment Statistics” 的 ”Segments by Row Lock Waits” 中定位等待的对象。根据等待的对象定位涉及到的 SQL。使用 ASH 或者 ADDM 进一步定位涉及到的会话以及 SQL。
解决方法
实例层面：
- 定位到阻塞会话，确认是否可以 KILL，如果可以 KILL，则 KILL 解决。
应用层面：
约束通常是为了保证数据完整性，在并发场景下，应充分考虑事务进行的逻辑顺序，避免多个会话事务交叉进行，触发约束冲突在事务级别发生竞争；
要提高并发效率，应当尽可能拆分大事务为小事务，提高 TX ENQUEUE 锁的获取释放速度；
如果要使用悲观锁（FOR UPDATE），应尽可能减少锁定的行范围；
数据库层面：
在 DML 频繁的表上建立适当的索引，提高 SQL 执行的效率，减少 TX ENQUEUE 锁持有的时间；避免全表扫描这种，容易造成 IO 开销巨大，热块竞争，会话堆积的访问方式。
在 DML 频繁的表上不应使用位图索引；
对于 DML 频繁的表，不应使用 IOT 表，物化视图等；
RAC 环境下，对于批量的 DML 操作，尽可能固定在单一节点，尽量降低网络开销、集群竞争、一致性块获取和日志刷盘等带来的影响。
注意事项：
RAC 环境下，如果要杀阻塞者会话，需要准确识别 INSTANCE，否则可能误杀其他重要进程。
在查询发生产生锁竞争的 SQL 语句时或具体行时，通常是查询被阻塞者执行的 SQL 语句或等待的行，因为阻塞者，未提交事务，可以未有动作处于 INACTIVE 状态，如果是 ACTIVE 状态，也是去执行别的 SQL 语句了，真正引发锁竞争的 SQL 可能被挤出库缓存，要查到 SQL 语句需要通过像 LOGMNR 这样的工具去挖日志。
Memory
AMM
自动内存管理（AMM）允许Oracle数据库自动调整 SGA 和 PGA 大小。只需要设置目标内存初始化参数（memory_target）和最大内存初始化参数（memory_max_target），数据库根据需要在 SGA 和 PGA 间动态调整内存。
例子：查看目标内存和最大内存初始化参数
SQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 800M
SQL> show parameter memory_max_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 800M
ASMM
使用自动共享内存管理（ASMM）简化 SGA 的内存管理。使用 SGA_TARGET 初始化参数指定实例的 SGA 可用内存，数据库自动调整 SGA 各组件。
从 AMM 修改为 ASMM 的步骤：
- 设置 MEMORY_TARGET 初始化参数 0。
ALTER SYSTEM SET MEMORY_TARGET = 0;
基于当前 SGA 内存分配设置 SGA_TARGET。
- 设置自动调整的 SGA 组件的值为 0。完成后重启实例。
例子：从 AMM 修改为 ASMM
SQL> select component,current_size/1024/1024 from v$memory_dynamic_components where component='SGA Target';
COMPONENT CURRENT_SIZE/1024/1024
------------------------------ ----------------------
SGA Target 520
SQL> alter system set memory_target=0;
System altered.
SQL> alter system set sga_target=520m;
System altered.
SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
SQL> show parameter pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_size big integer 0
streams_pool_size big integer 0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 542814208 bytes
Fixed Size 2255032 bytes
Variable Size 213911368 bytes
Database Buffers 322961408 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
可以设置 SGA_TARGET 为 0 禁用自动共享内存管理。在这种情况下，所有自动优化的参数的值设置为对应组件的当前值，即使用户之前为自动优化的参数指定了一个非零值。
例子：禁用 ASMM
SQL> select component,current_size/1024/1024 from v$memory_dynamic_components;
COMPONENT CURRENT_SIZE/1024/1024
------------------------------ ----------------------
shared pool 140
large pool 8
java pool 4
streams pool 4
SGA Target 520
DEFAULT buffer cache 356
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
Shared IO Pool 0
PGA Target 280
ASM Buffer Cache 0
16 rows selected.
SQL> show parameter pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_size big integer 0
streams_pool_size big integer 0
SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
SQL> alter system set sga_target=0;
System altered.
SQL> show parameter pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_shared_io_pool_size big integer 0
global_context_pool_size string
java_pool_size big integer 4M
large_pool_size big integer 8M
olap_page_pool_size big integer 0
shared_pool_size big integer 140M
streams_pool_size big integer 4M
SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 356M
从手动的共享内存管理修改为 ASMM 的步骤：
- 运行以下查询获取 SGA_TARGET 的值：
SELECT ((SELECT SUM(value) FROM V$SGA) - (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)) “SGA_TARGET” FROM DUAL;
- 设置 SGA_TARGET：
ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]
其中 value 是上一步计算的值或者是所有 SGA 组件的和到 SGA_MAX_SIZE 之间的值。
- 设置自动调整的 SGA 组件的值为 0，可以通过编辑文本初始化参数文件或者使用 ALTER SYSTEM 语句执行，根据需要重启实例。
| Instance | SGA | PGA | Description | Initialization Parameters |
|---|---|---|---|---|
| Auto | n/a | n/a | The database tunes the size of the instance based on a single instance target size. | You set: Total memory target size for the database instance (MEMORY_TARGET) Optional maximum memory size for the database instance (MEMORY_MAX_TARGET) |
| n/a | Auto | Auto | The database automatically tunes the SGA based on an SGA target.The database automatically tunes the PGA based on a PGA target. | You set: SGA target size (SGA_TARGET) Optional SGA maximum size (SGA_MAX_SIZE) Instance PGA target size (PGA_AGGREGATE_TARGET) |
| n/a | Auto | Manual | The database automatically tunes the SGA based on an SGA target.You control the PGA manually, setting the maximum work area size for each type of SQL operator. | You set: SGA target size (SGA_TARGET) Optional SGA maximum size (SGA_MAX_SIZE) PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE |
| n/a | Manual | Auto | You control the SGA manually by setting individual component sizes.The database automatically tunes the PGA based on a PGA target. | You set: Shared pool size (SHARED_POOL_SIZE) Buffer cache size (DB_CACHE_SIZE) Large pool size (LARGE_POOL_SIZE) Java pool size (JAVA_POOL_SIZE) Streams pool size (STREAMS_POOL_SIZE) Instance PGA target size (PGA_AGGREGATE_TARGET) |
| n/a | Manual | Manual | You must manually configure SGA component sizes.You control the PGA manually, setting the maximum work area size for each type of SQL operator. | You must manually configure SGA component sizes. You set: Shared pool size (SHARED_POOL_SIZE) Buffer cache size (DB_CACHE_SIZE) Large pool size (LARGE_POOL_SIZE) Java pool size (JAVA_POOL_SIZE) Streams pool size (STREAMS_POOL_SIZE) PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE |
SQL Tuning
Execution Plan
调优 SQL 的第一步就是获取 SQL 的执行计划，下面看看如何获取 Oralce 数据库（11gR2）的执行计划。
EXPLAIN PLAN
如果使用 PL/SQL Developer，直接按 F5 就会出来对应 SQL 的执行计划。
如果使用 SQL*Plus，则使用如下方法获取的执行计划与以上使用 PL/SQL Developer 获取的执行计划相同。
SQL> explain plan for select employee_id,last_name,salary from employees where employee_id=100;
Explained.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
14 rows selected.
SET AUTOTRACE
上面方法获取的执行计划没有统计信息，如果使用 SQL*Plus，可以使用 SET AUTOTRACE 命令来得到后续执行 SQL 的执行计划及其统计信息。
语法如下：
SET AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autotrace traceonly
--以上表示只生成执行计划，不将SQL结果显示出来。
SQL> select employee_id,last_name,salary from employees where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
677 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到使用这种方式可以得到 SQL 执行所耗费的物理读、逻辑读等统计信息。
DBMS_XPLAN
如果某条 SQL 已经执行过了，且其执行计划还在内存中，则后续可以使用 dbms_xplan.display_cursor 来查看。
语法：
select * from table(dbms_xplan.display_cursor('sql_id/hash_values',child_cursor_number,'advanced'));
执行 SQL：
SQL> select employee_id,last_name,salary from employees where employee_id=100;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 24000
直接查看刚刚执行过的 SQL 的执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID ftsw1cm8qh6nq, child number 0
-------------------------------------
select employee_id,last_name,salary from employees where employee_id=100
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22]
2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22]
45 rows selected.
如果 SQL 已经执行过了，则需要通过先查询 V$SQL，找到该 SQL 的 SQL_ID 再来查看执行计划。
SQL> select sql_text,sql_id from v$sql where sql_text like 'select employee_id,last_name%';
SQL_TEXT SQL_ID
-------------------------------------------------------------------------------- -------------
select employee_id,last_name,salary from employees where employee_id=100 ftsw1cm8qh6nq
SQL> select * from table(dbms_xplan.display_cursor('ftsw1cm8qh6nq',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID ftsw1cm8qh6nq, child number 0
-------------------------------------
select employee_id,last_name,salary from employees where employee_id=100
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22]
2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22]
45 rows selected.
如果目标 SQL 有多个执行计划，需要查看某个执行计划，只需要指定目标 SQL 的 child_cursor_number 就可以了。
SQL> conn sh/sh
Connected.
SQL> create table employees as select * from hr.employees;
Table created.
SQL> select employee_id,last_name,salary from employees where employee_id=100;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 24000
SQL> conn hr/hr
Connected.
SQL> select sql_text,sql_id,child_number from v$sql where sql_text like 'select employee_id,last_name%';
SQL_TEXT SQL_ID CHILD_NUMBER
-------------------------------------------------------------------------------- ------------- ------------
select employee_id,last_name,salary from employees where employee_id=100 ftsw1cm8qh6nq 0
select employee_id,last_name,salary from employees where employee_id=100 ftsw1cm8qh6nq 1
SQL> select * from table(dbms_xplan.display_cursor('ftsw1cm8qh6nq',1,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID ftsw1cm8qh6nq, child number 1
-------------------------------------
select employee_id,last_name,salary from employees where employee_id=100
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 40 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMPLOYEES"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25],
"SALARY"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement (level=2)
47 rows selected.
当然也可以不指定 child_cursor_number，获取所有版本的执行计划。
SQL> select * from table(dbms_xplan.display_cursor('ftsw1cm8qh6nq',null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID ftsw1cm8qh6nq, child number 0
-------------------------------------
select employee_id,last_name,salary from employees where employee_id=100
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22]
2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22]
SQL_ID ftsw1cm8qh6nq, child number 1
-------------------------------------
select employee_id,last_name,salary from employees where employee_id=100
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 40 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMPLOYEES"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25],
"SALARY"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement (level=2)
92 rows selected.
其中 EXPLAIN PLAN 和 SET AUTOTRACE 在目标 SQL 使用了绑定变量的情况下，获得的执行计划有可能是不准的。
--创建测试表：
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> select count(*) from test1;
COUNT(*)
----------
92110
--创建索引：
SQL> create index idx_test1_id on test1(object_id);
Index created.
--进行更新：
SQL> update test1 set object_id=1 where object_id<90000;
86864 rows updated.
SQL> commit;
Commit complete.
--收集统计信息：
SQL> exec dbms_stats.gather_table_stats('SYS','TEST1',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns');
PL/SQL procedure successfully completed.
--创建一个绑定变量并赋值：
SQL> var x number;
SQL> exec :x := 1;
PL/SQL procedure successfully completed.
--使用EXPLAIN PLAN获取执行计划：
SQL> explain plan for select count(*) from test1 where object_id = :x;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3237771381
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST1_ID | 18 | 72 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:X))
14 rows selected.
--使用SET AUTOTRACE获取执行计划：
SQL> set autot traceonly
SQL> select count(*) from test1 where object_id=:x;
Execution Plan
----------------------------------------------------------
Plan hash value: 3237771381
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST1_ID | 18 | 72 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:X))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
514 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autot off
--使用DBMS_XPLAN获取执行计划：
SQL> select count(*) from test1 where object_id=:x;
COUNT(*)
----------
86864
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID drj4fkb9vj73m, child number 0
-------------------------------------
select count(*) from test1 where object_id=:x
Plan hash value: 746261920
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 86 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_TEST1_ID | 85968 | 335K| 86 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TEST1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
49 rows selected.
可以看到 EXPLAIN PLAN，SET AUTOTRACE 获取的执行计划与 DBMS_XPLAN 不一致，说明在使用绑定变量的情况下，最好使用 DBMS_XPLAN 来获取准确的执行计划。
如果目标 SQL 的执行计划被刷出了 shared_pool，就需要使用 dbms_xplan.display_awr 来查看历史执行计划了。
语法：
select * from table(dbms_xplan.display_awr('sql_id'));
SQL> conn hr/hr
Connected.
--先清空共享池
SQL> alter system flush shared_pool;
System altered.
SQL> select employee_id,last_name,salary from employees where employee_id=100;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 24000
SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select employee_id,last_name%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
--------------------------------------------------------------------------- ------------- ---------- ------------
select employee_id,last_name,salary from employees where employee_id=100 ftsw1cm8qh6nq 3513260694 0
--创建AWR快照
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
--再清空共享池
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select employee_id,last_name%';
no rows selected
--找不到该SQL的执行计划
SQL> select * from table(dbms_xplan.display_cursor('ftsw1cm8qh6nq',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID: ftsw1cm8qh6nq, child number: 0 cannot be found
--这个时候就需要使用dbms_xplan.display_awr('sql_id')来查看目标SQL的历史执行计划了
SQL> select * from table(dbms_xplan.display_awr('ftsw1cm8qh6nq'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID ftsw1cm8qh6nq
--------------------
select employee_id,last_name,salary from employees where employee_id=100
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 40 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL_ID ftsw1cm8qh6nq
--------------------
select employee_id,last_name,salary from employees where employee_id=100
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 16 | 1 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
31 rows selected.
使用 dbms_xplan.display_awr('sql_id') 获取的执行计划缺少谓词信息。
也可以调用 AWR 脚本来生成目标 SQL 的执行计划：
SQL> conn hr/hr
Connected.
SQL> alter system flush shared_pool;
System altered.
SQL> select employee_id,last_name,salary from employees where employee_id=100;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 24000
SQL> select sql_text,sql_id from v$sql where sql_text like 'select employee_id,last_name%';
SQL_TEXT SQL_ID
--------------------------------------------------------------------------- -------------
select employee_id,last_name,salary from employees where employee_id=100 ftsw1cm8qh6nq
--将目标SQL标记为AWR必须抓取的SQL
SQL> exec dbms_workload_repository.add_colored_sql('ftsw1cm8qh6nq');
PL/SQL procedure successfully completed.
SQL> select * from dba_hist_colored_sql;
DBID SQL_ID CREATE_TIME
---------- ------------- ------------------
2569180619 ftsw1cm8qh6nq 26-JAN-21
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
--生成目标SQL的AWR报告
SQL> @?/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2569180619 RAC 1 rac1
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2569180619 1 RAC rac1 dbrac10
2569180619 2 RAC rac2 dbrac11
Using 2569180619 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
rac1 RAC 21909 26 Jan 2021 00:00 1
21910 26 Jan 2021 01:00 1
21911 26 Jan 2021 02:00 1
21912 26 Jan 2021 03:00 1
21913 26 Jan 2021 04:00 1
21914 26 Jan 2021 05:00 1
21915 26 Jan 2021 06:00 1
21916 26 Jan 2021 07:00 1
21917 26 Jan 2021 08:00 1
21918 26 Jan 2021 09:00 1
21919 26 Jan 2021 10:00 1
21920 26 Jan 2021 11:00 1
21921 26 Jan 2021 12:00 1
21922 26 Jan 2021 13:00 1
21923 26 Jan 2021 14:00 1
21924 26 Jan 2021 15:00 1
21925 26 Jan 2021 15:51 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 21924
Begin Snapshot Id specified: 21924
Enter value for end_snap: 21925
End Snapshot Id specified: 21925
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: ftsw1cm8qh6nq
SQL ID specified: ftsw1cm8qh6nq
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_21924_21925.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrsqlrpt_1_21924_21925.html
打开生成的 AWR 报告，就可以看到执行计划了，而且还有物理读、逻辑读等统计信息。
Optimizer Access Paths
Full Table Scans
全表扫描，使用多块读，需要读取该表高水位下的所有块。对应的执行计划为 TABLE ACCESS FULL。
SQL> select * from employees;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Rowid Scans
ROWID 扫描，大部分情况下 ROWID 都是通过访问索引获得的，然后再通过 ROWID 回表访问对应的记录，其执行计划为 TABLE ACCESS BY INDEX ROWID，当然也可以直接指定 ROWID 进行访问对应的记录，其执行计划为 TABLE ACCESS BY USER ROWID。
SQL> select employee_id,salary from employees where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 8 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
以上执行计划的意思就是通过扫描索引获取对应记录的 ROWID，再通过 ROWID 回表获取对应的记录。直接指定 ROWID 访问表记录的执行计划如下：
SQL> select employee_id,salary from employees where rowid='AAAXhIAAEAAABEDAAA';
Execution Plan
----------------------------------------------------------
Plan hash value: 549062733
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| EMPLOYEES | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Index Scans
INDEX UNIQUE SCAN
访问 B 树索引也有很多方式，最简单的是索引唯一扫描，出现在 WHERE 条件的字段有唯一索引，且使用等值查询的时候，返回的记录也只有 1 条。索引唯一扫描的执行计划为 INDEX UNIQUE SCAN。
--查看HR用户下EMPLOYEES表的EMPLOYEE_ID字段的索引，为唯一索引。
SQL> select a.table_name,column_name,a.index_name,index_type,uniqueness from user_indexes a,user_ind_columns b where a.index_name=b.index_name and a.table_name='EMPLOYEES' and column_name='EMPLOYEE_ID';
TABLE_NAME COLUMN_NAME INDEX_NAME INDEX_TYPE UNIQUENES
-------------------- -------------------- -------------------- -------------------- ---------
EMPLOYEES EMPLOYEE_ID EMP_EMP_ID_PK NORMAL UNIQUE
SQL> select employee_id,last_name,salary from employees where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
INDEX RANGE SCAN
如果目标 SQL 对有唯一索引字段指定范围查询条件，则会使用索引范围扫描，而对于非唯一索引字段，不管指定什么查询条件，均不会使用索引唯一扫描。索引范围扫描的执行计划为 INDEX RANGE SCAN。
--查看HR用户下EMPLOYEES表的EMPLOYEE_ID字段的索引，为唯一索引。
SQL> select a.table_name,column_name,a.index_name,index_type,uniqueness from user_indexes a,user_ind_columns b where a.index_name=b.index_name and a.table_name='EMPLOYEES' and column_name='EMPLOYEE_ID';
TABLE_NAME COLUMN_NAME INDEX_NAME INDEX_TYPE UNIQUENES
-------------------- -------------------- -------------------- -------------------- ---------
EMPLOYEES EMPLOYEE_ID EMP_EMP_ID_PK NORMAL UNIQUE
--使用范围查询条件：
SQL> select employee_id,last_name,salary from employees where employee_id between 100 and 110;
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 603312277
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 176 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 11 | 176 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 11 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
--查看HR用户下EMPLOYEES表的LAST_NAME字段的索引，为非唯一索引。
SQL> select a.table_name,column_name,a.index_name,index_type,uniqueness from user_indexes a,user_ind_columns b where a.index_name=b.index_name and a.table_name='EMPLOYEES' and column_name='LAST_NAME';
TABLE_NAME COLUMN_NAME INDEX_NAME INDEX_TYPE UNIQUENES
--------------- --------------- --------------- --------------- ---------
EMPLOYEES LAST_NAME EMP_NAME_IX NORMAL NONUNIQUE
SQL> select employee_id,last_name,salary from employees where last_name='Lee';
Execution Plan
----------------------------------------------------------
Plan hash value: 2077747057
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 16 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
INDEX FULL SCAN
当需要获取某个字段的全部数据时，如果该字段非空且有索引，或者通过 WHERE 条件将空值排除掉，则可能会使用索引全扫描，对应的执行计划为 INDEX FULL SCAN。
--查看HR用户下EMPLOYEES表的LAST_NAME字段的索引，为非唯一索引。
SQL> select a.table_name,column_name,a.index_name,index_type,uniqueness from user_indexes a,user_ind_columns b where a.index_name=b.index_name and a.table_name='EMPLOYEES' and column_name='LAST_NAME';
TABLE_NAME COLUMN_NAME INDEX_NAME INDEX_TYPE UNIQUENES
--------------- --------------- --------------- --------------- ---------
EMPLOYEES LAST_NAME EMP_NAME_IX NORMAL NONUNIQUE
--查看HR用户下EMPLOYEES表的LAST_NAME字段为非空。
SQL> select table_name,column_name,nullable from user_tab_columns where table_name='EMPLOYEES' and column_name='LAST_NAME';
TABLE_NAME COLUMN_NAME N
------------------------------ ------------------------------ -
EMPLOYEES LAST_NAME N
--查看获取last_name这个字段的所有记录的执行计划：
SQL> select last_name from employees;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2228653197
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | EMP_NAME_IX | 107 | 856 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--查看HR用户下EMPLOYEES表的DEPARTMENT_ID字段的索引，为非唯一索引。
SQL> select a.table_name,column_name,a.index_name,index_type,uniqueness from user_indexes a,user_ind_columns b where a.index_name=b.index_name and a.table_name='EMPLOYEES' and column_name='DEPARTMENT_ID';
TABLE_NAME COLUMN_NAME INDEX_NAME INDEX_TYPE UNIQUENES
--------------- --------------- -------------------- --------------- ---------
EMPLOYEES DEPARTMENT_ID EMP_DEPARTMENT_IX NORMAL NONUNIQUE
--查看HR用户下EMPLOYEES表的DEPARTMENT_ID字段，可以为空。
SQL> select table_name,column_name,nullable from user_tab_columns where table_name='EMPLOYEES' and column_name='DEPARTMENT_ID';
TABLE_NAME COLUMN_NAME N
--------------- --------------- -
EMPLOYEES DEPARTMENT_ID Y
SQL> select department_id from employees where department_id is not null;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3420648541
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 318 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | EMP_DEPARTMENT_IX | 106 | 318 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
INDEX FAST FULL SCAN
如果目标表比较大，相关字段上有索引，根据 CBO，Oracle 可能会使用索引快速全扫描（INDEX FAST FULL SCAN）来扫描索引，提高访问速度。索引快速全扫描使用多块读，可以并行执行，结果是无序的。产生 db file scattered reads 事件。
--创建一个测试表。
SQL> create table objtest as select object_id,object_name from dba_objects;
Table created.
SQL> select count(*) from objtest;
COUNT(*)
----------
87044
--创建一个非唯一索引。
SQL> create index idx_objtest_id on objtest(object_id);
Index created.
--收集统计信息。
SQL> exec dbms_stats.gather_table_stats('HR','OBJTEST',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
--获取字段所有非空值。
SQL> select object_id from objtest where object_id is not null;
87043 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1925724089
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87043 | 425K| 54 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_OBJTEST_ID | 87043 | 425K| 54 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
--获取字段某个范围的值。
SQL> select object_id from objtest where object_id between 1 and 80000;
79152 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1925724089
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77996 | 380K| 55 (2)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_OBJTEST_ID | 77996 | 380K| 55 (2)| 00:00:01 |
---------------------------------------------------------------------------------------
INDEX SKIP SCAN
对于复合索引，如果前导列的不同值很少，而非前导列的不同值很多，那么在查询谓词条件中没有指定复合索引的前导列，则可能会使用上索引跳跃扫描（INDEX SKIP SCAN）。
SQL> create table emp as select * from employees;
Table created.
SQL> alter table emp add(gender varchar2(10));
Table altered.
SQL> update emp set gender='M' where employee_id<155;
55 rows updated.
SQL> update emp set gender='F' where employee_id>=155;
52 rows updated.
SQL> commit;
Commit complete.
SQL> create index idx_emp_gender_id on emp(gender,employee_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','EMP',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
SQL> select employee_id,last_name,salary,gender from emp where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 3822662258
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 2 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_EMP_GENDER_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
How the Query Optimizer Chooses an Access Path
The query optimizer chooses an access path based on the following factors:
- The available access paths for the statement
- The estimated cost of executing the statement, using each access path or combination of paths
To choose an access path, the optimizer first determines which access paths are available by examining the conditions in the statement's
WHEREclause and itsFROMclause. The optimizer then generates a set of possible execution plans using available access paths and estimates the cost of each plan, using the statistics for the index, columns, and tables accessible to the statement. Finally, the optimizer chooses the execution plan with the lowest estimated cost.When choosing an access path, the query optimizer is influenced by the following:
Optimizer Hints
You can instruct the optimizer to use a specific access path using a hint, except when the statement's
FROMclause containsSAMPLEorSAMPLEBLOCK.Old Statistics
For example, if a table has not been analyzed since it was created, and if it has less than
DB_FILE_MULTIBLOCK_READ_COUNTblocks under the high water mark, then the optimizer thinks that the table is small and uses a full table scan. Review theLAST_ANALYZEDandBLOCKScolumns in theALL_TABLEStable to examine the statistics.
Joins
How the Query Optimizer Executes Join Statements
To choose an execution plan for a join statement, the optimizer must make these interrelated decisions:
Access Paths
As for simple statements, the optimizer must choose an access path to retrieve data from each table in the join statement.
Join Method
To join each pair of row sources, Oracle Database must perform a join operation. Join methods include nested loop, sort merge, cartesian, and hash joins.
Join Order
To execute a statement that joins more than two tables, Oracle Database joins two of the tables and then joins the resulting row source to the next table. This process continues until all tables are joined into the result.
How the Query Optimizer Chooses Execution Plans for Joins
The query optimizer considers the following when choosing an execution plan:
- The optimizer first determines whether joining two or more tables definitely results in a row source containing at most one row. The optimizer recognizes such situations based on
UNIQUEandPRIMARYKEYconstraints on the tables. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables.- For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule. Similarly, when a subquery has been converted into an antijoin or semijoin, the tables from the subquery must come after those tables in the outer query block to which they were connected or correlated. However, hash antijoins and semijoins are able to override this ordering condition in certain circumstances.
With the query optimizer, the optimizer generates a set of execution plans, according to possible join orders, join methods, and available access paths. The optimizer then estimates the cost of each plan and chooses the one with the lowest cost. The optimizer estimates costs in the following ways:
- The cost of a nested loops operation is based on the cost of reading each selected row of the outer table and each of its matching rows of the inner table into memory. The optimizer estimates these costs using the statistics in the data dictionary.
- The cost of a sort merge join is based largely on the cost of reading all the sources into memory and sorting them.
- The cost of a hash join is based largely on the cost of building a hash table on one of the input sides to the join and using the rows from the other of the join to probe it.
The optimizer also considers other factors when determining the cost of each operation. For example:
- A smaller sort area size is likely to increase the cost for a sort merge join because sorting takes more CPU time and I/O in a smaller sort area. See "PGA Memory Management" to learn how to size SQL work areas.
- A larger multiblock read count is likely to decrease the cost for a sort merge join in relation to a nested loop join. If the database can read a large number of sequential blocks from disk in a single I/O, then an index on the inner table for the nested loop join is less likely to improve performance over a full table scan. The multiblock read count is specified by the initialization parameter
DB_FILE_MULTIBLOCK_READ_COUNT.You can use the
ORDEREDhint to override the optimizer's choice of join orders. If theORDEREDhint specifies a join order that violates the rule for an outer join, then the optimizer ignores the hint and chooses the order. Also, you can override the optimizer's choice of join method with hints.
Oracle表连接方法有四种：
排序合并连接（Sort Merge Joins）
嵌套循环连接（Nested Loops Joins）
哈希连接（Hash Joins）
笛卡尔积（Cartesian Joins）
Sort Merge Joins
排序合并连接是将连接的两个表使用连接列排序后，对排序后的结果集进行合并后再得到匹配记录。如果连接列上面有索引，可以避免排序，那么优化器就有可能会选择排序合并连接。可以用于 =，>，>=，<，<= 连接条件，不适用于 <>，like 连接条件。对应执行计划为 **SORT JOIN **和 MERGE JOIN 。
SQL> select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1343509718
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3286 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 106 | 3286 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 1605 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
--如果没有索引，则会选择哈希连接：
SQL> create table emp as select * from employees;
Table created.
SQL> create table dept as select * from departments;
Table created.
SQL> select e.employee_id,d.department_name from emp e,dept d where e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 5936 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 5936 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 27 | 810 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 107 | 2782 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
--如果使用不等连接条件，则会选择嵌套循环连接：
SQL> select e.employee_id,d.department_name from employees e,departments d where e.department_id!=d.department_id;
2756 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2968905875
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2757 | 63411 | 41 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2757 | 63411 | 41 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES | 102 | 714 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Nested Loops Joins
嵌套循环连接是 Oracle 将连接的两个表根据结果集的大小，决定出驱动表和被驱动表。结果集小的作为驱动表，结果集大的作为被驱动表。对于驱动表的每一行，都要与被驱动表的所有行使用连接条件进行匹配。适用于驱动表结果集很小，被驱动表在连接列上有高效索引的表连接。可以用于所有连接条件。对应的执行计划为 NESTED LOOPS。
SQL> select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id and d.department_id=80;
34 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1492013603
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 1054 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 34 | 1054 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 34 | 510 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
--这里DEPARTMENTS表加了条件过滤后结果集只有1行，所以被选为驱动表。
SQL> select e.first_name,e.last_name,e.salary,d.department_name from employees e,departments d where d.department_name IN ('Marketing', 'Sales') and e.department_id = d.department_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1021246405
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 19 | 722 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 20 | 722 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
这里有2次NESTED LOOPS，是因为Oracle 11g引入了引入了向量I/O（Vector I/O），批量处理多个物理I/O请求来提高嵌套循环连接的效率。
如果驱动表的限制条件的字段上有索引，被驱动表的连接条件的字段上有索引，则使用嵌套循环连接的效率就会很高。
Hash Joins
如果两个表对应的结果集很大，使用排序合并的话，排序操作成本较高；使用嵌套循环的话，则循环次数又很多，需要多次访问被驱动表的结果集；为了提高这种情况下表连接的效率，优化器提供了新的表连接方法，即哈希连接。哈希连接是使用哈希运算来得到结果的表连接方法。只适用于等值连接条件。
SQL> create table emp as select * from employees;
Table created.
SQL> create table dept as select * from departments;
Table created.
SQL> select e.employee_id,d.department_name from emp e,dept d where e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 5936 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 5936 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 27 | 810 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 107 | 2782 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Cartesian Joins
如果两个表做表连接而没有连接条件，就会产生笛卡尔积，在实际工作中应该尽可能避免笛卡尔积。笛卡尔积对应的执行计划中有关键字 MERGE JOIN CARTESIAN。
SQL> select last_name,department_name from emp,dept;
2889 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2034389985
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2889 | 57780 | 41 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 2889 | 57780 | 41 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 27 | 324 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 107 | 856 | 38 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 107 | 856 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Outer Joins
前面连接方法的示例都是内连接，我们知道 Oracle 还有外连接，包括左外连接，右外连接和全外连接。执行计划中左外连接和右外连接对应的关键字是 OUTER，全外连接对应的关键字是 FULL OUTER。
--内连接：
SQL> select e.employee_id,e.last_name,d.department_name from employees e join departments d on e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1343509718
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3286 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 106 | 3286 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 1605 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
--左外连接：
SQL> select e.employee_id,e.last_name,d.department_name from employees e left join departments d on e.department_id=d.department_id;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2296652067
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 3317 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 107 | 3317 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
--右外连接：
SQL> select e.employee_id,e.last_name,d.department_name from employees e right join departments d on e.department_id=d.department_id;
122 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 514479674
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3286 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 106 | 3286 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 1605 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
--全外连接：
SQL> select e.employee_id,e.last_name,d.department_name from employees e full join departments d on e.department_id=d.department_id;
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2631508678
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 5368 | 6 (0)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 122 | 5368 | 6 (0)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 122 | 3782 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
SEMI Joins
如果 where 条件有 exists、in 或 =any 操作符 + 子查询，则 Oracle 会将其处理为半连接，执行计划中对应的关键字为 SEMI。
SQL> select department_id,department_name from departments d where exists (select 1 from employees e where d.department_id = e.department_id and e.salary > 2500);
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 10 | 230 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select department_id,department_name from departments d where department_id in (select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 10 | 230 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select department_id,department_name from departments d where department_id=any(select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 10 | 230 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
ANTI Joins
如果 where 条件有 not exists、not in 或 <>all 操作符 + 子查询，则 Oracle 会将其处理为反连接，执行计划中对应的关键字为 ANTI。
SQL> select department_id,department_name from departments d where not exists (select 1 from employees e where d.department_id = e.department_id and e.salary > 2500);
16 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 391 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 17 | 391 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select department_id,department_name from departments d where department_id not in (select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);
16 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 391 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 17 | 391 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select department_id,department_name from departments d where department_id<>all(select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);
16 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 391 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 17 | 391 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Optimizer Statistics
Overview
Oracle 数据库里的统计信息是这样的一组数据：它存储在数据字典里，且从多个维度描述了 Oracle 数据库里对象的详细信息。CBO 会利用这些统计信息来计算目标 SQL 各种可能的、不同的执行路径的成本，并从中选择一条成本值最小的执行路径来作为目标 SQL 的执行计划。
Oracle 数据库里的统计信息可以分为如下 6 中类型：
- 表的统计信息
- 索引的统计信息
- 列的统计信息
- 系统统计信息
- 数据字典统计信息
- 内部对象统计信息
表的统计信息用于描述 Oracle 数据库里表的详细信息，它包含了一些典型的维度，如记录数、表块（表里的数据块）的数量、平均行长度等。
索引的统计信息用于描述 Oracle 数据库里索引的详细信息，它包含了一些典型的维度，如索引的层级、叶子块的数量、聚簇因子等。
列的统计信息用于描述 Oracle 数据库里列的详细信息，它包含了一些典型的维度，如列的 distinct 值的数量、列的 null 值的数量、列的最小值、列的最大值以及直方图等。
系统统计信息用于描述 Oracle 数据库所在的数据库服务器的系统处理能力，它包含了 CPU 和 I/O 这两个维度，借助于系统统计信息，Oracle 可以更清楚地知道目标数据库服务器的实际处理能力。
数据字典统计信息用于描述 Oracle 数据库里数据字典基表（如 TAB$、IND$ 等）、数据字典基表上的索引、以及这些数据字典基表的列的详细信息，描述上述数据字典基表的统计信息与描述普通表、索引、列的统计信息没有本质区别。
内部对象统计信息用于描述 Oracle 数据库里的一些内部表（如 X$ 系列表）的详细信息，它的维度和普通表的统计信息的维度类似，只不过其表块的数量为 0，因为 X$ 系列表实际上只是 Oracle 自定义的内存结构，并不占用实际的物理存储空间。
Gathering Statistics
Manual
在 Oracle 数据库里，通常有两种方法可以用来收集统计信息：一种是使用 ANALYZE 命令；另一种是使用 DBMS_STATS 包。在前述的 6 种统计信息中，表、索引、列的统计信息和数据字典统计信息用 ANALYZE 命令或者 DBMS_STATS 包收集均可，但系统统计信息和系统内部对象统计信息只能使用 DMBS_STATS 包来收集。
从 Oracle 8.1.5 开始，DBMS_STATS 包被广泛用于统计信息的收集，用 DBMS_STATS 包收集统计信息也是 Oracle 官方推荐的方式。在收集 CBO 所需要的统计信息方面，可以简单地将 DBMS_STATS 包理解成是 ANALYZE命令的增强版。
DBMS_STATS 包里最常用的就是如下这 4 个存储过程：
- GATHER_TABLE_STATS：用于收集目标表、目标表的列和目标表上的索引的统计信息。
- GATHER_INDEX_STATS：用于收集指定索引的统计信息。
- GATHER_SCHEMA_STATS：用于收集指定 schema 下所有对象的统计信息。
- GATHER_DATABASE_STATS：用于收集全库所有对象的统计信息。
只对表 T2 收集统计信息，并且是以估算形式，采样的比例为 15%：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',estimate_percent=>15,method_opt=>'FOR TABLE',cascade=>false);
PL/SQL procedure successfully completed.
在调用 dbms_stats.gather_table_stats 时指定参数 method_opt 的值为 'FOR TABLE'，这表示只收集表 T2 的统计信息。这种只收集表统计信息的方法并不适用于 Oracle 数据库所有的版本。例如，这种方法就不适用于 10.2.0.4 和 10.2.0.5，在这两个版本里，即使指定了 'FOR TABLE'，Oracle 除了收集表统计信息之外还会对所有的列收集统计信息。
如果仅对表 T2 收集统计信息，并且是以计算模式收集，用 DBMS_STATS 包实现的方式就是将估算模式的采样比例（即参数 ESTIMATE_PERCENT）设为 100% 或 NULL：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>false);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',estimate_percent=>NULL,method_opt=>'FOR TABLE',cascade=>false);
PL/SQL procedure successfully completed.
对表 T2 的列 OBJECT_NAME 和 OBJECT_ID 以计算模式收集统计信息（不收集直方图）：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',estimate_percent=>100,method_opt=>'for columns size 1 object_name object_id',cascade=>false);
PL/SQL procedure successfully completed.
ANALYZE 命令可以做到只收集列而不收集表的统计信息，DBMS_STATS 包做不到在这一点。
用如下命令可以以计算模式收集索引 IDX_T2 的统计信息：
SQL> exec dbms_stats.gather_index_stats(ownname=>'SYS',indname=>'IDX_T2',estimate_percent=>100);
PL/SQL procedure successfully completed.
如下命令可以删除表 T2、表 T2 的所有列及表 T2 的所有索引的统计信息：
SQL> exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'T2');
PL/SQL procedure successfully completed.
一次性以计算模式收集表 T2、表 T2 的所有列及表 T2 的所有索引的统计信息，执行如下语句就可以了：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
系统统计信息使用 DBMS_STATS 包里的存储过程 GATHER_SYSTEM_STATS 来收集的，其收集方法为：
- 执行 DBMS_STATS.GATHER_SYSTEM_STATS('start') 开始收集系统统计信息。
- 接着开始实际地使用系统，最好是能以系统典型的负责实际使用一段时间。
- 最后使用 DBMS_STATS.GATHER_SYSTEM_STATS('stop') 停止收集系统统计信息。
系统统计信息实际上代表了目标数据库服务器在指定负载情况下的 CPU 和 I/O 的处理能力。
收集数据字典统计信息：
SQL> exec dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
收集所有 X$ 表的内部对象统计信息：
SQL> exec dbms_stats.gather_fixed_objects_stats();
PL/SQL procedure successfully completed.
Automatic
Oracle 11g 引入了一个名为 gather_stats_prog 的自动运行任务专用于自动收集统计信息，实际上就是 Oracle 10g 中的自动统计信息收集作业 GATHER_STATS_JOB 所对应的应用程序 GATHER_STATS_PROG。自动运行任务 GATHER_STATS_PROG 所对应客户端的名称为 “auto optimizer stats collection”
SQL> select client_name,task_name,operation_name,status from dba_autotask_task;
CLIENT_NAME TASK_NAME OPERATION_NAME STATUS
---------------------------------------- ------------------------------ ------------------------------ --------
auto optimizer stats collection gather_stats_prog auto optimizer stats job ENABLED
auto space advisor auto_space_advisor_prog auto space advisor job ENABLED
sql tuning advisor AUTO_SQL_TUNING_PROG automatic sql tuning task ENABLED
GATHER_STATS_PROG 实际上也是调用了 DBMS_STATS 包里的存储过程 gather_database_stats_job_proc，和Oracle 10g 一样。
SQL> select program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG';
PROGRAM_ACTION
------------------------------------------------------------
dbms_stats.gather_database_stats_job_proc
Oracle 11g 中的自动统计信息收集作业可配置的维护窗口增加到了 7 个。
SQL> select window_name,autotask_status from dba_autotask_window_clients;
WINDOW_NAME AUTOTASK
------------------------------ --------
MONDAY_WINDOW ENABLED
TUESDAY_WINDOW ENABLED
WEDNESDAY_WINDOW ENABLED
THURSDAY_WINDOW ENABLED
FRIDAY_WINDOW ENABLED
SATURDAY_WINDOW ENABLED
SUNDAY_WINDOW ENABLED
7 rows selected.
SQL> select window_name,repeat_interval,duration from dba_scheduler_windows where enabled='TRUE';
WINDOW_NAME REPEAT_INTERVAL DURATION
------------------------------ ------------------------------------------------------------ ---------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
SQL> select WINDOW_NAME,RESOURCE_PLAN from dba_scheduler_windows;
WINDOW_NAME RESOURCE_PLAN
------------------------------ ------------------------------
MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
WEEKNIGHT_WINDOW
WEEKEND_WINDOW
9 rows selected.
自动运行的任务 GATHER_STATS_PROG 每次运行时会先生成名为 ORA$AT_OS_OPT_xxx 的作业，然后再执行这个作业。
SQL> select job_name,actual_start_date,status from (select * from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT%' order by log_date desc) where rownum<4;
JOB_NAME ACTUAL_START_DATE STATUS
------------------------ ---------------------------------------- ---------------
ORA$AT_OS_OPT_SY_1942 25-APR-17 10.00.02.071935 PM PRC SUCCEEDED
ORA$AT_OS_OPT_SY_1939 24-APR-17 10.00.02.125502 PM PRC SUCCEEDED
ORA$AT_OS_OPT_SY_1922 23-APR-17 10.02.00.610507 PM PRC SUCCEEDED
SQL> select client_name,window_name,job_name,job_start_time from (select * from dba_autotask_job_history where client_name='auto optimizer stats collection' order by window_start_time desc) where rownum<4;
CLIENT_NAME WINDOW_NAME JOB_NAME JOB_START_TIME
---------------------------------------- --------------- ------------------------ ----------------------------------------
auto optimizer stats collection TUESDAY_WINDOW ORA$AT_OS_OPT_SY_1942 25-APR-17 10.00.02.071935 PM PRC
auto optimizer stats collection MONDAY_WINDOW ORA$AT_OS_OPT_SY_1939 24-APR-17 10.00.02.125502 PM PRC
auto optimizer stats collection SUNDAY_WINDOW ORA$AT_OS_OPT_SY_1922 23-APR-17 10.02.00.610507 PM PRC
总结一下在 Oracle 11g 中的自动统计信息收集作业的特点：
- Oracle 11g 中的自动统计信息收集作业是通过每天执行自动运行任务 GATHER_STATS_PROG 来实现的，每次运行时会先生成名为 ORA$AT_OS_OPT_xxx 的作业，然后再执行这个作业来自动收集统计信息，其本质和 10g 一样也是执行 DBMS_STATS 包里的存储过程 GAHTER_DATABASE_STATS_JOB_PROC。
- Oracle 11g 中的自动统计信息收集作业可配置的维护窗口有 7 个。
- Oracle 已经对上述 7 个维护窗口施加了资源控制，其对于的 Resource Plan 的名称为DEFAULT_MAINTENANCE_PLAN。
在 Oracle 10g 以后，如果参数 STATISTICS_LEVEL 的值为 TYPICAL 或者 ALL，则 MON_MODS_ALL$ 里会记录自上次自动统计信息收集作业完成之后对所有目标表的 insert、delete 和 update 操作所影响的记录数，并且还会记录下自上次自动统计信息收集作业完成之后是否对目标表执行过 truncate 操作。
Oracle 重复收集统计信息的策略测试：
SQL> create table t3 as select * from dba_objects;
Table created.
SQL> select object_id from dba_objects where object_name='T3';
OBJECT_ID
----------
99390
SQL> delete from t3 where rownum<10001;
10000 rows deleted.
SQL> update t3 set owner='STONE' where rownum<2;
1 row updated.
SQL> insert into t3 select * from dba_objects;
91918 rows created.
SQL> commit;
Commit complete.
SQL> select OBJ#,INSERTS,UPDATES,DELETES,FLAGS from mon_mods_all$ where obj#=99390;
no rows selected
SQL> select OBJ#,INSERTS,UPDATES,DELETES,FLAGS from mon_mods_all$ where obj#=99390;
no rows selected
SQL> exec dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
SQL> select OBJ#,INSERTS,UPDATES,DELETES,FLAGS from mon_mods_all$ where obj#=99390;
OBJ## INSERTS UPDATES DELETES FLAGS
---------- ---------- ---------- ---------- ----------
99390 91918 1 10000 0
SQL> truncate table t3;
Table truncated.
SQL> exec dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
SQL> select OBJ#,INSERTS,UPDATES,DELETES,FLAGS from mon_mods_all$ where obj#=99390;
OBJ## INSERTS UPDATES DELETES FLAGS
---------- ---------- ---------- ---------- ----------
99390 91918 1 91918 1
SQL> exec dbms_stats.gather_table_stats('SYS','T3',cascade=>true);
PL/SQL procedure successfully completed.
对表 T3 收集统计信息后，mon_mods_all$ 中记录的对表 T3 的原监控信息被删除：
SQL> select OBJ#,INSERTS,UPDATES,DELETES,FLAGS from mon_mods_all$ where obj#=99390;
no rows selected
Oracle 数据库中的自动统计信息收集作业重复收集统计信息的策略，或者说判断统计信息是否失效的标准是，自上次自动统计信息收集作业完成之后，如果 mon_mods_all$ 中记录的目标表的 delete+insert+update 操作所影响的记录数已经超过了 TAB$ 中记录的目标表记录数的10%，或者自上次自动统计信息收集作业完成之后目标表被执行过 truncate 操作，那么 Oracle 就会认为此时目标表的统计信息已经失效，自动统计信息收集作业在执行时就会对该目标表重新收集统计信息。
介绍一下如何自定义修改 Oracle 11g 中的自动统计信息收集作业。这主要可以通过如下两种方式来实现：
- 修改各个可配置的维护窗口。
- 修改 DBMS_STATS 包所使用的默认参数。
例子：禁掉和启用自动统计信息收集作业
SQL> exec dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>null,window_name=>null);
PL/SQL procedure successfully completed.
SQL> select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZE SEGMENT_ SQL_TUNE
------------------ -------- -------- --------
TUESDAY_WINDOW DISABLED ENABLED ENABLED
WEDNESDAY_WINDOW DISABLED ENABLED ENABLED
THURSDAY_WINDOW DISABLED ENABLED ENABLED
FRIDAY_WINDOW DISABLED ENABLED ENABLED
SATURDAY_WINDOW DISABLED ENABLED ENABLED
SUNDAY_WINDOW DISABLED ENABLED ENABLED
6 rows selected.
SQL> exec dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection',operation=>null,window_name=>null);
PL/SQL procedure successfully completed.
SQL> select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZE SEGMENT_ SQL_TUNE
------------------ -------- -------- --------
TUESDAY_WINDOW ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
6 rows selected.
禁掉和启用整个自动统计信息收集作业：
SQL> exec dbms_auto_task_admin.disable();
PL/SQL procedure successfully completed.
SQL> exec dbms_auto_task_admin.enable();
PL/SQL procedure successfully completed.
例子：在Oracle 11g 中，禁掉周一的自动统计信息收集作业；将周二的起始执行时间调整到晚上 11 点；将周三的起始执行时间调整到晚上 9 点，并同时将周三的最长执行时间调整为 5 小时。
SQL> select WINDOW_NAME,REPEAT_INTERVAL,DURATION,ENABLED from dba_scheduler_windows where windOW_name not in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW');
WINDOW_NAME REPEAT_INTERVAL DURATION ENABL
------------------ ------------------------------------------------------------ --------------- -----
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
7 rows selected.
和 Oracle 10g 一样，如下这种将整个维护窗口 MONDAY_WINDOW 禁掉是“杀伤”范围最广的一种方法，因为在 Oracle 11g 中各个维护窗口也不仅跑自动统计信息收集作业，还会跑其他的自动运行作业（如 Automatic Segment Advisor 和 Automatic SQL Tuning Advisor）。这里把维护窗口 MONDAY_WINDOW 禁掉了，相当于同时禁掉了原先在周一跑的自动统计信息收集作业，Automatic Segment Advisor 作业和 Automatic SQL Tuning Advisor作业：
SQL> exec dbms_scheduler.disable(name=>'"SYS"."MONDAY_WINDOW"',force=>true);
PL/SQL procedure successfully completed.
注意，要改可配置的维护窗口，就要先 DISABLE，然后改，再 ENABLE：
SQL> exec dbms_scheduler.disable(name=>'"SYS"."TUESDAY_WINDOW"',force=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.set_attribute(name=>'"SYS"."TUESDAY_WINDOW"',attribute=>'repeat_interval',value=>'freq=daily;byday=TUE;byhour=23;byminute=0;bysecond=0');
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.enable(name=>'"SYS"."TUESDAY_WINDOW"');
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.disable(name=>'"SYS"."WEDNESDAY_WINDOW"',force=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.set_attribute(name=>'"SYS"."WEDNESDAY_WINDOW"',attribute=>'repeat_interval',value=>'freq=daily;byday=WED;byhour=21;byminute=0;bysecond=0');
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.set_attribute(name=>'"SYS"."WEDNESDAY_WINDOW"',attribute=>'duration',value=>numtodsinterval(300,'minute'));
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.enable(name=>'"SYS"."WEDNESDAY_WINDOW"');
PL/SQL procedure successfully completed.
SQL> select WINDOW_NAME,REPEAT_INTERVAL,DURATION,ENABLED from dba_scheduler_windows where windOW_name not in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW');
WINDOW_NAME REPEAT_INTERVAL DURATION ENABL
------------------ ------------------------------------------------------------ --------------- -----
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 FALSE
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=23;byminute=0;bysecond=0 +000 04:00:00 TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=21;byminute=0;bysecond=0 +000 05:00:00 TRUE
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
7 rows selected.
如果只是想禁掉周一的自动统计信息收集作业而保留 Automatic Segment Advisor 作业和 Automatic SQL Tuning Advisor 作业，可以选择如下这种“杀伤”范围更小的方式：
SQL> exec dbms_scheduler.enable(name=>'"SYS"."MONDAY_WINDOW"');
PL/SQL procedure successfully completed.
SQL> select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZE SEGMENT_ SQL_TUNE
------------------ -------- -------- --------
MONDAY_WINDOW ENABLED ENABLED ENABLED
TUESDAY_WINDOW ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
7 rows selected.
禁掉在周一维护窗口跑的自动统计信息收集作业，其他的自动运行作业不会受到影响：
SQL> exec dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>null,window_name=>'MONDAY_WINDOW');
PL/SQL procedure successfully completed.
SQL> select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZE SEGMENT_ SQL_TUNE
------------------ -------- -------- --------
MONDAY_WINDOW DISABLED ENABLED ENABLED
TUESDAY_WINDOW ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
7 rows selected.
在 Oracle 10g 和 Oracle 11g 中，默认的直方图统计信息收集策略是 AUTO，表示 Oracle 会根据负载和列的使用情况来自动决定对哪些列收集直方图统计信息，这是不好的。为了更好地利用直方图统计信息同时尽可能保持执行计划的稳定，这里推荐对直方图统计信息的收集策略是对已经存在直方图统计信息的列才收集直方图统计信息，而目标列的初次直方图统计信息则是由了解系统的 DBA 手工执行存储过程 DBMS_STATS.GATHER_TABLE_STATS 来收集。
例子：在Oracle 11g 中，将自动统计信息收集作业中的直方图统计信息默认收集策略更改为，只对已存在直方图统计信息的列才收集直方图统计信息，同时将表 EMP 的重复收集策略更改为，不管它的数据如何改变，每天都必须对其收集统计信息。
SQL> select dbms_stats.get_param('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
----------------------------------------
FOR ALL COLUMNS SIZE AUTO
SQL> select dbms_stats.get_prefs(pname=>'METHOD_OPT') from dual;
DBMS_STATS.GET_PREFS(PNAME=>'METHOD_OPT'
----------------------------------------
FOR ALL COLUMNS SIZE AUTO
SQL> exec dbms_stats.set_global_prefs('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_param('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
----------------------------------------
FOR ALL COLUMNS SIZE REPEAT
SQL> select dbms_stats.get_prefs(pname=>'METHOD_OPT') from dual;
DBMS_STATS.GET_PREFS(PNAME=>'METHOD_OPT'
----------------------------------------
FOR ALL COLUMNS SIZE REPEAT
在 Oracle 11g 中，还可以使用 DBMS_STATS.SET_TABLE_PREFS 来对自动统计信息收集作业中单个表的收集设置做修改，针对单个表的设置会覆盖用 DBMS_STATS.SET_GLOBAL_PREFS 做的全局设置。Oracle 11g 中可以通过修改 STALE_PERCENT 的值来设置重复收集统计信息的阈值，默认值为 10%，如果要实现不管表 EMP 的数据如何变化，每天都必须对其收集统计信息的话，只需要使用 DBMS_STATS.SET_TABLE_PREFS 将针对表 EMP 的 STALE_PERCENT 的值设为 0 就可以了。
SQL> select dbms_stats.get_prefs(pname=>'STALE_PERCENT',ownname=>'SCOTT',tabname=>'EMP') stale_percent from dual;
STALE_PERCENT
--------------------
10
SQL> exec dbms_stats.set_table_prefs('SCOTT','EMP','STALE_PERCENT',0);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_prefs(pname=>'STALE_PERCENT',ownname=>'SCOTT',tabname=>'EMP') stale_percent from dual;
STALE_PERCENT
--------------------
0
Best Practices
在 Oracle 数据库里，收集统计信息总的原则就是量体裁衣，即要找到适合自己系统的统计信息收集策略，用尽量小的代价收集到能稳定跑出正确执行计划的统计信息即可，也就是说收集到的统计信息不一定要特别准，只要具备代表性，能稳定跑出正确的执行计划就可以了。
根据上述指导原则，建议采用如下的收集策略：根据系统的类型及特点来决定是采用 Oracle 自动统计信息收集作业，还是自己写 Shell 脚本来收集统计信息。对于那种数据量不大的 OLTP 类型的系统，建议是用前者。如果数据量很大的 OLAP 或者 DSS 系统，建议自己写 Shell 脚本来收集统计信息，因为对于这种系统而言，很多表的访问都是只有全表扫描这一种方式，这种情况下这些表的统计信息是否准确就无所谓了，甚至无须浪费资源和时间来对它们收集统计信息，而是写 Shell 脚本仅对那些需要收集统计信息的表和相关对象收集统计信息就可以了。
需要特别注意如下事项：
- 当系统中导入了大量数据后，如果要马上进行相关的业务处理，建议在进行相关的业务处理之前及时对相关表手工收集统计信息，因为这些被导入了大量数据的表已经来不及等到当天的自动统计信息收集作业自动收集统计信息了。
- 建议及时对包含日期型字段的表收集统计信息，避免出现谓词越界现象。
- 对于 Oracle 11g 及其以上的版本，收集统计信息的采样比例建议采用 DBMS_STATS.AUTO_SAMPLE_SIZE。Oracle 11g 中的 AUTO_SAMPLE_SIZE 采用了全新的哈希算法，它既能保证在较短的时间内执行完又能保证收集到的统计信息的质量。如果是 Oracle 10g，继续采用 DBMS_STATS.AUTO_SAMPLE_SIZE 就不大合适了，因为这个自动采样比例在 Oracle 10g 里是非常小的一个值，建议在 Oracle 10g 中将采样比例的初始值设为 30%，然后根据目标 SQL 的实际情况在做调整。
- 建议使用 DBMS_STATS 包来对分区表收集全局统计信息，并且收集分区表的统计信息时应使用一致的 GRANULARITY 参数。
- 建议要额外收集系统统计信息，但系统统计信息收集一次就够了，除非系统的硬件环境发生了变化。
- 建议要额外收集
X$表 的内部对象统计信息，但仅仅是在明确诊断出系统已有的性能问题是因为X$表的内部对象统计信息不准的情形下，对于其他情形就不要收集了。
如果是用写 Shell 脚本的方式手工收集统计信息，推荐采用如下的方式：
对于单表的统计信息收集，建议初始情况下采用如下的方式：
- 适用于 Oracle 11g 及其以上的版本
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'EMP',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true,method_opt=>'for all columns size repeat');
- 适用于 Oracle 9i/10g
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'EMP',estimate_percent=>30,cascade=>true,method_opt=>'for all columns size repeat');
对于单个 schema 的统计信息收集，建议初始情况下采用如下的方式：
- 适用于 Oracle 11g 及其以上的版本
exec dbms_stats.gather_schema_stats(ownname=>'scott',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true,method_opt=>'for all columns size repeat');
- 适用于 Oracle 9i/10g
exec dbms_stats.gather_schema_stats(ownname=>'scott',estimate_percent=>30,cascade=>true,method_opt=>'for all columns size repeat');
对于直方图统计信息的收集策略，建议采用如下的方式：只对已经存在的直方图统计信息的列重复收集直方图统计信息，而目标列的初次直方图统计信息的收集则是由了解系统的 DBA 手工来做：
设置 METHOD_OPT 的值为 'FOR ALL COLUMNS SIZE 1' 后先收集一次统计信息，这意味着删除了所有列上的直方图统计信息。
这已经删除了所有列上的直方图统计信息后，设置 METHOD_OPT 的值为 'FOR ALL COLUMNS SIZE REPEAT’，这意味着今后将只对已经存在直方图统计信息的列重复收集直方图统计信息。
在系统使用过程中，目标列的初次直方图统计信息的收集由了解系统的 DBA 手工来做。
Handling Missing Statistics
当 Oracle 数据库遇到缺少表统计信息时，默认情况下，数据库会动态收集优化器所需的统计信息。但是对于某些类型的表（包括远程表和外部表），Oracle 数据库不会收集动态统计信息。在这种情况下以及禁用动态统计信息时，优化器使用缺省值。
表的缺省值：
| Table Statistic | Default Value Used by Optimizer |
|---|---|
Cardinality | num_of_blocks * (block_size - cache_layer) / avg_row_len |
Average row length | 100 bytes |
Number of blocks | 100 or actual value based on the extent map |
Remote cardinality | 2000 rows |
Remote average row length | 100 bytes |
索引的缺省值：
| Index Statistic | Default Value Used by Optimizer |
|---|---|
Levels | 1 |
Leaf blocks | 25 |
Leaf blocks/key | 1 |
Data blocks/key | 1 |
Distinct keys | 100 |
Clustering factor | 800 |
Viewing Statistics
编写查看统计信息的脚本。
[oracle@stone ~]$ cat sosi.txt
set echo off
set scan on
set lines 150
set pages 66
set verify off
set feedback off
set termout off
column uservar new_value Table_Owner noprint
select user uservar from dual;
set termout on
column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
select table_name from dba_tables where owner=upper('&Table_Owner') order by 1
/
undefine table_name
undefine owner
prompt
accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
accept table_name prompt 'Please enter Table Name to show Statistics for: '
column TABLE_NAME heading "Table|Name" format a15
column PARTITION_NAME heading "Partition|Name" format a15
column SUBPARTITION_NAME heading "SubPartition|Name" format a15
column NUM_ROWS heading "Number|of Rows" format 9,999,999,990
column BLOCKS heading "Blocks" format 999,990
column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990
column AVG_SPACE heading "Average|Space" format 9,990
column CHAIN_CNT heading "Chain|Count" format 999,990
column AVG_ROW_LEN heading "Average|Row Len" format 990
column COLUMN_NAME heading "Column|Name" format a25
column NULLABLE heading Null|able format a4
column NUM_DISTINCT heading "Distinct|Values" format 999,999,990
column NUM_NULLS heading "Number|Nulls" format 9,999,990
column NUM_BUCKETS heading "Number|Buckets" format 990
column DENSITY heading "Density" format 990
column INDEX_NAME heading "Index|Name" format a15
column UNIQUENESS heading "Unique" format a9
column BLEV heading "B|Tree|Level" format 90
column LEAF_BLOCKS heading "Leaf|Blks" format 990
column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990
column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990
column COLUMN_POSITION heading "Col|Pos" format 990
column col heading "Column|Details" format a24
column COLUMN_LENGTH heading "Col|Len" format 9,990
column GLOBAL_STATS heading "Global|Stats" format a6
column USER_STATS heading "User|Stats" format a6
column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,990
column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10
prompt
prompt ***********
prompt Table Level
prompt ***********
prompt
select
TABLE_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from dba_tables t
where
owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
/
select
COLUMN_NAME,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT NULL',
'n','NOT NULL',
NULL) col,
NUM_DISTINCT,
DENSITY,
NUM_BUCKETS,
NUM_NULLS,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from dba_tab_columns t
where
table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
select
INDEX_NAME,
UNIQUENESS,
BLEVEL BLev,
LEAF_BLOCKS,
DISTINCT_KEYS,
NUM_ROWS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_indexes t
where
table_name = upper('&Table_name')
and table_owner = upper(nvl('&Owner',user))
/
break on index_name
select
i.INDEX_NAME,
i.COLUMN_NAME,
i.COLUMN_POSITION,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT NULL',
'n','NOT NULL',
NULL) col
from
dba_ind_columns i,
dba_tab_columns t
where
i.table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
and i.table_name = t.table_name
and i.column_name = t.column_name
order by index_name,column_position
/
prompt
prompt ***************
prompt Partition Level
prompt ***************
select
PARTITION_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_tab_partitions t
where
table_owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
order by partition_position
/
break on partition_name
select
PARTITION_NAME,
COLUMN_NAME,
NUM_DISTINCT,
DENSITY,
NUM_BUCKETS,
NUM_NULLS,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_PART_COL_STATISTICS t
where
table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
break on partition_name
select
t.INDEX_NAME,
t.PARTITION_NAME,
t.BLEVEL BLev,
t.LEAF_BLOCKS,
t.DISTINCT_KEYS,
t.NUM_ROWS,
t.AVG_LEAF_BLOCKS_PER_KEY,
t.AVG_DATA_BLOCKS_PER_KEY,
t.CLUSTERING_FACTOR,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_ind_partitions t,
dba_indexes i
where
i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/
prompt
prompt ***************
prompt SubPartition Level
prompt ***************
select
PARTITION_NAME,
SUBPARTITION_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_tab_subpartitions t
where
table_owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
order by SUBPARTITION_POSITION
/
break on partition_name
select
p.PARTITION_NAME,
t.SUBPARTITION_NAME,
t.COLUMN_NAME,
t.NUM_DISTINCT,
t.DENSITY,
t.NUM_BUCKETS,
t.NUM_NULLS,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_SUBPART_COL_STATISTICS t,
dba_tab_subpartitions p
where
t.table_name = upper('&Table_name')
and t.owner = upper(nvl('&Owner',user))
and t.subpartition_name = p.subpartition_name
and t.owner = p.table_owner
and t.table_name=p.table_name
/
break on partition_name
select
t.INDEX_NAME,
t.PARTITION_NAME,
t.SUBPARTITION_NAME,
t.BLEVEL BLev,
t.LEAF_BLOCKS,
t.DISTINCT_KEYS,
t.NUM_ROWS,
t.AVG_LEAF_BLOCKS_PER_KEY,
t.AVG_DATA_BLOCKS_PER_KEY,
t.CLUSTERING_FACTOR,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_ind_subpartitions t,
dba_indexes i
where
i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/
clear breaks
set echo on
查看统计信息。
SQL> conn scott/scott
Connected.
SQL> @/home/oracle/sosi.txt
SQL> set echo off
Tables owned by SCOTT
------------------------------
BONUS
DEPT
EMP
EMP1
EMP2
EMP_TEMP
SALGRADE
Please enter Name of Table Owner (Null = SCOTT):
Please enter Table Name to show Statistics for: EMP
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------
EMP 14 5 0 0 0 38 YES NO 14 03-29-2017
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------
EMPNO NUMBER(4,0) NOT NULL 14 0 1 0 YES NO 14 03-29-2017
ENAME VARCHAR2(10) 14 0 1 0 YES NO 14 03-29-2017
JOB VARCHAR2(9) 5 0 1 0 YES NO 14 03-29-2017
MGR NUMBER(4,0) 6 0 1 1 YES NO 13 03-29-2017
HIREDATE DATE 13 0 1 0 YES NO 14 03-29-2017
SAL NUMBER(7,2) 12 0 1 0 YES NO 14 03-29-2017
COMM NUMBER(7,2) 4 0 1 10 YES NO 4 03-29-2017
DEPTNO NUMBER(2,0) 3 0 3 0 YES NO 14 03-29-2017
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ ------- ----------
IDX_EMP_DEPT NONUNIQUE 0 1 3 14 1 1 1 YES NO 14 03-29-2017
IDX_EMP_MGR NONUNIQUE 0 1 6 13 1 1 1 YES NO 13 03-29-2017
PK_EMP UNIQUE 0 1 14 14 1 1 1 YES NO 14 03-29-2017
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_EMP_DEPT DEPTNO 1 NUMBER(2,0)
IDX_EMP_MGR MGR 1 NUMBER(4,0)
PK_EMP EMPNO 1 NUMBER(4,0) NOT NULL
***************
Partition Level
***************
***************
SubPartition Level
***************
Table Statistics
可以通过数据字典 DBA_TABLES、DBA_PARTITIONS 和 DBA_TAB_SUBPARTITIONS 来分别查看表、分区表的分区和分区表的子分区的统计信息。
上述数据字典中的字段 NUM_ROWS 存储的就是目标表的记录数，目标表的记录数是计算结果集的 Cardinality 的基础，而结果集的 Cardinality 则往往直接决定了 CBO 计算的成本值。比如对于嵌套循环连接而言，驱动结果集的 Cardinality 的值越大，则走嵌套循环连接的成本值就会越大。
上述数据字典中的字段 BLOCKS 存储的就是目标表表块的数量，即目标表的数据所占用数据块的数量，目标表表块的数量会直接决定 CBO 计算出来的对目标表做全表扫描的成本，目标表表块的数量越大，则对目标表走全表扫描的成本值就会越大。
上述数据字典中的字段 AVG_ROW_LEN 存储的就是目标表的平均行长度。平均行长度的计算方法是用目标表的所有行记录所占用的字节数（不算行头）除以目标表的总行数，它可能会被 Oracle 用来计算目标表对应的结果集所占用内存的大小。
来看一个计算 AVG_ROW_LEN 的例子，创建一个测试表 TEST：
SQL> conn scott/scott
Connected.
SQL> create table test(id number,name varchar2(10));
Table created.
插入10000条相同的记录：
SQL> declare
2 i number;
3 begin
4 for i in 1..10000 loop
5 insert into test values(100,'ORACLE');
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
数字 100 存储在 Oracle 数据块的行里会占用 2 字节，再加上描述其长度的 1 字节，所以总共占用 3 字节：
SQL> select dump(100,16) from dual;
DUMP(100,16)
-----------------
Typ=2 Len=2: c2,2
'ORACLE' 在数据块的行里会占用 6 字节，再加上描述其长度的 1 字节，所以总共占用 7 字节：
SQL> select dump('ORACLE',16) from dual;
DUMP('ORACLE',16)
-------------------------------
Typ=96 Len=6: 53,48,49,4c,45,49
对表 TEST 收集一下统计信息：
SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST',estimate_percent=>100);
PL/SQL procedure successfully completed.
由于上述 10000 条记录是一模一样的，所以表 TEST 的 AVG_ROW_LEN 就是 3+7=10 字节，这和对表 TEST 收集完统计信息后的结果是一致的：
SQL> @/home/oracle/sosi.txt
Tables owned by SCOTT
------------------------------
BONUS
DEPT
EMP
EMP1
EMP2
EMP_TEMP
SALGRADE
TEST
Please enter Name of Table Owner (Null = SCOTT):
Please enter Table Name to show Statistics for: TEST
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------
TEST 10,000 28 0 0 0 10 YES NO 10,000 03-30-2017
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------
ID NUMBER(22) 1 1 1 0 YES NO 10,000 03-30-2017
NAME VARCHAR2(10) 1 1 1 0 YES NO 10,000 03-30-2017
***************
Partition Level
***************
***************
SubPartition Level
***************
表统计信息里还有一些维度是与 CBO 计算成本无关的，比如表里空块的数量（对应的就是上述数据字典里的字段 EMPTY_BLOCKS），表里行迁移/行链接的数量（对应的就是上述数据字典里的字段 CHAIN_CNT），这些维度因为与 CBO 计算成本无关，所以他们不能使用 DBMS_STATS 包来收集。要想收集这些额外的表统计信息，可以使用 ANALYZE 命令。
Index Statistics
通过数据字典 DBA_INDEXES、DBA_IND_PARTITIONS 和 DBA_IND_SUBPARTITIONS 来分别查看索引、分区索引和分区和局部分区索引的子分区的统计信息。
上述数据字典中的字段 BLEVEL 存储的就是目标索引的层级，它表示的是从根节点到叶子块的深度，BLEVEL 被 CBO 用来计算访问索引叶子块的成本。BLEVEL 的值越大，则从根节点到叶子块所需要访问的数据块的数量就会越多，耗费的 I/O 就会越多，访问索引的成本就会越大。BLEVEL 的值从 0 开始算起，当 BLEVEL 的值为 0 时，表示该 B 树索引只有一层，且根节点和叶子块就是同一个块。
在 Oracle 数据库里，如果要降低目标 B 树索引的层级，可以通过 REBUILD 该索引的方式来实现。先创建一个测试表 T1：
SQL> create table t1 as select * from dba_objects;
Table created.
在创建目标 B 树索引 IDX_T1：
SQL> create index idx_t1 on t1(object_id);
Index created.
SQL> select count(*) from t1;
COUNT(*)
----------
91890
使用 ANALYZE 命令分析索引 IDX_T1 的结构：
SQL> analyze index idx_t1 validate structure;
Index analyzed.
字段 HEIGHT 其实就是 BLEVEL 的值加 1，这里 HEIGHT 的值为 2，代表索引 IDX_T1 的层级为 1：
SQL> select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
IDX_T1 2 91890 204 0
删除表 T1 中的记录：
SQL> delete from t1;
91890 rows deleted.
SQL> commit;
Commit complete.
在删除表 T1 的记录后再次分析索引 IDX_T1 的结构：
SQL> analyze index idx_t1 validate structure;
Index analyzed.
从如下结果可以看到，ANALYZE 命令认为所有的叶子块中的记录都被删除了（LF_ROWS 的值为 91890，DEL_LF_ROWS 的值也 91890），但现在索引 IDX_T1 的层级还是未变，这里 HEIGHT 的值还是为 2，说明索引 IDX_T1 的层级依然是 1：
SQL> select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
IDX_T1 2 91890 204 91890
索引 IDX_T1 的所有叶子块的记录都被删除了，显然此时应该只剩下根节点，所以 IDX_T1 的层级应该能降低到 0，重新 REBUILD 该索引：
SQL> alter index idx_t1 rebuild;
Index altered.
REBUILD 后再次分析索引 IDX_T1 的结构：
SQL> analyze index idx_t1 validate structure;
Index analyzed.
从如下结果可以看到，在 REBUILD 后索引 IDX_T1 的 HEIGHT 值已经从 2 降为 1，说明 IDX_T1 的层级已经从 1 降到了 0，即 REBUILD 操作确实有可能能降低目标索引的层级，进而提高访问该索引的效率：
SQL> select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
IDX_T1 1 0 1 0
上述数据字典中的字段 LEAF_BLOCKS 存储的就是目标索引的叶子块的数量，它被 CBO 用于计算目标索引做索引全扫描（INDEX FULL SCAN）和索引范围扫描（INDEX RANGE SCAN）的成本。目标索引叶子块的数量越多，则对目标索引做索引全扫描和索引范围扫描的成本值就会越大。
上述数据字典中的字段 CLUSTERING_FACTOR 存储的就是目标索引的聚簇因子，聚簇因子是指按照索引键值排序的索引行和存储于对应表中的数据行的存储顺序的相似程度。
上述数据字典中的字段 DISTINCT_KEYS 存储的就是目标索引的索引键值的 DISTINCT 值的数量。对于唯一性索引而言，在没有 NULL 的情况下，DISTINCT_KEYS 的值就等于对应表的记录数。
上述数据字典中的字段 AVG_LEAF_BLOCKS_PER_KEY 存储的就是目标索引的每个 DISTINCT 索引键值所占用的叶子块数量的平均值，对于唯一性索引而言，AVG_LEAF_BLOCKS_PER_KEY 显然只会是 1。
上述数据字典中的字段 AVG_DATA_BLOCKS_PER_KEY 存储的就是目标索引的每个 DISTINCT 索引键值所对应表中数据行所在数据块数量的平均值。
上述数据字典中的字段 NUM_ROWS 存储的就是目标索引的索引行的数量。
索引统计信息维度中需要重点关注的是 BLEVEL、LEAF_BLOCKS 和 CLUSTING_FACTOR，他们在 CBO 计算访问索引成本的过程中扮演举足轻重的作用。
Clustering Factor
在 Oracle 数据库中，聚簇因子是指按照索引键值排序的索引行和存储于对应表中的数据行的存储顺序的相似程度。Oracle 是按照如下的算法来计算聚簇因子的值的。
- 聚簇因子的初始值为 1。
- Oracle 首先定位到目标索引处于最左边的叶子块。
- 从最左边的叶子块的第一个索引键值所在的索引行开始顺序扫描，在顺序扫描的过程中，Oracle 会比对当前索引行的 rowid 和它之前的那个索引行（它们是相邻的关系）的 rowid，如果这两个 rowid 并不是指向同一个表块，那么 Oracle 就将聚簇因子的当前值递增 1；如果这两个 rowid 是指向同一个表块，Oracle 就不改变聚簇因子的当前值。注意，这里 Oracle 在比对 rowid 的时候并不需要回表去访问相应的表块。
- 上述比对 rowid 的过程会一直持续下去，直到顺序扫描完目标索引所有叶子块里的所有索引行。
- 上述顺序扫描操作完成后，聚簇因子的当前值就是索引统计信息中的 CLUSTERING_FACTOR，Oracle 会将其存储在数据字典里。
从上述聚簇因子的算法可以知道，如果聚簇因子的值接近对应表的表块的数量，则说明目标索引索引行和存储于对应表中的数据行的存储顺序的相似程度非常高。这也就意味着 Oracle 走索引范围扫描取得目标 rowid 再回表去访问对应表块的数据时，相邻的索引行所对应的 rowid 极有可能处于同一个表块中，即 Oracle 在通过索引行记录的 rowid 回表第一次去读取对应的表块并将该表块缓存在 buffer cache 中后，当再通过相邻索引行记录的 rowid 回表第二次去读取对应的表块时，就不需要再产生物理 I/O 了，因为这次要访问的和上次已经访问过的表块是同一个块，Oracle 已经将其缓存在了 buffer cache 中。而如果聚簇因子的值接近对应表的记录数，则说明目标索引索引行和存储于对应表中的数据行的存储顺序的相似程度非常低。这也就意味着 Oracle 走索引范围扫描取得目标 rowid 再回表去访问对应表块的数据时，相邻的索引行所对应的 rowid 极有可能不处于同一个表块中，即 Oracle 在通过索引行记录的 rowid 回表第一次去读取对应的表块并将该表块缓存在 buffer cache 中后，当再通过相邻索引行记录的 rowid 回表第二次去读取对应的表块时，还需要再产生物理 I/O，因为这次要访问的和上次已经访问过的表块并不是同一个块。
换句话说，聚簇因子高的索引走索引范围扫描时比相同条件下聚簇因子低的索引要耗费更多的物理 I/O，所以聚簇因子高的索引走索引范围扫描的成本会比相同条件下聚簇因子低的索引走索引范围扫描的成本高。
Column Statistics
可以通过 DBA_TABLE_COL_STATISTICS、DBA_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS 来分别查看表、分区表的分区和分区表的子分区的列统计信息。
上述数据字典中的字段 NUM_DISTINCT 存储的就是目标列的 DISTINCT 值的数量，CBO 用 NUM_DISTINCT 的值来评估用目标列做等值查询的可选择率（Selectivity）。在目标列上没有直方图且没有 NULL 值的情况下，用目标列做等值查询的可选择率用如下公式计算：
selectivity_without_null=（1/num_distinct）
上述数据字典中的字段 NUM_NULLS 存储的就是目标列的 NULL 值的数量，CBO 用 NUM_NULLS 的值来评估对目标列施加 “is null” 或者 “is not null” 条件后返回的结果集的 Cardinality。另外，CBO 还会用 NUM_NULLS 的值来调整对有 null 值的目标列做等值查询的可选择率。当目标列上没有直方图且有 NULL 值的情况下，用目标列做等值查询的可选择率用如下公式计算：
selectivity_with_null=（1/num_distinct）*((num_rows-num_nulls)/num_rows)
此计算公式其实也适用于 selectivity_without_null，因为当目标列没有 NULL 值的时候，NUM_NULLS 的值为 0，那么 (num_rows-num_nulls)/num_rows 的值就等于 1，所以此时 selectivity_without_null 就等于 selectivity_with_null。综合上述两个公示，我们可以推断当目标列上没有直方图的时候，对其做等值查询的可选择率的计算公式如下：
selectivity_with_null=（1/num_distinct）*((num_rows-num_nulls)/num_rows)
上述数据字典中的字段 LOW_VALUE 和 HIGH_VALUE 分别存储的就是目标列的最小值和最大值，CBO 用 LOW_VALUE 和 HIGH_VALUE 来评估对目标列做范围查询时的可选择率。当目标列上没有直方图时，用目标列做范围查询的可选择率的常用计算公式如下：
（1）目标列大于指定值 VAL，且 VAL 处于 LOW_VALUE 和 HIGH_VALUE 之间：
selectivity=((HIGH_VALUE-VAL)/(HIGH_VALUE-LOW_VALUE))*Null_Adjust
Null_Adjust=(num_rows-num_nulls)/num_rows
（2）目标列小于指定值 VAL，且 VAL 处于 LOW_VALUE 和 HIGH_VALUE 之间：
selectivity=((VAL-LOW_VALUE)/(HIGH_VALUE-LOW_VALUE))*Null_Adjust
Null_Adjust=(num_rows-num_nulls)/num_rows
（3）目标列大于或等于指定值 VAL，且 VAL 处于 LOW_VALUE 和 HIGH_VALUE 之间：
selectivity=((HIGH_VALUE-VAL)/(HIGH_VALUE-LOW_VALUE)+1/NUM_DISTINCT)*Null_Adjust
Null_Adjust=(num_rows-num_nulls)/num_rows
（4）目标列小于或等于指定值 VAL，且 VAL 处于 LOW_VALUE 和 HIGH_VALUE 之间：
selectivity=((VAL-LOW_VALUE)/(HIGH_VALUE-LOW_VALUE)+1/NUM_DISTINCT)*Null_Adjust
Null_Adjust=(num_rows-num_nulls)/num_rows
（5）目标列在指定值 VAL1 和 VAL2 之间，且 VAL1 和 VAL2 均处于 LOW_VALUE 和 HIGH_VALUE 之间：
selectivity=((VAL2-VAL1)/(HIGH_VALUE-LOW_VALUE)+2/NUM_DISTINCT)*Null_Adjust
Null_Adjust=(num_rows-num_nulls)/num_rows
来看一个利用上述公式计算目标列的可选择率的例子。创建一个测试表 T7：
SQL> conn hr/hr
Connected.
SQL> create table emp as select * from employees;
Table created.
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> select count(*) from emp where department_id is null;
COUNT(*)
----------
1
不收集直方图统计信息：
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'EMP',method_opt=>'for all columns size 1',estimate_percent=>100);
PL/SQL procedure successfully completed.
SQL> select low_value,high_value,num_distinct,num_nulls from user_tab_col_statistics where table_name='EMP' and column_name='DEPARTMENT_ID';
LOW_VALUE HIGH_VALUE NUM_DISTINCT NUM_NULLS
-------------------- -------------------- ------------ ----------
C10B C2020B 11 1
SQL> select count(distinct(department_id)) from emp;
COUNT(DISTINCT(DEPARTMENT_ID))
------------------------------
11
SQL> select max(department_id),dump(max(department_id),16) from emp;
MAX(DEPARTMENT_ID) DUMP(MAX(DEPARTMENT_ID),16)
------------------ ------------------------------
110 Typ=2 Len=3: c2,2,b
SQL> select min(department_id),dump(min(department_id),16) from emp;
MIN(DEPARTMENT_ID) DUMP(MIN(DEPARTMENT_ID),16)
------------------ ------------------------------
10 Typ=2 Len=2: c1,b
执行如下 SQL：
SQL> select count(*) from emp where department_id=60;
COUNT(*)
----------
5
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| EMP | 10 | 30 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
当目标列上没有直方图的时候，对其做等值查询的可选择率用如下公式计算：
selectivity_with_null=（1/num_distinct）*((num_rows-num_nulls)/num_rows)
执行计划中的 Rows 列的值表示的是 CBO 评估出来的对应执行步骤所返回结果集的行数（即 Cardinality），对于目标 SQL select count(*) from emp where department_id=60 而言，应用等值查询条件 department_id=60 全表扫描EMP的结果集的 Cardinality 就等于 NUM_ROWS*Selectiviety，可以据此来计算一下 CBO 评估出来的 Rows 的值：
SQL> select round(107*((1/11)*((107-1)/107))) from dual;
ROUND(107*((1/11)*((107-1)/107)))
---------------------------------
10
计算出来的值为 10，和目标 SQL 的实际执行计划中的 Rows 值相符。
同理，可以应用如下公式来计算目标 SQL select count(*) from emp where department_id>60 的执行计划中 Rows 的值：
Cardinality=NUM_ROWS*Selectiviety
selectivity=((HIGH_VALUE-VAL)/(HIGH_VALUE-LOW_VALUE))*Null_Adjust
Null_Adjust=(num_rows-num_nulls)/num_rows
SQL> select round(107*(((110-60)/(110-10))*((107-1)/107))) from dual;
ROUND(107*(((110-60)/(110-10))*((107-1)/107)))
----------------------------------------------
53
计算出来的值为 63，看看该 SQL 实际执行计划中的 Rows 的值：
SQL> select count(*) from emp where department_id>60;
COUNT(*)
----------
46
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| EMP | 53 | 159 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
同理，可以应用如下公式来计算目标 SQL select count(*) from emp where department_id>=60 的执行计划中 Rows 的值：
Cardinality=NUM_ROWS*Selectiviety
selectivity=((HIGH_VALUE-VAL)/(HIGH_VALUE-LOW_VALUE)+1/NUM_DISTINCT)*Null_Adjust
Null_Adjust=(num_rows-num_nulls)/num_rows
SQL> select round(107*(((110-60)/(110-10)+1/11)*((107-1)/107))) from dual;
ROUND(107*(((110-60)/(110-10)+1/11)*((107-1)/107)))
---------------------------------------------------
63
SQL> select count(*) from emp where department_id>=60;
COUNT(*)
----------
51
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| EMP | 63 | 189 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
同理，可以应用如下公式来计算目标 SQL select count(*) from emp where department_id between 60 and 100 的执行计划中 Rows 的值：
Cardinality=NUM_ROWS*Selectiviety
selectivity=((VAL2-VAL1)/(HIGH_VALUE-LOW_VALUE)+2/NUM_DISTINCT)*Null_Adjust
Null_Adjust=(num_rows-num_nulls)/num_rows
SQL> select round(107*(((100-60)/(110-10)+2/11)*((107-1)/107))) from dual;
ROUND(107*(((100-60)/(110-10)+2/11)*((107-1)/107)))
---------------------------------------------------
62
SQL> select count(*) from emp where department_id between 60 and 100;
COUNT(*)
----------
49
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| EMP | 62 | 186 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Histograms
在 Oracle 数据库中，CBO 会默认认为目标列的数据在其最小值 LOW_VALUE 和最大值 HIGH_VALUE 之间是均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的 Cardinality，进而据此来计算成本值并选择执行计划。但目标列的数据是均匀分布这个原则并不总是正确的，在实际的系统中，我们很容易就能看到有一些目标列的数据分布是不均匀的，甚至是极度倾斜、分布极度不均匀的。对这样的列如果还按照均匀分布的原则去计算可选择率与 Cardinality，并据此来计算成本、选择执行计划，那么 CBO 所选择的执行计划就可能是不合理的，甚至是错误的。
来看一个例子。创建一个测试表 T1：
SQL> create table t1(a number(5),b varchar2(5));
Table created.
SQL> declare
2 cnt number(5) := 1;
3 begin
4 loop
5 insert into t1 values(1,1);
6 if cnt=10000 then
7 exit;
8 end if;
9 cnt := cnt+1;
10 end loop;
11 insert into t1 values(2,2);
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> select b,count(*) from t1 group by b;
B COUNT(*)
----- ----------
1 10000
2 1
SQL> create index idx_t1_b on t1(b);
Index created.
不收集直方图统计信息：
SQL> exec dbms_stats.gather_table_stats('HR','T1',estimate_percent=>100,method_opt=>'for columns size 1');
PL/SQL procedure successfully completed.
SQL> select * from t1 where b='2';
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 25005 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 5001 | 25005 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
很显然上述 SQL 应该走列B上的索引 idx_t1_b，但实际上 CBO 这里选择了全表扫描。这是因为 CBO 默认列 B 的数据是均匀分布的，而列 B 上的 DISTINCT 值只有 1 和 2 这两个值，所以 CBO 评估出来的对列 B 施加等值查询条件的可选择率就是 1/2，进而评估出来的对列B施加等值查询条件的结果集的 Cardinality 就是 5001：
SQL> select round(10001*(1/2)) from dual;
ROUND(10001*(1/2))
------------------
5001
正是因为 CBO 评估出上述等值查询要返回结果集的 Cardinality 是 5001，已经占了表 T1 总记录数的一半，所以 CBO 认为此时再走列 B 上的索引 idx_t1_b 就已经不合适了，进而就选择了走对表 T1 的全表扫描。但实际上，CBO 对上述等值查询要返回结果集的 Cardinality 的评估已经与事实严重不符，评估出来的值是 50001，其实却只有 1，差了好几个数量级。这里 CBO 选错执行计划的根本原因就是表 T1 的列 B 的分布实际上是极度不均衡的。
为了解决上述问题，Oracle 引入了直方图（Histogram）。直方图是一种特殊的列统计信息，它详细描述了目标列的数据分布情况。直方图实际上存储在数据字典基表 HISTGRM$ 中，可以通过数据字典 DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS 和 DBA_SUBPART_HISTOGRAMS 来分别查看表、分区表的分区和分区表的子分区的直方图统计信息。
如果对目标列收集了直方图，则意味着 CBO 将不再认为该目标列上的数据是均匀分布的了，CBO 就会用该目标列的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的 Cardinality，进而据此计算成本并选择相应的执行计划。
Oracle 数据库里的直方图使用了一种称为 Bucket（桶）的方式来描述目标列的数据分布。这有点类似于哈希算法中的 Bucket，它实际上是一个逻辑上的概念，相当于分组，每个 Bucket 就是一组，每个 Bucket 里会存储一个或多个目标列中的数据。Oracle 会用两个维度来描述一个 Bucket，这两个维度分别是 ENDPOINT NUMBER 和 ENDPOINT VALUE。Oracle 会将每个 Bucket 的维度 ENDPOINT NUMBER 和 ENDPOINT VALUE 记录在数据字典基表 HISTGRM$ 中，这样就达到了将目标列的直方图统计信息记录在数据字典中的目的。维度 ENDPOINT NUMBER 和 ENDPOINT VALUE 分别对应于数据字典 DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS 和 DBA_SUBPART_HISTOGRAMS 中的字段 ENDPOINT NUMBER/BUCKET_NUMBER 和 ENDPOINT VALUE。同时，Oracle 还会记录目标列的直方图统计信息所占有的 Bucket 的总数，可以通过数据字典 DBA_TABL_COL_STATISTICS、DBA_PART_COL_STATISTICS 及 DBA_SUBPART_COL_STATISTICS 中的字段 NUM_BUCKETS 来查看目标列对应直方图的 Bucket 的总数。
在 Oracle 12c 之前，Oracle 数据库里的直方图分为两种类型，分别是 Frequency 和 Height Balanced（Oracle 12c中还存在名为 Top-Frequency 和 Hybrid 类型的直方图）。在 Oracle 12c 之前，如果存储在数据字典里描述目标列直方图的 Bucket 的数量等于目标列的 DISTINCT 值的数量，则这种类型的直方图就是 Frequency 类型的直方图。如果存储在数据字典里描述目标列直方图的 Bucket 的数量小于目标列的 DISTINCT 值的数据，则这种类型的直方图就是 Height Balanced 类型的直方图。
Frequency
对于 Frequency 类型的直方图而言，目标列直方图的 Bucket 的数量就等于目标列的 DISTINCT 值的数量，此时目标列有多少个 DISTINCT 值，Oracle 在数据字典 DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS 和 DBA_SUBPART_HISTOGRAMS（分别对应于表、分区和子分区的直方图统计信息）中就会存储多少条记录，每一条记录就代表了对其中的一个 Bucket 的描述，上述数据字典中的字段 ENDPOINT_VALUE 记录了这些 DISTINCT 值，而字段 ENDPOINT_NUMBER 则记录了到此 DISTINCT 值为止总共有多少条记录。注意，对 Frequency 类型的直方图而言，ENDPOINT_NUMBER 是一个累加值，实际上可以用一条记录的 ENDPOINT_NUMBER 值减去它上一条记录的 ENDPOINT_NUMBER 值来得到这条记录本身所对应的 ENDPOINT_VALUE 值的记录数。
之前也提到，这种 Frequency 类型的直方图所对应的收集方法并不适用于目标列的 DISTINCT 值非常多的情形，所以 Oracle 对 Frequency 类型的直方图有如下的限制：Frequency 类型的直方图所对应的 Bucket 的数量不能超过 254（注意，Oracle 12c 中将不再有这一限制，在 Oracle 12c 中 Frequency 类型的直方图所对应的 Bucket 的数量可以超过 254），即 Frequency 类型的直方图只适用于那些目标列的 DISTINCT 值数量小于或等于 254 的情形。
来看一个例子，创建一个测试表H，它只有列 X：
SQL> conn scott/scott
Connected.
SQL> create table h(x number);
Table created.
SQL> declare
2 i number;
3 begin
4 for i in 1 .. 3296 loop
5 insert into h values(1);
6 end loop;
7 for i in 1 .. 100 loop
8 insert into h values(3);
9 end loop;
10 for i in 1 ..798 loop
11 insert into h values(5);
12 end loop;
13 for i in 1 .. 3970 loop
14 insert into h values(7);
15 end loop;
16 for i in 1 .. 16293 loop
17 insert into h values(10);
18 end loop;
19 for i in 1 .. 3399 loop
20 insert into h values(16);
21 end loop;
22 for i in 1 .. 3651 loop
23 insert into h values(27);
24 end loop;
25 for i in 1 .. 3892 loop
26 insert into h values(32);
27 end loop;
28 for i in 1 .. 3521 loop
29 insert into h values(39);
30 end loop;
31 for i in 1 .. 1080 loop
32 insert into h values(49);
33 end loop;
34 commit;
35 end;
36 /
PL/SQL procedure successfully completed.
SQL> select count(*) from h;
COUNT(*)
----------
40000
按照上述 Frequency 类型直方图的定义，如果这里对列X收集 Frequency 类型的直方图，则 DBA_TAB_HISTOGRAMS 中应该有 10 条记录，而且这 10 条记录的 ENDPOINT_VALUE 记录的就是这 10 各 DISTINCT 值，对应的 ENDPOINT_NUMBER 就是到此 DISTINCT 值为止累加的行记录数。这 10 条记录的 ENDPOINT_VALUE 和 ENDPOINT_NUMBER 实际上可以用如下 SQL 的显示结果来模拟：
SQL> select x,count(*) as cardinality,sum(count(*)) over (order by x range unbounded preceding) as cum_cardinality from h group by x;
X CARDINALITY CUM_CARDINALITY
---------- ----------- ---------------
1 3296 3296
3 100 3396
5 798 4194
7 3970 8164
10 16293 24457
16 3399 27856
27 3651 31507
32 3892 35399
39 3521 38920
49 1080 40000
10 rows selected.
上述查询结果中的列X就模拟了 DBA_TAB_HISTOGRAMS 中那 10 条记录的 ENDPOINT_VALUES，列 CUM_CARDINALITY 就模拟了 DBA_TAB_HISTOGRAMS 中那 10 条记录的 ENDPOINT_NUMBER。
对表 H 的列 X 来实际收集一下直方图统计信息。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'H',method_opt=>'for columns size auto X',cascade=>true);
PL/SQL procedure successfully completed.
for columns size auto X 表示对目标表的列X收集直方图统计信息，这里 auto 的含义是指让 Oracle 自行决定到底是否对列 X 收集直方图以及使用哪种类型的直方图。
收集完统计信息后发现 DBA_TAB_COL_STATISTICS 中列 X 所对应的字段 HISTOGRAM 的值为 NONE，这表明现在列 X 上依然没有直方图统计信息：
SQL> select table_name,column_name,num_distinct,density,num_buckets,histogram from user_tab_col_statistics where table_name='H';
TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ------------ ---------- ----------- ---------------
H X 10 .1 1 NONE
这种现象是正常的。因为 Oracle 在自动收集直方图统计信息时会秉承一个原则，那就是只对那些用过的列（即在 SQL 语句 WHERE 条件中出现过的列）收集直方图统计信息。Oracle 会在表 SYS.COL_USAGE$ 中记录表中各列的使用情况，在自动收集直方图统计信息时 Oracle 会去查询 SYS.COL_USAGE$，如果发现其中没有目标列的相关使用记录，那就不会对目标列收集直方图统计信息。表 H 刚刚建立，还没有在 SQL 语句的 WHERE 条件中用到过列 X，所以这里 Oracle 并不会对列X收集直方图统计信息。
收集直方图的前提条件是：1.列上的数据分布不均匀，2.列在 SQL 的 WHERE 条件中被使用过
从如下查询结果可以看到，表 H 的 OBJECT_ID 是 99001：
SQL> select object_id from user_objects where object_name='H';
OBJECT_ID
----------
99061
表 H 的列 X 所对应的 INTCOL# 是 1：
SQL> select name,intcol## from sys.col$ where obj#=99061;
NAME INTCOL#
------------------------------ ----------
X 1
现在 SYS.COL_USAGE$ 中确实没有列 X 的使用记录：
SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=99061;
no rows selected
现在来使用一下列 X：
SQL> select count(*) from h where x=10;
COUNT(*)
----------
16293
再次表 H 的列 X 来自动收集一下直方图统计信息：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'H',method_opt=>'for columns size auto X',cascade=>true,estimate_percent=>100);
PL/SQL procedure successfully completed.
收集完后发现 SYS.COL_USAGE$ 中已经有了 OBJ# 为 99061，INTCOL# 为 1 的列（就是列 X）的使用记录：
SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=99061;
OBJ## INTCOL## EQUALITY_PREDS
---------- ---------- --------------
99061 1 1
现在 DBA_TAB_COL_STATISTICS 中列 X 所对应的字段 HISTOGRAM 的值已经由 NONE 变成了 FREQUENCY，这说明现在列 X 上已经有了 Frequency 类型的直方图：
SQL> select table_name,column_name,num_distinct,density,num_buckets,histogram from user_tab_col_statistics where table_name='H';
TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ------------ ---------- ----------- ---------------
H X 10 .0000125 10 FREQUENCY
可以从 DBA_TAB_HISTOGRAMS 中看到列 X 的 Frequency 类型的直方图的具体信息：
SQL> select table_name,column_name,endpoint_number,endpoint_value from user_tab_histograms where table_name='H';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
H X 3296 1
H X 3396 3
H X 4194 5
H X 8164 7
H X 24457 10
H X 27856 16
H X 31507 27
H X 35399 32
H X 38920 39
H X 40000 49
10 rows selected.
从结果中可以看出，USER_TAB_HISTOGRAMS 中的 10 条记录与之前模拟出来的结果一模一样。
Height Balanced
在介绍 Frequency 类型的直方图时已经提到，在 Oracle 数据库里（Oracle 12c之前），Frequency 类型的直方图所对应的 Bucket 的数量不能超过 254，即 Frequency 类型的直方图只适用于那些目标列的 DISTINCT 值的数量小于或等于 254 的情形。
如果目标列的 DISTINCT 值的数量大于 254 呢，此时 Oracle 会对目标列收集 Height Balanced 类型的直方图。
来看一个有 Frequency 类型的直方图转为 Height Balanced 类型直方图的实例。创建一个测试表 T1：
SQL> create table t1(id number(10));
Table created.
SQL> begin
2 for i in 1..254 loop
3 insert into t1 values(i);
4 end loop;
5 commit;
6 end;
7 /
SQL> begin
2 for i in 1..254 loop
3 insert into t1 values(254);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
插入一定量的数据后，现在列 ID 上的 DISTINCT 值的数量为 254：
SQL> select count(distinct id) from t1;
COUNT(DISTINCTID)
-----------------
254
对 ID 以自动方式收集直方图统计信息：
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',estimate_percent=>100,method_opt=>'for columns size auto id');
PL/SQL procedure successfully completed.
从如下查询结果可以看到，现在列 ID 上已经有了 Frequency 类型的直方图：
SQL> select table_name,column_name,num_distinct,density,num_buckets,histogram from user_tab_col_statistics where table_name='T1';
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ---------- ----------- ---------------
T1 ID 254 .003937008 1 NONE
SQL> select * from t1 where id=1;
ID
----------
1
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',estimate_percent=>100,method_opt=>'for columns size auto id');
PL/SQL procedure successfully completed.
SQL> select table_name,column_name,num_distinct,density,num_buckets,histogram from user_tab_col_statistics where table_name='T1';
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ---------- ----------- ---------------
T1 ID 254 .000984252 254 FREQUENCY
对表 T1 再插入一条包含不同 ID 值的记录：
SQL> insert into t1 values(255);
1 row created.
SQL> commit;
Commit complete.
现在列 ID 上的 DISTINCT 值的数量为 255，这已经超过了 Frequency 类型直方图所能处理的 DISTINCT 值的上限：
SQL> select count(distinct id) from t1;
COUNT(DISTINCTID)
-----------------
255
删除列 ID 上的直方图统计信息：
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',method_opt=>'for columns size 1 id',cascade=>true);
PL/SQL procedure successfully completed.
对列 ID 以自动方式重新收集直方图统计信息：
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',method_opt=>'for columns size auto id',cascade=>true);
PL/SQL procedure successfully completed.
从如下查询结果可以看到，现在列 ID 上的直方图类型已经从之前的 Frequency 变为了 Height Balanced：
SQL> select table_name,column_name,num_distinct,density,num_buckets,histogram from user_tab_col_statistics where table_name='T1';
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ---------- ----------- ---------------
T1 ID 255 .001964637 254 HEIGHT BALANCED
对于 Frequency 类型的直方图而言，目标列直方图的 Bucket 的数量就等于目标列的 DISTINCT 值的数量，所以实际上此时每个 Bucket 里只存储了 1 个目标列的 DISTINCT 值。DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS 和 DBA_SUBPART_HISTOGRAMS 中每一条记录的 ENDPOINT_VALUE 和ENDPOINT_NUMBER 都只描述目标列的 1 个 DISTINCT 值的相关信息，其中字段 ENDPOINT_VALUE 记录了这些 DISTINCT 值，而字段 ENDPOINT_NUMBER 则记录了到此 DISTINCT 值为止总共有多少条记录。
但对于 Height Balanced 类型的直方图而言，即当目标列直方图的 Bucket 的数量小于目标列的 DISTINCT 值的数量时，Oracle 就不是这么干的了。此时，Oracle 首先会根据目标列对目标表的所有记录按从小到大的顺序排序，然后用目标表总的记录数除以需要使用的 Bucket 的数量，来决定每个 Bucket 里需要描述的已经排好序的记录数。假设目标表的总记录数为 M，需要使用的 Bucket 数量为 N，每个 Bucket 里需要描述的已经排好序的记录数为 O，则 O=M/N；
然后 Oracle 会用 DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS 中的每一条记录的 ENDPOINT_NUMBER 来记录 Bucket 号，Bucket 号从 0 开始，一直到 N。其中 0 号 Bucket 里存储的是目标列的最小值，所以 0 号 Bucket 所在记录的 ENDPOINT_NUMBER 值为 0，其余 Bucket 所在记录的 ENDPOINT_NUMBER 从 1 一直递增到 N，这些记录除了 0 号 Bucket 所在记录的 ENDPOINT_VALUE 值是目标列的最小值外，其他所有记录的 ENDPOINT_VALUE 值实际上存储的是到此记录所描述 Bucket 为止之前所有 Bucket 描述的记录里目标列的最大值。
Gathering
在 Oracle 数据库里收集直方图统计信息，通常是在调用 DBMS_STATS 包中的存储过程 GATHER_DATABASE_STATS/GATHER_DICTIONARY_STATS/GATHER_SCHEMA_STAS/GATHER_TABLE_STATS 收集统计信息时通过指定输入参数 METHOD_OPT 来实现。
DBMS_STATS 包中上述存储过程的输入参数 METHOD_OPT 可以接受如下的输入值：
FOR ALL[INDEXES|HIDDEN] COLUMNS [size_clause]
FOR COLUMNS[size_clause] column|attribute [size_clause] [,column|attribute[size_clause]...]
其中的 size_clause 必须符合如下的格式：
SIZE {integer|REPEAT|AUTO|SKEWONLY}
size_clause 子名中各选项的含义如下所述：
- Integer：直方图的 Bucket 的数量，必须是在 1~254 的范围内，1 表示删除该目标列上的直方图统计信息。
- REPEAT：只对已经有直方图统计信息的列收集直方图统计信息。
- AUTO：让 Oracle 自行决定是否对目标列收集直方图统计信息，以及使用哪种类型的直方图。
- SKEWONLY：只对数据分布不均衡的列收集直方图统计信息。
来看几个使用上述方法收集直方图统计信息的例子，以 SCOTT 用户下的表 EMP 为例来说明：
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
例子：对表 EMP 上所有有索引的列以自动收集的方式收集直方图统计信息
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP',method_opt=>'for all indexed columns size auto');
PL/SQL procedure successfully completed.
例子：对表 EMP 上的列 EMPNO 和 DEPTNO 以自动收集的方式收集直方图统计信息
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP',method_opt=>'for columns size auto EMPNO DEPTNO');
PL/SQL procedure successfully completed.
例子：对表 EMP 上的列 EMPNO 和 DEPTNO 收集直方图统计信息，同时指定 Bucket 数量均为 10
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP',method_opt=>'for columns size 10 EMPNO DEPTNO');
PL/SQL procedure successfully completed.
例子：对表 EMP 上的列 EMPNO 和 DEPTNO 收集直方图统计信息，同时指定列 EMPNO 的 Bucket 数量为 10，列 DEPTNO 的 Bucket 数量为 5
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP',method_opt=>'for columns EMPNO size 10 DEPTNO size 5');
PL/SQL procedure successfully completed.
例子：只删除表 EMP 上列 EMPNO 的直方图统计信息
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP',method_opt=>'for columns EMPNO size 1');
PL/SQL procedure successfully completed.
例子：删除表 EMP 上所有列的直方图统计信息
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
Manage Execution Plan
SQL 执行效率突然的衰减往往是因为目标 SQL 执行计划的改变。计划发生更改的原因有很多，如优化器版本变更、优化器统计信息变化、优化器参数变化、数据量变化等。
有4种方式可以固定和控制执行计划：
Hint（提示）
Stored Outlines（存储概要）
SQL Profile（SQL概要文件）
SPM（SQL Plan Management，SQL计划管理）
SPM
Oracle 推荐使用 SPM（SQL Plan Management）。SPM 是一种主动的稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用。当由于种种原因（比如统计信息的变更）而导致目标 SQL 产生了新的执行计划，这个新的执行计划并不会被马上启用，直到它已经被验证过其执行效率会比原先执行计划高才会被启用。SPM 的推出可以说彻底解决了执行计划稳定性的问题，它既能够主动地稳定执行计划，又保留了继续使用新的执行效率可能更高的执行计划的机会。
当启用了 SPM 后，每一个 SQL 都会存在对应的 SQL Plan Baseline，这个 SQL Plan Baseline 里存储的就是该 SQL 的执行计划，如果一个 SQL 有多个执行计划 ，那么该 SQL 就可能会有多个 SQL Plan Baseline，可以从 DBA_SQL_PLAN_BASELINES 中查看目标 SQL 所有的 SQL Plan Baseline。
DBA_SQL_PLAN_BASELINES 中的列 ENABLED 和 ACCEPTED 用来描述一个 SQL Plan Baseline 所对应的执行计划是否能被 Oracle 启用。只有 ENABLED 和 ACCEPTED 的值均为 ”YES” 的 SQL Plan Baseline 所对应的执行计划才会被 Oracle 启用。如果一个 SQL 有超过 1 个以上的 SQL Plan Baseline 的 ENABLED 和 ACCEPTED 的值均为 “YES”，则 Oracle 会从中选择成本值最小的一个所对应的执行计划来作为该 SQL 的执行计划。
在 Oracle 11g 及其以上的版本中，有如下 2 种方法可以产生目标 SQL 的 SQL Plan Baseline。
- 自动捕获
- 手动生成/批量导入（批量导入尤其适用于 Oracle 数据库大版本升级，可以确保升级后原有系统所有 SQL 的执行计划不会发生变更）
参数 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 用于控制是否开启自动捕获 SQL Plan Baseline，其默认值为 FALSE，表示在默认情况下，Oracle 并不会自动捕获 SQL Plan Baseline。这个参数可以在 Session 和系统级别动态修改的，当我们将其值修改为 TRUE 后，则 Oracle 会对上述参数影响的范围内所有重复执行的 SQL 自动捕获其 SQL Plan Baseline，并且针对目标 SQL 第一次捕获的 SQL Plan Baseline 的 ENABLED 和 ACCEPTED 的值均为 “YES”，随后如果该 SQL 的执行计划发生了变更，则再次捕获到的 SQL Plan Baseline 的 ENABLED 的值依然为 “YES”，但 ACCEPTED 的值变为了 “NO”，这表示后续变更的执行计划虽然被捕获了，但是 Oracle 不会将其作为该 SQL 的执行计划来执行，即此时 Oracle 会永远沿用该 SQL 第一次捕获的 SQL Plan Baseline 所对应的执行计划（除非后续做了手工调整）。
参数 OPTIMIZER_USE_SQL_PLAN_BASELINES 用于控制是否启用 SQL Plan Baseline，其默认值为 TRUE，表示在默认情况下，Oracle 在生成执行计划时就会启用 SPM，使用已有的 SQL Plan Baseline，这个参数也可以在 Session 和系统级别动态修改。
SQL> show parameter sql_plan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
在当前 Session 中禁掉 SPM 并同时开启自动捕获 SQL Plan Baseline：
SQL> alter session set optimizer_use_sql_plan_baselines=false;
Session altered.
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
创建一个测试表 T3 即索引：
SQL> create table t3 as select * from dba_objects;
Table created.
SQL> create index idx_t3 on t3(object_id);
Index created.
收集一下统计信息：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T3',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
执行如下目标 SQL：
SQL> select object_id,object_name from t3 where object_id between 103 and 108;
执行计划如下：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 57khyp15hzbfx, child number 0
-------------------------------------
select object_id,object_name from t3 where object_id between 103 and 108
Plan hash value: 636101163
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 7 | 210 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T3 | 7 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
现在目标 SQL 的执行计划走的是对索引 IDX_T3 的索引范围扫描。
因为目标 SQL 只执行过一次，所以 Oracle 现在不会自动捕获其 SQL Plan Baseline。从如下查询结构可以看出，该 SQL 现在确实没有对应的 SQL Plan Baseline。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
no rows selected
再次执行上述 SQL：
SQL> select object_id,object_name from t3 where object_id between 103 and 108;
执行计划没有发生改变：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 57khyp15hzbfx, child number 0
-------------------------------------
select object_id,object_name from t3 where object_id between 103 and 108
Plan hash value: 636101163
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 7 | 210 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T3 | 7 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
因为目标 SQL 已经重复执行，所以现在 Oracle 就会自动捕获其 SQL Plan Baseline了。针对上述执行计划产生了一个 SQL Plan Baseline，其 ENABLED 和 ACCEPTD 的值均为 “YES”：
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
-------------------- ------------------------------ -------------- --- --- ------------------------------------------------------------------------
SQL_b82ee0eb24faf56b SQL_PLAN_bhbr0xckgpxbb7a0cc8b4 AUTO-CAPTURE YES YES select object_id,object_name from t3 where object_id between 103 and 108
将索引 IDX_T3 的聚族因子修改为 2400 万，目的是为了让目标 SQL 的执行计划变为对表 T3 的全表扫描：
SQL> exec dbms_stats.set_index_stats(ownname=>'SYS',indname=>'IDX_T3',clstfct=>24000000,no_invalidate=>false);
PL/SQL procedure successfully completed.
查看修改结果：
SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_T3';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T3 24000000
重新执行目标 SQL：
SQL> select object_id,object_name from t3 where object_id between 103 and 108;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 57khyp15hzbfx, child number 0
-------------------------------------
select object_id,object_name from t3 where object_id between 103 and 108
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 339 (100)| |
|* 1 | TABLE ACCESS FULL| T3 | 7 | 210 | 339 (1)| 00:00:05 |
--------------------------------------------------------------------------
现在该 SQL 的执行计划已经从对索引 IDX_T3 的索引范围扫描变为对表 T3 的全表扫描。因为目标 SQL 已经重复执行且同时又产生了一个新的执行计划，所以现在 Oracle 就会自动捕获并创建这个新的执行计划所对应的 SQL Plan Baseline 了，其 ENABLED 的值依然为 YES，但 ACCEPTED 的值变为了 “NO”。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
-------------------- ------------------------------ -------------- --- --- ------------------------------------------------------------------------
SQL_b82ee0eb24faf56b SQL_PLAN_bhbr0xckgpxbb2dae97d6 AUTO-CAPTURE YES NO select object_id,object_name from t3 where object_id between 103 and 108
SQL_b82ee0eb24faf56b SQL_PLAN_bhbr0xckgpxbb7a0cc8b4 AUTO-CAPTURE YES YES select object_id,object_name from t3 where object_id between 103 and 108
对当前 Session 关闭自动捕获 SQL Plan Baseline 并同时开启 SPM，即相当于恢复了 Oracle 11g 中的默认设置：
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
Session altered.
SQL> alter session set optimizer_use_sql_plan_baselines=true;
Session altered.
现在索引 IDX_T3 的聚族因子仍然是 24000 万：
SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_T3';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T3 24000000
再次执行目标 SQL：
SQL> select object_id,object_name from t3 where object_id between 103 and 108;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 57khyp15hzbfx, child number 2
-------------------------------------
select object_id,object_name from t3 where object_id between 103 and 108
Plan hash value: 636101163
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1906 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 7 | 210 | 1906 (0)| 00:00:23 |
|* 2 | INDEX RANGE SCAN | IDX_T3 | 7 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T3@SEL$1
2 - SEL$1 / T3@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T3"@"SEL$1" ("T3"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "T3".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_bhbr0xckgpxbb7a0cc8b4 used for this statement
49 rows selected.
现在目标 SQL 的执行计划已经从对表 T3 的全表扫描恢复为对索引 IDX_T3 的索引范围扫描，注意到上述执行计划中的 Note 部分有 “SQL plan baseline SQL_PLAN_bhbr0xckgpxbb7a0cc8b4 used for this statement”，这表明在 SPM 已开启的情况下，即使目标 SQL 产生了新的执行计划，Oracle 依然只会应用该 SQL 的 ENABLED 和 ACCEPTED 的值均为 “YES” 的 SQL Plan Baseline。
从上可以看到 SPM 确实能够稳定目标 SQL 的执行计划，当启用了 SPM 后，能够保证只有被验证过的执行计划（即 ENABLED 和 ACCEPTED 的值均为 “YES” 的 SQL Plan Baseline 所对应的执行计划）才会被启用。当由于某种原因（比如统计信息的变更）导致目标 SQL 产生了新的执行计划后，这个新的执行计划并不会被 Oracle 启用。
如果想启用目标 SQL 新的执行计划（即对表T3的全表扫描），应该如何做呢？针对不同的 Oracle 数据库版本，方法不同，如果是 11gR1 的环境，只需要将目标 SQL 所采用的名为 SQL_PLAN_bhbr0xckgpxbb7a0cc8b4 的 SQL Plan Baseline（即对索引 IDX_T3 的索引范围扫描所对应的 SQL Plan Baseline）的 ACCEPTED 的值设为 “NO” 就可以了。但 11gR2 使用如下方式执行 DBMS_SPM.ALTER_SQL_PLAN_BASELINE 时 Oracle 会报错（因为在 Oracle 11gR2 中，所有已经被 ACCEPTED 的 SQL Plan Baseline 的 ACCEPTED 值将不再能够被设为 “NO”）：
SQL> var temp varchar2(1000);
SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_b82ee0eb24faf56b',plan_name=>'SQL_PLAN_bhbr0xckgpxbb7a0cc8b4',attribute_name=>'accepted',attribute_value=>'NO');
BEGIN :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_b82ee0eb24faf56b',plan_name=>'SQL_PLAN_bhbr0xckgpxbb7a0cc8b4',attribute_name=>'accepted',attribute_value=>'NO'); END;
*
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2532
ORA-06512: at line 1
在 11gR2 中，可以联合使用 DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 和 DBMS_SPM.ALTER_SQL_PLAN_BASELINE 达到启用目标 SQL 新的执行计划的目的。
先使用 DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 将目标 SQL 新的执行计划（即对表 T3 的全表扫描）所对应的名为 SQL_PLAN_bhbr0xckgpxbb2dae97d6 的 SQL Plan Baseline 的 ACCEPTED 值设为 “YES”：
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
-------------------- ------------------------------ -------------- --- --- ------------------------------------------------------------------------
SQL_b82ee0eb24faf56b SQL_PLAN_bhbr0xckgpxbb2dae97d6 AUTO-CAPTURE YES NO select object_id,object_name from t3 where object_id between 103 and 108
SQL_b82ee0eb24faf56b SQL_PLAN_bhbr0xckgpxbb7a0cc8b4 AUTO-CAPTURE YES YES select object_id,object_name from t3 where object_id between 103 and 108
SQL> exec :temp:=dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_b82ee0eb24faf56b',plan_name=>'SQL_PLAN_bhbr0xckgpxbb2dae97d6',verify=>'NO',commit=>'YES');
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
-------------------- ------------------------------ -------------- --- --- ------------------------------------------------------------------------
SQL_b82ee0eb24faf56b SQL_PLAN_bhbr0xckgpxbb2dae97d6 AUTO-CAPTURE YES YES select object_id,object_name from t3 where object_id between 103 and 108
SQL_b82ee0eb24faf56b SQL_PLAN_bhbr0xckgpxbb7a0cc8b4 AUTO-CAPTURE YES YES select object_id,object_name from t3 where object_id between 103 and 108
在使用 DBMS_SPM.ALTER_SQL_PLAN_BASELINE 将原先的执行计划（即对索引 IDX_T3 的索引范围扫描）所对应的 SQL Plan Baseline 的 ENABLED 的值设为 “NO”：
SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_b82ee0eb24faf56b',plan_name=>'SQL_PLAN_bhbr0xckgpxbb7a0cc8b4',attribute_name=>'ENABLED',attribute_value=>'NO');
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
-------------------- ------------------------------ -------------- --- --- ------------------------------------------------------------------------
SQL_b82ee0eb24faf56b SQL_PLAN_bhbr0xckgpxbb2dae97d6 AUTO-CAPTURE YES YES select object_id,object_name from t3 where object_id between 103 and 108
SQL_b82ee0eb24faf56b SQL_PLAN_bhbr0xckgpxbb7a0cc8b4 AUTO-CAPTURE NO YES select object_id,object_name from t3 where object_id between 103 and 108
再次执行目标 SQL：
SQL> select object_id,object_name from t3 where object_id between 103 and 108;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 57khyp15hzbfx, child number 1
-------------------------------------
select object_id,object_name from t3 where object_id between 103 and 108
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 339 (100)| |
|* 1 | TABLE ACCESS FULL| T3 | 7 | 210 | 339 (1)| 00:00:05 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T3@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T3"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("OBJECT_ID"<=108 AND "OBJECT_ID">=103))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_bhbr0xckgpxbb2dae97d6 used for this statement
46 rows selected.
从上述显示内容中可以看出，现在 SQL 的执行计划已经从对索引 IDX_T3 的索引范围扫描变成了对表 T3 的全表扫描。
可以轻易地在目标 SQL 的多个执行计划中切换，所以 SPM 确实是既能够主动地稳定执行计划，又保留了继续使用新的执行计划的机会，并且我们很容易就能启用新的执行计划。
手工生成目标 SQL 的 SQL Plan Baseline 其实很简单，其核心就是调用 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE。这里不讨论批量导入 SQL Plan Baseline，只讨论针对单个 SQL 的 SQL Plan Baseline 的手工生成。
使用手工生成 SQL Plan Baseline 的步骤：
（1）针对目标 SQL 使用 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 手工生成其初始执行计划所对应的 SQL Plan Baseline。此时，使用 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 传入的参数如下所示：
dbms_spm.load_plans_from_cursor_cache{
sql_id=>'原目标SQL的SQL ID',
plan_hash_value=>原目标SQL的PLAN HASH VALUES
}
（2）改写原目标 SQL 的 SQL 文本，在其中加入合适的 Hint，直到加入 Hint 后的所改写的 SQL 能走出想要的执行计划，然后对改写后的 SQL 使用 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 手工生成新的执行计划所对应的 SQL Plan Baseline。此时，使用 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 传入的参数如下所示：
dbms_spm.load_plans_from_cursor_cache{
sql_id=>'加入合适Hint后的改写SQL的SQL ID',
plan_hash_value=>加入合适Hint后的改写SQL的PLAN HASH VALUES，
sql_handle=>'原目标SQL在步骤（1）中所产生的SQL Plan Baseline的sql_hanle'
}
（3）使用 DBMS_SPM.DROP_SQL_PLAN_BASELINE 删除在步骤（1）中手工生成的原目标 SQL 的初始执行计划所对应的 SQL Plan Baseline。此时，使用 DBMS_SPM.DROP_SQL_PLAN_BASELINE 传入的参数为如下所示：
dbms_spm.drop_sql_plan_baseline{
sql_handle=>'原目标SQL在步骤（1）中所产生的SQL Plan Baseline的sql_hanle',
plan_name=>'原目标SQL在步骤（1）中所产生的SQL Plan Baseline的plan_name'
}
例子：在不改变目标 SQL 的 SQL 文本的情况下更改其执行计划的实例。先执行如下 SQL（这里使用了Hint强制不使用索引 IDX_T3，以模拟那些执行错误的 SQL）：
SQL> select /*+ no_index(t3 idx_t3) */object_name,object_id from t3 where object_id=4;
OBJECT_NAME OBJECT_ID
--------------- ----------
TAB$ 4
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cqb7vt1pk07j9, child number 0
-------------------------------------
select /*+ no_index(t3 idx_t3) */object_name,object_id from t3 where
object_id=4
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 339 (100)| |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 30 | 339 (1)| 00:00:05 |
--------------------------------------------------------------------------
现在没有开启 SQL Plan Baseline 的自动捕获，故目标 SQL 还没有对应的 SQL Plan Baseline：
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t3 idx_t3) */object_name%';
no rows selected
使用目标 SQL 的初始执行计划（即对表 T3 的全表扫描）所对应的 SQL ID 和 PLAN HASH VALUE 来手工生成对应的 SQL Plan Baseline：
SQL> var temp number
SQL> exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'cqb7vt1pk07j9',plan_hash_value=>4161002650);
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t3 idx_t3) */object_name%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
-------------------- ------------------------------ ----------- --- --- --------------------------------------------------------------------------------
SQL_dae44217a815df04 SQL_PLAN_dpt222yn1brs42dae97d6 MANUAL-LOAD YES YES select /*+ no_index(t3 idx_t3) */object_name,object_id from t3 where object_id=4
改写原目标 SQL，加入强制走索引 IDX_T3 的 Hint 后重新执行：
SQL> select /*+ index(t3 idx_t3) */object_name,object_id from t3 where object_id=4;
OBJECT_NAME OBJECT_ID
--------------- ----------
TAB$ 4
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gj7r0tvbh2zp0, child number 0
-------------------------------------
select /*+ index(t3 idx_t3) */object_name,object_id from t3 where
object_id=4
Plan hash value: 636101163
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 277 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 30 | 277 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IDX_T3 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
原目标 SQL 现在依然只有其原执行计划（即对表 T3 的全表扫描）所对应的 SQL Plan Baseline：
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t3 idx_t3) */object_name%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
-------------------- ------------------------------ ----------- --- --- --------------------------------------------------------------------------------
SQL_dae44217a815df04 SQL_PLAN_dpt222yn1brs42dae97d6 MANUAL-LOAD YES YES select /*+ no_index(t3 idx_t3) */object_name,object_id from t3 where object_id=4
用上述改写后的 SQL 的新执行计划（即对索引 IDX_T3 的索引范围扫描）所对应的 SQL ID 和 PLAN HASH VALUE 以及原目标 SQL 的 SQL Plan Baseline 的 sql_handle 来手工生成新的 SQL Plan Baseline：
SQL> exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'gj7r0tvbh2zp0',plan_hash_value=>636101163,sql_handle=>'SQL_dae44217a815df04');
PL/SQL procedure successfully completed.
从如下查询可以看到改写过的 SQL 的新执行计划所对应的 SQL Plan Baseline 已经成功生成，注意到所有手工生成的 SQL Plan Baseline 的 ENABLED 和 ACCEPTED 的值均为 “YES”，这是和自动捕获的 SQL Plan Baseline 不一样的地方：
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t3 idx_t3) */object_name%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
-------------------- ------------------------------ ----------- --- --- --------------------------------------------------------------------------------
SQL_dae44217a815df04 SQL_PLAN_dpt222yn1brs42dae97d6 MANUAL-LOAD YES YES select /*+ no_index(t3 idx_t3) */object_name,object_id from t3 where object_id=4
SQL_dae44217a815df04 SQL_PLAN_dpt222yn1brs47a0cc8b4 MANUAL-LOAD YES YES select /*+ no_index(t3 idx_t3) */object_name,object_id from t3 where object_id=4
Drop 掉原执行计划（即对表 T3 的全表扫描）所对应的 SQL Plan Baseline：
SQL> exec :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_dae44217a815df04',plan_name=>'SQL_PLAN_dpt222yn1brs42dae97d6');
PL/SQL procedure successfully completed.
现在只剩下改写过的 SQL 所对应的新执行计划所对应的 SQL Plan Baseline：
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t3 idx_t3) */object_name%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
-------------------- ------------------------------ ----------- --- --- --------------------------------------------------------------------------------
SQL_dae44217a815df04 SQL_PLAN_dpt222yn1brs47a0cc8b4 MANUAL-LOAD YES YES select /*+ no_index(t3 idx_t3) */object_name,object_id from t3 where object_id=4
再次执行原目标 SQL：
SQL> select /*+ no_index(t3 idx_t3) */object_name,object_id from t3 where object_id=4;
OBJECT_NAME OBJECT_ID
--------------- ----------
TAB$ 4
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cqb7vt1pk07j9, child number 2
-------------------------------------
select /*+ no_index(t3 idx_t3) */object_name,object_id from t3 where
object_id=4
Plan hash value: 636101163
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 277 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 30 | 277 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IDX_T3 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T3@SEL$1
2 - SEL$1 / T3@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T3"@"SEL$1" ("T3"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=4)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "T3".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_dpt222yn1brs47a0cc8b4 used for this statement
50 rows selected.
从上述内容可以看出，现在原目标 SQL 的执行计划已经从对表 T3 的全表扫描变为对索引 IDX_T3 的索引范围扫描。注意到上述执行计划中的 Note 部分有如下内容：“SQL plan baseline SQL_PLAN_dpt222yn1brs47a0cc8b4 used for this statement”，这表明改写后的 SQL 的新执行计划（即对索引 IDX_T3 的索引范围扫描）所对应的 SQL Plan Baseline 已经被 Oracle 成功应用到了原目标 SQL 上，实现了在不改变目标 SQL 的 SQL 文本的情况下更改其执行计划的目的。