不建议把Oracle redo存放在SSD上

不建议把Oracle redo存放在SSD上

不建议把redo存放在SSD上,主要原因在于 SSD的优势为读取速度,其对 随机写也有一定优化,但 redo日志的IO类型主要为顺序写而非随机写。

 

oracle 官方Support文档 《How to Minimize Waits for ‘Log File Sync’ (Doc ID 857576.1)》指出不建议把redo 存放在RAID 5或者 Solid State Disk (SSD)上。

redo_ssd1

 

以下是REDO存放在SAS和SSD上的性能对比图(越短越好),可以看到当并发较高时SSD甚至比普通SAS要差:

redo_ssd2

一般建议用户考虑将以下几类文件存放在SSD上:

 

  • 读写最多的表空间的数据文件,包括Undo
  • 临时表空间

可视化Oracle调优与智能优化-2015oracle数据库大会版

可视化Oracle调优与智能优化-2015oracle数据库大会版

下载地址:http://7xi9ig.com2.z0.glb.qiniucdn.com/可视化Oracle调优与智能优化-2015oracle数据库大会版.pdf

oracle中导出统计信息到其他表的过程

oracle中导出统计信息到其他表的过程

exec dbms_stats.create_stat_table(‘&OWNER’,’MY_STATS_TAB’);

exec dbms_stats.export_table_stats(‘&OWNER’,’&SOURCE_TABNAME’,NULL,’MY_STATS_TAB’);

exec dbms_stats.import_table_stats(‘&OWNER’,’&TARGET_TABNAME’, null, ‘MY_STATS_TAB’);

检验:
select table_name, num_rows from dba_tables where table_name in (‘&SOURCE_TABNAME’ ,’&TARGET_TABNAME’ );

 

关于ROW CR特性_ROW_CR

ROW CR确实是10g以后引入的一个新特性,该特性针对fetch by key的数据访问优化减少一致性读Consistent read。但该特性也造成了一些问题 例如出现ORA-600错误、SQL性能下降等。
 有不少客户选择关闭了 “_ROW_CR”特性, 其性能影响主要体现在 fetch by key的查询的逻辑读可能略微上升。
注:实际上这类问题已经登记过bug 10425196,但最后oracle认定为“not a bug”

From the customers point of view, the root cause of this is the ROW_CR optimization. ROW_CR is enabled by default.

Solution:
Either
require some sort of application changes to avoid such issue;
OR
go back to the original behavior where row_cr is not implemented. To this you would need to run with _row_cr=false.
The developers explain that this is not a bug but an intended behavio

The behavior you are seeing is indeed due to ROW_CR. This optimisation
was brought with the aim of reducing consistent read rollbacks. What
happens is that for a transaction doing a query that has at least
one “fetch-by-key” row-source in it, we advance the snapshot forward; So,
in this example execution of the cursor for the select using the index
picks a snapshot with an scn of (lets call it) “S1” with row-cr turned
on. “S1” is then advanced (across the commit of the update) to “S2″ due to
ROW_CR. That’s why in your testcase the fetches from the cursor pick values
post-commit.

Q1. If disable parameter _row_cr, will it impact the database performance and function which is upgraded from 10g(10.2.0.4 and before) to 11g(11.2.0.3)?
A1. Disabling row_cr has no impact to function but it maybe impact performance.
_ROW_CR is only applicable to queries which use an unique index to determine the row in the table.
The most promising direction of the fix is to reduce the number of Cleanouts and rollbacks by doing ROW CR on the index blocks for Fetch BY Key operations.

The default value of _ROW_CR in 10.2.0.4 or lower is false (non-RAC). Turn off of this optimization in 11g so that things will work exactly as they used to work in 10.2.0.4.

Q2. To RAC, in which version _row_cr is set to true by default?
A2: It is from release 10.2.0.1

Q3. If we disable _row_cr, in which scenario will cause performance issue?
A3. Disabling row_cr could impact the whole database, but the degree of the impact will depend on how much consistent read (where we have to generate undo) the application does.
Monitoring consistent read undo requests would be necessary to really determine the extent of this.

If a block is modified heavily by one application, which does not commit for a long time, all queries on non modified records in the same block by other sessions have
to do a lot of CR rollback. The upcoming SQLs, which access the same block and are using INDEX UNIQUE SCAN, will be impacted and will need extra rollbacks to construct a CR block.

In RAC, when a select has to perform consistent read potentially you have to construct undo from the local and remote instances.
Potentially if a large number of index blocks have been changed then you can arrive at a situation where there’s a lot of cross instance shipping of blocks going on.

Q4. If we disable _row_cr, what’s the possible impact can be seen in AWR report?(RAC/Non RAC)
A4. In AWR part Instance Activity Stats,”CR blocks created” and “deferred (CURRENT) block cleanout applications” maybe will be increased.

(1)该问题是row cr特性导致,且该特性是一个“优化”,不是“bug”。如果不需要使用该特性,可以考虑通过设置_row_cr=false.关闭该特性。
(2)对于从10g升级到11g的单实例,可以关闭该特性,没有功能和性能方面的影响。
(3)对于从10g升级到11g的RAC,由于10g RAC默认是开启该特性的,是否在11g中关闭该特性,需要分析可能存在的隐患。

db3600 Oracle性能诊断工具

db3600 Oracle性能诊断工具

db3600是一个ORACLE数据库性能性能脚本工具,对ORACLE的最低版本要求是10g
解压后 以sqlplus / as sysdba登陆,并执行
@3600
请将生成的 HTML 报告发送到 service@parnassusdata.com

db3600 工具下载:

db3600 Oracle性能诊断工具.zip (91.66 KB)

2 小时前 上传

点击文件名下载附件

示例报告:

样例报告report_db3600.html (1.01 MB)

Oracle SQL优化 trace 应用程序跟踪

  • 配置 SQL 跟踪工具以收集会话统计信息
  • 使用 TRCSESS 实用程序合并 SQL 跟踪文件
  • 使用 tkprof 实用程序设置跟踪文件的格式
  • 解释 tkprof 命令的输出

端到端应用程序跟踪面临的挑战

  • 我要检索 CRM 服务的踪迹。
  • 我要检索客户机 C4 的踪迹。
  • 我要检索会话 6 的踪迹。

端到端应用程序跟踪面临的挑战

启用跟踪机制后,Oracle DB 通过为每个服务器进程生成跟踪文件来实施跟踪。

在专用服务器模型中,跟踪特定客户机通常不是问题,因为将由一个专用进程在会话生存期内负责会话跟踪。可以从属于负责会话跟踪的专用服务器的跟踪文件中查看该会话的所有跟踪信息。但是,在共享服务器配置中,通常由不同的进程交替为客户机提供服务。与用户会话有关的跟踪信息分散在属于不同进程的不同跟踪文件中,因此很难完整地了解会话在生存期内的踪迹。

而且,有时出于性能或调试目的需要合并特定服务的跟踪信息,又该怎么办呢?因为同时有几个客户机使用同一服务,而每个生成的跟踪文件都属于提供该服务的服务器进程,所以这也很困难。

 

 

端到端应用程序跟踪

  • 通过将应用程序工作量通知给以下项,可以简化在多层环境中诊断性能问题的过程:

–服务

–模块

–操作

–会话

–客户机

  • 端到端应用程序跟踪工具:

–Oracle Enterprise Manager

–DBMS_APPICATION_INFO、DBMS_SERVICE、DBMS_MONITOR、DBMS_SESSION

–SQL 跟踪和 TRCSESS 实用程序

–tkprof

端到端应用程序跟踪简化了在多层环境中诊断性能问题的过程。在多层环境中,来自最终客户机的请求通过中间层路由到不同的数据库会话,这使得跨不同数据库会话跟踪客户机变得较困难。端到端应用程序跟踪使用客户机标识符,经由所有层直到数据库服务器,唯一地跟踪一个特定最终客户机。

可以使用端到端应用程序跟踪确定超常工作量的来源,例如某条高负荷的 SQL 语句。并且,您还可以确定用户的会话在数据库级别所执行的活动,以解决用户性能问题。

端到端应用程序跟踪通过跟踪某项服务中的特定模块和操作,还简化了应用程序工作量的管理工作。端到端应用程序跟踪可以识别下列项的工作量问题:

  • 客户机标识符:基于登录 ID 指定一个最终用户,例如 HR。
  • 服务:指定一组具有共同属性、服务级别阈值和优先级的应用程序,或单个应用程序。
  • 模块:指定应用程序中的一个功能块。
  • 操作:指定模块中的一项操作,例如一项 INSERT 或 UPDATE 操作。
  • 会话:基于一个给定数据库会话标识符 (SID) 指定一个会话。

端到端应用程序跟踪的主要接口是 Oracle Enterprise Manager。幻灯片中列出的其它工具将在本课的后面部分中讨论。

 

诊断跟踪的位置

自动诊断资料档案库 (ADR) 是一个基于文件的资料档案库,用于存放数据库诊断数据(如跟踪、意外事件转储和程序包、预警日志、健康监视报告、核心转储等)。

从 Oracle Database 11gR1 开始,忽略传统的 …_DUMP_DEST 初始化参数。ADR 根目录又称为 ADR 基目录,其位置由 DIAGNOSTIC_DEST 初始化参数设置。幻灯片中显示的表说明了 Oracle Database 10g(以及先前版本)与 Oracle Database 11g 中驻留的不同类跟踪数据和转储。对于 Oracle Database 11g,前台和后台跟踪文件之间没有什么区别。这两种类型的文件都会放入 $ADR_HOME/trace 目录中。您可以使用 V$DIAG_INFO 列出一些重要的 ADR 位置。

所有非意外事件跟踪都存储在 TRACE 子目录中。以前的版本会将严重错误信息转储到相应的进程跟踪文件而不是意外事件转储,这就是新旧版本之间的主要区别。从 Oracle Database 11g 开始,意外事件转储将存放到独立于普通进程跟踪文件的文件中。

:跟踪和转储之间的主要区别在于,跟踪是较为连续的输出(如打开了 SQL 跟踪时),而转储是为了响应事件(如意外事件)而进行的一次性输出。另外,核心是特定于端口的二进制内存转储。

在幻灯片中,$ADR_HOME 表示由 DIAGNOSTIC_DEST 初始化参数定义的 ADR 主目录。但是,不存在名为 ADR_HOME 的正式环境变量。

 

诊断跟踪的位置

 

什么是服务

  • 是对执行同一种工作的会话进行分组的一种方式
  • 提供单一系统映像,而不提供多实例映像
  • 是一种常规管理任务,用于提供服务到实例的动态分配
  • 是实现高可用性的连接的基础
  • 提供了一个性能优化维度
  • 是一个用于捕获跟踪信息的句柄

服务的概念最早是在 Oracle8i 中引进的,当时它是监听程序在集群的节点和实例之间执行连接负载平衡的方式。现在,服务的概念、定义和实施都已经有了巨大的扩展。服务可在数据库内组织工作执行,以使其更便于管理、评估、优化和恢复。一个服务就是数据库内的一组相关任务,这些任务有共同的功能、质量预期值以及相对于其它服务的优先级。服务可提供单一系统映像,用于管理在单个实例内运行的竞争应用程序,以及跨多个实例和数据库运行的竞争应用程序。

使用标准接口、Oracle Enterprise Manager 和 SRVCTL,可将服务作为单个实体进行配置、管理、启用、禁用和度量。

服务提供可用性。服务中断时,会被快速恢复并自动定位到正常运行的实例。

服务提供了一个性能优化维度。有了服务,就可查看和评估工作量。在会话为匿名和共享的大多数系统中,按“服务和 SQL”优化取代了按“会话和 SQL”优化。

从跟踪角度来看,服务提供了一个句柄,无论会话为何,都允许按服务名称捕获跟踪信息。

 

将服务与客户机应用程序配合使用

ERP=(DESCRIPTION=

      (ADDRESS=(PROTOCOL=TCP)(HOST=mynode)(PORT=1521))  

    (CONNECT_DATA=(SERVICE_NAME=ERP)))

 

url=”jdbc:oracle:oci:@ERP”  

url=”jdbc:oracle:thin:@(DESCRIPTION=  

      (ADDRESS=(PROTOCOL=TCP)(HOST=mynode)(PORT=1521))  

    (CONNECT_DATA=(SERVICE_NAME=ERP)))”  

 

应用程序和中间层连接池将使用透明网络基础 (TNS) 连接描述符选择服务。

所选的服务必须与已创建的服务相匹配。

幻灯片中的第一个示例显示了可以用于访问 ERP 服务的 TNS 连接描述符。

第二个示例显示了使用以前定义的 TNS 连接描述符的胖 Java 数据库连接 (JDBC) 连接描述。

第三个示例显示了使用相同 TNS 连接描述符的瘦 JDBC 连接描述。

 

跟踪服务

  • 可以通过以下项进一步限定使用服务的应用程序:

–MODULE

–ACTION

–CLIENT_IDENTIFIER

  • 使用下列 PL/SQL 程序包进行设置:

–DBMS_APPLICATION_INFO

–DBMS_SESSION

  • 可以在所有级别上进行跟踪:

–CLIENT_IDENTIFIER

–SESSION_ID

–SERVICE_NAMES

–MODULE

–ACTION

–SERVICE_NAME、MODULE、ACTION 的组合

 

应用程序可以通过 MODULE 和 ACTION 名称限定服务,以标识该服务内的重要事务处理。这使您可以针对已归类的工作量定位性能较差的事务处理。使用连接池或事务处理监视程序监视系统中的性能时,这很重要。在这些系统中,会话是共享的,计算起来非常困难。SERVICE_NAME、MODULE、ACTION、CLIENT_IDENTIFIER 和 SESSION_ID 是 V$SESSION 中的实际列。SERVICE_NAME 是在用户登录时自动设置的。应用程序将使用 DBMS_APPLICATION_INFO PL/SQL 程序包或特殊 Oracle 调用接口 (OCI) 调用设置 MODULE 和 ACTION 的名称。应针对当前执行的程序将 MODULE 设置为用户可识别的名称。同样,应将 ACTION 设置为用户将在模块内执行的特定操作或任务,例如输入新

客户。

SESSION_ID 是在创建会话时由数据库自动设置的,而 CLIENT_IDENTIFIER 可使用 DBMS_SESSION.SET_IDENTIFIER 过程来设置。

 

跟踪各个会话的传统方法是使用 SQL 命令生成可跨越工作量执行的跟踪文件。这样,就可以通过确定是否命中的方法来诊断有问题的 SQL 了。利用您提供的条件(SERVICE_NAME、MODULE 或 ACTION),可以将特定的跟踪信息捕获到一组跟踪文件中,然后将它们合并到一个输出跟踪文件中。这样,生成的跟踪文件就包含了与特定工作量相关的 SQL。对于 CLIENT_ID 和 SESSION_ID 也可以执行相同的操作。

:DBA_ENABLED_TRACES 显示有关已启用的跟踪的信息。

 

使用 Oracle Enterprise Manager 来跟踪服务

 

在“Performance(性能)”页中,可以单击“Top Consumers(顶级使用者)”链接。此时将显示“Top Consumers(顶级使用者)”页。

“Top Consumers(顶级使用者)”页包含多个选项卡,将数据库显示为单一系统映像。“Overview(概览)”选项卡式页包含四个饼图:“Top Clients(顶级客户机)”、“Top Services(顶级服务)”、“Top Modules(顶级模块)”和“Top Actions(顶级操作)”。每个图都提供了有关数据库中顶级资源使用者的不同方面。

“Top Services(顶级服务)”选项卡式将显示数据库中定义的服务的性能有关信息。在此页中,您可以在服务级别启用或禁用跟踪。

 

使用 Oracle Enterprise Manager 来跟踪服务

服务跟踪:示例

  • 跟踪服务、模块和操作:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(‘AP’);

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(-

     ‘AP’, ‘PAYMENTS’, ‘QUERY_DELINQUENT’);

  • 跟踪特定客户机标识符:

exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE 

   (client_id=>’C4′, waits => TRUE, binds => FALSE);

 

在第一个代码框中,将跟踪在 AP 服务下登录的所有会话。无论模块和操作如何变换,都只为使用该服务的每个会话创建一个跟踪文件。为了精确起见,可以只跟踪服务内的特定任务。第二个示例对此操作进行了介绍,该示例对 PAYMENTS 模块内执行 QUERY_DELINQUENT 操作的 AP 服务的所有会话进行跟踪。

通过按服务、模块和操作进行跟踪,可以将精力放在对特定 SQL 的优化上,而不是花费在从不同的程序中筛选包含 SQL 的跟踪文件上。只有定义此任务的 SQL 语句会被记录在跟踪文件中。这是对按服务、模块和操作收集统计信息的一种补充,因为这样可以识别某个操作的相关等待事件。

您还可以启动对某个特定客户机标识符的跟踪,如第三个示例所示。在此例中,C4 是将对其启用 SQL 跟踪的客户机标识符。TRUE 参数指示跟踪文件中存在等待信息。FALSE 参数指示跟踪文件中没有绑定信息。

尽管本幻灯片没有显示,但您可以使用 CLIENT_ID_TRACE_DISABLE 过程为数据库全局禁用对特定客户机标识符的跟踪。对于前面的示例来说,要禁用跟踪,请执行下列命令:

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => ‘C4’);

: 可以使用 DBMS_SESSION.SET_IDENTIFIER 过程设置 CLIENT_IDENTIFIER。

会话级跟踪:示例

 

  • 对于数据库中的所有会话:

EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);

EXEC dbms_monitor.DATABASE_TRACE_DISABLE();

  • 对于特定会话:

EXEC dbms_monitor.SESSION_TRACE_ENABLE(session_id=> 27, serial_num=>60, waits=>TRUE, binds=>FALSE);

EXEC dbms_monitor.SESSION_TRACE_DISABLE(session_id =>27, serial_num=>60);

 

可以使用跟踪来调试性能问题。启用跟踪的过程已实现为 DBMS_MONITOR 程序包的一部分。这些过程将为数据库启用全局跟踪。

可以使用 DATABASE_TRACE_ENABLE 过程启用整个实例的会话级别 SQL 跟踪。该过程包含下列参数:

  • WAITS:指定是否要跟踪等待信息
  • BINDS:指定是否要跟踪绑定信息
  • INSTANCE_NAME:指定要为其启用跟踪的实例。如果省略了 INSTANCE_NAME,则会为整个数据库启用会话级跟踪。

使用 DATABASE_TRACE_DISABLE 过程可为整个数据库或特定实例禁用 SQL 跟踪。

同样,您可以使用 SESSION_TRACE_ENABLE 过程在本地实例上对给定数据库会话标识符启用跟踪。可以在 V$SESSION 中找到 SERIAL# 和 SID。

使用 SESSION_TRACE_DISABLE 过程可对给定数据库会话标识符和序列号禁用跟踪。

:SQL 跟踪会产生一些开销,因此通常不倾向在实例级别启用 SQL 跟踪。

跟踪自己的会话

 

  • 启用跟踪:

EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);

 

  • 禁用跟踪:

EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();

 

  • 轻松标识您的跟踪文件:

alter session set tracefile_identifier=’mytraceid‘;

 

尽管仅具有 DBA 角色的用户才能调用 DBMS_MONITOR 程序包,但是任何用户都可以使用 DBMS_SESSION 程序包对自己的会话启用 SQL 跟踪。任何用户都可以通过调用 SESSION_TRACE_ENABLE 过程,对自己的会话启用会话级别的 SQL 跟踪。幻灯片中显示了一个示例。

可以使用 DBMS_SESSION.SESSION_TRACE_DISABLE 过程停止转储到跟踪文件。

TRACEFILE_IDENTIFIER 是一个初始化参数,用于指定将作为 Oracle 跟踪文件名称一部分的自定义标识符。使用这样的自定义标识符,您仅根据名称即可识别一个跟踪文件,而不必打开它或查看其内容。每次在会话级别动态修改此参数时,会将下一个跟踪转储写入一个跟踪文件,该文件名称中嵌入了新的参数值。此参数只能用于更改前台进程的跟踪文件的名称;后台进程继续使用以常规格式命名的跟踪文件。对于前台进程,V$PROCESS 视图的 TRACEID 列包含此参数的当前值。设置了此参数值后,跟踪文件名称具有下列格式:sid_ora_pid_traceid.trc

:SQL_TRACE 初始化参数从 Oracle Database 10g 起被废弃。您可以使用以下语句获取已废弃的参数的完整列表:
SELECT name FROM v$parameter WHERE isdeprecated = ‘TRUE’

 

trcsess 实用程序

 

trcsess 实用程序

 

trcsess 实用程序根据下列几个条件合并所选跟踪文件的跟踪输出:会话 ID、客户机标识符、服务名称、操作名称和模块名称。trcsess 将跟踪信息合并到一个输出文件中之后,可以通过 tkprof 处理该输出文件。

使用 DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE 过程时,跟踪信息存在于多个跟踪文件中,必须使用 trcsess 工具将这些信息收集到一个文件中。

出于性能或调试目的合并特定会话或服务的跟踪信息时,trcsess 实用程序非常有用。

在专用服务器模型中,跟踪特定会话通常不是问题,因为将由一个专用进程在会话生存期内负责会话跟踪。可以从属于负责会话跟踪的专用服务器的跟踪文件中查看该会话的所有跟踪信息。但是,即使在专用服务器模型中,跟踪服务也可能是一项复杂的任务。

更不用说在共享服务器配置中了,在该配置中,通常由不同的进程交替为用户会话提供服务。与用户会话有关的跟踪信息分散在属于不同进程的不同跟踪文件中,因此很难完整地了解会话在其生存期内的踪迹。

 

调用 trcsess 实用程序

trcsess  [output=output_file_name]

         [session=session_id]

         [clientid=client_identifier]

         [service=service_name]

         [action=action_name]

         [module=module_name]

         [<trace file names>]

 

trcsess [output=output_file_name]

幻灯片中显示了 trcsess 实用程序的语法,其中:

  • output 指定了生成输出的文件。如果没有指定此选项,则将标准输出用作输出。
  • session 合并指定会话的跟踪信息。会话标识符是会话索引和会话序列号的组合,例如 21.2371。可以在 V$SESSION 视图中找到这些值。
  • clientid 合并给定客户机标识符的跟踪信息。
  • service 合并给定服务名称的跟踪信息。
  • action 合并给定操作名称的跟踪信息。
  • module 合并给定模块名称的跟踪信息。
  • <trace file names> 是由空格分隔的所有跟踪文件名称的列表,trcsess 应在其中查找跟踪信息。指定跟踪文件名称时可以使用通配符“*”。如果没有指定跟踪文件,则会将当前目录中的所有文件输入到 trcsess 中。您可以在 ADR 中找到跟踪文件。

:必须指定 session、clientid、service、action 或 module 选项之一。如果指定了多个选项,则满足所有指定条件的跟踪文件会被合并到输出文件中。

 

trcsess 实用程序:示例

 

trcsess 实用程序:示例

 

幻灯片中的示例展示了 trcsess 实用程序的一种可能用法。此示例假设您有三个不同会话:其中有两个会话(左侧会话和右侧会话)被跟踪,另一个会话(中间的会话)可为前两个会话启用或禁用跟踪,并级联前两个会话的跟踪信息。

第一个会话和第二个会话将其客户机标识符设置成“HR session”值。这是使用 DBMS_SESSION 程序包实现的。然后,第三个会话使用 DBMS_MONITOR 程序包为前两个会话启用了跟踪。

此时,ADR 中会生成两个新的跟踪文件;一个会话一个跟踪文件,会话由

“HR session”客户机标识符标识。

现在每个受跟踪的会话都执行自己的 SQL 语句。每条语句都在 ADR 下自己的跟踪文件中生成跟踪信息。

然后,第三个会话使用 DBMS_MONITOR 程序包停止生成跟踪信息,并针对

“HR session”客户机标识符在 mytrace.trc 文件中合并跟踪信息。示例假设在 $ORACLE_BASE/diag/rdbms/orcl/orcl/trace 目录下生成所有跟踪文件,在大多数情况下这是默认设置。

SQL 跟踪文件内容

  • 分析、执行和提取计数
  • CPU 和所用时间
  • 物理读取数和逻辑读取数
  • 处理的行数
  • 库高速缓存中的未命中数
  • 每次分析使用的用户名
  • 每次提交和回退
  • 每条 SQL 语句的等待事件和绑定数据
  • 显示每条 SQL 语句的实际执行计划的行操作
  • 一致读取数、物理读取数、物理写入数以及每个行操作所用时间

正如所看到的,SQL 跟踪文件提供有关各条 SQL 语句的性能信息。它为每条语句生成下列统计信息:

  • 分析、执行和提取计数
  • CPU 和所用时间
  • 物理读取数和逻辑读取数
  • 处理的行数
  • 库高速缓存中的未命中数
  • 每次分析使用的用户名
  • 每次提交和回退
  • 每条 SQL 语句的等待事件数据以及每个跟踪文件的摘要

如果 SQL 语句的游标已关闭,则 SQL 跟踪还提供行源信息,包括以下内容:

  • 显示每条 SQL 语句的实际执行计划的行操作
  • 行数量、一致读取数、物理读取数、物理写入数以及每项操作所用的时间。只有在 STATISTICS_LEVEL 初始化参数设置为 ALL 时,才可能显示这些信息。

:使用 SQL 跟踪工具会对性能产生严重影响,可能会增加系统开销和 CPU 使用率,导致磁盘空间不足。

 

SQL 跟踪文件内容:示例

 

*** [ Unix process pid: 19687 ] 
*** 2008-02-25 15:49:19.820 
*** 2008-02-25 15:49:19.820 
*** 2008-02-25 15:49:19.820 
*** 2008-02-25 15:49:19.820 
… 
==================== 
PARSING IN CURSOR #4 len=23 dep=0 uid=82 oct=3 lid=82 tim=1203929332521849 hv=4069246757 ad='34b6f730' sqlid='f34thrbt8rjt5' 
select * from employees 
END OF STMT 
PARSE #4:c=49993,e=67123,p=28,cr=403,cu=0,mis=1,r=0,dep=0,og=1,tim=1203929332521845 
EXEC #4:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1203929332521911 
FETCH #4:c=1000,e=581,p=6,cr=6,cu=0,mis=0,r=1,dep=0,og=1,tim=1203929332522553 
FETCH #4:c=0,e=45,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=1203929332522936 
… 
FETCH #4:c=0,e=49,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=1203929333649241 
STAT #4 id=1 cnt=107 pid=0 pos=1 obj=70272 op='TABLE ACCESS FULL EMPLOYEES (cr=15 pr=6 pw=6 time=0 us cost=3 size=7276 card=107)' 
*** [ Unix process pid: 19687 ] 
*** 2008-02-25 15:49:19.820 
*** 2008-02-25 15:49:19.820 
*** 2008-02-25 15:49:19.820 
*** 2008-02-25 15:49:19.820 
… 


 

Oracle DB 可以生成多种类型的跟踪文件。本课提到的跟踪文件通常被称为 SQL 跟踪文件。
幻灯片显示了摘自由前面示例生成的 mytrace.trc SQL 跟踪文件的一段示例输出。
在这种类型的跟踪文件中,针对每条被跟踪的语句,您可以找到语句自身以及一些相应的游标详细资料。可以查看语句的以下各个执行阶段的详细统计信息:PARSE、EXEC 和 FETCH。正如您所看到的,取决于查询返回的行数,一个 EXEC 可以有多个 FETCH。
跟踪的最后一部分是包含每个行源的累积统计信息的执行计划。
取决于启用跟踪的方式,您还可以在生成的跟踪文件中获得有关等待事件和绑定变量的
信息。
通常情况下,您不要尝试解释跟踪文件本身。这是因为您对会话执行的操作并没有一个整体概念。例如,一个会话可能在不同时间执行相同语句多次。因此相应跟踪分散在整个跟踪文件中,很难找到它们。
您可以改而使用其它工具(例如 tkprof)来解释原始跟踪信息的内容。

 

设置 SQL 跟踪文件的格式:概览

使用 tkprof 实用程序设置 SQL 跟踪文件的格式:

  • 对原始跟踪文件进行排序以显示顶级 SQL 语句
  • 筛选字典语句

使用 tkprof 实用程序设置 SQL 跟踪文件的格式

 

tkprof 是一个可执行文件,它分析 SQL 跟踪文件以生成可读性更好的输出。请记住,可以从原始跟踪文件中获取 tkprof 中的所有信息。可以使用 tkprof 调用许多排序函数。通过在命令提示符下输入 tkprof 可以访问大量排序选项。一个有用的起点是 fchela 排序选项,该选项可按所用的提取时间排序输出。生成的 .prf 文件将最费时的 SQL 语句列在文件的开头。另一个有用的参数是 sys。这可以用于防止显示以 SYS 用户身份运行的 SQL 语句。这会使输出文件变得更短且更易于管理。

在生成一定数量的 SQL 跟踪文件后,可以执行以下操作:

  • 对各个跟踪文件运行 tkprof,生成一定数量的已格式化的输出文件,一个会话一个输出文件。
  • 级联这些跟踪文件,然后对结果运行 tkprof,以针对整个实例生成一个已格式化的输出文件。
  • 运行 trcsess 命令行实用程序,以合并几个跟踪文件的跟踪信息,然后对结果运行 tkprof。

tkprof 不报告跟踪文件中记录的 COMMIT 和 ROLLBACK。

:跟踪会话时,请将 TIMED_STATISTICS 参数设置为 TRUE,因为不这样做就不能
执行基于时间的比较。在 Oracle Database 11g 中 TRUE 是默认值。

 

调用 tkprof 实用程序

tkprof inputfile outputfile [waits=yes|no]

                            [sort=option]

                            [print=n]

                            [aggregate=yes|no]

                            [insert=sqlscritfile]

                            [sys=yes|no]

                            [table=schema.table]

                            [explain=user/password]

                            [record=statementfile]

                            [width=n]

 

输入没有任何参数的 tkprof 命令时,它会生成一个用法消息,以及所有 tkprof 选项的说明。本幻灯片显示了各种参数:

  • inputfile:指定 SQL 跟踪输入文件
  • outputfile:指定 tkprof 将其已格式化输出写入到的文件
  • waits:指定是否为跟踪文件中的任何等待事件记录摘要。值为 YES 或 NO。默认值为 YES。
  • sorts:按指定排序选项的降序对跟踪 SQL 语句进行排序,然后将其列到输出文件中。如果指定了多个选项,则输出按排序选项中指定值的总和的降序排序。如果省略此参数,则 tkprof 会按第一次使用的顺序将语句列在输出文件中。
  • print:仅列出输出文件中第一批按整数排序的 SQL 语句。如果省略此参数,tkprof 会列出所有受跟踪的 SQL 语句。此参数不影响可选的 SQL 脚本。SQL 脚本始终为所有受跟踪的 SQL 语句生成插入数据。
  • aggregate:如果设置为 NO,tkprof 不会对相同 SQL 文本的多个用户进行累计。
  • insert:创建一个 SQL 脚本以在数据库中存储跟踪文件统计信息。tkprof 使用您为 sqlscritfile 指定的名称创建此脚本。此脚本会创建一个表,并在此表中为每条受跟踪的 SQL 语句插入一个统计信息行。
  • sys:允许或禁止在输出文件中列出由 SYS 用户发出的 SQL 语句,或递归 SQL 语句。默认值为 YES,此值会让 tkprof 列出这些语句。NO 值会让 tkprof 省略这些语句。此参数不影响可选的 SQL 脚本。SQL 脚本始终为所有受跟踪的语句(包括递归 SQL 语句)插入统计信息。
  • table:指定在将执行计划写入到输出文件之前,tkprof 在其中临时存放执行计划的方案和表名称。如果指定的表已存在,tkprof 会删除表中的所有行,将该表用于 EXPLAIN PLAN 语句(会在表中写入更多的行),然后删除这些行。如果此表不存在,tkprof 会创建并使用该表,然后删除它。指定的用户必须能够对表发出 INSERT、SELECT 和 DELETE 语句。如果表已不存在,用户也必须能够发出 CREATE TABLE 和 DROP TABLE 语句。此选项允许多个实例在 EXPLAIN 值中使用相同用户同时运行 tkprof。这些实例可以指定不同的 TABLE 值,避免破坏性地干扰彼此之间对临时计划表的处理。如果在没有 TABLE 参数的情况下使用 EXPLAIN 参数,则 tkprof 会使用 EXPLAIN 参数指定的用户方案中的 PROF$PLAN_TABLE 表。如果在没有 EXPLAIN 参数的情况下使用 TABLE 参数,则 tkprof 会忽略 TABLE 参数。如果计划表不存在,tkprof 会创建 PROF$PLAN_TABLE 表,然后在结束时删除它。
  • explain:确定跟踪文件中每条 SQL 语句的执行计划,并将这些执行计划写入到输出文件。tkprof 在使用此参数中指定的用户和口令连接到系统后,通过发出 EXPLAIN PLAN 语句确定执行计划。指定的用户必须具有 CREATE SESSION 系统权限。如果使用了 EXPLAIN 选项,则 tkprof 在处理大型跟踪文件时会花费较长的时间。
  • record:以指定文件名 statementfile 创建一个 SQL 脚本,使其包含跟踪文件中的所有非递归 SQL 语句。这可以用来重放跟踪文件中的用户事件。
  • width:一个整数,用于控制某些 tkprof 输出(例如解释计划)的输出行宽度。此参数对于 tkprof 输出的后处理很有用。

输入和输出文件是唯一的必需参数。

tkprof 排序选项

tkprof 排序选项

 

上表列出了可以与 tkprof 的排序参数一起使用的所有排序选项。

 

tkprof 排序选项2

 

tkprof 命令的输出

  • SQL 语句的文本
  • 划分为三个 SQL 处理步骤的跟踪统计信息(针对语句和递归调用):

 

tkrpof命令的输出

 

tkprof 命令输出按 SQL 处理步骤列出 SQL 语句的统计信息。
包含统计信息的每个行的步骤由调用列的值标识。

:PARSE 值包括硬分析和软分析。硬分析是指执行计划的制定(包括优化);它随后会存储在库高速缓存中。软分析意味着向数据库发送 SQL 语句以进行分析,但数据库会在库高速缓存中查找语句,并且仅需要验证一些事项,例如访问权限。硬分析的开销很高,特别是优化。软分析主要在库高速缓存活动方面开销较大。

 

 

跟踪统计信息有七种类别:

跟踪统计信息有七种类别

 

下一页对输出进行了解释。

示例输出如下所示:

call     count       cpu    elapsed       disk      query    current rows
——- ——  ——– ———- ———- ———- ———-  —

Parse        1      0.03       0.06          0          0          0    0
Execute      1      0.06       0.30          1          3          0    0
Fetch        2      0.00       0.46          0          0          0    1
——- ——  ——– ———- ———- ———- ———-  —

total        4      0.09       0.83          1          3          0    1

 

在 CALL 列旁边,tkprof 为每条语句显示下列统计信息:

  • Count:语句的分析次数、执行次数或提取次数(先检查此列的值是否大于 1,然后解释其它列中的统计信息。除非使用了 AGGREGATE = NO 选项,否则 tkprof 会将相同的语句执行累计到一个摘要表中。)
  • CPU:所有分析、执行或提取调用花费的总 CPU 时间,以秒为单位
  • Elapsed:所有分析、执行或提取调用的所用时间总计,以秒为单位
  • Disk:所有分析、执行或提取调用从磁盘的数据文件中物理读取的数据块总数
  • Query:所有分析、执行或提取调用在一致模式下检索的缓冲区总数(对于查询,通常在一致模式下检索缓冲区。)
  • Current:在当前模式下检索的缓冲区总数(对于数据操纵语言语句,通常在当前模式下检索缓冲区。但是,始终在当前模式下检索段标头块。)
  • Rows:SQL 语句处理的行数总计(此总数不包括 SQL 语句的子查询处理的行。对于 SELECT 语句,显示在提取步骤中返回的行数。对于 UPDATE、DELETE 和 INSERT 语句,显示在执行步骤中处理的行数。)

附注

  • DISK 等同于 v$sysstat 或 AUTOTRACE 中的 physical reads。
  • QUERY 等同于 v$sysstat 或 AUTOTRACE 中的 consistent gets。
  • CURRENT 等同于 v$sysstat 或 AUTOTRACE 中的 db block gets。

tkprof 命令的输出

 

tkprof 输出还包括下列内容:

  • 递归 SQL 语句
  • 库高速缓存未命中数
  • 分析用户 ID
  • 执行计划
  • 优化程序模式或提示
  • 行源操作

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Rows     Row Source Operation
——-  —————————————————
     24  TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=9 pr=0 pw=0 time=129 us)
     24   INDEX RANGE SCAN SAL_IDX (cr=3 pr=0 pw=0 time=1554 us)(object id
 

 

递归调用

要执行用户发出的 SQL 语句,Oracle 服务器有时必须发出其它语句。这样的语句称为递归 SQL 语句。例如,如果要在某个表中插入一行,但该表没有足够空间容纳该行,Oracle 服务器就进行递归调用以动态分配空间。当数据字典高速缓存中不包含数据字典信息,必须从磁盘检索时,也会生成递归调用。

如果在 SQL 跟踪工具处于启用状态下发生了递归调用,tkprof 会在输出文件中清晰地将其标记为递归 SQL 语句。通过设置 SYS=NO 命令行参数可以在输出文件中隐藏递归调用列表。请注意,递归 SQL 语句的统计信息始终包括在导致递归调用的 SQL 语句列表中。

库高速缓存未命中数

tkprof 还列出由每条 SQL 语句的分析和执行步骤产生的库高速缓存未命中数。这些统计信息显示在单独的行中,在表格式统计信息之后。

 

行源运算

提供对行和其它行源信息执行的每个操作处理的行数,例如物理读取数和写入数;

cr = 一致读取数,w = 实际写入数,r = 实际读取数,time = 时间(微秒)。

分析用户 ID

这是最后一位分析语句的用户的 ID。

行源操作

行源操作为 SQL 语句的执行显示数据源。只有在跟踪期间已关闭了游标的情况下才包含此项信息。如果跟踪文件中没有显示行源操作,则可能需要查看 EXPLAIN PLAN。

执行计划

如果在 tkprof 命令行中指定 EXPLAIN 参数,tkprof 使用 EXPLAINPLAN 命令为每条受跟踪的 SQL 语句生成执行计划。tkprof 还显示执行计划的每个步骤所处理的行数。

:请注意,执行计划是在运行 tkprof 命令时生成的,而不是在生成跟踪文件时生成的。例如,如果自跟踪语句后创建或删除了索引,则可能产生影响。

优化程序模式或提示

指明在执行语句期间使用的优化程序提示。如果没有提示,则显示使用的优化程序模式

 

无索引时的 tkprof 输出:示例

 

... 
select max(cust_credit_limit)from customerswhere cust_city ='Paris'call     count       cpu    elapsed       disk      query    current     rows 
------- ------  -------- ---------- ---------- ---------- ----------  ------- 
Parse        1      0.02       0.02          0          0          0        0 
Execute      1      0.00       0.00          0          0          0        0 
Fetch        2      0.10       0.09       1408       1459          0        1 
------- ------  -------- ---------- ---------- ---------- ----------  ------- 
total        4      0.12       0.11       1408       1459          0        1 
 
Misses in library cache during parse: 1 
Optimizer mode: ALL_ROWS 
Parsing user id: 61   
 
Rows     Row Source Operation 
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1459 pr=1408 pw=0 time=93463 us) 
     77   TABLE ACCESS FULL CUSTOMERS (cr=1459 pr=1408 pw=0 time=31483 us) 
 
 



幻灯片中的示例显示了从 CUSTOMERS 表提取的几个执行(行)的累积结果。这需要 0.12 秒的 CPU 提取时间。该语句通过对 CUSTOMERS 表进行全表扫描来执行,在输出的行源操作中可以看到此信息。
必须对此语句进行优化。
注:如果 CPU 或 elapsed 值为 0,则没有设置 timed_statistics。

 

有索引时的 tkprof 输出:示例

 

 

... 
select max(cust_credit_limit) from customerswhere cust_city ='Paris'call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  --------- 
Parse        1      0.01       0.00          0          0          0          0 
Execute      1      0.00       0.00          0          0          0          0 
Fetch        2      0.00       0.00          0         77          0          1 
------- ------  -------- ---------- ---------- ---------- ----------  ---------total        4      0.01       0.00          0         77          0          1 
 
Misses in library cache during parse: 1 
Optimizer mode: ALL_ROWS 
Parsing user id: 61   
 
Rows     Row Source Operation 
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=77 pr=0 pw=0 time=732 us) 
     77   TABLE ACCESS BY INDEX ROWID CUSTOMERS (cr=77 pr=0 pw=0 time=1760 us) 
     77    INDEX RANGE SCAN CUST_CUST_CITY_IDX (cr=2 pr=0 pw=0 time=100                                                             us)(object id 55097) 
 
 

幻灯片中显示的结果表明,针对 CUST_CITY 列创建了索引时,CPU 时间减少到 0.01 秒。这些结果可能已实现,因为该语句使用索引来检索数据。另外,由于此示例重复执行相同语句,大多数据块已在内存中。您可以通过有效地编制索引来显著改善性能。使用 SQL 跟踪工具确定需改善的区域。
注:除非必须使用,否则不要构建索引。因为使用索引必须添加、更改或删除对行的引用,因此会减慢 INSERT、UPDATE 和 DELETE 命令的处理速度。未使用的索引应删除。但是,不需要通过 EXPLAIN PLAN 处理所有应用 SQL,您可以使用索引监控来识别和删除任何未使用的索引。

 

 

Oracle SQL 使用绑定变量bind variable

  • 列出使用绑定变量的优点
  • 使用绑定扫视
  • 使用自适应游标共享

Cursor Sharing 游标共享和不同的文字值

如果在您使用的 SQL 语句中,为 WHERE 子句条件提供了不同的文字值,则会在库高速缓存中存储许多版本的几乎完全相同的 SQL 语句。因为每条 SQL 语句是使用不同值提交的,不能在库高速缓存中找到此语句,因此您必须执行所有步骤来处理新的 SQL 语句。这样不仅常常会导致不必要的语句分析,还会导致库高速缓存很快被填满,因为所有不同的语句都存储在其中。

在以此方式进行编码时,您没有利用游标共享。

但是,根据提供的文字值,优化程序可能会生成不同的执行计划。例如,可能存在大量 JOBS,其中 MIN_SALARY 大于 12000。另一方面,可能只有很少的 JOBS,其中 MIN_SALARY 大于 18000。数据分布上的差异可能表明需要添加一个索引,以便可以根据查询中提供的值使用不同的计划。本幻灯片展示了这样的情况。正如您所看到的,第一个查询和第三个查询使用相同的执行计划,但第二个查询使用了另一个执行计划。

从性能角度看,有独立游标时性能较好。但是,这不是很经济,因为您可以为本例中的第一个查询和最后一个查询实现共享游标。

注:在幻灯片示例中,第一个查询和第三个查询的 V$SQL.PLAN_HASH_VALUE 是
相同的。

 

 

游标共享和不同的文字值

 

Curosr Sharing 游标共享和绑定变量

 

游标共享和绑定变量1

如果使用绑定变量,而不是针对不同文字值发出不同语句,则可避免额外的分析活动(在理论上)。这是因为优化程序会认识到该语句已经过分析,因此决定重用相同的执行计划,即便在下次重新执行相同的语句时您指定了不同的绑定值,也是如此。

在幻灯片的示例中,绑定变量名为 min_sal。它将与 JOBS 表的 MIN_SALARY 列进行比较。不必发出三条不同的语句,发出使用绑定变量的一条语句即可。在执行时,会使用相同的执行计划,用特定值替换变量。

但是,从性能角度看,这不是最佳方案,因为在三次执行中,只有两次您获得了最佳性能。从另一方面看,这样做非常经济,因为您只需要在库高速缓存中存储一个共享游标便可执行全部三条语句。

 

SQL*Plus 中的绑定变量

 

SQL> variable job_id varchar2(10) 
SQL> exec :job_id := 'SA_REP'; 
 
PL/SQL procedure successfully completed. 
 
SQL> select count(*) from employees where job_id = :job_id; 
 
  COUNT(*) 
---------- 
        30 
 
SQL> exec :job_id := 'AD_VP'; 
 
PL/SQL procedure successfully completed. 
 
SQL> select count(*) from employees where job_id = :job_id; 
 
  COUNT(*) 
---------- 
         2 


可以在 SQL*Plus 会话中使用绑定变量。在 SQL*Plus 中,使用 VARIABLE 命令定义绑定变量。然后,通过使用 EXEC[UTE] 命令执行赋值语句可以为变量赋值。自此以后,对该变量的任何引用都使用您赋予的值。
在幻灯片的示例中,将 SA_REP 赋给变量后执行了第一个 Select Count 操作。结果是 30。然后,将 AD_VP 赋给变量,得到的计数是 2。

 

 

Oracle Enterprise Manager 中的绑定变量

Oracle Enterprise Manager 中的绑定变量

 

在 Oracle Enterprise Manager 的“SQL Worksheet(SQL 工作表)”页中(请参见“Database Home(数据库主页)”的“Related Links(相关链接)”区域中的“SQL Worksheet(SQL 工作表)”链接),可以指定某条 SQL 语句应当使用绑定变量。可以通过选中“Use bind variables for execution(在执行中使用绑定变量)”复选框完成此任务。选中此复选框后,会生成几个字段,您可以在其中输入绑定变量值。可在 SQL 语句中使用以冒号开头的变量名称引用这些值。变量的引用顺序定义了哪个变量获得哪个值。第一个被引用的变量将获得第一个值,第二个变量获得第二个值,依此类推。如果更改了语句中变量的引用顺序,则可能需要更改值列表以与此顺序一致。

 

 

绑定变量扫视

绑定变量扫视

 

如果查询中使用文字,优化程序可以使用这些文字值来确定最佳计划。但是,如果使用绑定变量,优化程序仍需要基于查询中条件的值选择最佳计划,但在 SQL 文本中无法方便地查看这些值。这意味着,分析 SQL 语句时,系统需要能够查看绑定变量的值,以确保选择适合于这些值的有效计划。优化程序通过扫视绑定变量中的值来完成此操作。在硬分析 SQL 语句时,优化程序估算每个绑定变量的值,并将其用作输入值来确定最佳计划。在第一次分析该查询确定好执行计划后,以后只要执行相同的语句,不管所用的绑定值为何,都会重用该计划。

此功能是在 Oracle9i Database R2 中引入的。Oracle Database 11g 更改了此行为。

 

 

绑定变量扫视

绑定变量扫视2

 

在某些情况下,绑定变量扫视会导致优化程序选择不太理想的计划。之所以发生这种情况,是因为在为查询的所有后续执行确定计划时使用的都是绑定变量的第一个值。所以,即使后续执行提供不同的绑定值,也使用同一计划。对于具有不同绑定变量值的执行,也许其它计划更合适。例如,当一个特定索引的选择性随列值有非常大的变化时。对于较低的选择性,全表扫描可能更快。对于较高的选择性,索引范围扫描可能更适合。如幻灯片所示,计划 A 可能适合于 min_sal 的第一个值和第三个值,但可能不是第二个值的最佳选择。假设只有很少高于 18000 的 MIN_SALARY 值,且计划 A 为全表扫描。在此情况下,对于第二个执行,全表扫描可能不是一个好计划。

因此绑定变量是有利的,因为它们可实现更多的游标共享,从而减少了 SQL 分析。但是,在这种情况下,对于某些绑定变量值而言,游标共享可能会导致选择不太理想的计划。对于决策支持系统 (DSS) 环境而言,这是一个不使用绑定变量的有效理由。在决策支持系统环境中,查询的分析只占提交查询时需做工作的一个很小百分比。分析也许一转眼的工夫就会完成,但执行可能会花费几分钟或几小时。使用较慢的计划执行时,不值得花费成本来节省分析时间。

 

游标共享增强

  • Oracle8i 引入了仅其中的文字值不同的 SQL 语句的共享。
  • Oracle9i 扩展了此功能,仅将共享语句界定为相似语句,而不是强制执行。
  • 相似:不管文字值为何,都使用同一执行计划

SQL> SELECT * FROM employees
  2  WHERE employee_id = 153;

 

  • 不相似:对于不同的文字值可能使用不同的执行计划

SQL> SELECT * FROM employees
  2  WHERE department_id = 50;

 

Oracle8i 引入了仅其中的文字值不同的 SQL 语句的共享。优化程序并不是在每次执行具有不同文字值的相同语句时制定一个执行计划,而是生成一个通用的执行计划,将其用于此语句的所有后续执行。

由于仅使用一个执行计划,而不是许多个,所以您应针对您的应用程序测试此功能,然后再确定是否启用它。这就是 Oracle9i 扩展此功能,仅将语句作为相似语句来共享的原因。换句话说,就是只有在优化程序确信执行计划独立于所使用的文字值时才使用该执行计划。例如,请考虑一个查询,其中 EMPLOYEE_ID 是主键:

SQL> SELECT * FROM employees WHERE employee_id = 153;

用任何值进行替代都会生产相同的执行计划。因此,对于多次出现的使用不同文字值执行的相同语句,优化程序只生成一个计划是安全的。

另一方面,假设在相同 EMPLOYEES 表中 DEPARTMENT_ID 列值的范围很广。例如,部门 50 包含的员工数可能超过员工总数的三分之一,而部门 70 可能只包含一两个员工。

 

请参见两个查询:

SQL> SELECT * FROM employees WHERE department_id = 50;

SQL> SELECT * FROM employees WHERE department_id = 70;

如果针对 DEPARTMENT_ID 列具有直方图统计信息,并且该列包含的数据存在偏差,则仅使用一个执行计划共享同一游标将是不安全的。在这种情况下,根据先执行哪条语句,执行计划可能包含全表(或索引快速完全)扫描,或者使用简单的索引范围扫描。

 

CURSOR_SHARING 参数

  • CURSOR_SHARING 参数值:

–FORCE

–EXACT(默认值)

–SIMILAR

  • 可以使用下列项更改 CURSOR_SHARING:

–ALTER SYSTEM

–ALTER SESSION

–初始化参数文件

  • CURSOR_SHARING_EXACT 提示

CURSOR_SHARING 初始化参数的值决定优化程序如何使用绑定变量处理语句:

  • EXACT:完全禁用文字值替换
  • FORCE:对所有文字值都执行共享
  • SIMILAR:仅对安全的文字值执行共享

在以前的版本中,您可能仅选择 EXACT 或 FORCE 选项。这会导致优化程序对语句进行检查,以确保仅对安全文字值执行替换。在执行此操作时,它可以使用有关任何可用索引的性质的信息(唯一或非唯一),以及在索引或基表(包括直方图)上收集的统计信息。

可以使用 ALTER SYSTEM SET CURSOR_SHARING 或 ALTER SESSION SET CURSOR_SHARING 命令覆盖初始化文件中的 CURSOR_SHARING 值。

CURSOR_SHARING_EXACT 提示会导致系统执行 SQL 语句,而不尝试用绑定变量替换文字值。

 

强制游标共享:示例

SQL> alter session set cursor_sharing = FORCE;

SELECT * FROM jobs WHERE min_salary > 12000;

SELECT * FROM jobs WHERE min_salary > 18000;

SELECT * FROM jobs WHERE min_salary > 7500;

 

SELECT * FROM jobs WHERE min_salary > :”SYS_B_0″

 

 

强制游标共享:示例

 

在幻灯片的示例中,由于使用 ALTER SESSION 命令强制游标共享,所有仅文字值不同的查询会被自动覆盖,从而使用名为 SYS_B_0 的、由系统生成的同一绑定变量。结果,您最后会具有一个子游标,而不是三个。

注:自适应游标共享也可能适用,在这种情况下可能会生成另一个子游标。

 

自适应游标共享:概览

  • 自适应游标共享:
  • 仅允许针对使用绑定变量的语句智能地共享游标
  • 用于在游标共享和优化之间找到一个平衡点
  • 具有以下优点:

–自动检测各种不同的执行什么情况下能从不同的执行计划受益

–将生成的子游标数限制到最少

–是自动机制,无法关闭

 

设计绑定变量,是为了使 Oracle DB 可以针对多条 SQL 语句共享单个游标,以减少分析 SQL 语句所使用的共享内存量。然而,游标共享和 SQL 优化是两个相互冲突的目标。编写带文字值的 SQL 语句可为优化程序提供更多的信息,无疑,这有利于生成更好的执行计划,但大量的硬分析会导致内存和 CPU 开销增加。Oracle9i Database 首次尝试推出了一个折衷的解决方案 — 允许共享使用不同文字值的相似 SQL 语句。对于使用绑定变量的语句,Oracle9i 还引入了绑定扫视概念。为了从绑定扫视中受益,假定使用游标共享,且假定语句的不同调用使用相同的执行计划。若使用不同的执行计划时语句的不同调用受益显著,则绑定扫视对生成有效的执行计划就不再有用。

为了尽可能解决此问题,Oracle Database 11g 引入了自适应游标共享。此功能是一项更复杂的策略,它不会盲目地共享游标,如果与分析时间和内存使用量开销相比,使用多个执行计划所带来的收益更大,则会为使用绑定变量的每条 SQL 语句生成多个执行计划。然而,由于使用绑定变量的目的是共享内存中的游标,因此在确定需要生成的子游标数目时必须采取一种折衷的方法。

 

自适应游标共享:体系结构

 

自适应游标共享:体系结构

 

使用自适应游标共享时,在此幻灯片中所示的方案中将执行下列步骤:

  1. 游标照常会先开始进行硬分析。如果发生绑定扫视,且使用直方图计算包含绑定变量的谓词的选择性,则该游标将被标记为对绑定敏感的游标。此外,还会存储一些有关包含绑定变量的谓词的信息,包括谓词选择性。在该幻灯片的示例中,所存储的谓词选择性是一个以 (0.15,0.0025) 为中心的立方体。由于进行了初始硬分析,将使用已扫视的绑定确定初始执行计划。执行游标后,绑定值和游标的执行统计信息存储在该游标中。

在下次使用一组新的绑定值执行该语句时,系统会执行常规软分析,并查找供执行使用的匹配游标。执行结束时,会将执行统计信息与当前存储在游标中的执行统计信息进行比较。然后,系统观察所有先前运行的统计信息模式(请参阅下一张幻灯片中的 V$SQL_CS_… 视图),并确定是否将游标标记为能识别绑定的游标。

 

  1. 下一次对此查询进行软分析时,如果游标已是能够识别绑定的,则会使用能识别绑定的游标匹配。假设具有新的一组绑定值的谓词选择性现在是 (0.18,0.003)。由于选择性用作能识别绑定的游标匹配的一部分,并且该选择性在现有立方体内,因此该语句使用现有子游标的执行计划运行。
  2. 下一次对此查询进行软分析时,假设具有一组新绑定值的谓词选择性现在是 (0.3,0.009)。由于该选择性不在现有立方体内,所以找不到子游标匹配项。因此,系统会执行硬分析,在本例中生成了一个具有另一个执行计划的新子游标。此外,新选择性立方体将存储为该新子游标的一部分。执行该新子游标后,系统会将绑定值和执行统计信息存储在该游标中。
  3. 下一次对此查询进行软分析时,假设具有一组新绑定值的谓词选择性现在是 (0.28,0.004)。由于该选择性不在现有的某个立方体内,系统将执行硬分析。假设此时硬分析生成与第一个执行计划相同的执行计划。因为该计划与第一个子游标相同,所以将合并这两个子游标。也就是说,这两个立方体将合并为一个较大的新立方体,并删除其中一个子游标。下次执行软分析时,如果选择性位于该新立方体内,子游标将匹配。

 

自适应游标共享:视图

下列视图提供有关自适应游标共享使用情况的信息:

下列视图提供有关自适应游标共享使用情况的信息

 

这些视图确定查询是否是能识别绑定的查询,是否为自动处理的,无需用户输入。但是,有关所发生的操作的信息显示在 V$ 视图中,这样在遇到问题时,您可以进行诊断。V$SQL 中新增了几列:

  • IS_BIND_SENSITIVE:指示游标是否是对绑定敏感的,值为 YES | NO。符合以下情况的查询称为对绑定敏感的查询:计算谓词选择性时优化程序为其扫视绑定变量值,并且绑定变量值的更改可能导致不同的计划。
  • IS_BIND_AWARE:指示游标是否为能识别绑定的游标,值为 YES | NO。位于游标高速缓存中、已标记为使用能识别绑定的游标共享的游标称为识别绑定的游标。
  • V$SQL_CS_HISTOGRAM:显示跨三个存储桶执行历史记录直方图的执行计数的分布情况。
  • V$SQL_CS_SELECTIVITY:显示为包含绑定变量且在游标共享检查中使用了其选择性的每个谓词存储在游标中的选择性立方体或范围。它包含谓词文本和选择性范围的下限值和上限值。
  • V$SQL_CS_STATISTICS:自适应游标共享监视查询的执行,在一段时间内收集相关的信息,并使用此信息确定是否切换为对该查询使用能识别绑定的游标。该视图汇总了它收集的用于做出该决定的信息。对于许多执行来说,它跟踪已处理的行数、缓冲区获取数和 CPU 时间。如果构建游标时使用了绑定集,则 PEEKED 列的值为 YES,否则为 NO。

自适应游标共享:示例

 

自适应游标共享:示例

 

请考虑该幻灯片中的数据。JOB_ID 列具有相应的直方图统计信息,这些统计信息表示 SA_REP 比 AD_ASST 多出现数千次。在这种情况下,如果使用文字值而不使用绑定变量,查询优化程序会发现 AD_ASST 值出现在不足 1% 的行中,而 SA_REP 值出现在近三分之一的行中。如果表包含一百万以上的行,则针对每个值的查询执行计划是不同的。AD_ASST 查询会导致索引范围扫描,因为具有该值的行很少。SA_REP 查询会导致全表扫描,因为具有该值的行很多,这样可以更高效地读取整个表。但是,实际上,起初使用绑定变量会导致对这两个值使用相同的执行计划。因此,即使对于其中每个值存在更好的不同计划,也使用相同的计划。

在使用绑定变量执行几次此查询后,系统会考虑查询的绑定识别,此时系统会基于绑定值更改计划。这意味着基于绑定变量值对查询使用最佳计划。

 

 

与自适应游标共享交互

  • CURSOR_SHARING:

–如果 CURSOR_SHARING <> EXACT,则可能会使用绑定变量重写包含文字的语句。

–如果语句被重写,则可能会对其应用自适应游标共享。

  • SQL 计划管理 (SPM):

–如果将 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 设置为 TRUE,则仅使用生成的第一个计划。

–一种解决方法是:将此参数设置为 FALSE,然后运行您的应用程序,直到将所有计划都加载到游标高速缓存中。

–手动将游标高速缓存加载到相应的计划基线中。

 

  • 自适应游标共享与 CURSOR_SHARING 参数无关。此参数的设置决定是否用系统生成的绑定变量替换文字。如果替换,则自适应游标共享的行为跟用户在开始时已提供绑定一样。
  • 如果使用 SPM 自动计划捕获,则将为使用绑定变量的 SQL 语句捕获的第一个计划标记为相应的 SQL 计划基线。如果同一 SQL 语句存在其它计划(自适应游标共享可能就是这种情况),则该计划将添加到 SQL 语句计划历史记录中,并对其进行标记以便验证:不会立即使用该计划。因此,即使自适应游标共享提供了基于一组新绑定值的新计划,在该计划得到验证前 SPM 也不允许使用它。这样就退回到了 10g 的行为,该语句的所有后续执行仅使用基于第一组绑定值生成的计划。一种可能的解决方法是:将自动计划捕获设置为 False,然后将系统运行一段时间,在使用具有绑定的 SQL 语句拥有的所有计划填充游标高速缓存后,将整个计划直接从游标高速缓存加载到相应的 SQL 计划基线。通过执行此操作,默认情况下,单个 SQL 语句的所有计划都将被标记为 SQL 基线计划。此课程不介绍 SQL 计划管理。请参阅《SQL 性能优化》课程或《面向管理员的新增功能》课程。

 

Oracle SQL CBO 优化器/优化程序 统计信息

  • 收集优化程序统计信息
  • 收集系统统计信息
  • 设置统计信息首选项
  • 使用动态采样
  • 处理优化程序统计信息

 

SQL CBO 优化器/优化程序 统计信息

 

优化程序统计信息描述有关数据库及其中对象的详细资料。查询优化程序使用这些统计信息,为每个 SQL 语句选择最佳的执行计划。

由于数据库中的对象经常发生更改,所以必须定期更新统计信息,以使它们能够准确地描述这些数据库对象。统计信息由 Oracle DB 自动进行维护,您也可以使用 DBMS_STATS 程序包手动维护优化程序统计信息。

  • 描述数据库以及数据库中的对象
  • 查询优化程序使用以下信息进行评估:

–谓词的选择性

–每个执行计划的成本

–访问方法、联接顺序和联接方法

–CPU 和输入/输出 (I/O) 成本

  • 与收集优化程序统计信息相比,对过时的统计信息进行刷新同样重要。

–由系统自动收集

–用户使用 DBMS_STATS 手动收集

 

优化程序统计信息的类型

  • 表统计信息:

–行数

–块数

–平均行长度

  • 索引统计信息:

–B* 树级别

–唯一键

–叶块数量

–聚簇因子

  • 系统统计信息:

–I/O 性能及利用率

–CPU 性能及利用率

  • 列统计信息:

–基本统计信息:相异值数量、空值数量、平均长度、最小值、最大值

–直方图(列数据有偏差时的数据分布)

–扩展的统计信息

幻灯片中列出了大多数优化程序统计信息。

自 Oracle Database 10g 开始,在创建或重建索引时会自动收集索引统计信息。

注:此幻灯片提及的统计信息是优化程序统计信息,是为查询优化创建的,存储在数据字典中。不应将此类统计信息与 V$ 视图中显示的性能统计信息相混淆。

 

 

表统计信息 (DBA_TAB_STATISTICS)

NUM_ROWS
这是基数计算的基础。如果表是嵌套循环联接的驱动表,则行计数尤为重要,因为它定义了内部表被探测多少次。

BLOCKS
已使用数据块的数量。块计数与 DB_FILE_MULTIBLOCK_READ_COUNT 组合在一起给出了基表访问成本。

EMPTY_BLOCKS
表中空的(从未使用的)数据块数量。这是已使用数据块和高水位标记之间的块。

AVG_SPACE
分配给表的数据块中空闲空间的平均数量(以字节为单位)。

CHAIN_CNT
这是表中从一个数据块链接到另一个数据块、或者移植到了新块,且需要用链接来保留原有 ROWID 的行的数量。

AVG_ROW_LEN
表中行的平均长度(以字节为单位)。

STALE_STATS

指明统计信息在相应表中是否有效。

 

  • 用于确定以下项:

–表访问成本

–联接基数

–联接顺序

  • 收集的一些统计信息包括:

–行计数 (NUM_ROWS)

–块计数 (BLOCKS) 精确值

–空块数 (EMPTY_BLOCKS) 精确值

–每个块的平均空闲空间 (AVG_SPACE)

–链接行的数量 (CHAIN_CNT)

–平均行长度 (AVG_ROW_LEN)

–统计信息状态 (STALE_STATS)

 

索引统计信息 (DBA_IND_STATISTICS)

 

  • 用于确定以下项:

–全表扫描与索引扫描

  • 收集的统计信息包括:

–B* 树级别 (BLEVEL) 精确值

–叶块计数 (LEAF_BLOCKS)

–聚簇因子 (CLUSTERING_FACTOR)

–唯一键 (DISTINCT_KEYS)

–它指明索引中的每个相异值平均出现在多少个叶块中 (AVG_LEAF_BLOCKS_PER_KEY)

–索引中的每个相异值所指向的表数据块的平均数量 (AVG_DATA_BLOCKS_PER_KEY)

–索引中的行数 (NUM_ROWS)

 

一般来说,要选择索引访问,优化程序要求针对索引列的前缀使用一个谓词。但是,如果没有谓词,并且查询中引用的所有列都存在于索引中,则优化程序会考虑使用完全索引扫描,而不是全表扫描。

BLEVEL
该信息用于计算叶块查找成本。它指明索引从根块到叶块的深度。深度为“0”表明根块和叶块是相同的。

LEAF_BLOCKS
该信息用于计算完全索引扫描成本。

CLUSTERING_FACTOR
它基于索引的值测量表中行的顺序。如果该值接近块数量,则表明表的顺序良好。在这种情况下,一个叶块中的索引项通常指向同一数据块中的行。如果该值接近行数量,则表明表的顺序是随机的。在这种情况下,同一叶块中的索引项可能没有指向同一数据块中的行。

STALE_STATS

指明统计信息在相应索引中是否有效。

 

DISTINCT_KEYS
相异索引值的数量。对于强制执行 UNIQUE 和 PRIMARY KEY 约束条件的索引,此值等于表行数。

AVG_LEAF_BLOCKS_PER_KEY
它指明索引中的每个相异值平均出现在几个叶块中,该值舍入到最近的整数。对于强制执行 UNIQUE 和 PRIMARY KEY 约束条件的索引,此值始终等于一 (1)。

AVG_DATA_BLOCKS_PER_KEY
索引中的每个相异值所指向的表数据块的平均数量,舍入到最近的整数。对于每个给定的索引列值,该统计信息表示其行中包含该值的数据块的平均数量。

NUM_ROWS
索引中的行数。

索引聚簇因子

 

系统按块执行输入/输出 (I/O)。所以,优化程序是否使用全表扫描取决于所访问的块的百分比,而不是行百分比。这称作索引聚簇因子。如果每个块都包含单个行,则访问的行数和访问的块数是相同的。

但是,大多数表在每个块中都包含多个行。因此,所需数量的行可能聚集在几个块中,也可能散布在大量块中。除 B* 树级别,叶块数量以及索引选择性之类的信息之外,索引范围扫描的成本公式还包括聚簇因子。这是因为较小的聚簇因子表示行集中在表中的少量块中。较大的聚簇因子表示行随机分布在表中各个块之间。所以,较大的聚簇因子意味着使用索引范围扫描按 ROWID 获取行会花费较多的成本,因为需要访问表中较多的块才能返回数据。在实际环境中,因为叶块数量和 B* 树高度与聚簇因子和表选择性相比,相对较小,所以聚簇因子在决定索引范围扫描的成本方面似乎起着重要作用。

注:如果某个表有多个索引,则一个索引的聚簇因子可能较小,而同时另一个索引的聚簇因子可能较大。如果尝试重新组织此表以改进一个索引的聚簇因子,则可能会导致另一个索引的聚簇因子性能降低。

index_clustering_1

 

在收集索引的统计信息时,系统会在 DBA_INDEXES 视图的 CLUSTERING_FACTOR 列中计算聚簇因子。计算方式相对来讲比较简单。从左到右读取索引,对于每个索引项,如果相应行所在的块不同于上一行所在的块,将聚簇因子加一。基于此算法,聚簇因子最小可能值是块数量,最大可能值是行数量。

幻灯片中的示例说明了聚簇因子如何影响成本。假设存在下列情况:有一个表包含 9 行,在此表的 col1 上有一个非唯一索引,c1 列当前存储的值为 A、B 和 C,此表仅有三个 Oracle 块。

  • 情形 1:如果组织表中的行,使索引值分散在各个表块中(而不是并列排置),则索引聚簇因子较高。
  • 情形 2:如果将具有相同值的行并列排置在相同的块中,则这些行的索引聚簇因子
    较低。

注:对于位图索引,聚簇因子不适用,因而不会使用。

 

列统计信息 (DBA_TAB_COL_STATISTICS)

  • 列的相异值计数 (NUM_DISTINCT)
  • 下限值 (LOW_VALUE) 精确值
  • 上限值 (HIGH_VALUE) 精确值
  • 空值数量 (NUM_NULLS)
  • 非常用值的选择性估计 (DENSITY)
  • 直方图存储桶数量 (NUM_BUCKETS)
  • 直方图类型 (HISTOGRAM)

NUM_DISTINCT 用在选择性计算中,例如,1/NDV

LOW_VALUEHIGH_VALUE:基于成本的优化程序 (CBO) 假定所有数据类型的值在下限值和上限值之间是均匀分布的。这些值用于确定范围选择性。

NUM_NULLS 对可为空值的列以及 IS NULL 和 IS NOT NULL 谓词的选择性很有帮助。

DENSITY 仅与直方图相关。它可用作非常用值的选择性估计。可以将其视为在此列中发现某个特殊值的概率。它的计算取决于列是否有一个直方图以及直方图的类型。

NUM_BUCKETS 是列直方图中存储桶的数量。

HISTOGRAM 表明直方图是否存在或直方图类型:NONE、FREQUENCY、HEIGHT BALANCED

 

直方图

  • 优化程序假定数据均匀分布;当数据有偏差时,这可能会导致访问计划不太理想。
  • 直方图:

–存储其它列分布信息

–在数据分布不均匀的情况下给出更准确的选择性估计。

  • 使用无限的资源,您可以存储每个不同的值以及与该值对应的行数量。
  • 如果相异值很多,这会变得难以管理,此时应使用其它
    方法:

–频率直方图(#相异值 ≤ #存储桶)

–高度平衡直方图(#存储桶 < #相异值)

  • 它们存储在 DBA_TAB_HISTOGRAMS 中。

直方图捕获列中不同值的分布情况,因此会生成更准确的选择性估计。如果列包含有偏差的数据,或包含在重复数量上有很大变化的值,则针对这类列创建直方图可以帮助查询优化程序生成准确的选择性估计,并针对索引使用、联接顺序、联接方法等等做出更好的
决定。

如果没有直方图,则假定数据均匀分布。如果某个列的直方图可用,则评估人员会使用该图,而不使用相异值数量。

创建直方图时,Oracle DB 使用两种不同类型的直方图表示方法,具体取决于在相应列中找到的相异值数量。如果您的数据集包含的相异值少于 254 个,并且没有指定直方图存储桶数量,则系统会创建频率直方图。如果相异值数量大于所需的直方图存储桶数量,则系统会创建高度平衡直方图。

在以下字典视图中可以找到有关直方图的信息:DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS 和 DBA_SUBPART_HISTOGRAMS

注: 在统计信息收集方面,收集直方图统计信息是一项最耗费资源的操作。

 

频率直方图

频率直方图在幻灯片的示例中,假定您有一个包含 40,001 个数字的列,但相异值只有 10 个:1、3、5、7、10、16、27、32、39 和 49。值 10 是最常用的值,出现了 16,293 次。

当请求的存储桶数量等于(或大于)相异值数量时,您可以存储每个不同的值并记录精确的基数统计信息。在这种情况下,在 DBA_TAB_HISTOGRAMS 中,ENDPOINT_VALUE 列存储列值,ENDPOINT_NUMBER 列存储该列值的行计数。

该行计数以累积形式进行存储,这样范围扫描可以减少一些计算。为了清楚起见,幻灯片中使用曲线显示行计数的实际数量;仅 ENDPOINT_VALUE 和 ENDPOINT_NUMBER 列存储在数据字典中。

histogramz1

 

查看频率直方图

幻灯片中的示例显示了查看频率直方图的方法。由于 INVENTORIES 表的 WAREHOUSE_ID 列中相异值的数量为 9,并且请求的存储桶数量为 20,所以系统会自动创建一个具有 9 个存储桶的频率直方图。可以在 USER_TAB_COL_STATISTICS 视图中查看此信息。

要查看直方图本身,可以查询 USER_HISTOGRAMS 视图。您可以看到 ENDPOINT_NUMBER 和 ENDPOINT_VALUE,前者对应着相应 ENDPOINT_VALUE 的累积频率,后者在本例中代表列数据的实际值。

注:本课稍后将对 DBMS_STATS 程序包进行介绍。

 

 

BEGIN DBMS_STATS.gather_table_STATS (OWNNAME=>'OE', TABNAME=>'INVENTORIES', 
       METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id'); 
END; 




SELECT column_name, num_distinct, num_buckets, histogram 
FROM   USER_TAB_COL_STATISTICS 
WHERE  table_name = 'INVENTORIES' AND 
       column_name = 'WAREHOUSE_ID'; 
 
COLUMN_NAME  NUM_DISTINCT NUM_BUCKETS HISTOGRAM 
------------ ------------ ----------- --------- 
WAREHOUSE_ID            9           9 FREQUENCY 

SELECT endpoint_number, endpoint_value 
FROM   USER_HISTOGRAMS 
WHERE  table_name = 'INVENTORIES' and column_name = 'WAREHOUSE_ID' 
ORDER BY endpoint_number; 
 
ENDPOINT_NUMBER ENDPOINT_VALUE 
--------------- -------------- 
36                           1 
213                          2 
261                          3 
… 


高度平衡直方图

 

在高度平衡直方图中,列值被分成若干段,每个段包含的行数几乎相同。直方图可表明在值范围中端点终止的位置。在幻灯片示例中,假定您有一个包含 40,001 个数字的列。但相异值只有 10 个:1、3、5、7、10、16、27、32、39 和 49。值 10 是最常用的值,出现了 16,293 次。如果存储桶数量少于相异值数量,则 ENDPOINT_NUMBER 记录存储桶数量,而 ENDPOINT_VALUE 记录与此端点对应的列值。在示例中,每个存储桶包含的行数是总行数的五分之一,即 8000。

根据此假设,值 10 的出现次数在 8000 和 24000 之间。因此您确定值 10 是一个常用值。

此类型的直方图适用于常用值的等式谓词和范围谓词。

不记录每个存储桶的行数,因为这可以从值的总数以及所有存储桶都包含相同数量的值这一事实推导出来。在本例中,值 10 是一个常用值,因为它跨越了多个端点值。为了节约空间,直方图不实际存储重复的存储桶。由于此原因,在幻灯片的示例中,不会在 DBA_TAB_HISTOGRAMS 中记录存储桶 2(端点值为 10)。

注:本例中的密度统计值将为 1/9 x 4/5 = 0.088 或 8.8%(9=#NPV 和 4=#NPB)。此值将用于非常用值的选择性计算。

 

histogramz2

幻灯片中的示例显示了查看高度平衡直方图的方法。由于 INVENTORIES 表的 QUANTITY_ON_HAND 列中相异值的数量为 237,并且请求的存储桶数量为 10,所以系统会自动创建一个具有 10 个存储桶的高度平衡直方图。可以在 USER_TAB_COL_STATISTICS 视图中查看此信息。

要查看直方图本身,可以查询 USER_HISTOGRAMS 视图。您可以看到与存储桶数量对应的 ENDPOINT_NUMBER,以及与端点的末端值对应的 ENDPOINT_VALUE。

注:本课稍后将对 DBMS_STATS 程序包进行介绍。

 

 

BEGIN 
  DBMS_STATS.gather_table_STATS(OWNNAME =>'OE', TABNAME=>'INVENTORIES',  
  METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand'); 
END; 


SELECT column_name, num_distinct, num_buckets, histogram  
  FROM USER_TAB_COL_STATISTICS 
 WHERE table_name = 'INVENTORIES' AND column_name = 'QUANTITY_ON_HAND'; 
 
COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM 
------------------------------ ------------ ----------- --------------- 
QUANTITY_ON_HAND                        237          10 HEIGHT BALANCED 


SELECT endpoint_number, endpoint_value  
FROM USER_HISTOGRAMS 
WHERE table_name = 'INVENTORIES' and column_name = 'QUANTITY_ON_HAND' 
ORDER BY endpoint_number; 
 
ENDPOINT_NUMBER ENDPOINT_VALUE 
--------------- -------------- 
              0              0 
              1             27 
              2             42 
              3             57 
… 


 

直方图注意事项

  • 如果列的数据分布有大幅偏差,则直方图很有用。
  • 对于以下情况,直方图没有太大用处:

–列没有出现在 WHERE 或 JOIN 子句中

–列的数据分布较均匀

–对唯一性列使用了等式谓词

  • 存储桶的最大数量等于 254 和相异值数量这两者之中的最小值。
  • 除非直方图能够显著改善性能,否则不要使用直方图。

直方图只有在能够反映给定列的当前数据分布时才是有用的。在数据分布保持不变的情况下,列中的数据可能会更改。如果列的数据分布频繁发生变化,您必须频繁地重新计算其直方图。

如果您要为其创建直方图的列包含有高度偏差的数据,则直方图很有用。

然而,对于没有出现在 SQL 语句的 WHERE 子句中的列,没有必要为其创建直方图。同样,也没有必要为数据分布较均匀的列创建直方图。

另外,直方图对声明为 UNIQUE 的列也没有用处,因为此时选择性是显而易见的。此外,存储桶的最大数量为 254,可能更低,具体取决于相异列值的实际数量。直方图会影响性能,只有在直方图能够显著改善查询计划时,才应使用它们。如果数据分布很均匀,则优化程序不使用直方图就可以相当准确地估算出执行一个特殊语句所需的成本。

注:字符列有一些不同的行为,因为对于任何字符串只有前 32 个字节被存储为直方图
数据。

 

多列统计信息:概览

 

使用 Oracle Database 10g,在以下有限的情况下,查询优化程序在计算多个谓词的选择性时,将考虑列之间的相互关系:

  • 如果连接谓词的所有列与级联索引关键字的所有列匹配,并且谓词是等值联接中使用的等式,则优化程序将使用索引中的唯一键 (NDK) 的数量来估计选择性 (1/NDK)。
  • 如果将 DYNAMIC_SAMPLING 设置为级别 4,则查询优化程序将使用动态采样来评估涉及同一个表中多个列的复杂谓词的选择性。但是,样本的大小很小,并且分析时间有所增加。因此,从统计学的角度来看样本可能不准确,并且可能会弊大于利。

在其它所有情况下,优化程序将假定复杂谓词中所用的列的值互不相关。优化程序会通过将各个谓词的选择性相乘来估计连接谓词的选择性。如果列之间存在相关性,则此方法会低估选择性。为了避免此问题,Oracle Database 11g 允许您收集、存储并使用以下统计信息来捕获两个或更多列(也称为列组)之间的功能相关性:相异值的数量、空值的数量、频率直方图和密度。

 

extended_statistics1

例如,假定有一个 VEHICLE 表,其中存储了有关汽车的信息。MAKE 和 MODEL 列是高度相关的,因为 MODEL 决定 MAKE。这是一种强依赖关系,优化程序应将这两个列看成是高度相关的两个列。可以使用 CREATE_EXTENDED_STATS 函数将该关系传送给优化程序(如幻灯片上的示例所示),然后计算所有列的统计信息(包括创建的相关组的统计
数据)。

优化程序仅对等式谓词使用多列统计信息。

附注

  • CREATE_EXTENDED_STATS 函数会返回一个虚拟的隐藏列名称,如 SYS_STUW_5RHLX443AN1ZCLPE_GLE4。
  • 根据幻灯片中的示例,可以使用以下 SQL 确定名称:
    select dbms_stats.show_extended_stats_name(‘jfv’,’vehicle’,'(make,model)’) from dual
  • 创建统计信息扩展名之后,可以通过使用 ALL|DBA|USER_STAT_EXTENSIONS 视图检索它们。

 

表达式统计信息:概览

 

与列表达式相关的谓词是查询优化程序的一个重要的问题。计算 function(Column) = constant 形式的谓词的选择性时,优化程序将假定静态选择性值为 1%。此方法是错误的,因为这会导致优化程序生成不太理想的计划。

查询优化程序已得到了扩展,可以在有限的几种情况下更好地处理此类谓词。在这些情况下,函数保留列的数据分布特征,因而允许优化程序使用列统计信息。例如,TO_NUMBER 就是此类函数之一。

此外,还对相应功能做了进一步增强,可在查询优化过程中对内置函数求值,以便使用动态采样来获得更高的选择性。最后,优化程序收集所创建的虚拟列的统计信息以支持基于函数的索引。

但是,这些解决方案或者局限于特定的函数类,或者仅适合于用于创建基于函数的索引的表达式。通过使用 Oracle Database 11g 中的表达式统计信息,您可以使用更加通用的解决方案;这些解决方案包括了用户定义的任意函数,并且与是否存在基于函数的索引无关。如幻灯片中的示例所示,此功能将依靠虚拟列基础结构来创建列表达式的统计信息。

 

express_statistics1

 

收集系统统计信息

 

  • 通过系统统计信息,CBO 可以使用 CPU 和 I/O 特征。
  • 必须定期收集系统统计信息;这不会使所缓存的计划失效。
  • 收集系统统计信息等同于分析一个指定期间内的系统活动:
  • 过程:

–DBMS_STATS.GATHER_SYSTEM_STATS

–DBMS_STATS.SET_SYSTEM_STATS

–DBMS_STATS.GET_SYSTEM_STATS

  • GATHERING_MODE:

–NOWORKLOAD|INTERVAL

–START|STOP

 

使用系统统计信息,优化程序可以考虑系统的 I/O 和 CPU 性能以及利用情况。针对每个候选计划,优化程序计算 I/O 和 CPU 成本的估计值。要选择最高效的、I/O 和 CPU 成本达到最佳比例的计划,需要了解系统特征,这一点很重要。系统 CPU 和 I/O 特征并不总是保持不变,它们与许多因素相关。使用系统统计信息管理例程,可以捕获系统在最常见工作量下运行时一定时间间隔内的统计信息。例如,数据库应用程序可以在白天处理联机事务处理 (OLTP) 事务,在晚上运行 OLAP 报表。这两种状态的统计信息您都可以收集,并且可以根据需要激活适当的 OLTP 或 OLAP 统计信息。这样优化程序便可以针对可用的系统资源计划生成相关的成本。系统生成系统统计信息时,会分析指定期间内的系统活动。与表、索引或列统计信息不同,系统统计信息更新后,系统不会使已分析的 SQL 语句失效。所有新的 SQL 语句都使用新的统计信息进行解析。

我们强烈建议您收集系统统计信息。可以使用 DBMS_STATS.GATHER_SYSTEM_STATS 例程在用户定义的时间段内收集系统统计信息。也可以使用 DBMS_STATS.SET_SYSTEM_STATS 显式设置系统统计信息值。可使用 DBMS_STATS.GET_SYSTEM_STATS 验证系统统计信息。

 

使用 GATHER_SYSTEM_STATS 过程时,应指定 GATHERING_MODE 参数:

  • NOWORKLOAD:这是默认设置。此模式可捕获 I/O 系统的特征。收集统计信息可能会花费几分钟的时间,具体取决于数据库大小。在此期间系统会估算 I/O 系统的平均读取寻道时间和传输速度。此模式适用于所有工作量。建议您在创建数据库和表空间后运行 GATHER_SYSTEM_STATS (‘noworkload’)。
  • INTERVAL:捕获指定间隔内的系统活动。此参数与指定捕获时间量的 interval 参数组合在一起发挥作用。您应提供一个以分钟为单位的间隔值,之后系统会在字典或登台表中创建或更新系统统计信息。可以使用 GATHER_SYSTEM_STATS (gathering_mode=>’STOP’) 提前停止收集信息。
  • START | STOP:获取指定开始时间和停止时间之间的系统活动,用该过去时间段内的统计信息刷新字典或登台表。

注:自 Oracle Database 10gR2 开始,系统会在启动时自动收集重要的系统统计信息。

 

收集系统统计信息:示例

 

gather_statistics1

 

幻灯片中的示例显示了在白天处理 OLTP 事务、在晚上运行报表的数据库应用程序。

首先,必须在白天收集系统统计信息。在本例中,收集过程在 120 分钟后结束,统计信息存储在 mystats 表中。

然后在晚上收集系统统计信息。收集过程在 120 分钟后结束,统计信息存储在 mystats 表中。

通常,收集系统统计信息时使用幻灯片中的语法。在调用指定了 INTERVAL 参数的 GATHER_SYSTEM_STATS 过程之前,必须使用 SQL> alter system set job_queue_processes = 1; 之类的命令激活作业进程。也可以调用具有不同参数的相同过程来启用手动收集,而不使用作业。

适当的时候,可以在收集的统计信息之间进行切换。请注意,可以通过提交一个作业用正确的统计信息更新字典,使此过程自动化。在白天,可使用一个作业导入 OLTP 统计信息用于白天的运行,在晚上,可使用另一个作业导入联机分析处理 (OLAP) 统计信息用于晚上的运行。

 

 

  • 手动在数据字典中启动系统统计信息收集:

SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( –

  2  gathering_mode => ‘START’);

 

  • 生成工作量。
  • 结束收集系统统计信息:

SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( –

  2  gathering_mode => ‘STOP’);

上一幻灯片中的示例显示了如何通过 DBMS_STATS.GATHER_SYSTEM_STATS 过程的内部参数使用作业收集系统统计信息。要手动收集系统统计信息,可以使用此过程的另一个参数,如幻灯片中所示。

首先,必须启动系统统计信息的收集过程,在确定实例中已生成了具有代表性的工作量后,可以随时结束收集过程。

本示例直接在数据字典中收集系统统计信息。

 

 

用于收集统计信息的机制

  • 自动统计信息收集

–gather_stats_prog 自动化任务

  • 手动统计信息收集

–DBMS_STATS 程序包

  • 动态采样
  • 缺少统计信息时:

gather_stats_prog

 

Oracle DB 提供了几种机制来收集统计信息。这些机制将在后面的幻灯片中详细讲述。建议您为对象使用自动统计信息收集功能。

注:当系统发现表缺少统计信息时,它会动态收集优化程序所需要的必需统计信息。但是,对于某些类型的表,它不会执行动态采样,其中包括远程表和外部表。在这些情况下以及动态采样被禁用时,优化程序将使用默认的统计信息值。

 

统计信息首选项:概览

自动统计信息收集功能是在 Oracle Database 10gR1 中引入的,用于减轻优化程序统计信息的维护工作。但是,在有些情况下,必须禁用该功能,并运行自己的脚本。其中的一个原因是缺少对象级别的控制。只要发现一小部分对象的默认收集统计信息选项的效果不佳,就必须锁定统计信息,并使用您自己的选项单独对其进行分析。例如,如果列包含频率偏差很大的数据,则尝试针对这些列自动确定合适样本大小的功能 (ESTIMATE_PERCENT=AUTO_SAMPLE_SIZE) 不会有很好的效果。解决此问题的唯一方法就是用自己的脚本手动指定样本大小。

Oracle Database 11g 中的统计信息首选项功能具有一定的灵活性,因此,在有些对象需要不同于数据库默认设置的设置时,也可以较灵活地使用自动统计信息收集功能来维护优化程序统计信息。

通过此功能,您可以在对象级别或方案级别,将覆盖 GATHER_*_STATS 过程的默认行为的统计信息收集选项与自动优化程序统计信息收集任务关联起来。可以使用 DBMS_STATS 程序包管理幻灯片中显示的收集统计信息选项。

 

gather_statistics_profile

 

可以在表级、方案级、数据库级和全局级设置、获取、删除、导出和导入这些首选项。全局首选项用于没有首选项的表,而数据库首选项则用于设置针对所有表的首选项。以各种方式指定的首选项值的优先顺序为从外圈到内圈(如幻灯片中所示)。

在幻灯片的图形中,最后三个突出显示的选项是 Oracle Database 11gR1 中新增的选项:

  • CASCADE 还收集索引统计信息。索引统计信息收集不是一个并行化操作。
  • ESTIMATE_PERCENT 是用于计算统计信息的行的估计百分比(空值代表所有行):有效范围是 [0.000001,100]。使用常量 DBMS_STATS.AUTO_SAMPLE_SIZE 可让系统自己决定适当的采样范围以获得准确的统计信息。这是推荐的默认值。
  • NO_INVALIDATE 可能会使也可能不会使从属游标失效。如果将其设置为 TRUE,则不会使从属游标失效。如果将其设置为 FALSE,则此过程会立即使从属游标失效。使用 DBMS_STATS.AUTO_INVALIDATE 可让系统自己决定何时使从属游标失效。这是默认设置。
  • PUBLISH 用于确定是将统计信息发布到字典还是先将其存储在临时等待区中。
  • STALE_PERCENT 用于确定阈值级别,达到该级别时将认为对象具有过时统计信息。该值是上次收集统计信息以来修改过的行数的百分比。示例仅将 SH.SALES 的值从默认值 10% 更改为 13%。
  • DEGREE 决定用于计算统计信息的并行度。并行度的默认值为空值,这表示使用由 CREATE TABLE 或 ALTER TABLE 语句的 DEGREE 子句指定的表默认值。使用常量 DBMS_STATS.DEFAULT_DEGREE 将基于初始化参数指定默认值。AUTO_DEGREE 值可自动决定并行度。此值可能是 1(串行执行),也可能是 DEFAULT_DEGREE(基于 CPU 数量和初始化参数的系统默认值),具体取决于对象大小。
  • METHOD_OPT 是一个 SQL 字符串,用于收集直方图统计信息。默认值为 FOR ALL COLUMNS SIZE AUTO。
  • GRANULARITY 是用于为分区表收集统计信息的粒度。
  • INCREMENTAL 用于以增量方式收集分区表中的全局统计信息。

请注意,您可以使用 DBMS_STATS.SET_PARAM 过程更改上述参数的默认值,这一点很重要。

注:可以使用 DBA_TAB_STAT_PREFS 视图说明所有相关表的全部有效统计信息首选项设置。

 

何时手动收集统计信息

  • 主要依赖自动统计信息收集:

–更改自动统计信息收集频率来满足您的需要。

–请记住,应将 STATISTICS_LEVEL 设置为 TYPICAL 或 ALL,自动统计信息收集功能才能正常运行。

  • 对于下列对象,请手动收集统计信息:

–易失对象

–在批处理操作中修改的对象

–外部表、系统统计信息、修复的对象

–在批处理操作中修改的对象:在批处理操作过程中收集统计信息。

–新建对象:在对象创建之后收集统计信息。

 

自动统计信息收集机制可使所有统计信息保持为最新。确定新统计信息的收集时间和收集频率非常重要。默认的收集间隔是晚上,但是您可以更改此间隔以适应您的业务需要。可以通过更改维护窗口的特征来达到此目的。但是,在某些情况下可能需要手动收集统计信息。例如,如果白天对表进行了大幅修改,则表的统计信息可能会过时。这样的对象一般有两种类型:

  • 在白天受到大幅修改的易失表
  • 经过大型批量装载的对象,在两个统计信息收集间隔之间该对象的总大小增加了 10% 或更多

对于外部表,系统不会在 GATHER_SCHEMA_STATS、GATHER_DATABASE_STATS 和自动优化程序统计信息收集处理期间收集其统计信息。但是,您可以使用 GATHER_TABLE_STATS 收集单个外部表的统计信息。不支持对外部表进行采样,因此应显式将 ESTIMATE_PERCENT 选项设置为空值。因为不允许对外部表执行数据处理,所以只在相应文件发生更改时分析外部表就足够了。需要手动收集其中统计信息的其它区域是系统统计信息和修复的对象,如动态性能表。系统不会自动收集这些统计信息。

 

手动统计信息收集 

可以使用 Oracle Enterprise Manager 和 DBMS_STATS 程序包来完成以下任务:

  • 生成并管理统计信息,以供优化程序使用:

–收集/修改

–查看/命名

–导出/导入

–删除/锁定

  • 收集下列项的统计信息:

–索引、表、列、分区

–对象、方案或数据库

  • 串行或并行收集统计信息
  • 收集/设置系统统计信息(当前在 EM 中尚无法实现)

使用 Oracle Enterprise Manager 和 DBMS_STATS 程序包,都可以为优化程序手动生成和管理统计信息。可以使用 DBMS_STATS 程序包收集、修改、查看、导出、导入、锁定和删除统计信息。还可以使用此程序包标识(或命名)收集的统计信息。您可以按多种粒度收集索引、表、列和分区的统计信息:对象、方案和数据库级别。

DBMS_STATS 仅收集优化所需的统计信息,而不收集其它统计信息。例如,DBMS_STATS 收集的表统计信息包括行数、当前包含数据的块数量和平均行长度,但不包括链接行数、平均空闲空间或未使用的数据块数量。

注:不要使用 ANALYZE 语句的 COMPUTE 和 ESTIMATE 子句收集优化程序统计信息。仅仅是为了实现向后兼容才支持这些子句,在以后的版本中可能会将其删除。DBMS_STATS 程序包可收集更广泛、更准确的统计信息集,并且收集效率更高。对于与优化程序统计信息收集无关的其它用途,您可以继续使用 ANALYZE 语句:

  • 使用 VALIDATE 或 LIST CHAINED ROWS 子句
  • 收集空闲列表块的信息

 

手动统计信息收集:因素

  • 监控 DML 的对象。
  • 确定正确的样本大小。
  • 确定并行度。
  • 确定是否应使用直方图。
  • 确定索引的级联影响。
  • 在 DBMS_STATS 中使用的过程:

–GATHER_INDEX_STATS

–GATHER_TABLE_STATS

–GATHER_SCHEMA_STATS

–GATHER_DICTIONARY_STATS

–GATHER_DATABASE_STATS

–GATHER_SYSTEM_STATS

 

手动收集优化程序统计信息时,必须特别注意下列因素:

  • 监控成批数据操纵语言 (DML) 操作的对象,必要时收集统计信息
  • 确定正确的样本大小
  • 确定并行度以加快对大型对象的查询速度
  • 确定是否对包含有偏差数据的列创建直方图
  • 确定对象的更改是否级联到任何从属索引

 

管理统计信息收集:示例

 

 

dbms_stats.gather_table_stats 
('sh'              -- schema 
,'customers'       -- table 
, null             -- partition 
, 20               -- sample size(%) 
, false            -- block sample? 
,'for all columns' -- column spec 
, 4                -- degree of parallelism 
,'default'       -- granularity  
, true );          -- cascade to indexes 


dbms_stats.set_param('CASCADE',                  'DBMS_STATS.AUTO_CASCADE'); 
dbms_stats.set_param('ESTIMATE_PERCENT','5'); 
dbms_stats.set_param('DEGREE','NULL');  


第一个示例使用 DBMS_STATS 程序包收集 SH 方案的 CUSTOMERS 表的统计信息。它使用前面幻灯片中讨论的某些选项。

设置参数默认值

可以使用 DBMS_STATS 中的 SET_PARAM 过程为所有 DBMS_STATS 过程的参数设置默认值。幻灯片中的第二个示例演示了这种用法。也可以使用 GET_PARAM 函数获取参数的当前默认值。

注:只有表是分区表时,才需要考虑要收集的统计信息的粒度。此参数决定应在哪一级别收集统计信息。级别可以是分区、子分区或表。

 

优化程序动态采样:概览

 

  • 对于符合以下特征的表和索引,可以执行动态采样:

–没有统计信息

–其统计信息不可信

  • 在评估时用于确定更准确的统计信息:

–表基数

–谓词选择性

  • 此功能由以下项控制:

–OPTIMIZER_DYNAMIC_SAMPLING 参数

–OPTIMIZER_FEATURES_ENABLE 参数

–DYNAMIC_SAMPLING 提示

–DYNAMIC_SAMPLING_EST_CDN 提示

 

动态采样可确定更准确的选择性和基数估计值,这样优化程序便可生成更有效的执行计划,因而提高了服务器性能。例如,虽然建议您收集所有表的统计信息以供 CBO 使用,但您可能不会收集临时表和用于临时数据处理的工作表的统计信息。在此类情况下,CBO 通过一个简单算法提供一个值,这可能会导致不太理想的执行计划。可以使用动态采样完成下列任务:

  • 当收集到的统计信息无法使用或可能会导致重大估计错误时,估计单表的谓词选择性
  • 为没有统计信息的表和相关索引,或者其统计信息太旧已不再可信的表估计表基数

可以使用 OPTIMIZER_DYNAMIC_SAMPLING 初始化参数控制动态采样。可使用 DYNAMIC_SAMPLING 和 DYNAMIC_SAMPLING_EST_CDN 提示进一步控制动态采样。

注:如果设置为 9.2 之前的版本,OPTIMIZER_FEATURES_ENABLE 初始化参数将关闭动态采样。

 

优化程序动态采样的工作方式

  • 采样在编译时完成。
  • 如果查询受益于动态采样:

–执行递归 SQL 语句对数据进行采样

–被采样的块数量取决于 OPTIMIZER_DYNAMIC_SAMPLING 初始化参数

  • 在动态采样期间,将对样本应用谓词,以确定选择性。
  • 在以下情况下使用动态采样:

–采样时间只占执行时间的一小部分

–查询被执行多次

–您认为会找到更好的计划

 

主要的性能属性是编译时间。系统在编译时将确定查询是否会从动态采样受益。如果能从动态采样受益,则发出递归 SQL 语句扫描表块的一个小型随机样本,并应用相关的单个表谓词估计谓词选择性。

动态采样查询会读取一定数量的块,具体数量取决于 OPTIMIZER_DYNAMIC_SAMPLING 初始化参数的值。

对于通常可快速完成的查询(在不到几秒钟的时间内即可完成),您不需要费力地去执行动态采样。但是,动态采样在下列任一条件下是有益的:

  • 使用动态采样可找到更好的计划。
  • 采样时间只占查询总执行时间的一小部分。
  • 查询被执行多次。

注:动态采样可以应用于单个表的谓词子集,并可与未执行动态采样的谓词标准选择性估计值组合在一起。

 

OPTIMIZER_DYNAMIC_SAMPLING

 

  • 动态会话或系统参数
  • 可以设置为 0 到 10 之间的值
  • 值为 0 时会关闭动态采样
  • 值为 1 时对所有未分析的表进行采样,前提是未分析的表满足下列条件:

–联接至另一个表,或出现在子查询或不可合并的视图中

–没有索引

–包含的块超过 32 个

  • 值为 2 时对所有未分析的表进行采样
  • 值越高,就越积极地应用采样
  • 如果没有更新活动,动态采样可重复进行

可通过将 OPTIMIZER_DYNAMIC_SAMPLING 参数设置为 0 到 10 之间的值来控制动态采样。值为 0 时表示不执行动态采样。

值为 1(默认值)时表示对所有满足下列条件的未分析的表执行动态采样:

  • 查询中至少有一个未分析的表。
  • 此未分析的表联接至另一个表,或出现在子查询或不可合并的视图中。
  • 此未分析的表没有索引。
  • 此未分析的表包含的块数多于对此表进行动态采样可使用的默认块数。此默认数量
    是 32。

如果 OPTIMIZER_FEATURES_ENABLE 设置为 10.0.0 或更高,则默认值为 2。在此级别,系统会对所有未分析的表应用动态采样。采样的块数量是动态采样的默认块数量 (32) 的两倍。

提高该参数值会导致更积极地应用动态采样,这表现在采样的表类型(分析或未分析)以及在采样上花费的 I/O 量这两方面。

注:如果自上次采样操作后没有在进行采样的表中插入、删除或更新任何行,则动态采样可重复进行。

 

锁定统计信息

 

  • 防止自动收集
  • 主要用于易失表:

–不带统计信息锁定意味着要进行动态抽样。

BEGIN 

  DBMS_STATS.DELETE_TABLE_STATS(‘OE’,’ORDERS’); 

  DBMS_STATS.LOCK_TABLE_STATS(‘OE’,’ORDERS’);

END;

 

 

–带统计信息锁定可获得典型值。

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(‘OE’,’ORDERS’);
  DBMS_STATS.LOCK_TABLE_STATS(‘OE’,’ORDERS’);
END;

  • FORCE 参数可覆盖统计信息锁定。

SELECT stattype_locked FROM dba_tab_statistics;

 

自 Oracle Database 10g 开始,您可以使用 DBMS_STATS 程序包的 LOCK_TABLE_STATS 过程锁定指定表的统计信息。可以锁定没有统计信息的表的统计信息,或使用 DELETE_*_STATS 过程将其设置为 NULL,以防止自动收集统计信息,这样便可以对没有统计信息的易失表使用动态采样。易失表填满时,可以锁定该表的统计信息,这样该表的统计信息就会成为具有代表性的表填充。

也可以通过使用 LOCK_SCHEMA_STATS 过程在方案级别锁定统计信息。可以查询 {USER | ALL | DBA}_TAB_STATISTICS 视图中的 STATTYPE_LOCKED 列,以确定表的统计信息是否已锁定。

可以使用 UNLOCK_TABLE_STATS 过程取消对指定表统计信息的锁定。

即使统计信息处于锁定状态,也可以将 FORCE 参数的值设置为 TRUE 来覆盖它们。FORCE 参数在下列 DBMS_STATS 过程中:DELETE_*_STATS、IMPORT_*_STATS、RESTORE_*_STATS 和 SET_*_STATS。

注:锁定表的统计信息后,所有从属统计信息都被认为处于锁定状态。这包括表统计信息、列统计信息、直方图和从属索引统计信息。

 

 

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569