数据库优化

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

01SQL> 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 

2721 rows selected.

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

01SQL> select sql_text from v$sqltext where sql_id='dfftdnm7cu76f' order by piece

02 

03SQL_TEXT

04---------------------------------------------------------------------------------------------------------

05select *

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 

4324 rows selected.

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

01SQL> show parameter cursor

02 

03NAME                                 TYPE        VALUE

04------------------------------------ ----------- ------------------------------

05cursor_bind_capture_destination      string      memory+disk

06cursor_sharing                       string      SIMILAR

07cursor_space_for_time                boolean     FALSE

08open_cursors                         integer     3000

09session_cached_cursors               integer     50

10SQL> alter system set cursor_sharing=SIMILAR sid='*';

11 

12System altered.

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

01SQL> 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 

2721 rows selected.

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

1SQL> show parameter block

2 

3NAME                                 TYPE        VALUE

4------------------------------------ ----------- ------------------------------

5db_block_buffers                     integer     0

6db_block_checking                    string      FALSE

7db_block_checksum                    string      TYPICAL

8db_block_size                        integer     4096

9db_file_multiblock_read_count        integer     136

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

01SQL> 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 

11OWNER                          OBJECT_NAME                              OBJECT_TYPE          TOUCHTIME

12------------------------------ ---------------------------------------- ------------------- ----------

13PRESSO                         BIZ_REPORTER_BASE_INFO                   TABLE                  3691280

14PRESSO                         BIZ_REPORTER_EDU_INFO                    TABLE                  3547004

15PRESSO                         BIZ_REPORTER_ORG_INFO                    TABLE                  2273524

16PRESSO                         BIZ_PRESSCARD_LOGOUT                     TABLE                  2099499

17PRESSO                         BIZ_REPORTER_INFO                        TABLE                  1619598

18PRESSO                         BIZ_PRESSCARD_APPLICATION                TABLE                  1191751

19PRESSO                         BIZ_SYSTEM_MESSAGE_INFO                  TABLE                   730829

20PRESSO                         BIZ_REPORTER_EXTEND_INFO                 TABLE                   610540

21SYS                            WRH$_SQLSTAT                             TABLE PARTITION         193465

22PRESSO                         IDX_REPORTER_UUID                        INDEX                   190901

23 

2410 rows selected.

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

01SQL> ALTER TABLE PRESSO.BIZ_REPORTER_BASE_INFO PCTFREE 30;

02 

03Table altered.

04 

05SQL> ALTER TABLE PRESSO.BIZ_REPORTER_EDU_INFO PCTFREE 30;

06 

07Table altered.

08 

09SQL> ALTER TABLE PRESSO.BIZ_REPORTER_ORG_INFO PCTFREE 30;

10 

11Table altered.

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

1Setting Resource Manager plan SCHEDULER[0x32DB]:DEFAULT_MAINTENANCE_PLAN via scheduler window

2Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

3Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"

4End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"

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

01SQL> alter system set resource_manager_plan='' sid='*';

02 

03System altered.

04 

05SQL> execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');

06 

07PL/SQL procedure successfully completed.

08 

09SQL> execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

10 

11PL/SQL procedure successfully completed.

12 

13SQL> execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');

14 

15PL/SQL procedure successfully completed.

16 

17SQL> execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');

18 

19PL/SQL procedure successfully completed.

20 

21SQL> execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');

22 

23PL/SQL procedure successfully completed.

24 

25SQL> execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');

26 

27PL/SQL procedure successfully completed.

28 

29SQL> execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');

30 

31PL/SQL procedure successfully completed.

32 

33SQL> execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');

34 

35PL/SQL procedure successfully completed.

36 

37SQL> 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 

45PL/SQL procedure successfully completed.

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

01SQL> 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, ' _', ' ')

15SQL> /

16 

17NAME                                                                             VALUE      ISDEFAULT

18-------------------------------------------------------------------------------- ---------- ---------

19_client_result_cache_bypass                                                      FALSE      TRUE

20client_result_cache_lag                                                          3000       TRUE

21client_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

35result_cache_max_result                                                          100        TRUE

36result_cache_max_size                                                            2147483648 TRUE

37result_cache_mode                                                                FORCE      TRUE

38result_cache_remote_expiration                                                   0          TRUE

39_result_cache_timeout                                                            10         TRUE

40 

4121 rows selected.

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

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

01SQL> alter profile "DEFAULT" limit CONNECT_TIME 15;

02 

03Profile altered.

04 

05SQL> select * from dba_profiles;

06 

07PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

08------------------------------ -------------------------------- -------- ----------------------------------------

09DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED

10DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED

11DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED

12DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED

13DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED

14DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED

15DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED

16DEFAULT                        CONNECT_TIME                     KERNEL   15

17DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED

18DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED

19DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

20DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

21DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

22DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

23DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED

24DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

25 

2616 rows selected.

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