关闭

数据库优化

383人阅读 评论(0) 收藏 举报
分类:

客户反映数据库时快时慢,数据库版本11.2.0.4.0,操作系统RHEL5U5,查看数据库的活动会话,发现基本100%的SQL都在同一条SQL,而且大多都是latch: row cache objects等待。

01 SQL> select inst_id,username,sql_id,event from gv$session where username is not null and status='ACTIVE';
02  
03    INST_ID USERNAME                       SQL_ID        EVENT
04 ---------- ------------------------------ ------------- ---------------------------------
05          1 SYS                            8s2qkvk056ugr PX Deq: Execution Msg
06          1 SYS                            8s2qkvk056ugr PX Deq: Execute Reply
07          1 PRESSO                         9rwzwamtgv6m6 gc cr request
08          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
09          2 PRESSO                         dfftdnm7cu76f resmgr:cpu quantum
10          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
11          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
12          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
13          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
14          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
15          2 PRESSO                         4z9c5071vvaz5 resmgr:cpu quantum
16          2 PRESSO                         dfftdnm7cu76f resmgr:cpu quantum
17          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
18          2 SYS                            8s2qkvk056ugr PX Deq: Execution Msg
19          2 PRESSO                         dfftdnm7cu76f resmgr:cpu quantum
20          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
21          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
22          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
23          2 PRESSO                         dfftdnm7cu76f resmgr:cpu quantum
24          2 PRESSO                         dfftdnm7cu76f resmgr:cpu quantum
25          2 PRESSO                         dfftdnm7cu76f enq: RC - Result Cache: Contention
26  
27 21 rows selected.

latch: row cache objects等待事件通常是在修改数据字典相关的等待,通常由于硬解析过高导致,查看这条SQL的SQL语句。

01 SQL> select sql_text from v$sqltext where sql_id='dfftdnm7cu76f' order by piece
02  
03 SQL_TEXT
04 ---------------------------------------------------------------------------------------------------------
05 select *
06   from (select t.apply_id,
07                bb.cn_name,
08                o.cn_name as oname,
09                UPPER(bb.card_code),
10                (select count(bd.BAD_RECORD_ID) as n
11                   from biz_badrecoed_info bd wherebd.BAD_RECORD_ID = 4 and bd.PUNISH_ORG = o.org_id) || '-' ||
12                (select count(bd.BAD_RECORD_ID) as nll
13                   from biz_badrecoed_info bd
14                  where bd.BAD_RECORD_ID = 1
15                    and bd.ID_CARD = bb.card_code) || '-' ||
16                (select count(bd.BAD_RECORD_ID) as nlllfrom biz_badrecoed_info bd where bd.BAD_RECORD_ID = 2 and bd.ID_CARD = bb.card_code) || '-' ||
17                (select count(bd.BAD_RECORD_ID) as nllll
18                   from biz_badrecoed_info bd
19                  where bd.BAD_RECORD_ID = 3
20                    and bd.ID_CARD = bb.card_code) bdn,
21                o.type_id,
22                bcode.code_name,
23                trim(ps.cn_name) as psname,
24                t.apply_status as st,
25                lead(t.apply_id, 1) over(order by t.create_time, t.apply_id desc) as nextID,
26                lag(t.apply_id, 1) over(order by t.create_time, t.apply_id desc) as prevID,
27                t.create_time as create_time
28           from biz_presscard_application t,
29                biz_org_info              o,
30                biz_reporter_base_info    bb,
31                base_code_info            bcode,
32                biz_pressstation_info     ps
33          where t.org_id = o.org_id
34            and bcode.code_sort_id = '1'
35            and t.reporter_uuid = bb.reporter_uuid
36            and to_char(t.apply_status) = bcode.code_id
37          andt.sat_id = ps.sat_id(+)
38            and t.apply_status = '2'
39            and t.next_orgid = '1'
40          ORDER BY T.create_time, t.apply_id DESC)
41  where rownum <= :1
42  
43 24 rows selected.

可见这条SQL没有使用绑定变量,修改cursor_sharing参数,在数据库层打开绑定变量。

01 SQL> show parameter cursor
02  
03 NAME                                 TYPE        VALUE
04 ------------------------------------ ----------- ------------------------------
05 cursor_bind_capture_destination      string      memory+disk
06 cursor_sharing                       string      SIMILAR
07 cursor_space_for_time                boolean     FALSE
08 open_cursors                         integer     3000
09 session_cached_cursors               integer     50
10 SQL> alter system set cursor_sharing=SIMILAR sid='*';
11  
12 System altered.

再次查询这个等待已经很少,但出现了大量的latch: cache buffers chains等待事件。

01 SQL> select inst_id,username,sql_id,event from gv$session where username is not null and status='ACTIVE' and username !='SYS';
02  
03    INST_ID USERNAME                       SQL_ID        EVENT
04 ---------- ------------------------------ ------------- ------------------------------------
05          1 PRESSO                         1fsdcuajuxncg enq: RC - Result Cache: Contention
06          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
07          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
08          2 PRESSO                         dfftdnm7cu76f resmgr:cpu quantum
09          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
10          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
11          2 PRESSO                         dfftdnm7cu76f resmgr:cpu quantum
12          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
13          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
14          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
15          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
16          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
17          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
18          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
19          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
20          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
21          2 PRESSO                         dfftdnm7cu76f latch: cache buffers chains
22          2 PRESSO                         dfftdnm7cu76f resmgr:cpu quantum
23          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
24          2 PRESSO                         dfftdnm7cu76f latch: row cache objects
25          2 PRESSO                         dfftdnm7cu76f enq: RC - Result Cache: Contention
26  
27 21 rows selected.

这套应用代码写的相当那啥,SQL还不能动,只能从数据库角度去解决问题。对这套系统来说,热点块一直都是个问题,为了解决这个问题,数据库的block_size已经修改为4K。

1 SQL> show parameter block
2  
3 NAME                                 TYPE        VALUE
4 ------------------------------------ ----------- ------------------------------
5 db_block_buffers                     integer     0
6 db_block_checking                    string      FALSE
7 db_block_checksum                    string      TYPICAL
8 db_block_size                        integer     4096
9 db_file_multiblock_read_count        integer     136

现在热点块依旧很严重,那么就只能通过调整pctfree来减少热点块了,先查出热点块严重的表。

01 SQL> SELECT *
02   2    FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME
03   3            FROM X$BH B, DBA_OBJECTS O
04   4           WHERE B.OBJ = O.DATA_OBJECT_ID
05   5             AND B.TS# > 0
06   6           GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
07   7           ORDER BY SUM(TCH) DESC)
08   8   WHERE ROWNUM <= 10;
09  
10  
11 OWNER                          OBJECT_NAME                              OBJECT_TYPE          TOUCHTIME
12 ------------------------------ ---------------------------------------- ------------------- ----------
13 PRESSO                         BIZ_REPORTER_BASE_INFO                   TABLE                  3691280
14 PRESSO                         BIZ_REPORTER_EDU_INFO                    TABLE                  3547004
15 PRESSO                         BIZ_REPORTER_ORG_INFO                    TABLE                  2273524
16 PRESSO                         BIZ_PRESSCARD_LOGOUT                     TABLE                  2099499
17 PRESSO                         BIZ_REPORTER_INFO                        TABLE                  1619598
18 PRESSO                         BIZ_PRESSCARD_APPLICATION                TABLE                  1191751
19 PRESSO                         BIZ_SYSTEM_MESSAGE_INFO                  TABLE                   730829
20 PRESSO                         BIZ_REPORTER_EXTEND_INFO                 TABLE                   610540
21 SYS                            WRH$_SQLSTAT                             TABLE PARTITION         193465
22 PRESSO                         IDX_REPORTER_UUID                        INDEX                   190901
23  
24 10 rows selected.

调整热点块较高的表的pctfree。

01 SQL> ALTER TABLE PRESSO.BIZ_REPORTER_BASE_INFO PCTFREE 30;
02  
03 Table altered.
04  
05 SQL> ALTER TABLE PRESSO.BIZ_REPORTER_EDU_INFO PCTFREE 30;
06  
07 Table altered.
08  
09 SQL> ALTER TABLE PRESSO.BIZ_REPORTER_ORG_INFO PCTFREE 30;
10  
11 Table altered.

这条SQL同样伴随着resmgr:cpu quantum等待事件,这显然是和资源管理器相关的等待事件,告警日志也可以看到相关的信息。

1 Setting Resource Manager plan SCHEDULER[0x32DB]:DEFAULT_MAINTENANCE_PLAN via scheduler window
2 Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
3 Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
4 End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"

当前服务器CPU使用率在50%左右,对PC服务器来讲,CPU超过50%通常是很危险的,就当前系统而言,AUTO_SQL_TUNING和资源管理器是不想看到的,禁用这些。

01 SQL> alter system set resource_manager_plan='' sid='*';
02  
03 System altered.
04  
05 SQL> execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
06  
07 PL/SQL procedure successfully completed.
08  
09 SQL> execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
10  
11 PL/SQL procedure successfully completed.
12  
13 SQL> execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
14  
15 PL/SQL procedure successfully completed.
16  
17 SQL> execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
18  
19 PL/SQL procedure successfully completed.
20  
21 SQL> execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
22  
23 PL/SQL procedure successfully completed.
24  
25 SQL> execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
26  
27 PL/SQL procedure successfully completed.
28  
29 SQL> execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
30  
31 PL/SQL procedure successfully completed.
32  
33 SQL> execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
34  
35 PL/SQL procedure successfully completed.
36  
37 SQL> BEGIN
38   2  DBMS_AUTO_TASK_ADMIN.DISABLE(
39   3  client_name => 'auto space advisor',
40   4  operation => NULL,
41   5  window_name => NULL);
42   6  END;
43   7  /
44  
45 PL/SQL procedure successfully completed.

过断时间再次查看,发现这条SQL的等待事件又出现了enq: RC – Result Cache: Contention。这受隐含参数_result_cache_timeout的影响,在11.2.0.4.0版本默认是10秒,在10g版本是60秒。

01 SQL> select
02   1   x.ksppinm name,
03   2   y.ksppstvl value,
04   3   y.ksppstdf isdefault
05   4  from
06   5   sys.x$ksppi x,
07   6   sys.x$ksppcv y
08   7  where
09   8   x.inst_id = userenv('Instance') and
10   9   y.inst_id = userenv('Instance') and
11  10   x.indx = y.indx and
12  11   x.ksppinm like '%result_cache%'
13  12  order by
14  13*  translate(x.ksppinm, ' _', ' ')
15 SQL> /
16  
17 NAME                                                                             VALUE      ISDEFAULT
18 -------------------------------------------------------------------------------- ---------- ---------
19 _client_result_cache_bypass                                                      FALSE      TRUE
20 client_result_cache_lag                                                          3000       TRUE
21 client_result_cache_size                                                         0          TRUE
22 _optimizer_ads_use_result_cache                                                  TRUE       TRUE
23 _result_cache_auto_dml_monitoring_duration                                       15         TRUE
24 _result_cache_auto_dml_monitoring_slots                                          4          TRUE
25 _result_cache_auto_dml_threshold                                                 16         TRUE
26 _result_cache_auto_dml_trend_threshold                                           20         TRUE
27 _result_cache_auto_execution_threshold                                           1          TRUE
28 _result_cache_auto_size_threshold                                                100        TRUE
29 _result_cache_auto_time_distance                                                 300        TRUE
30 _result_cache_auto_time_threshold                                                1000       TRUE
31 _result_cache_block_size                                                         1024       TRUE
32 _result_cache_copy_block_count                                                   1          TRUE
33 _result_cache_deterministic_plsql                                                FALSE      TRUE
34 _result_cache_global                                                             TRUE       TRUE
35 result_cache_max_result                                                          100        TRUE
36 result_cache_max_size                                                            2147483648 TRUE
37 result_cache_mode                                                                FORCE      TRUE
38 result_cache_remote_expiration                                                   0          TRUE
39 _result_cache_timeout                                                            10         TRUE
40  
41 21 rows selected.

降低_result_cache_timeout参数的值可以减少enq: RC – Result Cache: Contention等待事件。

经过查询,发现这些SQL运行时间已经超过2小时,在这期间应用程序重启过,这些进程消耗了大量的资源,并且有释放。经开发人员确认,超过15分钟以上的连接都是不正常的,要回收掉,通过修改profile,设定会话连接时间15分钟。

01 SQL> alter profile "DEFAULT" limit CONNECT_TIME 15;
02  
03 Profile altered.
04  
05 SQL> select * from dba_profiles;
06  
07 PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
08 ------------------------------ -------------------------------- -------- ----------------------------------------
09 DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
10 DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED
11 DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
12 DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
13 DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
14 DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
15 DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
16 DEFAULT                        CONNECT_TIME                     KERNEL   15
17 DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
18 DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED
19 DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
20 DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
21 DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
22 DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
23 DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
24 DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED
25  
26 16 rows selected.

杀掉这些进程后,问题解决,再次运行这些SQL,很快就有结果返回了。这个故障导致数据库响应时快时慢,原因是连到节点2的会话,由于服务器资源被这些没有回收的进程消耗和占用,速度就相对较慢,而节点1资源充足,分配到节点1的操作就相对较快。

0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

数据库性能优化详解

1.数据库访问优化法则 要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?而大多数情况性能最慢的设备会是瓶颈点,如下载时网络速度可能会是瓶颈点,本地复制文件时硬盘可能会是瓶颈点,为什么这些一般的工作我们能快速确认瓶颈点呢,因为我们对这些慢速设...
  • yzllz001
  • yzllz001
  • 2017-02-03 17:38
  • 34660

如何实现数据库优化

如何实现数据库优化
  • gmjabc
  • gmjabc
  • 2016-02-03 13:43
  • 927

数据库优化操作及方案

MySQL数据量大时,优化可以进行的操作:MySQL分库分表,MySQL缓存,MySQL索引,MySQL优化查询语句? 数据库优化方案:缓存;数据库表的大字段剥离;恰当的使用索引;表库的拆分;字段冗余;从磁盘上做文章;放弃关系数据库的某些特性deng; 主机性能;内存使用性能;网络传输性能;SQL...
  • ShareUs
  • ShareUs
  • 2017-05-12 12:58
  • 535

数据库性能优化策略

有数据表明:用户可以承受的最大等待时间为8秒。 之前曾见过某个产品的一个列表页,40秒左右才能加载出来,几乎没有进行任何优化措施。 没有索引,没有缓存机制,没有进行sql优化(sql语句很长,并且各种left join表关联)。 数据库优化策略有很多,设计初期,建立好的数据结构对于后期性能优化...
  • u013628152
  • u013628152
  • 2016-07-05 22:24
  • 2226

数据库SQL优化大总结之 百万级数据库优化方案

网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。 这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉好的话推荐给更多的人,让更多的人看到、纠正以及补充。   1.对查询进行优化,要尽量避免全...
  • zhushuai1221
  • zhushuai1221
  • 2016-06-23 09:43
  • 23358

Android 性能优化 (二)数据库优化 秒变大神

1.数据库插入效率  有三种方法: 1)直接拼接SQL语句,执行execSQL方法; 2)借用ContentValues进行插入; 3)使用compileStatement进行插入; 1)直接拼接SQL语句,执行execSQL方法, 一个一个插入 /** * 向表中...
  • WHB20081815
  • WHB20081815
  • 2017-04-12 13:51
  • 748

mysql数据库优化总结

一、MySQL的主要适用场景 1、Web网站系统 2、日志记录系统 3、数据仓库系统 4、嵌入式系统 二、MySQL架构图:   三、MySQL存储引擎概述 1)MyISAM存储引擎 MyISAM存储引擎的表在数据库中,每一个表...
  • samjustin1
  • samjustin1
  • 2016-08-15 16:33
  • 9168

数据库的优化-应付面试

在数据仓库项目中,由于数据规模庞大,提高数据的查询效率是永恒的主题,常见的优化手段有: 1、 硬件优化,提高机器性能,增加硬件等; 2、 优化查询语句,将限定性强的where条件放前,用exists代替in操作等; 3、 优化索引,建立有效的索引并检查和修复缺少的统计信息等; 4、 ...
  • m8396017
  • m8396017
  • 2016-05-08 23:57
  • 15164

数据库设计和优化

1.      主键&外键 键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个设计过...
  • kingmicrosoft
  • kingmicrosoft
  • 2014-07-25 11:11
  • 988

mysql数据库优化小结

一,sql语句的优化 1,任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。 2,对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3,应尽量避免在 where 子句中对字段进行 nul...
  • shuaishuai123485615
  • shuaishuai123485615
  • 2017-02-23 11:28
  • 200
    个人资料
    • 访问:567108次
    • 积分:8623
    • 等级:
    • 排名:第2642名
    • 原创:316篇
    • 转载:125篇
    • 译文:0篇
    • 评论:85条
    最新评论