分类目录归档：Oracle性能优化

在不少时候我们需要对一个值是否是null的查询,根据Oracle的特点,我们单纯在在这个列上创建一个index不能满足这个需求,因为b-tree index中就是不包含null列.通过创建含常数列的复合index可以满足该需求
数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

创建测试表

SQL> create table t_xifenfei 
  2  as
  3  select * from dba_objects;

Table created.

SQL> desc t_xifenfei;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

创建可能含null列index

SQL> create index ind_object_id on t_xifenfei(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T_XIFENFEI',cascade => true);

PL/SQL procedure successfully completed.

查看执行计划

SQL> SET AUTOT TRACE EXP stat
SQL> SELECT * FROM T_XIFENFEI WHERE OBJECT_ID IS NULL;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    95 |   159   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    95 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID" IS NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        695  consistent gets
          0  physical reads
          0  redo size
        995  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

可以看到我们创建的一个普通的index,查询判断是否为null的时候,没有被应用该index,而是直接使用全表扫描.

创建支持null index

SQL> drop index ind_object_id ;

Index dropped.

SQL> create index ind_object_id on t_xifenfei(object_id,0);  

Index created.

SQL>  exec dbms_stats.gather_table_stats(user,'T_XIFENFEI',cascade => true);

PL/SQL procedure successfully completed.

查看执行计划

SQL> SELECT * FROM T_XIFENFEI WHERE OBJECT_ID IS NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 804765899

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    95 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI    |     1 |    95 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID" IS NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        995  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

这里使用了index,并且执行中逻辑读大幅度下降,很大程度提高了程序的执行效率,逻辑读从695降低为2.

原因分析
建立一个包含列和常数的复合index,可以实现该需求,根据b-tree index的特点,只有当index中包含的列都为null的时候,才不会别在index中记录,因为设置了index中包含的常数列,所以就是列为null,也会被包含在该index中,从而查询null值的时候依然可以使用到该index

分析一份awr,发现不太熟悉的等待事件”inactive transaction branch”,awr相关信息如下



分析top 1 sql中的对象

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select owner,object_type from dba_objects where object_name =upper('wCommonShortMsg');

OWNER                          OBJECT_TYPE
------------------------------ -------------------
PUBLIC                         SYNONYM
OFFONQUERY                     SYNONYM

SQL> COL DB_LINK FOR A12
SQL> select TABLE_OWNER,TABLE_NAME,DB_LINK from dba_SYNONYMS WHERE 
   2 SYNONYM_NAME=upper('wCommonShortMsg');

TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------
                               WCOMMONSHORTMSG                CRMDB_LINK
                               WCOMMONSHORTMSG                DB_LINK_CRM

通过这里查询,可以确定引起dblink相关等待严重的是关于wCommonShortMsg同义词查询导致(使用dblink连接到其他库),结合数据库版本，大致可以确定inactive transaction branch等待和MOS中的bug 10413418相符

有朋友问我,通过awr怎么来判断系统这个时间段的会话建立情况,也就是说如果中间件或者客户端程序发生异常,过多的连接数据库.
我这里有个例子,用户的数据库大概在每秒钟建立10个连接左右(相对而言比较频繁)

[oracle@xifenfei tmp]$ grep "18-JUL-2012 18:40:03" listener.log |wc -l
9
[oracle@xifenfei tmp]$ grep "18-JUL-2012 18:40:04" listener.log |wc -l
7
[oracle@xifenfei tmp]$ grep "18-JUL-2012 18:41:04" listener.log |wc -l
12
[oracle@xifenfei tmp]$ grep "18-JUL-2012 18:40" listener.log |wc -l
554
[oracle@xifenfei tmp]$ grep "18-JUL-2012 18:50" listener.log |wc -l
598
[oracle@xifenfei tmp]$ grep "18-JUL-2012 19:00" listener.log |wc -l
597

我们分析这个时间段的awr,看那些地方是可以表明用户会话建立频繁
awr汇总信息
通过这个信息我们可以发现awr报告时间段(120分钟),和数据库在起点和终点的会话数
说明:该数据库问题很多,出现负载高,不全是会话建立频繁导致,这里只分析建立会话相关情况

Load Profile信息
通过这里的Logons为36对于这样的系统来说,明显异常

Top 5 Timed Events
这里的latch: session allocation等待就是比较明显的建立会话时候出现的等待

Time Model Statistics
Time Model Statistics中的connection management call elapsed time大家都明白的,建立会话花费时间

Dictionary Cache Stats
dc_usernames和dc_users请求值偏大

总结说明
在实际工作中:遇到过因为session建立太频繁导致监听繁忙,tnsping延迟比较严重案例,也遇到因为会话建立频繁导致系统内存被消耗完的案例.
在遇到会话建立过于频繁的案例,最有力的说明证据是监听日志,因为awr中的相关数据没有绝对标准(而且awr本身也是一个相对性的东西),而且一般客户对awr中我刚刚列举的数据概念性不强,所以一般只能作为分析的辅助工具,或者为进一步分析监听日志提供理由依据.