Re: Question on concurrency wait time

Thank You Jonathan.

Regarding the strategic fix, we will check with DEV team on this unix_time_id column. However this partition key part_date_time is already used in many queries. So making it virtual hopefully wont ask us to change the code. But yes, we need to test this properly. Also, currently we don't see a cpu spike, but as you mentioned ctwr process constrained by cpu cycle can cause this bct buffer space waits, so we have to think of limiting the overall workload during that point in time.

Out of curiosity , Say for the other local index which on one column "create_date" still shows up as second top in the buffer busy waits, if we have to think of a strategy to eliminate the index contention in general for an index, would hash partitioning is the only option at hand which Sayan suggested? And again for that , do we have to endup subpartitioning the whole table and there by subpartitioning all other local indexes with that same key?

On Tue, 25 Oct, 2022, 12:43 am Jonathan Lewis, <jlewisoracle_at_gmail.com> wrote:

> I am a little curious about the "unix_time_id". It looks like 4 bytes for
> the number of seconds since 1st Jan 1970 followed by 16 bytes (maybe a
> digest of the row) stored as a character string.
>
> Two things (for strategic planning - this is nothing to do with the
> performance glitch).
> a) Why not store it as a RAW and save 20 bytes per row and 20 bytes per
> index entry.
> b) Is the unix time component the same as the part_date_time (with a
> deterministic adjustment, perhaps, for daylight savings time). If so could
> you do something to make the partition key a virtual column and eliminate
> on column from the index.
>
> Regards
> Jonathan Lewis
>
>
> P.S.
> While I agree with Stefan's analysis of the wait chains, there's one
> further layer to consider.
> You said: "but we see the concurrency during the time when there is heavy
> activity happening on the database from another app."
> The overrun on the change tracking buffer MIGHT be the consequence of the
> CTWR process having to wait to get on the run queue because of CPU overload
> due to the other application. While increasing the buffer size may reduce
> the risk of over-run it might be more important to choke the other app so
> that it can't cause the problem,
>
>

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