某银行数据插入慢问题分析过程

一．环境

目标库：支付系统数据库

环境：RAC （two nodes）+ AIX 7.1

时间：2017年4月9日，周日，早4点至5点多

二．故障现象

故障描述： 表bapbup.BUPMTRANADDINFO插入速度缓慢，该表为复合分区表，1级分区按日，二级分区按某字段值分区，定时每周日（9号为周日）凌晨00:30增加分区，对MAX分区做SPLIT。数据库定时每天4点开始收集统计信息（该操作为Oracle数据库自动收集）

三．分析原因

  1．查看 awrrpt_1_20985_20986.html文件，发现主要的等待事件是

从上边的前5个等待事件发现Librarycache lock 占了89.69的db时间。

 下边是Library cache lock解释和产生的原因

当我们想要访问某个library cache object，我们首先要获得这个指向这个object的handle的lock，获得这个lock之后我们就需要pin住指向这个object的heap。

 

   当我们对包，存储过程，函数，视图进行编译的时候，Oracle就会在这些对象的handle上面首先获得一个library cache lock，然后再在这些对象的heap上获得pin，这样就能保证在编译的时候其它进程不会来更改这些对象的定义，或者将对象删除。

 

  当一个session对SQL语句进行硬解析的时候，这个session就必须获得librarycache lock，这样其他session就不能够访问或者更改这个SQL所引用的对象。如果这个等待事件花了很长时间，通常表明共享池太小(由于共享池太小，需要搜索free的chunk，或者将某些可以被移出的object page out，这样要花很长时间)，当然了，也有可能另外的session正在对object进行修改(比如split 分区),而当前session需要引用那个table，那么这种情况下我们必须等另外的session进行完毕。

 我们怀疑是数据库中可能有存储过程或包在进行编译的时候，被调用，或是数据库中有ddl操作。

但是通过查看awrrpt_1_20985_20986.html在第一个节点的sql 中没有ddl操作的sql

然后查看global_awr_report_21153_21154.html，发现两个节点的等待事件

两个节点的第一等待事件全部是library cache lock,然后去查看对应sql。

然后查看sql语句

这个sql语句应该在00：30开始执行，但是在4：00-5：00的awr中还存在，并且执行次数为0次，说明一直执行没有成功，此sql是在执行ddl操作，那么在library中会产生library cache lock，并且此时dml操作只有等待。

 

由于我们没有真实环境，因此举例：split 分区内部操作：

1.创建表

CREATE TABLE test

  (    "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),

      NAMESPACE      NUMBER ,                                                   

      EDITION_NAME   VARCHAR2(30)

  )

  partition byrange(object_id)

  (partition p1values less than (99999) tablespace users,

  partition p2values less than (maxvalue) tablespace users);

  2．插入记录

 insert into testselect * from dba_objects;

 commit;

  3.创建索引

createindex ind_obj ontest(OBJECT_ID,LAST_DDL_TIME,TIMESTAMP,object_type,owner,status);

 

  4．对表进行分析

  execdbms_stats.gather_table_stats('sys','test',cascade=>true,degree=>2);

  5．进行split分区，捕获信息

  oradebugsetmypid;

  oradebug event10046 trace name context forever,level 12;

 

  alter table testsplit partition p2 at (100001)

    into (partitionp3, partition p4);

    6．用tkprof进行分析trace文件

   F:> tkprofF:\app\oracle\diag\rdbms\qyl\qyl\trace\qyl_ora_824.trc  a.txt

7.查看a.txt文件

发现split分区会递归产生两个sql

    SQL ID:57xmjuuk66gj4

Plan Hash: 2453191704

select /*+ FIRST_ROWS(1) PARALLEL("TEST", 1) */1

from

 "SYS"."TEST" PARTITION("P2")  where ( (  (  ("OBJECT_ID" < 100001 ) )  ) )

   and rownum <2

 

 

call     count       cpu   elapsed       disk      query   current        rows

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

Parse        1      0.00      0.00          0          0          0           0

Execute      1      0.00      0.00          0          0          0           0

Fetch        1      0.01      0.00          0        140          0           0

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

total        3      0.01      0.00          0        140         0           0

 

Misses in library cache during parse: 1

Optimizer mode: FIRST_ROWS

Parsing user id: SYS  (recursive depth: 1)

 

Rows     Row SourceOperation

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

      0  COUNT STOPKEY (cr=140 pr=0 pw=0 time=0 us)

      0   INDEX RANGE SCAN IND_OBJ (cr=140 pr=0 pw=0time=0 us cost=1 size=13 card=1)(object id 63717)

      会进行全局索引的扫描

      第二个：

      SQL ID:4mdj4dnjwt01b

Plan Hash: 1630110373

select /*+ FIRST_ROWS(1) PARALLEL("TEST", 1) */1

from

 "SYS"."TEST" PARTITION("P2")  where ( (  (  ("OBJECT_ID" >= 100001 OR

 "OBJECT_ID" IS NULL )  )  ) ) and rownum < 2

 

 

call     count       cpu   elapsed       disk      query   current        rows

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

Parse        1      0.00      0.00          0          0          0           0

Execute      1      0.00      0.00          0          0          0           0

Fetch        1      0.00      0.00          0          4          0           1

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

total        3      0.00      0.00          0          4          0           1

 

Misses in library cache during parse: 1

Optimizer mode: FIRST_ROWS

Parsing user id: SYS  (recursive depth: 1)

 

Rows     Row SourceOperation

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

      1  COUNT STOPKEY (cr=4 pr=0 pw=0 time=0 us)

      1   PARTITION RANGE SINGLE PARTITION: 2 2 (cr=4pr=0 pw=0 time=0 us cost=3 size=13 card=1)

      1    TABLE ACCESS FULL TEST PARTITION: 2 2 (cr=4pr=0 pw=0 time=0 us cost=3 size=13 card=1)

进行空分区全扫描

 

通过上边看出，split空分区，如果对分区列存在索引，会进行索引扫描，因此，如果表很大，索引很多，有可能会很慢

四，解决办法

  有两个解决办法：

1．    不要再使用oracle的splitpartition的办法，直接使用oracle11g的 分区表新特性internal分区。

提前收工或自动把分区创建，不要再使用max值得方法。