关闭

数据库优化

270人阅读 评论(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网站的观点或立场
    个人资料
    • 访问:310800次
    • 积分:6043
    • 等级:
    • 排名:第4152名
    • 原创:292篇
    • 转载:124篇
    • 译文:0篇
    • 评论:51条
    最新评论