Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alok.

Asked: January 13, 2022 - 9:28 pm UTC

Last updated: January 19, 2022 - 12:58 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi Tom,

We are supporting a Siebel application having Oracle 11g as backed. Last month we upgraded database to 19C and after that we are facing performance issues on daily basis. When we checked DB reports like AWR and ASH, I have below observations:

a) In AWR report random queries are coming on top and there are no consistency so I am assuming issue is not related to a particular SQL.

b) In the DB reports we can see top event is "gc buffer busy acquire" most of the time and this event is related to a insert statement.

c) All top SQL visible in AWR/ASH reports are having multiple plans. Below is the snap shot for one of the SQL:

SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ --------------
2459 2 12-JAN-22 06.00.34.887 4hdhq06bahcnq 912142910 207 17.761 .0
2460 1 12-JAN-22 06.30.10.663 4hdhq06bahcnq 728991164 293 .024 1,376.6
2460 2 12-JAN-22 06.30.10.670 4hdhq06bahcnq 912142910 107 95.319 2,889,181.3
2461 1 12-JAN-22 07.00.04.996 4hdhq06bahcnq 728991164 439 .035 1,251.3
2461 2 12-JAN-22 07.00.04.990 4hdhq06bahcnq 912142910 149 66.469 2,106,097.7

My question from you guys are:

1. To investigate "gc buffer busy acquire" event what could be the sequence of investigation?

2. Since most of the SQLs coming on top in DB reports are generating multiple plans, Do we have any parameters in 19C which can be responsible for so many plans?

Thanks

and Connor said...

1. To investigate "gc buffer busy acquire" event what could be the sequence of investigation?


"gc buffer busy acquire" is when a session in the current instance is forced to wait on the completion of a GC operation by another session in the same instance. So sessions waiting for "gc buffer busy acquire" are often not the problem, they are the "victim". Check the blocking_session column on the local instance, or use the p1/p2/p3 parameter in the wait information to see what other session(s) might be causing the issue. (They'll often have a different GC related wait which helps get to the root cause).

2. Since most of the SQLs coming on top in DB reports are generating multiple plans,

Check these parameters - true/false are the defaults, but if you have both true, then you'll get more aggressive adaptive plans

optimizer_adaptive_plans TRUE
optimizer_adaptive_statistics FALSE

Also check v$sql for the following columns for those statements with multiple plans

IS_BIND_SENSITIVE
IS_BIND_AWARE
SQL_PROFILE
SQL_PATCH
SQL_PLAN_BASELINE

That will give some insight as to potential causes of multiple plans.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

gc buffer busy acquire

A reader, January 18, 2022 - 5:25 pm UTC

Thanks for coming back.

I am clear on point 2 and thanks for sharing those parameters.

But for point one, can I ask some more questions:

1. When we checked the ASH report, top DB reports section, its pointing to Index related to inserts statements:

Event % Event Object Name (Type)
gc buffer busy release 6.25 SIEBEL.S_AUDIT_READ_P1
gc buffer busy release 2.79 SIEBEL.S_AUDIT_READ_M4

what does it mean?

2. Also top blocking session in ASH report shows "enq: TX - index contention".

So not sure what to check further.

Thanks
Connor McDonald
January 19, 2022 - 12:58 am UTC

A possibility is that you have an index with an ascending key (sequence, timestamp etc) and thus multiple inserts are competing for the "leading" leaf block.

You can attack this with a few options, generally the most scalable one being partitioning the index. You can see examples of that in the latter half of a video I did on physical design


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.