Performance Optimization — ash of Oracle Database

Time:2021-3-20

Active session history (ash) view and real table are valuable tuning tools provided by ORALCE database.

Ash provides two fine-grained Views:V$ACTIVE_SESSION_HISTORYandDBA_HIST_ACTIVE_SESS_HISTORYV$ACTIVE_SESSION_HISTORYIs a dynamic view sampled once a second,DBA_HIST_ACTIVE_SESS_HISTORYIs 10 seconds 1 sampling, stored in the real table (WRH)$_ ACTIVE_ SESSION_ Activity session history data in history.

For the sampling logic and associated process of ash data, please refer to the following.

Performance Optimization -- ash of Oracle Database

Let me use a simple example to illustrate how to use ash data to investigate the problem of high CPU utilization.

Customer problem description

In the 9-node RAC environment, one node has high CPU utilization and the following standby events in the following time periods.

[day time]
2020/12/01 
①12:00 ~ 13:00
②19:00 ~ 21:00
③22:00 ~ 23:00

Standby event
latch: row cache objects
enq: US - contention
enq: SQ - contention

In order to confirm the processing content of “on CPU” in the first time period (12:00-13:00), I checked the “session” of ash data_ The output of “state =’on CPU ‘”.

SQL> select substr(to_char(SAMPLE_TIME,'yyyy/mm/dd hh24:mi'),1,15) aa,SESSION_STATE,SQL_OPNAME,PROGRAM,count(*)
from m_dba_hist_active_sess_history
where INSTANCE_NUMBER=5
and SESSION_STATE='ON CPU'
and to_char(SAMPLE_TIME,'yyyy/mm/dd hh24')='2020/12/01 12'
group by substr(to_char(SAMPLE_TIME,'yyyy/mm/dd hh24:mi'),1,15),SESSION_STATE,SQL_OPNAME,PROGRAM
order by substr(to_char(SAMPLE_TIME,'yyyy/mm/dd hh24:mi'),1,15);
  2    3    4    5    6    7
AA                             SESSION_STATE        SQL_OPNAME      PROGRAM                          COUNT(*)
------------------------------ -------------------- --------------- ------------------------------ ----------
2020/12/01 12:0                ON CPU               INSERT          JDBC Thin Client                        6
2020/12/01 12:0                ON CPU               SELECT          JDBC Thin Client                      232
2020/12/01 12:0                ON CPU               SELECT          [email protected] (PZ99)                  1
2020/12/01 12:0                ON CPU               SELECT          [email protected] (TNS V1-V3)             2
2020/12/01 12:0                ON CPU               UPDATE          JDBC Thin Client                        1
2020/12/01 12:0                ON CPU               UPDATE          [email protected] (SMON)                  1
2020/12/01 12:0                ON CPU                               JDBC Thin Client                        8
2020/12/01 12:0                ON CPU                               [email protected] (DIA0)                  1
2020/12/01 12:0                ON CPU                               [email protected] (LCK0)                  1
2020/12/01 12:0                ON CPU                               [email protected] (LMD0)                  1
2020/12/01 12:0                ON CPU                               [email protected] (LMON)                  1
2020/12/01 12:0                ON CPU                               [email protected] (LMS0)                  2
2020/12/01 12:0                ON CPU                               [email protected] (LMS1)                  1
2020/12/01 12:1                ON CPU               INSERT          JDBC Thin Client                       13
2020/12/01 12:1                ON CPU               PL/SQL EXECUTE  JDBC Thin Client                        3
2020/12/01 12:1                ON CPU               SELECT          JDBC Thin Client                      249
2020/12/01 12:1                ON CPU               UPDATE          [email protected] (SMON)                  1
2020/12/01 12:1                ON CPU                               JDBC Thin Client                       10
2020/12/01 12:1                ON CPU                               [email protected] (DIA0)                  1
2020/12/01 12:1                ON CPU                               [email protected] (LCK0)                  1
2020/12/01 12:1                ON CPU                               [email protected] (LGWR)                  3
2020/12/01 12:1                ON CPU                               [email protected] (LMD0)                  1
2020/12/01 12:1                ON CPU                               [email protected] (LMS0)                  5
2020/12/01 12:1                ON CPU                               [email protected] (LMS1)                  2
2020/12/01 12:1                ON CPU                               [email protected] (PSP0)                  1
2020/12/01 12:2                ON CPU               INSERT          JDBC Thin Client                       34
2020/12/01 12:2                ON CPU               SELECT          JDBC Thin Client                      317
2020/12/01 12:2                ON CPU               UPDATE          JDBC Thin Client                        1
2020/12/01 12:2                ON CPU                               JDBC Thin Client                       11
2020/12/01 12:2                ON CPU                               [email protected] (DIA0)                  2
2020/12/01 12:2                ON CPU                               [email protected] (LCK0)                  2
2020/12/01 12:2                ON CPU                               [email protected] (LGWR)                  2
2020/12/01 12:2                ON CPU                               [email protected] (LMS1)                  1
2020/12/01 12:3                ON CPU               INSERT          JDBC Thin Client                       25
2020/12/01 12:3                ON CPU               SELECT          JDBC Thin Client                      335
2020/12/01 12:3                ON CPU               SELECT          [email protected] (PZ99)                  2
2020/12/01 12:3                ON CPU               UPDATE          JDBC Thin Client                        2
2020/12/01 12:3                ON CPU               UPDATE          [email protected] (SMON)                  1
2020/12/01 12:3                ON CPU                               JDBC Thin Client                       12
2020/12/01 12:3                ON CPU                               [email protected] (DBW3)                  1
2020/12/01 12:3                ON CPU                               [email protected] (DIA0)                  2
2020/12/01 12:3                ON CPU                               [email protected] (LGWR)                  3
2020/12/01 12:3                ON CPU                               [email protected] (LMD0)                  1
2020/12/01 12:3                ON CPU                               [email protected] (LMON)                  1
2020/12/01 12:3                ON CPU                               [email protected] (LMS0)                  4
2020/12/01 12:3                ON CPU                               [email protected] (LMS1)                  2
2020/12/01 12:4                ON CPU               INSERT          JDBC Thin Client                       90
2020/12/01 12:4                ON CPU               PL/SQL EXECUTE  JDBC Thin Client                        2
2020/12/01 12:4                ON CPU               SELECT          JDBC Thin Client                      407
2020/12/01 12:4                ON CPU               SELECT          [email protected] (PZ99)                  3
2020/12/01 12:4                ON CPU               SELECT          [email protected] (TNS V1-V3)             1
2020/12/01 12:4                ON CPU               UPDATE          JDBC Thin Client                        5
2020/12/01 12:4                ON CPU               UPDATE          [email protected] (SMON)                  1
2020/12/01 12:4                ON CPU                               JDBC Thin Client                       13
2020/12/01 12:4                ON CPU                               [email protected] (DBW3)                  1
2020/12/01 12:4                ON CPU                               [email protected] (DIA0)                  1
2020/12/01 12:4                ON CPU                               [email protected] (LCK0)                  1
2020/12/01 12:4                ON CPU                               [email protected] (LMS0)                  1
2020/12/01 12:4                ON CPU                               [email protected] (LMS1)                  1
2020/12/01 12:5                ON CPU               INSERT          JDBC Thin Client                      171
2020/12/01 12:5                ON CPU               SELECT          JDBC Thin Client                      424   ★
2020/12/01 12:5                ON CPU               UPDATE          JDBC Thin Client                        2
2020/12/01 12:5                ON CPU                               JDBC Thin Client                        9
2020/12/01 12:5                ON CPU                               [email protected] (DBW1)                  1
2020/12/01 12:5                ON CPU                               [email protected] (DIA0)                  2
2020/12/01 12:5                ON CPU                               [email protected] (LCK0)                  3
2020/12/01 12:5                ON CPU                               [email protected] (LMD0)                  1
2020/12/01 12:5                ON CPU                               [email protected] (LMS0)                  4
2020/12/01 12:5                ON CPU                               [email protected] (LMS1)                  2

The 69 lines are the same.

From the above results, we can see that the number of “select” processing of “program =’jdbc thin client” is relatively large in every 10 minutes. There are usually two reasons for this situation: the same processing is implemented in a large number, or different processing is implemented at the same time. In order to confirm the specific reasons for the use of SQL by the customer:

SQL> select SQL_ID,count(*)
from m_dba_hist_active_sess_history
where INSTANCE_NUMBER=5
and SESSION_STATE='ON CPU'
and PROGRAM='JDBC Thin Client'
and to_char(SAMPLE_TIME,'yyyy/mm/dd hh24')='2020/12/01 12'
group by SQL_ID
order by count(*) desc;

SQL_ID               COUNT(*)
------------------ ----------
7f3a4khpu7n25             164  ★
1fa2urjuhszw0             112  ★
4f72h6sug6vah              75
7au5hjhjz8nd1              68
                           63
gmrv0dahmkhg6              58
0x1wyyvgkwx4g              49
ataahn09x2qr2              47
8mpsb34rxj96w              41
a0wsrytjhnncz              37
4wn22n0t23x8f              28
47ha0qjatpapp              28
2ppp78vj7ac34              28
273srycwymvpy              24
8037vz8n5t4j4              23
d94xfcs7yzwwn              21
19rf604u0dycw              21
6r4216jcwbyp2              21
9dqkydrfrpgm2              21
3d9pv0n2vsfp5              20
b9yjdusam9wp2              19
d9f8yxm8r3k3r              17
7mg7gtjwp644w              17
f7tqxnbcvv2ah              17
aszugq4dt7qcc              17
a97t6fkmquuj1              17
f21uh36n93rzv              16
1dg8hng84mrnc              15
bu51ut3y2ttr2              15
09u5mfdu2dyau              15
19d5qmbdwg028              15
4uf4x7zz96ban              15
2t21r20y0a3k7              15
65m1gs66zhw7z              14
05uya8bx3u382              14
ayknqa5wkd9zp              14
6ytwz7bgkbksc              14
bk3q8xpbr46n8              14
3kf4b3s3bdzaf              13
6z1hf4txv7f8c              13
438r3mjyuht18              12
8c1h7xwtm6gzv              12
fu9y7cmx4wy98              12
fgdtg91uuds65              12
4f96jfgdrwxgf              11
09yxbt27kpbq4              11
bghzspvbjh2vm              10
5xkjqnuqkbg2b              10
6za6t3kv5c7mj              10
fudk908tvjdfy              10

… In brief

From the above results, we can basically confirm that the reason for this problem is that there are too many services published through “JDBC thin client” in a specific period of time, and it is not a bug.

Of course, before reaching this conclusion, we also confirm the following two points:

1. Besides the time period mentioned by the customer, the number of business processes released through "JDBC thin client" is less.
2. In the other two time periods mentioned by the customer, the phenomenon between 12:00 and 13:00 was also confirmed.

Finally, this is just a small example of using ash to analyze performance problems. As the most fine-grained historical data that Oracle database can provide, there are many practical methods. If you want to use these data flexibly, I recommend reading every field of ash data carefully.