performance issues

[raiq1]

Dear Erman sir,

I have some performance issues in prod database, I have 19c database with 12.1.3 ebs application.

Below are the details of the problem.

a) Once concurrent report are run on daily basis very frequently , some time same report with one org_id is finish with in 1 minute 10 second but different org id takes 3 to 4 minutes, users are complaining to fix this issue.

b) some monthly job are running individually are taken 20 to 30 minute but when the same report are running with multiple with 10 session are running simultaneously , all system are very slow and other jobs are taken time to finish.

c)  I have generated a awr report for your review, just check and guide me to fix it , there is CPU utilization are high.

d)  MTL_MATERIAL_TRANSACTIONS are showing high in Segments by Direct Physical Reads , kindly check the awr report and advice.


Regards,
shahzadawrrpt_30oct_2pm_3pm.html

[ErmanArslansOracleBlog]

You got 16 cores and you are utilizing a significant percentage of them. But it is not a hang situation.
Your main waits are on CPU, and I/O.. You are doing work.
Index read waits are high, but it seems your IO Subsystem is doing okay..

rwrun.. those reports should be tuned. The ones with the sqlids -> 9ks8h2k7sw172, gru84zkj370md
Check your custom code ->  XXHADDAD_PERSONALIZATION_PKG, sqlid 7w5v6kfbc752s
What is that sqlservr.exe doing there? Check the associated query .. (6nuu3b0mjrjr4) It is executed frequently, increasing the load.

See the end of the AWR report.
You got sql tuning recommendations under the title ADDM Task ADDM:3107712059_1_11453 for those relevant SQLs. Consider the recommendation given there.

[raiq1]

Dear Erman sir,

Thanks for your suggestion , few queries are running base on concurrent report are frequently basis since long time. I check with my developer as per your suggestion and recommendation , he said most of the concurrent program are running since years, he put all the things on my shoulder that you did database upgrade from 11g to 19c , you check your system not a coding issue.


I don't know how to fix it .

Morever if you see the awr report they is below .

Segments by Physical Reads

 MTL_MATERIAL_TRANSACTIONS are 64 gb SIZE, can we rebuild the index for this table. Most of the concurrent program are using this table.

Kindly advice.

Regards,
Raiq.

[ErmanArslansOracleBlog]

This may be due to some SQL plans, which might be changed after 19C upgrade..
You need to work with your developers to make those run as they should. Especially for the custom code. Your developers are responsible too. This is an upgrade and your query optimizer changed.
Actually you should have tested all these before doing such an upgrade.

Currently, your performance issue is generic and broad. Hands-on work is needed for fixing your issue.. It requires analysis, and actions.
If you need consultancy, contact our sales team -> [e-mail]

Still, see my latest update.. The latest line ->

--See the end of the AWR report.
--You got sql tuning recommendations under the title ADDM Task ADDM:3107712059_1_11453 for those relevant SQLs. Consider the recommendation given there.