OraGlance是免费的，你可以安心使用它。

OraGlance is free software , use it as you like.

下载地址URL：https://zcdn.askmac.cn/OraGlance2103.zip

2021-03-04:  现在可以 针对任意SQL_ID查看SQL Detail了

OraGlance的目标

• 完全免费
• 一键即运行的图形化Oracle数据库性能监控软件
• 无需安装、部署，不占用服务器资源
• 支持从Oracle 11.2.0.1开始的所有版本
• 支持Oracle RAC
• 非植入式，对Oracle只读，不在数据库内创建任何对象
• 资源占用极低，内存使用在50MB左右，CPU占用率极低
• 可回溯历史性能数据，可以观察到过往时间的性能问题
• 提供SQL历史运行情况历史
• 提供SQL优化接口
• 无需外网访问权限，纯本地程序，不上传任何数据到任何服务器

产品比较

OraGlance的使用技巧

• 主面板每10秒钟自动刷新一次，可以通过左侧面板的Update够选项，临时关闭面板刷新；关闭面板刷新后仍会每10秒钟更新后台数据
• “<< ONE Minute”按钮可以让主面板数据回溯到之前的时间，回溯后主面板将停止更新，直到使用”backup to current”回到当前时间
• 可以在左侧时间栏中指定时间，并按下”go to above time”按钮来回溯到过去某个时间点；如果对应时间点不可用则会报错
• 支持托盘运行，可以监控桌面上后台运行，不打扰用户
• 例如18：00用户反应出现大量应用程序等待，则可以回溯到该时间点来观察TOP SQL和阻塞情况。

同时支持多开程序，可以在同一台pc上多开监控多个数据库：

SQL优化接口

• 在主面板上点击View Detail按钮，可以进入对应SQL的优化界面
• 优化界面中显示了SQL的历史运行情况，包括逻辑读、物理读、运行时间，执行计划HASH等
• 点击Run Advisor按钮可以对该SQL执行SQL Tuning Task优化作业
• 之后点击View Result可以查看优化建议结果，以下为可能的几种结果：

–建议重写该SQL

–建议收集对应的统计信息

–给出SQL Profile以改善执行计划

–建议添加对应的索引

–无建议

OraGlance是一个轻量级的图形化Oracle性能监控工具。由诗檀软件开发，它致力于提供简单有效的几个指标来监控Oracle数据库，而不提供过多的指标。

它是绿色的，非植入式的；你可以直接运行它，而基本不需要做任何准备工作。

不需要在Oracle数据库内创建任何对象，其只需要以下几个查询权限:

OraGlance is a lightweight oracle performance monitor tool , developed by parnassusdata.com . It will only focus on most important metric .

you can easily run it without any prerequisite. It will ask for below permission:

supports oracle version:  11gR2 12c

grant create session to pd1;
grant select on gv_$active_session_history to pd1;
grant select on gv_$SQL to pd1;
grant select on gv_$SQL_MONITOR to pd1;
grant select on gv_$SQLSTATS to pd1;
grant select on v_$database to pd1;
grant select on gv_$instance to pd1;
grant select on gv_$statname  to pd1;
grant select on gv_$sysstat  to pd1;
grant select on gv_$osstat  to pd1;
grant select on gv_$dlm_misc  to pd1;
grant select on gv_$session_blockers to pd1;
grant select on dba_hist_sqltext to pd1;
grant select on dba_hist_sqlstat to pd1;
grant select on dba_hist_snapshot to pd1;
grant select on dba_advisor_sqlstats to pd1;
grant select on dba_sqlset_statements to pd1;


如果要运行SQL Tuning Advisor 则需要授予dba权限

grant dba to pd1;

update log:

适配了12c，增加了登录界面保存，增加了托盘功能，优化了界面， 为sqlite中的表增加了索引。

build 2020-07-01         :  https://zcdn.askmac.cn/OraGlance200701.zip

2020-07-08:

1. 现在oraglance支持最老的版本是11.2.0.1了 ，
2. 现在支持对top sql的历史执行情况查看了
3. 可以在查看SQL情况页面 调用sql tuning advisor 优化SQL语句 并给出建议了

build 2020-07-08:              https://zcdn.parnassusdata.com/OraGlance200708.zip

2020-07-09：

现在登陆密码会以密文形式存放了

适用于:

Oracle Database – Enterprise Edition – 版本 10.1.0.2 和更高版本
本文档所含信息适用于所有平台

用途

SQL Profiles 是10g 引入的新特性，它通过 DBMS_SQLTUNE 包来管理，您也可以通过 Oracle Enterprise Manager 的自动 SQL 调优流程的功能来管理。这个 FAQ 回答关于 SQL profile 基本的问题，并且提供了一个使用 DBMS_SQLTUNE 包的例子。

提问，获得帮助，并分享您对于这篇文档的经验。

您是否希望与其他 Oracle 客户、Oracle 员工和业内专家进一步探讨此主题？

请点击这里 进入 Oracle 社区（中文）.
请点击这里进入My Oracle Support 社区的数据库安装/升级（英文）主页发现更多的话题和讨论。

问题和答案

什么是自动 SQL 调优？

有时候，因为缺乏相关的统计信息，会导致 SQL 优化器不能产生精准的评估，导致产生了差的执行计划。
一般的办法是用户不得不以手工在程序代码里面添加一些 hint 来指导优化器产生好的执行计划。对于已经封装的应用程序来说，修改代码是不可行的，唯一的办法是给应用开发商提 bug 并等其修改。

自动 SQL 调优正好是为这种场景而设计的。自动调优优化器（Automatic Tuning Optimizer）可以为 SQL 文本创建一个 Profile，叫SQL profile，它是由和 sql 语句相关的辅助统计信息所组成。在正常模式下的查询优化器在评估数据量（cardinality），选择率（selectivity）和成本（cost）时，可能因为缺乏足够数量的信息而导致不准，SQL profile 可以通过收集附加的信息，如采样、部分执行等技术来验证评估是否准确，必要时会调整优化器的评估。

在 SQL Profile 工作时，自动调优优化器（Automatic Tuning Optimizer）也会使用 SQL 语句的历史执行信息来设置合适的优化器参数，例如把 OPTIMIZER_MODE 从ALL_ROWS 改成 FIRST_ROWS。

此类型的分析输出通常是推荐您接受这个 SQL Profile。

对于一个 SQL Profile，一旦被接受，就会持续的存在数据字典中。SQL Profile 是针对某个特定 SQL 有效的，如果被接受，工作在正常模式下的优化器就会连同 SQL Profile 中的信息和其他正常统计信息一起来生成执行计划。这些可用的附加信息使得针对某个特定 SQL 产生更优的执行计划成为可能，而这无需修改任何应用程序代码。

下面的文档提供更多关于 SQL 调优顾问（SQL Tuning Advisor）的信息。

什么是 SQL Profile?

SQL Profile 是一些信息的集合体，存储在数据字典中，使得 SQL 优化器可以为 SQL 语句产生最优的执行计划。SQL Profile 包含自动 SQL 调优期间发现的对产生错误执行计划的评估的纠正信息。这些信息能改善优化器的数据量（cardinality），选择率（selectivity）评估，而这些信息会指引优化器找到更好的执行计划。

一个 SQL profile 不包含某个特定执行计划的相关联信息，相反，优化器当选择执行计划的时候参考如下信息数据：

• 环境信息，包括数据库配置，绑定变量值，统计信息，数据集等等
• 存储在 SQL profile 中的补充信息

需要提醒的是，SQL Profile 不会像存储纲要（stored outlines）那样固定一个 SQL 的执行计划,当表的数据量增长或者有索引创建或删除时，使用了 SQL Profile 的执行计划仍会及时调整，也就是说，当数据分布或访问路径变化后，存储在 SQL Profile 中的相关信息仍然被参考。当然，经过相当长时间后，它的内容可能变得陈旧过时，需要重新生成。您可以通过再次运行自动 SQL 调优（Automatic SQL Tuning）来重新生成这个 SQL 的 Profile。

SQL Profile 的范围如何控制?

SQL Profile 的范围可以通过 CATEGORY 属性来控制，这个属性决定了哪个 sessoin 可以使用这个 profile，您可以通过查看 DBA_SQL_PROFILES 视图的 CATEGORY 字段来了解相关信息。

默认情况下，所有的 profiles 都被指定为 DEFAULT category，这就意味着所有 session 当初始化参数 SQLTUNE_CATEGORY 设置 DEFAULT 的时候都可以用这个 profile。

通过修改 SQL profile 的 category 属性，你可以指定哪个 session 受 profile 影响。例如，设置 category 为 DEV，那么 session 级设置了初始化参数 SQLTUNE_CATEGORY 为 DEV 的那些 session 可以使用这个 profile，其他 session 则不能使用这个 SQL profile，执行计划当然也不会受这个 SQL profile 影响。这个特性可以让您用来在某个限制环境上提前测试某个 SQL profile 而避免影响其他 session。

SQL Profile 适用什么语句？?

SELECT 语句
UPDATE 语句
INSERT 语句（必须包含select语句）
DELETE 语句
CREATE TABLE 语句（必须包含 AS SELECT 子句）
MERGE 语句（update 或者 insert 操作）

如何管理 SQL Profile？

SQL Profiles 可以通过 Oracle 企业管理器（Enterprise Manager）的自动 SQL 调优流程部分来管理它，或者通过 DBMS_SQLTUNE 包。

使用企业管理器：

1. 在 Performance 页面, 点击 Top Activity。
   Top Activity 页面出现。
2. 在 Top SQL 下, 点击使用 SQL profile 的 SQL 语句的 SQL ID 链接。
   SQL Details 页面显示出来。
3. 点击 Plan Control 栏。
   SQL Profiles 和 Outlines 部分会显示 SQL Profile 的列表。
4. 选择想要管理的 SQL profile。
   执行下面的任意操作 one of the following：
   • 要启用一个当前被禁用的 SQL profile，点击 Disable/Enable。
   • 要禁用一个当前被启用的 SQL profile，点击 Disable/Enable。
   • 要删除一个 SQL profile，点击 Delete.
5. Confirmation 页面显示出来。
   点击 Yes 来确认，或者 No 取消刚才的操作。

使用 DBMS_SQLTUNE 包：

若是用 SQL Profiles APIs，您需要有 CREATE ANY SQL_PROFILE，DROP ANY SQL_PROFILE，ALTER ANY SQL_PROFILE 等系统权限。

接受 SQL Profile

使用 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 存储过程来接受一个 SQL Tuning Advisor 推荐的 SQL Profile。这会创建 SQL Profile 并存储在数据字典中。

my_sql_tuning_task 是一个 SQL 调优任务的名字，您可以通过 DBA_SQL_PROFILES 来查看一个 SQL Profile 的相关信息。

修改 SQL Profile

您可以通过 ALTER_SQL_PROFILE 来修改一个已知 SQL Profile 的状态，名字，描述信息和 CATEGORY 属性。

这个例子中，my_sql_profile 是您想要修改的 SQL Profile 名字。
状态被设置成 disabled，意思是 SQL Profile 将不会被 SQL 使用。

删除 SQL Profile

可以通过 DROP_SQL_PROFILE 来删除 SQL Profile。

样例：

SESSION 1 — SCOTT

创建表，插入数据，创建索引并收集统计信息。
执行带有 no_index hint 的 SQL，全表扫描将会被使用：

 
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)

SESSION 2 — SYS

创建并执行调优任务并查询它的输出结果。
然后接受一个推荐的 SQL Profile

Output:

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_2
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 09/24/2012 12:36:44
Completed at       : 09/24/2012 12:36:49

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 90.95%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .001004           .000331      67.03 %
  CPU Time (s):                    .001                 0        100 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       22                 2       90.9 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)

2- Using SQL Profile
--------------------
Plan hash value: 1416057887

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

-------------------------------------------------------------------------------

SESSION 1 — SCOTT

再次运行 SQL
可以看到即便是有 no_index 暗示，索引仍然被使用了
注意：执行计划里面可以看到使用了 my_sql_profile

Execution Plan                                                                
-------------------------------------------------------------------------     
Plan hash value: 1416057887                                                   
                                                                              
----------------------------------------------------------------------------- 
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 | 
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 | 
----------------------------------------------------------------------------- 
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   1 - access("N"=1)                                                          
---------------                                                               
Note                                                                          
-----                                                                         
   - SQL profile "my_sql_profile" used for this statement                     

如何获取每个调优集的报告？

Oracle数据库性能诊断课程.pdf

本文永久链接地址：https://www.askmac.cn/archives/oracle-11g-ocm-spa.html

5 SQL性能分析

5.1 目标

在完成这个课程后，你应该能够完成下列事情：

• 确定使用SQL 性能分析器的好处
• 描述SQL性能分析器工作流程
• 使用SQL 性能分区器在数据库变更后确定性能

5.2 性能变化时DBA面临的挑战

• 通过改变硬件或软件的配置来维护服务级别协议（SLA）
• 提供生产级别的工作负载环境来达到测试的目的
• 有效的预测和分析SQL性能的影响

大的关键业务应用是复杂的，并且具有高度变化的负载和使用模式。在同一时刻，这些业务系统被希望提供确定的服务，保障响应时间，吞吐量，正常运行时间和可用性。任何系统的改变（例如数据库升级或者修改了配置），在这些更改之前，经常需要进行广泛的测试和验证，以使其成为生产系统。为了有信心移动到生产，数据库管理员（DBA）必须以生产环境中的经验，公开测试系统工作负荷的工作量。它也拥有利于DBA以一个更有效的方式来分析SQL性能对整体系统水平变化的影响，这样在生产之前可以进行任何所需的调整更改。

[Read more…]

不建议把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存放在SAS和SSD上的性能对比图（越短越好），可以看到当并发较高时SSD甚至比普通SAS要差：

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

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

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

下载地址：https://zcdn.askmac.cn/可视化Oracle调优与智能优化-2015oracle数据库大会版.pdf

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' );

 

• 配置 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（顶级服务）”选项卡式将显示数据库中定义的服务的性能有关信息。在此页中，您可以在服务级别启用或禁用跟踪。

服务跟踪：示例

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

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

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

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 中的所有信息。可以使用 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 命令的输出

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

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，您可以使用索引监控来识别和删除任何未使用的索引。

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

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

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

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

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

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

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

Curosr Sharing 游标共享和绑定变量

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

在幻灯片的示例中，绑定变量名为 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 的“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 更改了此行为。

绑定变量扫视

在某些情况下，绑定变量扫视会导致优化程序选择不太理想的计划。之所以发生这种情况，是因为在为查询的所有后续执行确定计划时使用的都是绑定变量的第一个值。所以，即使后续执行提供不同的绑定值，也使用同一计划。对于具有不同绑定变量值的执行，也许其它计划更合适。例如，当一个特定索引的选择性随列值有非常大的变化时。对于较低的选择性，全表扫描可能更快。对于较高的选择性，索引范围扫描可能更适合。如幻灯片所示，计划 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_… 视图），并确定是否将游标标记为能识别绑定的游标。

2. 下一次对此查询进行软分析时，如果游标已是能够识别绑定的，则会使用能识别绑定的游标匹配。假设具有新的一组绑定值的谓词选择性现在是 (0.18,0.003)。由于选择性用作能识别绑定的游标匹配的一部分，并且该选择性在现有立方体内，因此该语句使用现有子游标的执行计划运行。
3. 下一次对此查询进行软分析时，假设具有一组新绑定值的谓词选择性现在是 (0.3,0.009)。由于该选择性不在现有立方体内，所以找不到子游标匹配项。因此，系统会执行硬分析，在本例中生成了一个具有另一个执行计划的新子游标。此外，新选择性立方体将存储为该新子游标的一部分。执行该新子游标后，系统会将绑定值和执行统计信息存储在该游标中。
4. 下一次对此查询进行软分析时，假设具有一组新绑定值的谓词选择性现在是 (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 性能优化》课程或《面向管理员的新增功能》课程。