Question

List the consuming queries IFS EE10

  • 17 June 2021
  • 5 replies
  • 90 views

Badge +1
  • Do Gooder
  • 3 replies

Hello,

 

if there a way to create a lobby which list queries consuming lot of resources !

 

because Some non-optimized queries can be resource-intensive and also cause significant downturns in the database.

 

because i have sometimes some problems when i try to import permission of 1 mo, i get an error :

insufficient system resources

 

Regards.


This topic has been closed for comments

5 replies

Userlevel 5
Badge +11

Hi,

If you can create a PLSQL query which results the required information, then I don’t see a problem of creating a lobby for that. But, sometimes you may need administrative privileges, because you may need to access database tables where it needs SYS/DBA access.  

@Rusiru Dharmadasa Can you add something here?

 

Best regards

Savinda

Badge +1

Thank you,

Lobby is just to give an example, the most important for me is to have a way, and get list of queries consuming lot of resources,

 

And after that i can report to the company which program is consuming lot to optimize it.

 

Regards.

 

Userlevel 5
Badge +11

Hi,

If you do some research in internet, you may be able to collect good queries written on this area. Because, these queries are not IFS DB table specific.

 

Example (not an exact scneario): https://asktom.oracle.com/pls/apex/asktom.search?tag=how-can-i-track-the-execution-of-plsql-and-sql

 

 

Best regards

Savinda

Userlevel 7
Badge +19

From the IFS Lobby functionality I don’t think there is a direct way to achieve your requirement. However we do provide an informative guideline with instructions to optimize lobby performances - see https://docs.ifs.com/techdocs/Foundation1/040_administration/220_user_interface/280_lobby/070_development_guidelines/default.htm

 

As @Savinda Suvimal highlighted you can use an Oracle query to find long running and heavy queries and its best that you run such at the same time you face your issues. 

 

Refer to following guidelines as well:

https://www.brentozar.com/responder/get-top-resource-consuming-queries/

https://www.red-gate.com/simple-talk/blogs/how-to-find-cpu-intensive-queries/

 

To get optimum results your oracle user might need to have administrative rights.

 

Badge +1

Thanks à lot

Regards