RE: Question on concurrency wait time
Date: Sun, 23 Oct 2022 09:30:39 -0400
Message-ID: <0a7001d8e6e3$a4cc80a0$ee6581e0$_at_rsiz.com>
What is the texture of status?
Is part_date_time the date of a partition?
Read up on the negative side effects and so forth on reverse indexes. IF they don’t apply, possibly that is a solution.
Are there multiple insert jobs firing simultaneously?
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap
Sent: Sunday, October 23, 2022 8:33 AM
To: Oracle L
Subject: Question on concurrency wait time
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-lReceived on Sun Oct 23 2022 - 15:30:39 CEST