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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
