Re: Question on concurrency wait time

Hi,

From your columns' names and datatypes in the index I suppose that new rows go to the same index blocks because they have the same insert ordering as your index. You can try to use subpartioning by hash (Unix_time_id, status) to avoid concurrency for the same index blocks.

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE
http://orasql.org

On Sun, 23 Oct 2022, 13:33 Pap, <oracle.developer35_at_gmail.com> wrote:

> 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