sql解析中参数MAXOPENCURSORS, HOLD_CURSOR, and RELEASE_CURSOR 的解释

简介: WHERE clause predicates are sent as string literals. If you use precompilers to develop the application, then make sure to reset t...
WHERE clause predicates are sent as string literals. If you use precompilers to develop the application, then make sure to reset the parameters MAXOPENCURSORS, 

HOLD_CURSOR, and RELEASE_CURSOR from the default values before precompiling the application


我们说的游标概念比较复杂,它可以是客户端程序中的游标,服务进程中的私有游标,以及服务器端共享池里的共享游标。假设一个游标被打开了,一般来说它的共享游标信息(包括执行计划,优化树等)总是会在SQL AREA里,无需再次软/硬解析。

参数文件中的连个游标相关的值:

SQL> show parameter CURSORS

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors     integer300
session_cached_cursors     integer50


open_cursors:指明每个session最多能够打开的cursors的数量。

SESSION_CACHED_CURSORS:是Oracle中的一个初始化参数(修改必须重启实例),指定了每个会话缓存的游标上限(保留在PGA中);客户端程序中open cursor的要求仍会被传递给服务进程,服务进程首先扫描自身缓存的游标信息,如果命中则可以避免软解析,也有人称它为“软软解析”。

如果Open_cursors设置太小,对系统性能不会有明显改善,还可能触发ORA-O1000:m~imum open CUrsOrs exceeded.的错误。如果设置太大,则无端消耗系统内存。我们可以通过如下的sql语句查看你的设置是否合理

SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR  
      FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P  
     WHERE A.STATISTIC# = B.STATISTIC#  
       AND B.NAME = 'opened cursors current'  
       AND P.NAME = 'open_cursors'  
     GROUP BY P.VALUE;  

HIGHEST_ OPEN CUR是实际打开的cursors 的最大值,MAX_OPEN_ CUR是参数Open_cursors的设定值,如果二者太接近,甚至触发eRA一01000错误,那么你就应该调大参数Open_cursors的设定值。



HOLD_CURSOR:是预编译程序中的一个参数,它指定了私有游标是否应该该被缓存,这里不做展开。

在分析工具tkprof中hard parse与soft parse被同等对待,都被认为是parse;软解析即会造成parse总数上升。

软解析避免了硬解析过程中的几个步骤,但仍包括了初始化的语法,语义解析并计算语句HASH值与SQL AREA中已有语句进行对比;若匹配则查询优化等昂贵的操作得以避免。

另请注意,10053事件仅在硬解析过程中被触发。

What are the Oracle Precompiler options HOLD_CURSOR and RELEASE_CURSOR and how do they affect the performance of a PCC program?

First of all, these options apply to implicit and explicit cursors in all precompiled languages except Pro*Ada. They apply ONLY to implicit cursors associated with INSERT, UPDATE, DELETE, or a single row SELECT in Pro*Ada. An explicit cursor in a program is one explicitly created with EXEC SQL DECLARE C1 CURSOR FOR...

This bulletin discusses what these options do internally and how changing them affects program performance.

HOLD_CURSOR is a precompiler command line parameter.

Summary Operation:
~~~~~~~~~~~~~~~~~~




NB: Both the soft and hard parse register as a parse in tkprof. If the cursor is open and it is not in the SQL_AREA then it clearly has to parse it (not shown in the diagram!)


The HOLD_CURSOR and RELEASE_CURSOR Options
------------------------------------------
.
Following will be a discussion of what these options do internally and how changing them affects program performance.

What exactly do we mean by CURSOR? Unfortunately, we mean two different things:

1. The program cursor - A data structure associated with a SQL statement.

A program cursor is declared for each SQL statement that the  precompiler finds in your program. For the statements

EXEC SQL DECLARE SEL_EMP_CURS CURSOR FOR...
EXEC SQL INSERT...

PCC will declare two program cursors, say c1 and c2.
2. The Oracle cursor (also called the context area) - The workarea created dynamically at run time; 

this area contains the  parsed statement, the addresses of the host variables, and  other information necessary to execute the SQL statement.

These two cursors are linked together via the cursor cache. The initial size of the cursor cache is determined by the MAXOPENCURSORS option.

The following diagram illustrates the relationship described above after an insert and an update have been executed in your program:



How are the HOLD_CURSOR and RELEASE_CURSOR options related to this view?

The HOLD_CURSOR option deals with the link between the program cursor and its cache entry.

The RELEASE_CURSOR option deals with the link between the Oracle cursor and the cache entry.

For SQL statements that are FREQUENTLY RE-EXECUTED, the bottom line is this: if you want to maximize performance, make sure these SQL statements stay "glued" to their respective Oracle cursor.

What does it mean when a SQL statement is "glued" to its Oracle cursor? 

It means that both links between the SQL statement and its Oracle cursor are made permanent.

Why would you want to keep a statement "glued" to its context area?
Because the context area contains the parsed statement and other information necessary to execute the statement, such as the addresses of the host variables. Maintaining access to this information makes subsequent execution of the statement much faster.

How do you "glue" a statement to a cache entry? 

By correct use of the  HOLD_CURSOR and RELEASE_CURSOR options via the PCC command line or inline with EXEC ORACLE OPTION(...).

For instance, with HOLD_CURSOR=YES as the Oracle option, a cache entry cannot be flagged for reuse. This has important implications. If all cache entries have been used up and a new cache entry is needed for a new SQL statement such that the number of cache entries would now exceed the number specified in MAXOPENCURSORS, Oracle will use the first cache entry marked reuseable.

For example, in the above diagram, if the cache entry C(1) is marked reusable, and the program is about to execute the EXEC SQL SELECT...
(program cursor P(MAXOPENCURSORS+1), and the number of cache entries in use already equals MAXOPENCURSORS, cache entry C(1) and its Oracle
cursor will now be linked to the select statement. A subsequent execution of the insert statement would require pre-empting a cache entry and its Oracle cursor from another SQL statement and performing a reparse.

Correspondingly(相应的), with the default RELEASE_CURSOR=NO as the Oracle option, the link between the cache entry and the Oracle cursor (the con-
text area) is maintained after the statement is executed so that the  parsed statement and, more importantly, the allocated memory stay available.

The freeing up of this memory by RELEASE_CURSOR=YES means that the  next statement that gets linked to this cache entry will require an  expensive reallocation of memory in addition to a reparse. Ugh! Why  would anybody want RELEASE_CURSOR=YES? We will see later on.

Program cursor - - - - - [ Cursor cache entry ] - - - - - Oracle for SQL statement cursor
                         ^                                            ^
HOLD_CURSOR=YES                     RELEASE_CURSOR=NO
program cursor is permanently           cache entry maintains the
linked to its cache entry.                   address of its context area.

So the HOLD_CURSOR option is intimately tied to the MAXOPENCURSORS option. What exactly is the MAXOPENCURSORS option? First of all, MAX-
OPENCURSORS is a misnomer(用词不当). It should more appropriately be called INITIAL_CURSOR_CACHE_SIZE. (Okay, so it's a mouthful.) Anyway, if
all cursor cache entries are currently marked "not reusable" either because of the HOLD_CURSOR option or because the associated statement is currently being executed (an explicitly opened cursor is still being fetched on and hasn't been closed yet), then a request for a new cursor will actually result in the extension of the cursor cache at runtime (i.e. if MAXOPENCURSORS=10, and all 10 entries are active,then an 11th will be created). 

Just letting the precompiler reuse the oldest cache entry won't always work, as the following example illustrates: Imagine the case where the user has ten explicitly declared  cursors opened, and wants to execute an eleventh. If the program actually reuses the oldest program cursor, the user would lose his current position in the first cursor and would not be able to fetch from it anymore.

By the way, if an eleventh cache entry is created, when that cursor is closed the eleventh entry is not removed. Setting MAXOPENCURSORS low saves memory, but causes potentially expensive dynamic allocations of  new cache entries if they're needed. Setting it high assures quick  execution, but may use more memory than necessary.

What if a statement is not executed repeatedly in a program? 

Then you could go with the other options HOLD_CURSOR=NO and RELEASE_CURSOR=YES.   With the HOLD_CURSOR=NO option, the link between a program cursor and  its cache entry is not permanent. The cache entry is automatically  marked reusable in case it is needed. With the RELEASE_CURSOR=YES option, the Oracle cursor (the context area) is automatically freed and the parsed statement lost. A reason you might use this option is if  you are limited by the number of Oracle cursors (MAXOPENCURSORS) at your site due to memory issues. You may want to incur the cost of reallocating memory and reparsing in order to manage memory more effectively.

An advantage of setting RELEASE_CURSOR=YES is that until the link be-
tween the cache entry and the Oracle cursor (context area) is removed,
ORACLE keeps parse locks on any tables referenced in the SQL state-
ment. These parse locks prevent other users and you from ALTERing or
DROPping the tables (does ORA-0057 sound familiar?). Also, in Version
5, it will free up the read-consistent image of the referenced tables
stored in ORACLE's Before Image file.

What do we mean when we say that RELEASE_CURSOR=YES takes precedence
over HOLD_CURSOR=YES? With RELEASE_CURSOR=YES, the link between the
Oracle cursor and the cache entry is cut and the Oracle cursor is
freed (closed), so even if your program cursor is permanently linked
to the cache entry because HOLD_CURSOR=YES, you will still have to re-
allocate memory and reparse the statement. So subsequent executions
of a statement don't benefit from the HOLD_CURSOR=YES option because
RELEASE_CURSOR=YES.

For programmers experienced with OCI, here's the OCI equivalent of 
what's happening: 
 
#define MAXOPENCURSORS 5 
 
char     *sql_stmts[10];  
curs_def cursor[MAXOPENCURSORS]; 
 
oopen(cursor[0],...); 
osql3(cursor[0],...,sql_stmts[0],...);  
 
An example of a "cache entry" being linked to another SQL statement 
later on in the program is as follows: 
 
osql3(cursor[0],...,sql_stmts[5],...);  
 
I am forced to reuse one of my "cache entries" to execute the sixth 
SQL statement. 
 
An example of a context area being freed is:  
                 
oclose(cursor[0]);      
 
Reusing cursor[0] would require another oopen() and another osql3()-- 
another dynamic allocation of memory and another reparse. 
 
 
Conclusion 
---------- 
 
As a programmer, you will get the most from these options by using 
them selectively inline rather than specifying them as options at pre- 
compile time.

Reference:

Pro*C/C++ Precompiler Programmer's Guide

相关文章
|
12天前
|
SQL 数据可视化 关系型数据库
MCP与PolarDB集成技术分析:降低SQL门槛与简化数据可视化流程的机制解析
阿里云PolarDB与MCP协议融合,打造“自然语言即分析”的新范式。通过云原生数据库与标准化AI接口协同,实现零代码、分钟级从数据到可视化洞察,打破技术壁垒,提升分析效率99%,推动企业数据能力普惠化。
87 3
|
5月前
|
SQL 安全 关系型数据库
SQL注入之万能密码:原理、实践与防御全解析
本文深入解析了“万能密码”攻击的运行机制及其危险性,通过实例展示了SQL注入的基本原理与变种形式。文章还提供了企业级防御方案,包括参数化查询、输入验证、权限控制及WAF规则配置等深度防御策略。同时,探讨了二阶注入和布尔盲注等新型攻击方式,并给出开发者自查清单。最后强调安全防护需持续改进,无绝对安全,建议使用成熟ORM框架并定期审计。技术内容仅供学习参考,严禁非法用途。
837 0
|
4月前
|
SQL 存储 自然语言处理
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
|
8月前
|
SQL Java 数据库连接
如何在 Java 代码中使用 JSqlParser 解析复杂的 SQL 语句?
大家好,我是 V 哥。JSqlParser 是一个用于解析 SQL 语句的 Java 库,可将 SQL 解析为 Java 对象树,支持多种 SQL 类型(如 `SELECT`、`INSERT` 等)。它适用于 SQL 分析、修改、生成和验证等场景。通过 Maven 或 Gradle 安装后,可以方便地在 Java 代码中使用。
2652 11
|
9月前
|
JSON 自然语言处理 Java
OpenAI API深度解析:参数、Token、计费与多种调用方式
随着人工智能技术的飞速发展,OpenAI API已成为许多开发者和企业的得力助手。本文将深入探讨OpenAI API的参数、Token、计费方式,以及如何通过Rest API(以Postman为例)、Java API调用、工具调用等方式实现与OpenAI的交互,并特别关注调用具有视觉功能的GPT-4o使用本地图片的功能。此外,本文还将介绍JSON模式、可重现输出的seed机制、使用代码统计Token数量、开发控制台循环聊天,以及基于最大Token数量的消息列表限制和会话长度管理的控制台循环聊天。
3282 7
|
9月前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
10月前
|
SQL Java 数据库连接
canal-starter 监听解析 storeValue 不一样,同样的sql 一个在mybatis执行 一个在数据库操作,导致解析不出正确对象
canal-starter 监听解析 storeValue 不一样,同样的sql 一个在mybatis执行 一个在数据库操作,导致解析不出正确对象
|
10月前
|
SQL IDE 数据库连接
IntelliJ IDEA处理大文件SQL:性能优势解析
在数据库开发和管理工作中,执行大型SQL文件是一个常见的任务。传统的数据库管理工具如Navicat在处理大型SQL文件时可能会遇到性能瓶颈。而IntelliJ IDEA,作为一个强大的集成开发环境,提供了一些高级功能,使其在执行大文件SQL时表现出色。本文将探讨IntelliJ IDEA在处理大文件SQL时的性能优势,并与Navicat进行比较。
163 4
|
10月前
|
SQL 监控 安全
员工上网行为监控软件:SQL 在数据查询监控中的应用解析
在数字化办公环境中,员工上网行为监控软件对企业网络安全和管理至关重要。通过 SQL 查询和分析数据库中的数据,企业可以精准了解员工的上网行为,包括基础查询、复杂条件查询、数据统计与分析等,从而提高网络管理和安全防护的效率。
162 0
|
11月前
|
SQL 数据可视化 BI
SQL语句及查询结果解析:技巧与方法
在数据库管理和数据分析中,SQL语句扮演着至关重要的角色
1552 0

推荐镜像

更多
  • DNS