Question

Database Performance issues by using Oracle parallel query concept

  • 21 March 2022
  • 4 replies
  • 1420 views
AUIFENNW
Rank
Badge +3

Hi,

we are running IFSAPP10 on a Oracle 19c Enterprise Edition Database, using Oracle PDB concept.

There, we ran into severe issues while people just have been trying to populate one single Customer Order. While observing the database sessions, we noticed that the query has been parallelized, divided into several sessions. Strangely, these sessions sometimes have been blocking each other. As a summary, instead of improving the performance with parallelism, it caused a severe performance drop.

To solve it, we switched off parallel query mechanism by setting Oracle parameter “parallel_max_servers” to 1 (was 400).

Is there a recommendation from IFS how these parameters “parallel_max_servers” and “parallel_min_servers” should be set? Or, more generally spoken, how parallel query feature should be set up together with IFS Applications?

Please also share your experience with Oracle parallel query mechanism, and with which settings it runs best for you.

 

Thank you!

Wolfgang

 

Wolfgang
This topic has been closed for comments

4 replies

Rusiru Dharmadasa
Rank
Userlevel 7
Badge +19

@Anushka Kumar @Kasun Alahakoon Do you have any input on this or someone who might know about this? 

Rusiru
S
Rank
Userlevel 1
Badge +4

For additional information. I see that this setting is mentioned in post

but there is no clear Information if change in setting make a difference. Also it do not mention about lock situation as this customer experiencing.

K
Rank
Userlevel 5
Badge +9

Hi Wolfgang @AUIFENNW,

From my experience, these parameters are derived from other factors in DB such as CPU counts, threads, etc. Parallel execution for application queries isn't commonly used. Meaning, for large queries it’s beneficial and can be enforced from application logic, aka SQL parallel hints. Unless specified, DB will decide whether or not to execute parallelly. And as I’m aware there’s no recommendation for these parameters from the application perspective.

  • For optimal values for these parameters, it’s better to consult with your DBA/Oracle support if necessary.
  • Do you see this behavior only for customer order related queries? or are there other examples? If it’s a large query, check if parallel hints were used in SQL text
~Kasun A.
AUIFENNW
Rank
Badge +3

Hi Kasun @Kasun Alahakoon, 

thank you very much for your quick and detailed answer.

Does this mean IFS does not recommend to enable parallel query execution by default?

I agree that this makes sense in almost every scenario, since most times we only want to fetch a small portion of data.

This also means, that in other scenarios, e. g. where we need to create big reports, individual SQL statements might need to be tuned by introducing parallel query hints. And IFS will adhere to that as well, and might introduce query hints via bugfixes, if that was identified to be the best solution to solve a reported performance issue?

 

Best Regards,

Wolfgang

Wolfgang
Terms & ConditionsCookie settings