Skip to content

Database - 69. page

并发性能问题一例

朋友昨天问我在对一个生产环境进程压力测试时候，当并发达到1000时候等待时间很长，怎么弄？

这是他的环境：
双机rac
M8000,128G的内存，SGA设置为64G，PGA设置为13G
测试的插入表大约有3000多万的数据量

为教育部的一个库。

———————————————–

首先就是让他先查看告警日志，看看有什么异常提示
结果并没有发现什么异常，日志是30秒切换一次

把日志扩大到6组，每组1G
依然是这样情况

采集AWR发现：
首要等待事件为：

 
 

根据上面的tx 等待事件 从index下手

接着从awr往下看，可以发现




这里的一个主键索引引起的等待事件，

采取措施：

ALTER INDEX PK_MVS_B_STUDENT REBUILD ONLINE INITRANS 32；
ALTER INDEX MVS.STUDENT INITRANS 16;

性能改善明显，提高500%

Oracle | Guang Cai Li |

一个SQL的优化

郁闷，只是价格count 就不扫描表了
SQL> create index idx_luda_zhutou_obna on zhutou(upper(object_name)) online;

Index created.

SQL> select /*+ INDEX(a IDX_LUDA_ZHUTOU_OBNA)*/ count(a.object_name)
2 from zhutou a
3 where upper(a.object_name) like ‘LUDA%’;

Execution Plan
———————————————————-
Plan hash value: 4142235787

—————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 66 | 158 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ZHUTOU | 8 | 528 | 158 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IDX_LUDA_ZHUTOU_OBNA | 464 | | 4 (0)| 00:00:01 |
—————————————————————————————————–

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

3 – access(UPPER(“OBJECT_NAME”) LIKE ‘LUDA%’)
filter(UPPER(“OBJECT_NAME”) LIKE ‘LUDA%’)

Note
—–
– dynamic sampling used for this statement

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

SQL> ^Cselect /*+ INDEX(a IDX_LUDA_ZHUTOU_OBNA)*/ count(upper(a.object_name))

SQL> from zhutou a
SP2-0734: unknown command beginning “from zhuto…” – rest of line ignored.
SQL> where upper(a.object_name) like ‘LUDA%’
SP2-0734: unknown command beginning “where uppe…” – rest of line ignored.
SQL>
SQL> select /*+ INDEX(a IDX_LUDA_ZHUTOU_OBNA)*/ count(upper(a.object_name))
2 from zhutou a
3 where upper(a.object_name) like ‘LUDA%’;

Execution Plan
———————————————————-
Plan hash value: 3214512578

——————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 66 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | INDEX RANGE SCAN| IDX_LUDA_ZHUTOU_OBNA | 8 | 528 | 4 (0)| 00:00:01 |
——————————————————————————————

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

2 – access(UPPER(“OBJECT_NAME”) LIKE ‘LUDA%’)
filter(UPPER(“OBJECT_NAME”) LIKE ‘LUDA%’)

Note
—–
– dynamic sampling used for this statement

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

SQL>

Data access tuning performance tuning | Guang Cai Li |

Mysql 备份方式

MYSQLDUMP

在linux下的mysql在刚安装时候的默认用户都是root

导出的语句

mysql -uusername -ppassword -hlocalhost database_name > name.sql

导入的语句

mysql -uusername -ppassword -hlocalhost database_name < name.sql

Oracle恢复 开源数据库 | Guang Cai Li |

数据库性能调优报告

1 10.*********（单实列）存在的问题：

1. 业务高峰期响应慢，处理步骤如下所示，

1.1 创建如下索引：

create index idx_ZZJGDM_20100617_sh on TAX_SI_ZZJGDM_20100617(sh,aac001) online tablespace IND_DATAEXCHANGE;


create index idx_F_KBH1_aac001 on F_KBH1(aac001,ekc092) online tablespace IND_DATAEXCHANGE;


create index idx_iaa2_aac001 on iaa2(aac001) online tablespace DATAEXCHANGE;


create index idx_SBDI_BANKFEE_sblsh on SBDI_BANKFEE(sblsh,sbbm) online tablespace IND_SIBUSI;


create index idx_ic13_aad124 on ic13(aad124,aac001,aic142) online tablespace DATAEXCHANGE;


create index idx_kca1_aac001_aae140 on kca1(aac001,aae140,aae109,eae003) online tablespace DATAEXCHANGE;


create index idx_userlog_aab001 on sbds_userlog(aab001,functionid,eae024) online tablespace SIBUSI;

create index idx_sbds_userlog_flag on sbds_userlog(userflag) online tablespace SIBUSI;

1.2 发现oem消耗较大的数据库资源，关闭oem的job程序

exec emd_maintenance.remove_em_dbms_jobs;（要在sysman下才能关闭的一个进程）

1.3 select min(bitmapped) from ts$ where dflmaxext =:1 and bitand(flags, 1024) = 1024语句

查metalink：

Oracle Server – Enterprise Edition – Version: 10.1 to 10.2；This problem can occur on any platform. Symptoms；After the

introduction of temporary tablespace groups following query using a lot of CPU and executed many times，Cause：

Temp tablespace group uses a recursive query on ts$ to get the minimum extent size among its temp tablespaces.

Solution1> Use single temp tablespace as user’s temp tablespace.

Solution2> Apply patch for the bug 5455880

1.4 占用资源比较多，需要程序开发人员介入的SQL：

Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id

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

1,926,819 478 4,031.0 12.5 10.35 13.58 6hpkyfr0cmm4c Module: repsc2.exe

SELECT "CUI" ,"REP_SERVER_NAME" ,"REP_MODE" ,"REP_BEGIN_TIME" ,"REP_END_TIME" ,

"REPID" ,"QUERYID" ,"PARAM_STR" ,"OP_ID" ,"OP_NAME" ,"DEP_ID" ,"DEP_NAME" ,

"EXEC_STATE" ,"EXEC_DESC" FROM "REPSERVERLOG" ORDER BY "CUI" ASC

该SQL执行较频率，且无任何过滤条件。

1.5 占用资源比较多，需要程序开发人员介入的SQL：

Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id

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

2,203,673 60 36,727.9 18.2 44.73 44.95 3q1h43vdhaxz9 Module: sxdb2

SELECT '330699' YYCSDM, '3' KLB, '1.00' GFBB, '91560000023306003301005D' JGDM, T

O_CHAR(B.EKC184, 'yyyymmdd') FKRQ, TO_CHAR(B.EKC185, 'yyyymmdd') KYXQ, B.EKC183KH, A.AAC002 SFZH, A.AAC003 XM, A.AAC004

XB, C.AAC005 MZ, SUBSTR(A.AAC002, 1, 6) CSD, TO_CHAR(A.AAC006, 'yyyymmdd') CSRQ, C.AAE005 LXFS, C.AAE006 JTZZ, A.EAC109

ZJLX, C.AAB027 YHBM, A.AAC001 GRBM, B.EKC101 KZT, A.AAB001 DWBM

, C.EAE033 SJHM FROM AC01 A, KCK1 B, ACA1 C WHERE A.AAC001 = B.A

AC001 AND A.AAC001 = C.AAC001 AND B.EKC101 IN ('0', '1')

表kck1，ac01，aca1无有效过滤条件，3个表全扫描，每1分钟运行1次。

1.6 占用资源较多，需要程序开发人员介入的SQL：

Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id

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

2,003,652 9 222,628.0 7.1 11.28 18.31 8yn7yvp8gdz41

UPDATE ACC8 SET AAC001=:B2 WHERE AAC001=:B1



1,259,027 9 139,891.9 4.4 8.18 33.94 6jrq2qfz2c2bb

INSERT INTO ACC8MERGE SELECT * FROM ACC8 WHERE AAC001=:B1

这两个SQL单次执行逻辑读很高，怀疑:B1这个SQL绑定变量的数据类型代入有问题

Oracle | Guang Cai Li |