优化日志_apxzz8ks4hsa1_20201027

zyt

360

9页

0次

2020-10-27

5墨值下载

一、场景和现象：2020-10-26 16；11 开始监控提示活动会话数过多，apxzz8ks4hsa1 这条 sql 存在大

量 direct path read 和 latch: row cache objects 的等待事件，登录查询会话和进一步分析

实例 1 活动会话数过多

详细信息 2020-10-26 16:11

当前系统时间: 2020-10-26 16:11:01

实例 1 活动的会话数已经达到 61

会话数过多

会话信息如下

1139 DBAMONITOR ydjsc-rac1 8f54cj95uwp7r enq: PS - contention

2846 IAUSER ods-lb3 apxzz8ks4hsa1 direct path read

13 IAUSER ods-lb3 apxzz8ks4hsa1 direct path read

1710 IAUSER ods-lb3 apxzz8ks4hsa1 direct path read

1149 IAUSER ods-lb4 apxzz8ks4hsa1 direct path read

2847 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

3410 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

1709 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

11 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

1707 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

1146 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

583 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

581 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

3407 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

2279 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

14 IAUSER ods-lb1 apxzz8ks4hsa1 latch: row cache objects

3411 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

3977 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

1711 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

571 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

573 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

575 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

5 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

15 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

3408 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

1148 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

1140 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

3963 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

3979 IAUSER ods-lb2 apxzz8ks4hsa1 latch: row cache objects

2842 IAUSER ods-lb3 apxzz8ks4hsa1 latch: row cache objects

576 IAUSER ods-lb3 apxzz8ks4hsa1 latch: row cache objects

2273 IAUSER ods-lb3 apxzz8ks4hsa1 latch: row cache objects

582 IAUSER ods-lb3 apxzz8ks4hsa1 latch: row cache objects

9 IAUSER ods-lb3 apxzz8ks4hsa1 latch: row cache objects

2849 IAUSER ods-lb3 apxzz8ks4hsa1 latch: row cache objects

16 IAUSER ods-lb3 apxzz8ks4hsa1 latch: row cache objects

3974 IAUSER ods-lb3 apxzz8ks4hsa1 latch: row cache objects

2841 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

2840 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

2280 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

2275 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

1708 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

1702 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

1147 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

1144 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

579 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

6 IAUSER ods-lb4 apxzz8ks4hsa1 latch: row cache objects

2848 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

2845 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

2278 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

2277 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

3409 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

2276 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

1145 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

3414 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

3415 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

1141 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

3972 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

584 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

10 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

3978 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

2844 IAUSER ods-lb5 apxzz8ks4hsa1 latch: row cache objects

二、问题分析

2.之前没有提示，查询执行计划是否改变：执行计划没有改变，看执行的时间，大概是当天新上的 sql。

set lines 155

col execs for 999,999,999

col avg_etime for 999,999.999

col avg_lio for 999,999,999.9

col stime for a30

col node for 99999

col sql_id for a13

--break on plan_hash_value on startup_time skip 1

select ss.snap_id, ss.instance_number node,

to_char(begin_interval_time,'yy-mm-dd hh24:mi:ss') stime,

sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

round((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/

1000000,3) avg_etime_s,

round((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)))

avg_lio

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id = nvl('&sql_id','4dqs2k5tynk61')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

order by 1, 2, 3;

SNAP_ID NODE STIME SQL_ID PLAN_HASH_VALUE

EXECS AVG_ETIME_S AVG_LIO

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

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

25917 1 20-10-26 10:00:02 apxzz8ks4hsa1 2713976478

63 590.307 13,754,557.0

25918 1 20-10-26 11:00:07 apxzz8ks4hsa1 2713976478

59 1781.767 28,610,350.0

25919 1 20-10-26 12:00:16 apxzz8ks4hsa1 2713976478

51 1662.491 28,073,282.0

25920 1 20-10-26 13:00:25 apxzz8ks4hsa1 2713976478

58 1379.418 .0

25921 1 20-10-26 14:00:32 apxzz8ks4hsa1 2713976478

51 2314.631 29,102,589.0

25922 1 20-10-26 15:00:39 apxzz8ks4hsa1 2713976478

60 2651.643 25,955,822.0

6 rows selected.

3.查看执行计划:发现是一个简单的单表查询，走的是全表扫表.这张表有 150g，并查看过滤条件列并没有索

引。

select * from table(dbms_xplan.display_cursor('2388715707',1,'advanced'))

new 1: select * from

table(dbms_xplan.display_cursor('apxzz8ks4hsa1',1,'advanced'))

PLAN_TABLE_OUTPUT

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

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

SQL_ID apxzz8ks4hsa1, child number 1

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

SELECT :1 INSURED_ID, RISKCODE,

RISKNAME, AMNT,

COPREM, RISKTYPE FROM IAVD_PER_PRODUCT T

WHERE Insured_Id = :2 and POLICY_ID = :3

Plan hash value: 2713976478

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

------

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

------

| 0 | SELECT STATEMENT | | | | 4095K(100) |

|* 1 | TABLE ACCESS FULL| IAVD_PER_PRODUCT | 22989 | 6106K| 4095K (1)|

13:39:01 |

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

------

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / T@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "T"@"SEL$1")

END_OUTLINE_DATA

Peeked Binds (identified by position):

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

2 - :2 (VARCHAR2(30), CSID=873): '1338031883'

3 - :3 (VARCHAR2(30), CSID=873): '605967572'

Predicate Information (identified by operation id):

of 9

5墨值下载

oracle

相关文档

评论