Question on concurrency wait time

From: Pap <oracle.developer35_at_gmail.com>
Date: Sun, 23 Oct 2022 18:02:51 +0530
Message-ID: <CAEjw_fh6EhwngofjYeukUbipurEZuG3rX2tDsq=UPHt5qyXg=A_at_mail.gmail.com>

Hello Listers,
We have a customer database on version 19.15. We are experiencing high concurrency waits(Buffer busy waits) for one of the INSERT query and the object its pointing to in ASH is the primary key composite index which is on three columns(Unix_time_id,status,part_date_time) followed by other one which is on one column i.e create_date column. Both of these two indexes are local indexes. And the table is a weekly range partition on the date column (part_date_time which is populated by sysdate value from application).

Below is the output from the Tanel's DASH_wait_chain query from the issue period. This spike in concurrency happens for 2-3minutes(even less time duration in many occasions) impacting one of the critical latency sensitive jobs. Our understanding was , as the first column of the primary key index is generated from application code as a unix timestamp(defined as VARCHAR2(40) data type) and is mostly unique, so the contention should be minimal. For a specific time period, the values of the first column - Unix_time_id looks like below i.e even different but the first 7 to 8 characters are the same. So can it be the cause of concurrency here and if yes, how can we avoid it?

60D2B1EE6D0B99ECCE3B3558DD4AADB5BFA0539F 60D2B1EE02489AC1FA88E80491AEE64E370B53EC %This SECONDS AAS DISTINCT_SIDS WAIT_CHAIN FIRST_SEEN LAST_SEEN 28% 1380 -7.7 53 -> USER1:(PRG1) ON CPU 10/21/2022 5:43 10/21/2022 5:45 20% 969 -5.4 52 -> USER1:(PRG2) buffer busy waits [data block] -> USER1:(PRG2) block change tracking buffer space 10/21/2022 5:44 10/21/2022 5:45
10% 510 -2.8 44 -> USER1:(PRG1) block change tracking buffer space 10/21/2022 5:44 10/21/2022 5:45
8% 414 -2.3 142 -> USER1:(PRG2) ON CPU 10/21/2022 5:43 10/21/2022 5:45 4% 196 -1.1 111 -> USER1:(PRG2) buffer busy waits [data block] 10/21/2022 5:44 10/21/2022 5:45
4% 195 -1.1 114 -> USER1:(PRG2) block change tracking buffer space 10/21/2022 5:44 10/21/2022 5:45
4% 182 -1 46 -> USER1:(PRG1) cell single block physical read: pmem cache 10/21/2022
5:43 10/21/2022 5:45
4% 176 -1 5 -> USER2:(JDBC Thin Client) ON CPU 10/21/2022 5:43 10/21/2022 5:45
3% 145 -0.8 7 -> USER1:(PRG2) buffer busy waits [data block] -> USER1:(PRG2) buffer busy waits [data block] -> USER1:(PRG2) block change tracking buffer space 10/21/2022 5:44 10/21/2022 5:45 2% 86 -0.5 2 -> DBSNMP:(oracle) ON CPU 10/21/2022 5:44 10/21/2022 5:45 2% 80 -0.4 61 -> USER1:(PRG2) cell smart table scan 10/21/2022 5:43 10/21/2022
5:45
1% 64 -0.4 21 -> USER1:(PRG1) buffer busy waits [data block] -> USER1:(PRG1) block change tracking buffer space 10/21/2022 5:44 10/21/2022 5:45
1% 36 -0.2 27 -> USER1:(PRG2) reliable message 10/21/2022 5:43 10/21/2022 5:45
1% 35 -0.2 20 -> USER1:(PRG1) cell single block physical read: RDMA 10/21/2022
5:43 10/21/2022 5:45
1% 32 -0.2 22 -> USER1:(PRG1) library cache: mutex X 10/21/2022 5:43 10/21/2022
5:45
1% 32 -0.2 29 -> USER1:(PRG2) direct path read 10/21/2022 5:43 10/21/2022 5:45

The table ,column and index stats are as below. The table holds ~68 weekly partitions.

INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY
IDX_PK NORMAL 3 6889551.000000000000000 5336629508 3560601121 5336629508 1 INDEX_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND

IDX_PK Unix_time_id 1 40 40 ASC
IDX_PK status 2 22 0 ASC
IDX_PK part_date_time 3 7 0 ASC

INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
IDX_CREATE_DT NORMAL 3 31579617.000000000000000 41199260 1169996447 5336609043

INDEX_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND DESCEND
IDX_CREATE_DT CREATE_DATE 1 7 0 ASC ASC COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS HISTOGRAM Unix_time_id 3029480675 0.000000000330090 0 1 NONE status 1997 0.000208000000000 0 6 HYBRID part_date_time 41140472 0.000000024306965 0 1 NONE create_date 41199260 0.000000024272281 0 1 NONE

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
TAB 5336536528 231402068 252.000000000000000

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Oct 23 2022 - 14:32:51 CEST