oracle - oracle 解析器如何处理序列

Question

为什么 oracle 总是解析下面的查询？

select MY_SEQUENCE_SEQ.nextval
from dual

来自 Quest SQL Optimizer (8.6.0) 的 SGA 统计数据:

Executions : 83630

Parse_calls: 83630

序列详细信息:

• 最后缓存值:1
• 增加:1
• 缓存大小:20
• 周期:否
• 订单:否

测试场景:

1. 创建音序器:

   CREATE SEQUENCE MY_SEQUENCE_SEQ
     START WITH 1
     MAXVALUE 999999999999999999999999999
     MINVALUE 1
     NOCYCLE
     CACHE 20
     NOORDER;
   

2. 对有权访问 v$sql View 的用户执行此查询。

   select executions, 
          parse_calls 
     from v$sql 
    where sql_text like 'select MY_SEQ%';`
   

3. 执行n次序列查询

4. 从点 2 开始执行查询。

得到的结果:

EXECUTIONS  - n
PARSE_CALSS - n

测试于:

数据库:Oracle 数据库 10g 版本 10.2.0.4.0 - 64 位生产

客户端:Toad 版本 11.5.1.2

Answer 1

这不是 Oracle 的错误，这只是 TOAD 将 SQL 发送到 oracle 的方式。即toad 不会将语句句柄缓存到oracle，它只是在完成时将其关闭。

当查询发送到 SQL 引擎时，Oracle 将对查询执行三个主要操作之一。

1. 硬解析
2. 软解析
3. 不解析

即我们希望处于情况 3 而我们当然不希望处于情况 1!那么每种情况会在什么时候发生？

当 SQL 根本不在共享池中或 SQL 在共享池中但使用的绑定(bind)变量/文字意味着当前 SQL 不可用时，将发生硬解析。例如，假设我们发出此 SQL 三次 select MY_SEQUENCE_SEQ.nextval from dual。这将在 Oracle 第一次看到此 SQL 并将其放入共享池时进行硬解析，并将在第 2 次和第 3 次调用时进行软解析。我们可以很容易地看到这种情况发生:

SQL> select n.name, s.value from v$mystat s, v$statname n where n.statistic# = s.statistic# and n.name in ('parse count (hard)', 'parse count (total)');

NAME                      VALUE
-------------------- ----------
parse count (total)         522
parse count (hard)          287

SQL> select /* test1 */ MY_SEQUENCE_SEQ.nextval
  2  from dual;

   NEXTVAL
----------
        62

SQL> select /* test1 */ MY_SEQUENCE_SEQ.nextval
  2  from dual;

   NEXTVAL
----------
        63

SQL> select /* test1 */ MY_SEQUENCE_SEQ.nextval
  2  from dual;

   NEXTVAL
----------
        64

SQL> select n.name, s.value from v$mystat s, v$statname n where n.statistic# = s.statistic# and n.name in ('parse count (hard)', 'parse count (total)');

NAME                      VALUE
-------------------- ----------
parse count (total)         526
parse count (hard)          288

SQL> select sql_text, executions, parse_calls from v$sql where sql_text like 'select /* test1 */%';

SQL_TEXT                       EXECUTIONS PARSE_CALLS
------------------------------ ---------- -----------
select /* test1 */ MY_SEQUENCE          3           3
_SEQ.nextval from dual

硬解析加一，sql 注册了 3 次解析，所以 1 次硬解析(将其放入共享池)和 2 次软解析。

为什么要软解析？为了“不解析”发生​​，客户端代码必须保留语句句柄并重新执行它。也就是说，如果我们用 Java 编写这个，我们会这样写:

    public static int getNextSeq(String str)
  throws Exception 
    {
        if (sel == null)
      {
        sel = con.prepareStatement("select MY_SEQUENCE_SEQ.nextval v from dual "+str);
      }
    ResultSet rs = sel.executeQuery();
    int seqVal=0;
    while (rs.next()) 
    {
      seqVal = rs.getInt("V");
    }
    return seqVal;
    }

即如果我们还没有这样做，我们只会调用 PrepareStatement。如果我们用

执行这段代码

System.out.println(getNextSeq(args[0]));
System.out.println(getNextSeq(args[0]));
System.out.println(getNextSeq(args[0]));

我们可以在实际中看到这一点:

SQL> host java Prep two
70
71
72

SQL> select sql_text, executions, parse_calls from v$sql where sql_text like 'select %two';

SQL_TEXT                       EXECUTIONS PARSE_CALLS
------------------------------ ---------- -----------
select MY_SEQUENCE_SEQ.nextval          3           1
 v from dual two

现在除了 1 次硬解析外，oracle 没有解析 SQL。如果 Java 代码写得不好，我们会看到:

sel = con.prepareStatement("select MY_SEQUENCE_SEQ.nextval v from dual "+str);
ResultSet rs = sel.executeQuery();


SQL> host java Prep three
73
74
75

SQL> select sql_text, executions, parse_calls from v$sql where sql_text like 'select %three';

SQL_TEXT                       EXECUTIONS PARSE_CALLS
------------------------------ ---------- -----------
select MY_SEQUENCE_SEQ.nextval          3           3
 v from dual three

现在我们看到解析计数 = 执行。换句话说，我们正在软解析每个不理想的调用。同样不是 Oracle 限制，只是客户端实现不佳。

有了 PL/SQL，我们就不必担心这个了。为什么？ PL/SQL 不解析，因为它为运行 SQL 做了很多优化(不出所料!)。例如:

SQL> declare
  2    v_seq number;
  3  begin
  4    for idx in 1..3 loop
  5      select MY_SEQUENCE_SEQ.nextval into v_seq from dual pls_test;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select sql_text, executions, parse_calls from v$sql where sql_text like 'SELECT %PLS_TEST';

SQL_TEXT                       EXECUTIONS PARSE_CALLS
------------------------------ ---------- -----------
SELECT MY_SEQUENCE_SEQ.NEXTVAL          3           1
 FROM DUAL PLS_TEST

现在，pl/sql 为我们进行这种优化时有一个警告，那就是参数 SESSION_CACHED_CURSORS。在给定的 session 中，Oracle 将为我们保持打开一组游标(即它们是软打开的，也就是说，如果我们需要更多游标，它将关闭它们)。所以如果我们有 SESSION_CACHED_CURSORS=0 并重复上面的测试，我们会看到软解析突然出现:

SQL> alter session set session_cached_cursors=0;

Session altered.

SQL> declare
  2    v_seq number;
  3  begin
  4    for idx in 1..3 loop
  5      select MY_SEQUENCE_SEQ.nextval into v_seq from dual pls_test2;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select sql_text, executions, parse_calls from v$sql where sql_text like 'SELECT %PLS_TEST2';

SQL_TEXT                       EXECUTIONS PARSE_CALLS
------------------------------ ---------- -----------
SELECT MY_SEQUENCE_SEQ.NEXTVAL          3           3
 FROM DUAL PLS_TEST2

显然，缓存游标的值越高，我们就越有机会避免软解析并达到完全避免解析的 chalice 。