老熊的三分地-Oracle、UNIX、数据恢复

客户新上线的一套重要生产系统，某个存储过程每小时调用约11万次，每次调用的逻辑读超过了10000，消耗的CPU占数据库CPU Time的25-30%。很显然，这样一个存储过程是值得优化的。

不幸的是，这个存储过程的业务逻辑很复杂，光是参数就有9个。而存储过程所在的包其代码超过了1万行。通过复查代码的方式，耗时耗力，还不一定能够找出问题。

诊断性能问题，首要的是了解这个存储过程所大概要完成的业务功能，然后通过trace或instrument收集足够详细的性能数据。从客户和开发商那里了解到，存在性能问题的存储过程，主要用于系统之间的数据查询接口，根据不同的参数输入查询不同的数据，那么，对于这种小数据量的存储过程，可以考虑使用10046事件来分析是哪些SQL产生了这么多的逻辑读。

但是通过10046事件，发现存储过程中实际执行的SQL并不多，同时并没有逻辑读高的SQL语句。也许问题并没有出现在SQL语句中，而是出现在存储过程中其他非SQL部分。虽然用10046没有找到SQL语句，但还是有重大发现，在过程执行时，大量的逻辑读来自于current方式的读，这显示不是通常的SELECT语句所产生的。接下来我们用dbms_profiler来分析存储过程：

select dbms_profiler.start_profiler from dual;
exec intf.CRM_SERVICE_INTF.QueryService(.....);
exec dbms_profiler.stop_profiler;

然后使用来自MOS文档“Implementing and Using the PL/SQL Profiler [ID 243755.1]”中的profiler.sql脚本，生成一个profiler的结果文件，格式为html。下面是部分的内容：

点击其显示的代码行(line)，跳转到相应的源代码，发现大量的字符串拼接代码，很明显是用于拼接成XML格式。这很容易理解，因为现在系统之间的文本数据交互，xml几乎成了标准。从上面的截图中也可以看到很多类型于xml:=xml || ‘xxxx’ 这样的代码。是这样的代码引起的问题吗？

检查代码发现，这里用于拼接字符串的变量xml，被定义为clob类型，这引起了我极大的关注。在oracle的标准数据类型中，lob类型由于其能够存储大数据的本质，导致其内部格式和操作是最复杂的。有理由怀疑是clob的大量拼接引起的问题。

在有怀疑对象后，我们可以构造下面的测试来进行验证：

首先创建下面3个不同的存储过程，但是实现的功能是一致的：

create or replace procedure p1 ( v_out out clob)
is
  v_lob clob;
begin
  v_lob:='';
  for rec in (select object_name from dba_objects where rownum< =1000) loop
      v_lob:=v_lob || rec.object_name;
  end loop;
  v_out:=v_lob;
end;
/

      
create or replace procedure p2 ( v_out out clob)
is
  v_lob varchar2(32767);
begin
  v_lob:='';
  for rec in (select object_name from dba_objects where rownum<=1000) loop
      v_lob:=v_lob || rec.object_name;
  end loop;
  v_out:=v_lob;
end;
/

create or replace procedure p3 ( v_out out clob)
is
  v_lob clob;
  v_str varchar2(32767);
  v_cnt number;
begin
  v_lob:='';
  v_cnt:=0;
  v_str:='';
  for rec in (select object_name from dba_objects where rownum<=1000) loop
        v_str:=v_str || rec.object_name;
        v_cnt:=v_cnt+1;
        if v_cnt = 50 then
           v_cnt:=0;
           v_lob:=v_lob || v_str;
           v_str:='';
        end if;   
  end loop;
  if v_cnt <>0 then
     v_lob:=v_lob || v_str;
  end if;   
  v_out:=v_lob;
end;
/

Read the rest of this entry »

提起Oracle数据库的Hint，几乎每一个DBA都知道这一强大工具。在Oracle中，Hint可以用来改变SQL的执行计划、固定SQL的执行计划。Oracle数据库内部的很多特性也依赖于Hint，比如Outline、Profile等。

但是在日常工作中，很多开发人员或DBA，对Hint的使用仍然存在一些错误的方式。下面将列举主要的2种。（本文不讨论Hint的滥用即过度使用问题）。

1. NOLOGGING的不正确使用。

很多人知道，在进行数据处理时，如果不产生日志或只产生少量的日志，将会有明显的、甚至是巨大的效率提升。下面有几条不同的SQL：

INSERT INTO T1 NOLOGGING;
INSERT INTO T1 SELECT * FROM T2 NOLOGGING;
INSERT /*+ NOLOGGING */ INTO T1 VALUES ('0');
INSERT /*+ NOLOGGING */ INTO T1 SELECT * FROM T2;
DELETE /*+ NOLOGGING */ FROM T1;
UPDATE /*+ NOLOGGING */ T1 SET A='1';

实际上，上述所有的SQL没有一个能够实现“不产生”日志的数据更改操作。第1-2条SQL语句虽然没有将NOLOGGING写为Hint的形式，但是也是很多人的错误写法，一并列在此处。事实上，NOLOGGING并不是Oracle的一个有效的Hint，而是一个SQL关键字，通常用于DDL语句中。这里NOLOGGING相当于给SELECT的表指定了一个别名为“NOLOGGING”。下面是NOLOGGING的一些正确用法：

CREATE TABLE T1 NOLOGGING AS SELECT * FROM T2;
CREATE INDEX T1_IDX ON T1(A) NOLOGGING;
ALTER INDEX T1_IDX REDUILD ONLINE NOLOGGING;
ALTER TABLE T1 NOLOGGING;

上述SQL中，最后一条SQL只是将表的LOGGING属性改为”NO”。而之前的几条SQL能够有效地减少DDL操作时减少的日志量。

在DML操作中，只有下面一种方式能够在大数据量时仍然只会产生极少量的日志：

INSERT /*+ APPEND */ INTO T1 SELECT * FROM T2;

也就是使用append hint。但是这个hint要达到目的，需要以下几个条件：

• 使用INSERT /*+ APPEND */ INTO .. SELECT .. FROM形式的INSERT SQL。
• 如果是在归档模式下，需要将表的LOGGING属性置为NO。
• 表空间或数据库的FORCE LOGGING属性为NO。注意在非归档模式下也是可以设置FORCE LOGGING的。

这里提到的insert语句中的append hint，对于索引，仍然会产生日志，也就是说append hint对索引是没有效果的。
另外，DDL中使用的nologging关键字和inset语句中使用的append hint，并不是说完全不产生日志，只是对表的数据块的数据部分的更改不会有日志产生，但是SQL执行过程中数据字典的更改、空间分配等递归SQL、段头和位图块的更改、将数据块标记为unrecoverable等仍然会产生少量日志。

2. Hint的不正确写法。

这是一个比较不容易发现的问题。下面几条SQL，哪一条SQL的append hint会生效：

1. INSERT /*+ append,parallel(t1) */ INTO T1 SELECT * FROM T2;
2. INSERT /*+ parallel(t1), append */ INTO T1 SELECT * FROM T2;
3. INSERT /*+ this is append */ INTO T1 SELECT * FROM T2;
4. INSERT /*+ this append */ INTO T1 SELECT * FROM T2;

要回答这个问题，请先看下面的测试（测试环境：10.2.0.1 for Windows）：

SQL> INSERT /*+ append,parallel(t1) */ INTO T1 SELECT * FROM T2;
已创建55640行。
统计信息
----------------------------------------------------------
      12304  redo size
SQL> COMMIT;

SQL> INSERT /*+ parallel(t1), append */ INTO T1 SELECT * FROM T2;
已创建55640行。
统计信息
----------------------------------------------------------
    5739584  redo size
SQL> COMMIT;

SQL> INSERT /*+ this is append */ INTO T1 SELECT * FROM T2;
已创建55640行。
统计信息
----------------------------------------------------------
    5746604  redo size
SQL> COMMIT;

SQL> INSERT /*+ this append */ INTO T1 SELECT * FROM T2;
已创建55640行。
统计信息
----------------------------------------------------------
      12052  redo size
SQL> COMMIT;

从上面的输出可以看到，通过insert语句执行产生的redo size判断，4条SQL语句中，1和4这2条SQL中的append hint起了作用，而2和3这2条SQL中的append hint没有起作用。我们看看第1和第2条SQL，只不过是parallel和append换了个位置，结果就截然不同；而第3和第4条SQL，只是一个多了”is”这个词，另一个没有，其结果也完全不同。这里有什么玄机吗？

这里就需要了解Oracle在解析SQL时，是怎样解析hint的。
Oracle在解析hint，从左到右进行，如果遇到一个词是oracle关键字或者说是保留字，将忽略这个词以及之后的所有词。如果遇到的一个词即不是关键字也不是hint，就忽略该词。如果遇到的一个词是有效的hint，那么就会保留该hint。

Oracle的保留字或者说是关键词（虽然二者在意义不一样，但这里不将其区分），可以通过视图v$reserved_words来查询。”is”正是一个关键词，甚至连”,”（逗号）也是一个关键词。这样，上面的第2和第3条SQL，Oracle解析时当遇到”,”和”is”时，就忽略了后面的所有hint。在第4条SQL中，this并不是一个关键词，所以append hint有效。基于这个原理，下面的一条SQL中的hint也是不起作用的：

INSERT /*+ NOLOGGING APPEND */ INTO T1 SELECT * FROM T2;

在9.2.0.8和11.2.0.2这2个版本下进行同样的测试，结果完全一样。
为了避免这样的情况，在SQL中书写hint时，在/*+ */和--+这2种结构内只写hint，而不要写逗号，或者是其他的注释。如果要对SQL写注释，在专门的注释结构中写入。比如/* test comment */。如果与hint混写注释，虽然当时没有关键词在里面，但随着版本升级，很可能会加入新的关键词。

另外，一些很常见的hint形式，比如/*+ parallel(t,8) */，/*+ index(t,t_idx) */，虽然当前没有问题，但标准的写法应该是：
/*+ parallel(t 8) */，/*+ index(t t_idx) */

--end end.

在上一篇《SQL Profiles-Part I》，我向大家介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。那么在今天，将向大家介绍如何手工创建SQL Profiles（即不通过SQL Tuning Advisor)来达成2个目的：

• 锁定或者说稳定SQL执行计划。
• 在不能修改应用的SQL的情况下，来改变或者说是强制使SQL使用我们指定的执行计划，即使原始的SQL包含了Hints。

那么，这里最关键的一点是，如何来手工创建SQL Profiles?
答案是，正如上一篇中有朋友的留言，使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程。

SQL> desc dbms_sqltune
...
PROCEDURE IMPORT_SQL_PROFILE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 PROFILE                        SQLPROF_ATTR            IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT
 REPLACE                        BOOLEAN                 IN     DEFAULT
 FORCE_MATCH                    BOOLEAN                 IN     DEFAULT
...

这个过程其名字与实际功能有所差异，其实可以理解为CREATE OR REPLACE SQL_PROFILE。过程中的PROFILE参数为SYS.SQLPROF_ATTR，这种类型其实就是VARCHAR2的集合类型(COLLECTION)：

SQL> select text from dba_source where name='SQLPROF_ATTR' and owner='SYS';

TYPE     sqlprof_attr
 AS VARRAY(2000) of VARCHAR2(500)

下面我们就用这个过程来创建SQL PROFILE：
为避免干扰，将上一篇测试中生成的SQL Profile删除掉，同时恢复T1表的统计信息中的表行数：

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>49953);

PL/SQL 过程已成功完成。

现在我们手工创建一个SQL Profile：

SQL> declare
  2    v_hints sys.sqlprof_attr;
  3  begin
  4    v_hints:=sys.sqlprof_attr('USE_NL(T1 T2)','INDEX(T2)');
  5    dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
  6                v_hints,'SQLPROFILE_NAME1',force_match=>true);
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b
  2  where a.signature = b.signature
  3  and a.name='SQLPROFILE_NAME1';

ATTR_VAL
----------------------------------------
USE_NL(T1 T2)
INDEX(T2)

下面执行SQL Profiles对应的SQL：

SQL> select  t1.*,t2.owner
  2       from t1,t2
  3       where t1.object_name like '%T1%'
  4       and t1.object_id=t2.object_id;

已选择29行。

执行计划
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2498 | 99920 |   219   (4)| 00:00:03 |
|*  1 |  HASH JOIN         |      |  2498 | 99920 |   219   (4)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |  2498 | 72442 |    59   (6)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 49954 |   536K|   159   (2)| 00:00:02 |
---------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')

Note
-----
   - SQL profile "SQLPROFILE_NAME1" used for this statement

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        933  consistent gets
        

可以看到，SQL使用了SQL Profile，不过没有达到我们预期的效果。

看起来是SQL Profile使用的Hints有问题。我们重新设置SQL Profile的Hints，在Hints中加上“Query Block Name”。这一次在执行IMPORT_SQL_PROFILE过程时，将REPLACE参数设置为TRUE，以替换现有的SQL Profile：

SQL> declare
  2    v_hints sys.sqlprof_attr;
  3  begin
  4    v_hints:=sys.sqlprof_attr('USE_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)');
  5    dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
  6                v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true);
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b
  2  where a.signature = b.signature
  3  and a.name='SQLPROFILE_NAME1';

ATTR_VAL
----------------------------------------
USE_NL(T1@SEL$1 T2@SEL$1)
INDEX(T2@SEL$1)

再次执行下面的SQL：

SQL> select  t1.*,t2.owner
  2       from t1,t2
  3       where t1.object_name like '%T1%'
  4       and t1.object_id=t2.object_id;

已选择29行。

执行计划
----------------------------------------------------------
Plan hash value: 3787413387

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  2498 | 99920 |  5061   (1)| 00:01:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |  2498 | 99920 |  5061   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL        | T1     |  2498 | 72442 |    59   (6)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SQLPROFILE_NAME1" used for this statement

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        294  consistent gets

这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。
那么我们再一次手工修改T1表的统计信息，看看结果如何：
Read the rest of this entry »

Oracle 11g从发布到现在，也有几个年头了。而在国内来说，Oracle 10g仍然是主流，甚至一些电信运营商的核心系统仍然在使用9i。作为Oracle 10g的一项新特性，SQL Profiles被使用得并不太多。不管是在论坛、个人的BLOG还是其他一些地方，SQL Profiles的介绍也相对较少。对我个人来说，已经在多个优化场合中使用SQL Profiles，在这里向大家介绍SQL Profiles，就是希望能够了解Oracle数据库的这一功能。

SQL Profiles可以说是Outlines的进化。Outlines能够实现的功能SQL Profiles也完全能够实现，而SQL Profiles具有Outlines不具备的优化，个人认为最重要的有2点：

• SQL Profiles更容易生成、更改和控制。
• SQL Profiles在对SQL语句的支持上做得更好，也就是适用范围更广。

关于这2方面的优点，我后面会详细地阐述。

现在我在使用Outlines的场合，均使用SQL Profiles来替代。有一次准备对1条SQL语句使用Outline进行执行计划的稳定，结果使用Outline之后，系统出现大量的library cache latch的争用，不得不关闭Outline的使用，但是改用SQL Profiles不再有这个问题。这或许是个BUG，不过既然能用SQL Profiles代替，也就没再深入去研究这个问题。

使用SQL Profiles无非是两个目的：

• 锁定或者说是稳定执行计划。
• 在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。

那么SQL Profile到底是什么？在我看来，SQL Profile就是为某一SQL语句提供除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为SQL语句选择更适合的执行计划。这些说法显得比较枯燥，还是来看看下面的测试。

首先建2个测试表：

SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;

表已创建。

SQL> create table t2 as select * from dba_objects;

表已创建。

SQL> create index t2_idx on t2(object_id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL 过程已成功完成。

然后看看下面这一条SQL：

SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

已选择29行。

执行计划
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2498 | 99920 |   219   (4)| 00:00:03 |
|*  1 |  HASH JOIN         |      |  2498 | 99920 |   219   (4)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |  2498 | 72442 |    59   (6)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 49954 |   536K|   159   (2)| 00:00:02 |
---------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        932  consistent gets
          0  physical reads
          0  redo size
       1352  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         29  rows processed

这里省略了SELECT出来的具体数据，但是我们关心的是返回的结果行数、执行计划以及逻辑读这些信息。
首先从执行计划可以看到，这条SQL语句在2个表上都是全表扫描。在第1个表T1上，有 like ‘%T1%’这样的条件，导致只能全表扫描，这没有问题。但是第2个表，也是全表扫描，这里有没有问题呢？或者说是有没有优化的余地，答案显然是肯定的。
这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1 like ‘%T1%’返回的结果行数为2498行，即T1表总行数的5%，如果2个表采用index range scan+nested loop连接，oracle评估的成本会高于full table scan+hash join。下面可以看到Oracle优化器评估的index range_scan+nested loop的成本：

SQL> explain plan for select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner 
     from t1,t2 
     where t1.object_name like '%T1%' 
     and t1.object_id=t2.object_id;

已解释。

SQL> @showplan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  2498 | 99920 |  5061   (1)| 00:01:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |  2498 | 99920 |  5061   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL        | T1     |  2498 | 72442 |    59   (6)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

从执行计划可以看到Oracle优化器评估的成本为5061，远远高于原来的219。
但是实际的逻辑读是多少呢？

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        290  consistent gets
          0  physical reads
          0  redo size
       1352  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         29  rows processed

加了HINT之后实际的逻辑读只有290，低于原始SQL的932。所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。

下面我们用Oracle的SQL Tuning Advisor来尝试这条SQL：

SQL> var tuning_task varchar2(100);
SQL> DECLARE
  2    l_sql_id v$session.prev_sql_id%TYPE;
  3    l_tuning_task VARCHAR2(30);
  4  BEGIN
  5    l_sql_id:='4zbqykx89yc8v';
  6    l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
  7    :tuning_task:=l_tuning_task;
  8    dbms_sqltune.execute_tuning_task(l_tuning_task);
  9    dbms_output.put_line(l_tuning_task);
 10  END;
 11  /
任务_74

PL/SQL 过程已成功完成。

SQL> print tuning_task;

TUNING_TASK
---------------------------------------------------------------------------------------------------------
任务_74

SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : 任务_74
Tuning Task Owner                 : TEST1
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 12/15/2010 09:56:02
Completed at                      : 12/15/2010 09:56:03
Number of SQL Profile Findings    : 1

-------------------------------------------------------------------------------
Schema Name: TEST1
SQL ID     : 4zbqykx89yc8v
SQL Text   : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
             and t1.object_id=t2.object_id

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

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
 为此语句找到了性能

  Recommendation (estimated benefit: 46.62%)
  ------------------------------------------
  -考虑接受推荐的 SQL
    executedbms_sqltune.accept_sql_profile(task_name => '任务_74', replace =
            TRUE);

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

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    29 |  1160 |   219   (4)| 00:00:03 |
|*  1 |  HASH JOIN         |      |    29 |  1160 |   219   (4)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |    29 |   841 |    59   (6)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 49954 |   536K|   159   (2)| 00:00:02 |
---------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')

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

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    29 |  1160 |   117   (3)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |    29 |  1160 |   117   (3)| 00:00:02
 |
|*  3 |    TABLE ACCESS FULL        | T1     |    29 |   841 |    59   (6)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

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

Read the rest of this entry »

我们都知道drop table, truncate table时都会先做一次checkpoint，将被删除对象的脏块写入磁盘。

客户有一套系统，Oracle 9.2.0.8，需要做数据迁移，由于种种原因，采用的是逻辑迁移的方式。由于库比较大，超过了1.5T，而停机时间又有限，因此在正式迁移之前需要做大量的测试，测试的目的，一方面是看迁移流程上是否存在问题，另一方面是看迁移的时候，哪个地方会存在性能瓶颈，并进行优化，同时估算实施迁移时间。

第一次测试后，需要把测试产生的大量用户及其对象全部删除，删除用的是drop user username cascade。不幸的是这种方式删除得相当地慢。一个9000多个表的用户，删除了1个半小时才删除了4000多个表。为什么这么慢？有没有办法提高速度？

drop table既然要做checkpoint，那么在db cache非常大的情况下，这需要消耗的时间是比较长的。如果能够减少这个时间无疑将大幅提高速度。首先尝试做一次checkpoint，将buffer cache全部刷新出去：

alter system checkpoint;
alter session set events 'immediate trace name flush_cache level 1';

发现没什么效果。

由于db_cache_size有50GB左右，db_keep_cache_size有6G左右。重新设置参数，将db_keep_cache_size设为0，将db_cache_size设为200M，重启一下数据库，重新执行删除用户的操作，操作很快完成。

在另一次同样的过程中，采用同样的修改参数的方式，效果同样非常明显。

这是个简单的案例，与君共享。

Oracle提供了方便的树形查询功能，也就是connect by 语句。树形结构是很常见的，比如组织机构树，产品目录树等。本文不讲述connect by 如何使用，只是提出在使用树形查询时需要考虑的一个有关于性能方面的问题。

这里提到的问题，主要是Oracle优化器在评估connect by 语句的cardinality时，存在的缺陷，下面将举例说明。

在这个例子中所使用的表，是一个真实的生产系统中的表，BSS_ORG：

SQL> desc bss_org
 名称                         是否为空? 类型
 ---------------------------- -------- --------------

 BSS_ORG_ID                   NOT NULL NUMBER(9)
 NAME                         NOT NULL VARCHAR2(64)
 BSS_PARENT_ORG_ID                     NUMBER(9)
 BSS_ORG_LEVEL_ID             NOT NULL NUMBER(3)
 STATE                        NOT NULL VARCHAR2(3)
 STATE_DATE                            DATE
 BSS_ORG_CODE                          VARCHAR2(15)

在这个BSS_ORG表中，BSS_ORG_ID是主键，BSS_PARENT_ORG_ID与BSS_ORG_ID形成上下层级关系。这个表的统计信息如下：

Table                   Number                 Empty Average    Chain Average Global
Name                   of Rows   Blocks       Blocks   Space    Count Row Len Stats
--------------- -------------- -------- ------------ ------- -------- ------- ------
BSS_ORG                  5,739       52            0       0        0      53 YES

Column                    Column                       Distinct            Number
Name                      Details                        Values   Density Buckets
------------------------- ------------------------ ------------ --------- ------- --
BSS_ORG_ID                NUMBER(9,0) NOT NULL            5,739   .000174       1
NAME                      VARCHAR2(64) NOT NULL           5,034   .000361     200
BSS_PARENT_ORG_ID         NUMBER(9,0)                       905   .002189     200
BSS_ORG_LEVEL_ID          NUMBER(3,0) NOT NULL                6   .000087       6
STATE                     VARCHAR2(3) NOT NULL                2   .000087       2
STATE_DATE                DATE                            1,624   .001434     200
BSS_ORG_CODE              VARCHAR2(15)                    5,639   .000179     200

下面的测试，是在Oracle 11.1.0.6 for Windows版本下进行的测试。在Oracle 9i、Oracle 10g下测试的结果与Oracle 11g下测试的结果是相符的。当然这里谈到的问题是cardinality，因此在三个版本下，SQL的执行计划可能有所不同，但最终的结论是一致的。(BTW：从10g开始，connect by语句有一个新的执行步骤，称为CONNECT BY NO FILTERING，对应的Hint是no_connect_by_filtering）。

SQL> explain plan for
  2  SELECT   bss_org_id
  3        FROM bss_org t
  4  START WITH bss_org_id = 1
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id;

已解释。

SQL> @showplan

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |  5739 | 80346 |    16   (0)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|         |       |       |            |          |
|   2 |   TABLE ACCESS FULL                     | BSS_ORG |  5739 | 80346 |    16   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   1 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")
       filter("BSS_ORG_ID"=1)

SQL> explain plan for
  2  SELECT   bss_org_id
  3        FROM bss_org t
  4  START WITH bss_org_id = 832044754
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id;

已解释。

SQL> @showplan

---------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |  5739 | 80346 |    16   (0)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|         |       |       |            |          |
|   2 |   TABLE ACCESS FULL                     | BSS_ORG |  5739 | 80346 |    16   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   1 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")
       filter("BSS_ORG_ID"=832044754)

上面的2条SQL，第1条实际应该返回的行数为5739，第2条SQL实际应该返回的行数为4，但是从执行计划上看，Oracle优化器评估的行数均为5739。

SQL> create index bss_org_idx1 on bss_org(bss_parent_org_id);

索引已创建。

SQL> explain plan for
  2  SELECT   *
  3        FROM bss_org t
  4  START WITH bss_org_id = 1
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id;

已解释。

SQL> @showplan

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     6 |   318 |     4   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING    |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | BSS_ORG      |     1 |    53 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN          | PK_BSS_ORG   |     1 |       |     1   (0)| 00:00:01 |
|   4 |   NESTED LOOPS                |              |       |       |            |          |
|   5 |    CONNECT BY PUMP            |              |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| BSS_ORG      |     6 |   318 |     4   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | BSS_ORG_IDX1 |     6 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")
   3 - access("BSS_ORG_ID"=1)
   7 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")

SQL> explain plan for
  2  SELECT   *
  3        FROM bss_org t
  4  START WITH bss_org_id = 832044754
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id;

已解释。

SQL> @showplan

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     6 |   318 |     4   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING    |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | BSS_ORG      |     1 |    53 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN          | PK_BSS_ORG   |     1 |       |     1   (0)| 00:00:01 |
|   4 |   NESTED LOOPS                |              |       |       |            |          |
|   5 |    CONNECT BY PUMP            |              |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| BSS_ORG      |     6 |   318 |     4   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | BSS_ORG_IDX1 |     6 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")
   3 - access("BSS_ORG_ID"=832044754)
   7 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")

这里在BSS_PARENT_ORG_ID列上建一个索引，是为了使执行计划与9i、10g下的一致。
这2条SQL返回的结果行数，与前面的2条SQL一样，分别是5739和4，但是从执行计划上看，Oracle优化器评估出来的行数都是6。

从前面的两个测试来看，优化器评估出来的SQL返回的行数要么是5739（表BSS_ORG的总行数），要么是6（总行数/BSS_PARENT_ORG_ID的Disctint Values）。但无论如何，随着不同的start with条件，这个行数（cardinality）与实际返回的结果行数可能会存在非常大的差异。如果仅仅是测试中这样一个简单的SQL，实际上不会有什么问题，很容易出现问题的地方在于，一个复杂的SQL中，有类似于测试SQL这样的子查询，这样使得表连接的评估出现很大的偏差，这样容易引起非常大的性能问题。

在9i下，如果BSS_PARENT_ORG_ID上如果没有索引，那么最后一个测试SQL的执行计划如下：

SQL> explain plan for
  2  SELECT   *
  3        FROM bss_org t
  4  START WITH bss_org_id = 832044754
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id
  7                                ;

已解释。

SQL> @showplan

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  5739 |   297K|     9 |
|*  1 |  CONNECT BY WITH FILTERING   |             |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| BSS_ORG     |       |       |       |
|*  3 |    INDEX UNIQUE SCAN         | PK_BSS_ORG  |     1 |     7 |     1 |
|   4 |   HASH JOIN                  |             |       |       |       |
|   5 |    CONNECT BY PUMP           |             |       |       |       |
|   6 |    TABLE ACCESS FULL         | BSS_ORG     |  5739 |   297K|     9 |
|   7 |   TABLE ACCESS FULL          | BSS_ORG     |  5739 |   297K|     9 |
----------------------------------------------------------------------------

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

   1 - filter("T"."BSS_ORG_ID"=832044754)
   3 - access("T"."BSS_ORG_ID"=832044754)

这里9i的优化器评估出来的cardinality为5739，而11g与此同样的执行计划，评估的cardinality是6。

前段时间就遇上由于connect by语句引起的性能问题。数据库为Oracle 9208，开始由于bss_org表的bss_parent_org_id列上没有索引，导致connect by部分得到的cardinality为5739，结果SQL性能非常差，在bss_parent_org_id上建索引后，执行计划改变，connect by 部分得到的cardinality为6，SQL性能大幅提升。

对于Oracle优化器不能准确评估connect by 语句的cardinality，目前没有比较好的解决办法。必要的时候只有考虑使用Hint了。欢迎朋友们针对此问题进行讨论。

一条看上去很简单的SQL：

SELECT * FROM V_CALL_EVENT_10906
WHERE to_char(start_date, 'yyyymmdd') in ('20090620', '20090621', '20090622') 

执行时长比较长，以至于出现ORA-01555错误，由于返回的结果数据行数非常大，取1月之内3天的数据，不太适合于使用索引，同时应用结构上决定了，也不能按天分区。

这里如果我们能够把表访问从6次，改为1次，那么性能就能大幅提升，这里修改视图的定义如下：

V_CALL_EVENT_10906视图定义如下：

CREATE VIEW V_CALL_EVENT_10906
AS
SELECT ACCT_ID1 ACCT_ID,
               SERV_ID,
               EVENT_TYPE_ID,
               ACCT_ITEM_TYPE_ID1 ACCT_ITEM_TYPE_ID,
               CALLING_AREA_CODE,
               CALLING_NBR,
               CALLED_AREA_CODE,
               CALLED_NBR,
               START_DATE,
               START_DATE + DURATION / 3600 / 24 END_DATE,
               DURATION,
               CHARGE1 CHARGE,
               BILLING_CYCLE_ID,
               TO_DATE(CREATED_DATE) CREATED_DATE,
               TO_DATE(START_DATE) DATA_DATE,
               RESERVED_FIELD1,
               1 SPLIT_ID
 FROM CALL_EVENT_10906
union all
SELECT ACCT_ID1 ACCT_ID,
               SERV_ID,
               EVENT_TYPE_ID,
               ACCT_ITEM_TYPE_ID2 ACCT_ITEM_TYPE_ID,
               CALLING_AREA_CODE,
               CALLING_NBR,
               CALLED_AREA_CODE,
               CALLED_NBR,
               START_DATE,
               START_DATE + DURATION / 3600 / 24 END_DATE,
               DURATION,
               CHARGE2 CHARGE,
               BILLING_CYCLE_ID,
               TO_DATE(CREATED_DATE) CREATED_DATE,
               TO_DATE(START_DATE) DATA_DATE,
               RESERVED_FIELD1,
               2 SPLIT_ID
 FROM CALL_EVENT_10906
WHERE ACCT_ITEM_TYPE_ID2 != 0
  AND ACCT_ITEM_TYPE_ID2 IS NOT NULL

为节省篇幅，这个视图的定义实际上没有完全列出，视图中实际有5个“UNION ALL”，也就是CALL_EVENT_10906实际访问了6次。

CREATE VIEW V_CALL_EVENT_10906 
AS 
select /*+ no_merge(v) no_push_pred(v) */
v.* FROM 
(SELECT /*+   parallel(a,4) */
          ACCT_ID1 ACCT_ID,
                   SERV_ID,
                   EVENT_TYPE_ID,
                   DECODE(B.SPLIT_ID, 1, ACCT_ITEM_TYPE_ID1, 2, ACCT_ITEM_TYPE_ID2, 3, ACCT_ITEM_TYPE_ID3,   4, ACCT_ITEM_TYPE_ID4, 5, ACCT_ITEM_TYPE_ID5, 6,ACCT_ITEM_TYPE_ID6,0) ACCT_ITEM_TYPE_ID,
                   CALLING_AREA_CODE,
                   CALLING_NBR,
                   CALLED_AREA_CODE,
                   CALLED_NBR,
                   START_DATE,
                   START_DATE + DURATION / 3600 / 24 END_DATE,
                   DURATION,
                   DECODE(B.SPLIT_ID, 1, CHARGE1, 2, CHARGE2, 3, CHARGE3, 4, CHARGE4, 5, CHARGE5, 6,CHARGE6,0) CHARGE,
                   BILLING_CYCLE_ID,
                   TO_DATE(CREATED_DATE) CREATED_DATE,
                   TO_DATE(START_DATE) DATA_DATE,
                   RESERVED_FIELD1,
                   B.SPLIT_ID SPLIT_ID
           FROM CALL_EVENT_10906812 A,
                                   (
                  SELECT 1 SPLIT_ID
                    FROM DUAL
                  UNION ALL
                  SELECT 2
                    FROM DUAL  
                  UNION ALL  
                  SELECT 3
                    FROM DUAL
                  UNION ALL  
                  SELECT 4
                    FROM DUAL
                  UNION ALL  
                  SELECT 5
                    FROM DUAL
                  UNION ALL  
                  SELECT 6 FROM DUAL) B
) v,(select /*+ no_merge */ 0 id from dual) K
where nvl(v.acct_item_type_id,0) !=k.id;

通过UNION DUAL表，得到6行结果，同时与CALL_EVENT_10906表之间没有任何关联条件，这样就会形成笛卡尔连接（cartesian join），CALL_EVENT_10906这个表的每一行数据，将实际产生6行输出。这样就避免了对这个表扫描6次。

为什么这里还要嵌套一层，再加上这样的条件：

where nvl(v.acct_item_type_id,0) !=k.id

这个条件实际上是：

where nvl(v.acct_item_type_id,0) !=0

如果不嵌套一层，那么就会形成CALL_EVENT_10906与DUAL表UNION之后的结果之后的连接关系，就不会使用cartesian join了。

我们经常使用Sql Trace和10046 event来诊断Oracle数据库性能问题。而level超过1的10046事件通常称为extended sql trace，通常用于诊断确定的单个SQL、存储过程或会话的性能问题，具有如下的几个优点：

• 可以得到SQL执行时实际的执行计划。
• 可以得到SQL执行时所花时间的具体分布，CPU消耗了多长时间，多块读消耗了多长时间等等。
• 可以得到SQL执行时的各种与性能相关的统计数据，逻辑读、物理读、fetch次数、parse次数等等。
• 不仅能够用于性能测试，同时能够用于诊断正在执行的SQL或存储过程的性能。
• 有很多的工具用于格式化生成的trace文件，除了Oracle自带的TKPROF、Metalink Note 224270.1 Trace Analyzer，以及第三方的免费工具如orasrp，《Troubleshooting Oracle Performance》作者开发的TVD$XTAT，甚至还有商业化的软件Hotsos Profiler等。

不过前段时间在用10046事件诊断一个性能问题的时候，却让生成的结果误导了。后来仔细检查发现，在会话开启sql trace的情况下，SQL语句会重新解析，导致开启sql trace之后与开启之前相比，执行计划可能发生了变化，导致sql trace的结果不能真实地反映会话执行SQL的情况，在分析时容易发生偏差。

下面是一个测试：

测试的环境是Oracle 10.2.0.1 for Windows，不过前面提到的案例，是发生在Oracle 9i下的，所以9i和10g都有这个问题，而11g目前还没有测试过，有兴趣的朋友可以在11g上进行测试。

首先创建一个sql文件，内容为：

select /*+ testsql */ sum(value) from t1 where flag=:v_flag;

创建一个列上数据有倾斜的表：
SQL> create table t1 (value number ,flag number,pad varchar2(2000));

表已创建。

SQL> insert into t1 select rownum,mod(rownum,2000),lpad('x',1000,'x') from dba_objects;

已创建49796行。

SQL> commit;

提交完成。

SQL> insert into t1 select rownum,3000,lpad('x',1000,'x') from dba_objects where rownum<=10000;

已创建10000行。

SQL> commit;

提交完成。

SQL> create index t1_idx on t1(flag);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1',cascade=>true,method_opt=>'for all indexed columns');

PL/SQL 过程已成功完成。
SQL> select column_name,num_distinct,num_buckets from user_tab_columns where table_name='T1';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS
------------------------------ ------------ -----------
VALUE                                                  
FLAG                                   2030          75
PAD         

在创建的测试表中，FLAG列有2001个不同的值，其中，0-1999之间每个值约为25个，而有一个特殊的值3000，有10000个。收集统计信息时，在FLAG列上收集了直方图。

下面运行test.sql：

SQL> var v_flag number;
SQL> exec :v_flag:=3000;
SQL> set autot on stat
SQL> @test

SUM(VALUE)
----------
  50005000
统计信息
-------------------------------------------------------
          0  recursive calls
          0  db block gets
       8575  consistent gets
          0  physical reads
          0  redo size
        412  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

我们来看看SQL的执行计划：
Read the rest of this entry »

其实这是一篇技术文章。

最近比较忙，通宵干活也逐渐平常起来，BLOG更新也少了，其实想写的东西挺多的。

闲话少扯，切入正题。

■ Poor connection management can cause poor response times and unreliable
systems.
----摘自《Oracle Database Performance Tuning Guide 10g Release 2 (10.2)》”Understanding Scalability--Factors Preventing Scalability“一节.

■ Good Database Connection Management
Connecting to the database is an expensive operation that is highly unscalable.
Therefore, the number of concurrent connections to the database should be
minimized as much as possible. A simple system, where a user connects at
application initialization, is ideal. However, in a Web-based or multitiered
application, where application servers are used to multiplex database connections
to users, this can be difficult. With these types of applications, design efforts
should ensure that database connections are pooled and are not reestablished for
each user request.

----摘自《Oracle Database Performance Tuning Guide 10g Release 2 (10.2)》”Application Design Principles--SQL Execution Efficiency“一节.

1. Bad Connection Management
The application connects and disconnects for each database interaction. This
problem is common with stateless middleware in application servers. It has over
two orders of magnitude impact on performance, and is totally unscalable.

----摘自《Oracle Database Performance Tuning Guide 10g Release 2 (10.2)》”The Oracle Performance Improvement Method--Top Ten Mistakes Found in Oracle Systems“一节.

以上的内容，全部是关于连接管理（connection management）的，也就是应用系统连接到数据库的方式，其中之一就是，是使用长连接还是短连接。其实在以前，我看到如上所述的内容，并没有引起重视的，甚至可以说是不以为然。因为现在的使用Oracle数据库的大型的高并发的应用系统，在连接数据库上，一般都是使用了连接池，连接管理基本上都不存在什么问题。

然而事实证明，我错了。就在前不久，遇上一套系统，Oracle数据库的会话数保持在4000以上的高并发系统，一个关键的应用居然用的短连接。不幸的是，这个应用连接数据库的速率非常的快，而创建一个数据库的连接耗时非常的长，闲时都在150ms以上。在业务高峰期，连接数据库的排队已经非常高，Listener已经不能够及时处理连接请求，连接数据库通常需要1s以上，甚至数秒，严重影响了系统的性能。就算使用两个Listener都已经承受不了压力。

解决这个问题的根本办法还是修改应用，使用连接池。

看起来真是“只有想不到，没有做不到”，一切皆有可能啊。

这次的案例同样是一个省电信的数据库，只不过比《记一个SQL优化案例》中提到的数据库规模要大得多。先简单地介绍一下环境，运行在AIX 5300 TL05上的Oracle 9.2.0.8。系统维护人员发现一个应用的中间件队列全部堵塞。检查数据库的等待事件，发现这个应用连接的会话，基本上都是在等待latch free，latch#为98，很明显是SQL性能出现了问题。因此，检查几个会话正在运行的SQL，都是下面类似的SQL：

select c.acct_id,
       a.serv_id,
       d.cust_code,
       d.cust_id,
       a.acc_nbr,
       c.acct_name,
       c.acct_nbr_97,
       e.name serv_acct_state,
       to_char(b.state_date, ’yyyymmdd’) state_date,
       f.name serv_state,
       h.product_name
  from serv a,
       serv_acct b,
       acct c,
       cust d,
       (select domain, name
          from v_domain
         where table_name = ’SERV_ACCT’
           and field_name = ’STATE’) e,
       (select domain, name
          from v_domain
         where table_name = ’SERV’
           and field_name = ’STATE’) f,
       product h
 where a.serv_id = b.serv_id
   and b.acct_id = c.acct_id
   and a.cust_id = d.cust_id
   and b.state = e.domain
   and a.state = f.domain
   and a.product_id = h.product_id
   and b.state = ’10A’
   and c.state = ’10A’
   and a.state in (’2HA’, ’2HC’, ’2HD’, ’2HE’, ’2HH’, ’2HN’, ’2HS’)
   and a.serv_id in
       (SELECT distinct serv_id
          FROM serv_attr
         WHERE attr_val = ’0xx833xxxxx’
           AND attr_id IN (SELECT attr_id
                             FROM a_query_acct_attr
                            WHERE state = ’A0A’
                              and attr_type = ’ACT’))

我隐去了代码中那ATTR_VAL=条件后真实的值，以’0xx833xxxxx’代替。

这个SQL咋一看跟《记一个SQL优化案例》提到的SQL都很相似，想想也能明白，都是一家开发商开发的系统^_^。

我在这条SQL中看到下面这样的代码，我就头痛：

SELECT distinct serv_id
          FROM serv_attr
         WHERE attr_val = ’0xx833xxxxx’
           AND attr_id IN (SELECT attr_id
                             FROM a_query_acct_attr
                            WHERE state = ’A0A’
                              and attr_type = ’ACT’)

对于SERV_ATTR这个表，我们可理解为这个表存储了所有用户的属性，每一个用户有多行，每一行有一个ATTR_ID，表示属性ID，也就是表示是什么属性，而ATTR_VAL则是属性的值。这样可以很方便地进行扩展，比如增加属性类型，甚至是自定义属性等。但是这样的设计，虽然具有了很大的灵活性，但不得不说，这样的设计，放在数据库中，基本上违背了关系型数据库的初衷。ATTR_VAL虽然定义为VARCHAR2类型，但实际存储的数据也可能是数值，日期，只不过都以字符串来表示。这样的表，对于Oracle的优化器来说，可以说是一个巨大的挑战。

虽然如此，不得不先接受现实，得想办法让这个SQL正常。第一步当然还是看执行计划了：
Read the rest of this entry »