Skip to main content

Hi,

Whenever we ran the AWR report for the database server . The following below query always appear in the report on daily basis consuming more than 99% cpu on the database server .

insert into MX_U_S (MX_U_S_01, MX_U_S_02, MX_U_S_03, TOTAL, CREATED_DTTM) select MX_U_01, MX_U_02, MX_U_03, count(1), sysdate from MX_U where (MX_U_06 = 'START' or MX_U_06 is null) and (created_dttm is null or created_dttm <= to_date('2022-01-05T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')) group by MX_U_01, MX_U_02, MX_U_03

 

DELETE FROM MX_U where (created_dttm is null or created_dttm <= to_date('2021-10-13T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'))

Need you support on to reduce the utilization of the above query on the database server?

 

 

Hi @TatKhandM , 

What you noticed is for me a standard behavior from the system.
It could be that one of your existing BR or internal process is triggering a lot this standard behavior. 

Do you know what is triggering this massive usage of CPU? 


Regards,

Clement 
 

Hi Clement,

How can we find which BR and APP Parameters is triggering this standard behavior?

 

Regards,

Murali

 

 

Hi @TatKhandM , 

What I mean is that it is probably a custom BR, custom mpm, etc which is triggering standard code. 

For example if you have a lot of after commit custom BR this might happened , you will create parallelism (multiple threads will be executed at the same time) . This most of the time increase CPU usage. 

In my opinion you should investigate in the system which object is triggering this performance issue. 

Good practice for me is to try to reproduce the issue in your DEV or TEST environment. 
If you can reproduce the issue, then you should activate the server logs and start the trigger of the performance problem at a certain timestamp. 

Once the server logs are downloaded,  you could track inside the different threads at the same timestamp. This should help you to find the origin of the problem.

As far as I remember you will not be able to see the after commit into the server logs.

Therefore, I will advice you to check the server logs and if you get the timestamp around the start time of the performance issue, you will be able to identify the trouble maker table_name  (object). 

You can check in the list of custom BR if there are after commit BR attached to the same table_name. 
After that you will need to further investigate, to fix the BR. 

other possibilities could be a bad view is called in BR, etc. 


Regards,

Clement 

 

Terms & ConditionsCookie settings