0

My environment :

  • Oracle Database 12.2
  • Oracle Application Express 5.1.4

I am having a lot of issues with users running queries with SQL Developer, a problem I already described in this question some days ago

Oracle Parallel Query behaviour with IDE tools as SQL Developer or Toad

As a intermediate solution, I was wondering on how Oracle did build the SQL Worksheet in Oracle Live SQL. You might have a look here

Live SQL

My idea would be to build a small apex application that might provide a similar functionality, thereby the users can access to a SQL editor of some kind and run queries. With this method, I can use the Java pool of ORDS through Tomcat to take care of reuse existing sessions and close ones with no use, avoiding my current issue with so many active parallel slaves in status active when their query coordinators are inactive in SQL Developer. I know for a fact that with a Java Pool I don't suffer the problem of slaves in status active when their parent processes are inactive.

I know that I might create a user with only access to the SQL Workshop, removing it from accessing the other components of the workspace, but that implies that the user must have access to the workspace itself, which I can't do in Production, unless I don't find any other way.

enter image description here

Does anyone know of any plugin or any other way that I can have the same in-built functionality but inside an application ?

Thank you

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
  • your problem isn't with the tools or the database per se, but your users they open queries and don't grab all the data, hence the processes are left idle/waiting on the server. have you considered a resource consumer group? you could set idle periods that would throw their sessions away...or prevent them from consuming more than X parallel sessions if using a query tool like sqldev. – thatjeffsmith Aug 07 '20 at 15:09
  • comment two, you don't need to build anything, we already did, it's called SQL Developer Web, runs out of ORDS, is like the SQL Workshop in APEX, only more complete/modern. The queries runs out of it are paged, so even though they do select * from, we only really get the first 25 records, and we're DONE - nothing left hanging around on the server to worry about – thatjeffsmith Aug 07 '20 at 15:10
  • @thatjeffsmith, regarding you point 1, indeed I tried resource manager, but at the end I ran in more problems than the ones it could have solved. I know now why those slaves remained active, so I am looking for a solution to avoid using SQL Developer , or any other IDE for that matter, in Production – Roberto Hernandez Aug 07 '20 at 16:49
  • @thatjeffsmith, regarding your second point, I know about SQL Developer Web, but as far as I know, it is not compatible with APEX 5.1.4, and even more, I though it was only available for Oracle Cloud. Do you know otherwise ? can it be installed on 12.2 with Apex 5.1 and ORDS 3.0.2 ? – Roberto Hernandez Aug 07 '20 at 16:54
  • Sure its compatible with apex 5.1 and it's available on prem...ords 302 isn't appropriate for ANY environment... upgrade your ords pronto – thatjeffsmith Aug 07 '20 at 16:59
  • I know that , our ords is quite old, but my Spanish colleagues are giving me a hard time just to simply upgrade the ORDS current installation that supports our Apex 5.1.4 . I don't know yet how can I enable access for users that are logged against LDAP and they are not DB schemas. Thanks anyway for your ideas. Appreciated. – Roberto Hernandez Aug 07 '20 at 20:28
  • @RobertoHernandez Why do you care about "parallel slaves in status active when their query coordinators are inactive"? What's the real problem? – Jon Heller Aug 07 '20 at 20:45
  • @JonHeller, my database might run at the same time batch processing and user activity. When users are running queries, they leave a lot parallel slaves active when their QC are active. I implemented a lot of controls to remove them, cleaning sessions, etc, but sometimes they are so many slaves active ( for QCs inactive ) that the batch can't acquire the ones it need. That is why I want to build a SQL interface in Apex ( or use SQL Developer Web as @@thatjeffsmith suggested ) – Roberto Hernandez Aug 08 '20 at 08:05
  • @RobertoHernandez So why not just increase the number of allowed sessions? It seems like you're spending too much time worrying about query coordinators and parallel sessions. – Jon Heller Aug 08 '20 at 08:23
  • @JonHeller, I tried that honestly, but when PARALLEL DEGREE is automatic in all the fact tables of the data warehouse, increasing the number of parallel_max_servers is not enough. I tried to use the double of the one recommended by Oracle and is was not enough. I have so many users working concurrently, that at the end I decided to use PARALLEL_DEGREE_POLICY to AUTO for only those users, thereby I could limt the slaves , but still not enough. However, even though the parallel is an issue, I want to get rid off of users accessing production with SQL Developer, when they are not developers. – Roberto Hernandez Aug 08 '20 at 08:42

1 Answers1

1

I have created an application in Oracle Apex 20.2. It is the same as the option SQL Commands in SQL Workshop. You can download it from GitHub via the following link:

https://github.com/devvinish/sql-command

Below is the screenshot of the app:

enter image description here

Vinish Kapoor
  • 669
  • 1
  • 7
  • 13