Re: Connection spike resmgr:internal state change

Hi Mani,

That’s not just an average wait time, that’s the only wait time that gets hit for this.

I’ve raised this before with support and apparently it’s expected behaviour. Every 20 minutes, every active session is required to wait 0.1 seconds on this event so that the system can get an accurate idea of cpu load.

After a lot of back and forth with support, I was told an Enhancement Request has been logged to take a look at this but I don’t expect any movement on this. The ER is 30967101 and hasn’t had movement since it was raised 2 years ago.

Maybe this thread is exactly what’s need to get eyes on it though.

Sorry I can’t be of more help,
Andy

On Sat, Sep 3, 2022 at 4:04 PM, manikandan <pvmanikandan_at_gmail.com> wrote:

> Hi,
>
>
>
> Env :- AIX P9 , Oracle RAC 19.14 (Upgraded to 19c from 12.1.0.2 a month
> back) – 5 nodes
>
>
>
> We are observing a moderate spike on application and during that time from
> ASH, we see that the sessions are waiting on event “resmgr:internal state
> change” for an avg of 100ms on Node 3. This behavior is happening on any
> one of the 5 nodes at a time. Please let me know in which situations we
> see this behavior and how to get rid of it.
>
>
>
> *SAMPLETIME*
>
> *INST_ID*
>
> *EVENT*
>
> *COUNT(*)*
>
> *AVGMS*
>
> 2022-09-02 10:10:12
>
> 3
>
> resmgr:internal state change
>
> 165
>
> 100
>
>
>
> From DBRM trace
>
>
>
> *** 2022-09-02T10:07:10.062162-04:00
>
> s: 1 740002 0 31.75 0.00 0
>
> t: [0] rng=3 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [1] rng=2 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [2] rng=2 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [3] rng=3 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [4] rng=2 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [5] rng=1 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [6] rng=4 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [7] rng=6 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [8] rng=3 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [9] rng=3 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> t: [10] rng=3 rbl=0 lowt=48 uadj=12 madj=0 hid=12 dyn=[60-72]
>
> r: 51024157 20943 25970 1 0 1 0
>
> kgsksysstop: successful; Total stall time = 0 ms.
>
>
>
> *** 2022-09-02T10:12:10.761588-04:00
>
> s: 0 800992 0 35.30 0.00 0
>
>
>
> We haven’t made any changes to resource plan.
>
>
>
> SQL> sho parameter resource_manager_plan
>
>
>
> NAME TYPE VALUE
>
> ------------------------------------ -----------
> ------------------------------
>
> resource_manager_plan string DEFAULT_PLAN
>
>
>
> SQL> select name, cpu_managed from v$rsrc_plan where name = 'DEFAULT_PLAN';
>
>
>
> NAME
> CPU
>
>
> -------------------------------- ---
>
>
> DEFAULT_PLAN
> ON
>
>
>
>
> SQL> select group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3, mgmt_p4, mgmt_p5,
> mgmt_p6, mgmt_p7, mgmt_p8, max_utilization_limit from
> dba_rsrc_plan_directives where plan = (select name from v$rsrc_plan where
> name = 'DEFAULT_PLAN');
>
>
>
> GROUP_OR_SUBPLAN MGMT_P1 MGMT_P2 MGMT_P3
> MGMT_P4 MGMT_P5 MGMT_P6 MGMT_P7 MGMT_P8
> MAX_UTILIZATION_LIMIT
>
>
> ------------------------------ ---------- ---------- ---------- ----------
> ---------- ---------- ---------- ----------
> ---------------------
>
>
> SYS_GROUP 90 0 0
> 0 0 0 0
> 0
>
>
> OTHER_GROUPS 9 0 0
> 0 0 0 0 0
>
>
> ORA$AUTOTASK 1 0 0
> 0 0 0 0 0
> 90
>
>
>
> *OWNER*
>
> *WINDOW_NAME*
>
> *RESOURCE_PLAN*
>
> *REPEAT_INTERVAL*
>
> *DURATION*
>
> *WINDOW_PRIORITY*
>
> *NEXT_START_DATE*
>
> *LAST_START_DATE*
>
> *ENABLED*
>
> *ACTIVE*
>
> SYS
>
> WEEKNIGHT_WINDOW
>
> DEFAULT_MAINTENANCE_PLAN
>
> freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
>
> +00 08:00:00.000000
>
> LOW
>
> 4/18/2014 10:00:00.500000 PM -04:00
>
> 4/17/2014 10:00:00.698771 PM -04:00
>
> FALSE
>
> FALSE
>
> SYS
>
> WEEKEND_WINDOW
>
> DEFAULT_MAINTENANCE_PLAN
>
> freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
>
> +02 00:00:00.000000
>
> LOW
>
> 4/19/2014 12:00:00.000000 AM -04:00
>
> 4/12/2014 6:00:05.593192 AM -04:00
>
> FALSE
>
> FALSE
>
> SYS
>
> MONDAY_WINDOW
>
> freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0
>
> +00 04:00:00.000000
>
> LOW
>
> 9/5/2022 10:00:00.000000 PM -04:00
>
> 8/29/2022 10:00:00.002518 PM -04:00
>
> TRUE
>
> FALSE
>
> SYS
>
> TUESDAY_WINDOW
>
> freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0
>
> +00 04:00:00.000000
>
> LOW
>
> 9/6/2022 10:00:00.000000 PM -04:00
>
> 8/30/2022 10:00:00.041928 PM -04:00
>
> TRUE
>
> FALSE
>
> SYS
>
> WEDNESDAY_WINDOW
>
> freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0
>
> +00 04:00:00.000000
>
> LOW
>
> 9/7/2022 10:00:00.000000 PM -04:00
>
> 8/31/2022 10:00:00.042308 PM -04:00
>
> TRUE
>
> FALSE
>
> SYS
>
> THURSDAY_WINDOW
>
> freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0
>
> +00 04:00:00.000000
>
> LOW
>
> 9/8/2022 10:00:00.000000 PM -04:00
>
> 9/1/2022 10:00:00.026817 PM -04:00
>
> TRUE
>
> FALSE
>
> SYS
>
> FRIDAY_WINDOW
>
> freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0
>
> +00 04:00:00.000000
>
> LOW
>
> 9/2/2022 10:00:00.000000 PM -04:00
>
> 8/26/2022 10:00:00.000553 PM -04:00
>
> TRUE
>
> FALSE
>
> SYS
>
> SATURDAY_WINDOW
>
> freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0
>
> +00 20:00:00.000000
>
> LOW
>
> 9/3/2022 6:00:00.000000 AM -04:00
>
> 8/27/2022 6:00:00.026665 AM -04:00
>
> TRUE
>
> FALSE
>
> SYS
>
> SUNDAY_WINDOW
>
> freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0
>
> +00 20:00:00.000000
>
> LOW
>
> 9/4/2022 6:00:00.000000 AM -04:00
>
> 8/28/2022 6:00:00.030427 AM -04:00
>
> TRUE
>
> FALSE
>
>
>
>
>
> Thanks,
>
> Mani
>

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