一.环境
目标库:支付系统数据库
环境: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值得方法。