oracle父游标和子游标,Oracle 性能优化 之 游标及 SQL

SQL_TEXT：SQL 语句的文本

SQL_FULLTEXT：SQL 语句的完全文本

SQL_ID

SHARABLE_MEM：游标所占共享内存

PERSISTENT_MEM：游标持续期所占用的 Fixed(固定)内存

RUNTIME_MEM：游标在运行期所占用的 Fixed(固定)内存

SORTS：游标完成的排序次数

LOADED_VERSIONS：游标在库缓存所占的内存堆是否被加载

OPEN_VERSIONS：游标是否被锁定。

USERS_OPENING：打开游标的会话数。也就是当正在缓存游标到 PGA 中的会话数。游标被执行三次后，就会被缓存到 PGA 中。此数值就加 1。

FETCHES：抓取的次数

EXECUTIONS：执行次数

PX_SERVERS_EXECUTIONS：以并行方式执行的总次数

END_OF_FETCH_COUNT：抓取全部行的次数

USERS_EXECUTING：当前正在执行此游标的会话数

LOADS：游标被加载或重新加载到库缓存中的次数。游标只所以被重新加载有可能是游标无效或库缓存内存不足。

FIRST_LOAD_TIME：游标被第一次被加载的时间。也就是生成执行计划的时间

INVALIDATIONS：游标的无效次数

PARSE_CALLS：游标的解析次数，包括硬解析与软解析

DISK_READS：游标执行了多少次物理读

DIRECT_WRITES：游标直接写的次数

BUFFER_GETS：逻辑读的次数

APPLICATION_WAIT_TIME：应用程序的等待时间，单位微秒

CONCURRENCY_WAIT_TIME：并行的等待时间，单位微秒

CLUSTER_WAIT_TIME：Cluster 等待时间

USER_IO_WAIT_TIME：用户 I/O 等待时间

PLSQL_EXEC_TIME：PL/SQL 执行时间

JAVA_EXEC_TIME：Java 执行时间

ROWS_PROCESSED：游标一共抓取了多少行。同样的行，每抓取一次此列都会增加

COMMAND_TYPE：命令类型

OPTIMIZER_MODE：优化器模式

OPTIMIZER_COST：执行计划的成本

OPTIMIZER_ENV：执行时的环境

OPTIMIZER_ENV_HASH_VALUE：环境的 HASH 值

PARSING_USER_ID：最先解析此游标的用户的 ID

PARSING_SCHEMA_ID：最先解析此游标的方案 ID

PARSING_SCHEMA_NAME：最先解析此游标的方案 ID

KEPT_VERSIONS：是否使用 DBMS_SHARED_POOL 包将游标 Pin 到库缓存中

ADDRESS：父游标句柄的地址

TYPE_CHK_HEAP：

HASH_VALUE：游标的 HASH 值

OLD_HASH_VALUE：老 HASH 值

PLAN_HASH_VALUE：执行计划的 HASH 值。(上述三个 HASH 值并不相同)

CHILD_NUMBER：子游标数量

SERVICE：

SERVICE_HASH

MODULE：第一次解析游标的应用程序名。可以在应用程序中通过调用 DBMS_APPLICATION_INFO.SET_MODULE 设置。

MODULE_HASH：应用程序名的 HASH 值

ACTION：第一次解析时的动作名。可以在应用程序中通过调用 DBMS_APPLICATION_INFO.SET_ACTION 设置。

ACTION_HASH：动作名的 HASh 值

SERIALIZABLE_ABORTS：每个游标产生 ORA-08177 errors 错误(事务串行化无效)的次数。

OUTLINE_CATEGORY：大纲类型

CPU_TIME：游标解析、执行、抓取时所用的 CPU 时间。单位是微秒。

ELAPSED_TIME：游标解析、执行、抓取时所用的总时间。单位是微秒。

OUTLINE_SID：大纲会话的 SID

CHILD_ADDRESS：游标本身的地址

SQLTYPE：游标所用的 SQL 语言的版本

REMOTE：游标是否是远端映像的

OBJECT_STATUS：对象状态

LITERAL_HASH_VALUE：游标文本的 HASH 值

LAST_LOAD_TIME：执行计划最后一次被加载到库缓存中的时间。

IS_OBSOLETE：当子游标太多时，此子游标是否被荒废。

CHILD_LATCH：保护游标的子闩编号

SQL_PROFILE：SQL 的概要文件

PROGRAM_ID：过程 ID

PROGRAM_LINE#

EXACT_MATCHING_SIGNATURE

FORCE_MATCHING_SIGNATURE

LAST_ACTIVE_TIME：最后一次使用执行计划的时间。

BIND_DATA：绑定变量的信息

这个视图中 DISK_READS、BUFFER_GETS、CPU_TIME、ELAPSED_TIME 这四个列在调优 SQL 语句时最为重要。在数据库系统的速度不是太另人满意时，如果你已经确定过了，不是其他方面的原因，而是 SQL 语句性能的问题，只是无法确定是那条、或那些条语句拖慢了整体的速度。那么此时选择调优物理读、逻辑读最多的，或最耗 CPU 时间的 SQL 语句进行调节，往往可以取得今人满意的性能增长。

我们也可以以 EXECUTIONS(执行次数)最多的 SQL 语句为调优对象。另外，PARSE_CALLS 是解析次数，对于此列值最多的 SQL 语句，我们可以看看是否可以降低语句的解析次数。

关于 SQL 调优，和程序的调优是一样的。如果我们从事过代码优化这样的工作，就会知道，对于一个大型的应用程序来说调优的方法也是要从执行次数最多的那部分代码、或从最消耗资源的代码入手。

还有一个问题，就是文档中关于这个视图会经常提到一个概念：子游标与父游标。如果两个游标的文本一模一样，但由于环境不同，比如，游标所操作的表是不同用户下的同名表，这两个游标是不能共享执行计划的。它们都有各自的执行计划存在库缓存中。这两个游标就是子游标，Oracle 还会建立一个父游标，父游标中没有执行计划，它只是文本相同但执行计划不同的所有游标的代表。

其实在库缓存中，即使没有文本相同的子游标，Oracle 会为每个游标都创建父游标。因为父游标是文本相同的子游标的代表吗，所有文本相同的游标共享同一个父游标。

也就是说，只要你执行 SQL 语句，Oracle 都会在库缓存中保存一父一子两个游标。如果你执行了文本相同但环境不同因而不能共享执行计划的 SQL 语句，那么一个父游标可能就对应多个子游标。

父游标没有执行计划，它只有一信息管理性数据，Oracle 添加它的目的就是为了管理文本相同的游标。有一个视图是专门针对父游标的，就是 V$sqlarea。下面我们说一下这个视图。

2. V$SQLAREA

V$SQLAREA 和 V$SQL 的列几乎是一模一样的。在 V$SQLAREA 中汇总了子游标的数据。如果有两个语句：语句 A 和语句 B，它们文本一模一样，但是由于环境不同没有共享执行计划，而是有各自的执行计划。也就是语句 A 和语句 B 是同一父游标下的子游标。在 V$SQL 视图中，因为它是显示子游标的，所以语句 A 和语句 B 各占一行，假设语句 A 的 DISK_READS(物理读)是 100，语句 B 的物理读是 3000。V$SQLAREA 是显示父游标信息的，语句 A 和语句 B 因为文本相同，它们两个对应同一个父游标，在 V$SQLAREA 中占一行。在 V$SQLAREA 中，语句 A 和语句 B 父游标行中的 DISK_READS 就是 3100，也就是语句 A 和语句 B 的和。V$SQLAREA 中的其它列也是如此，都是 V$SQL 中相应子游标的合计。

有一个列是 V$SQL 中没有的，就是：VERSION_COUNT，它是对应同一父游标的子游标的数量。如果这个数字太高，可能代表由于某些原因使本可以共享执行计划的游标没有共享。

3．V$open_cursor 与 Open_cursor 参数

这个视图和参数涉及游标的打开。什么是游标的打开，就是在库缓存中，用户在软、硬解析游标时，会在游标对象的句柄上加一个锁，也就是 Library cache lock。在解析并执行完游标后，这个锁并不会马上去掉，而是会一直保留着，直到用户发出了 Close 命令关闭游标时为止。我们在 SQL*Plus 命令窗口中发出的命令，在抓取完所有行后，SQL*Plus 将自动为我们发出 Close 命令来关闭游标。

当游标打开时，Library cache lock 将一直保持，这样，即使库缓存内存紧张，需要老化对象，也不会老化这些还正在加锁的对象。因此，如果用户不停的要求数据库服务器打开游标、执行 SQL，但却忘了关闭游标，这很容易耗尽共享池的内存。为此，Oracle 准备了一个参数，就是 Open_cursor，它的默认值在 9i 下是 50，在 10g 中是 300，也就是说，在 10g 下，每个会话最多只能同时打开 300 个游标。有了这个限制，就不用害怕用户不停的打开游标但又不关闭它，而耗尽共享池内存了。

如果会话同时打开的游标数量超出了 Open_cursor 参数的限制，Oracle 将禁止会话打开新的游标。同时报出错误：ORA-01000: 超出打开游标的最大数 。

在用户断开会话的连接后，会话打开的这些游标将自动关闭。

V$open_cursor 视图专用来查看当前会话打开的游标信息。它只能查看当前会话打开的游标。

4．CURSOR_SHARING 参数

如果应用程序中有很多类似下面这样的 SQL 语句：

select * from 某表 where id=1;

select * from 某表 where id=2;

select * from 某表 where id=50;

等等，这些 SQL 语句严格来说是无法共享游标(也就是共享执行计划)的，但是这些语句所需要执行计划其实都是一样的。无论你在表中查询 ID 为 1 的行还是查询 ID 为 100 的行，执行方式应该是一样的。如果你想让这样的语句共享游标，那么，你可以改变 Cursor_sharing 参数的值。

此参有三个值：

Ÿ EXACT：这个值是默认值。除非游标文本一模一样，否则不会共享游标。

Ÿ SIMILAR：这个最智能，如果游标只有条件中的数据值部分不同，并且库缓存中原有游标的执行计划对于新执行的 SQL 语句也是最优的，将不再为 SQL 语句创建新的游标，而是让它共享库缓存中原有的游标。

Ÿ FORCE ：不比较执行计划是否最优，只要游标中除了条件中的数据值部分不同外，其他部分都相同，就会共享游标。

此参数可以在会话级修改，也就是可以使用 Alter session 修改它的值，这将只影响某一个会话，而不会影响其他会话。