I am running a trial version of 2.7.0.348. I’ve been encountering a recurring problem.
I am creating and running select statements against an Oracle database in editor. When I run it the 1st time, everything is great. However, if I modify it and run it a 2nd time, I get a message stating that X number of active queries are already running (which they are not) and then it takes a really long time to run. Sometimes the program locks up on me.
It does not happen each time, but quite frequently. I discovered today that it happens less often if I select the entire select statement (instead of just having my cursor somewhere in the middle or end) and hit F9.
What am I doing wrong?
Thanks,
Michele Colson
Step 1: Let’s determine what type of connection you have.
How did you make your Oracle connection? When you defined your connection did you select the type of ODBC? If you did please change to the Oracle type of connection.
If you did define as Oracle type, are you using an OCI client? Or using direct connection? Using an OCI client will give you the best performance.
If you are unsure of your connection type please post a screenshot of your connect dialog.
Step 2: Getting more info on the editor behavior
By default each editor has it’s own connection. We check the status of the connection prior to every execution. Often slows are caused by the connection being dropped and having to be re-established.
To watch the behavior closer please do the following:
a) From the View menu open up and pin the Background Processes window. This will show us each query as it runs and it’s progress.
b) From the Tools menu select ‘Execution Trace | To Output’. Then from the view window select the output window and pin it open.
Now execute your SQL and watch both windows. The background process window will show what is being executed from the editor and the output window will let you know if a the connection had to be opened again.
Let me know results on both steps.
Debbie
My connection is setup as Oracle and I believe that I am using a direct connection. I have attached a screenshot of the connection properties.
I pinned the background processes and trace output windows as you suggested. It does not appear that the connecton was being reset. However, I did receive an error message each time I ran the SQL stating that active queries were already running. I attached a print screen of that as well. I selected "Cancel already running query" although my previous query had already finished.
The only thing that showed up in the background processes was "Executing Script: Waiting for Connection (SID 529).
This was all run with one SQL statement in one editor window.
Next, I opened a saved sql file and ran it (same environment). This time a new connection was opened. I ran it a 2nd time, received the error message re running queries. Told it to cancel and background process shows it executing. It took nearly 4 min to run SQL that finished in approx 10 seconds the first time. Nothing else shown in the output window.
I switched back to my original editor window, re-ran the SQL. It executed in a few seconds without opening a new connection. Ran it a final time and received the error message.
Thanks.
My connection is setup as Oracle and I believe that I am using a direct connection. I have attached a screenshot of the connection properties.
I pinned the background processes and trace output windows as you suggested. It does not appear that the connecton was being reset. However, I did receive an error message each time I ran the SQL stating that active queries were already running. I attached a print screen of that as well. I selected “Cancel already running query” although my previous query had already finished.
The only thing that showed up in the background processes was "Executing Script: Waiting for Connection (SID 529).
This was all run with one SQL statement in one editor window.
Next, I opened a saved sql file and ran it (same environment). This time a new connection was opened. I ran it a 2nd time, received the error message re running queries. Told it to cancel and background process shows it executing. It took nearly 4 min to run SQL that finished in approx 10 seconds the first time. Nothing else shown in the output window.
I switched back to my original editor window, re-ran the SQL. It executed in a few seconds without opening a new connection. Ran it a final time and received the error message.
Thanks.
My connection is setup as Oracle and I believe that I am using a direct connection. I have attached a screenshot of the connection properties.
I pinned the background processes and trace output windows as you suggested. It does not appear that the connecton was being reset. However, I did receive an error message each time I ran the SQL stating that active queries were already running. I attached a print screen of that as well. I selected "Cancel already running query" although my previous query had already finished.
The only thing that showed up in the background processes was "Executing Script: Waiting for Connection (SID 529).
This was all run with one SQL statement in one editor window.
Next, I opened a saved sql file and ran it (same environment). This time a new connection was opened. I ran it a 2nd time, received the error message re running queries. Told it to cancel and background process shows it executing. It took nearly 4 min to run SQL that finished in approx 10 seconds the first time. Nothing else shown in the output window.
I switched back to my original editor window, re-ran the SQL. It executed in a few seconds without opening a new connection. Ran it a final time and received the error message.
Thanks.
You are using Direct Connect and this may be part of the issue. I would like to try two things. (Each separately)
Step 1:
There is some refreshing of the object palette being done when using default layout. I would like to change your layout to not use the Navigation Manager (which contains Object Palette). To do so, go to Tools and open the Configuration Wizard. Choose the Toad for Oracle layout. See screenshot.
Close app and restart. Try execution test again.
Step 2:
Let’s change to an Oracle client. If you have access to full client install that. Otherwise download Oracle instant client. There is instructions on how to use an instanct client in the TDA help file under Oracle | Create Connections. (Scroll to the middle of the topic)
You will need to use a tnsnames.ora file. After you set this up try execution test again.
Let me know how it goes.
Debbie
I tried Step 1. There may be an improvement in that executing the sql does not cause the program to freeze up any longer. However, I am still receiving the error message that I have additional queries running and then I must tell it to cancel those queries. And it still takes much longer to run a query the 2nd time.
I am having trouble with step 2. I installed Oracle 10g and updated my tnsnames.ora file. I had received an email earlier in the week from a support staff person (I had sent a strange error message to support) saying my Oracle client was outdated. However, I can’t seem to connect to TDA. I receive the following error message:
ORA-12514: TNS:listener does not currently know of service requested in connect descripto
I attached the error message that I received yesterday when executing my query the 2nd time.
Today, I connected to my database. Ran a simple sql - it returned my rows of data. I attempted to modify the sql, and I receive an error stating that it cannot reestablish lost connection. Then the program froze up on me. This all happened within a matter of minutes (no more than 5).
Earlier today, I had received a time out message.
I attached a print screen of the output messages.
Today, I connected to my database. Ran a simple sql - it returned my rows of data. I attempted to modify the sql, and I receive an error stating that it cannot reestablish lost connection. Then the program froze up on me. This all happened within a matter of minutes (no more than 5).
Earlier today, I had received a time out message.
I attached a print screen of the output messages.
Today, I connected to my database. Ran a simple sql - it returned my rows of data. I attempted to modify the sql, and I receive an error stating that it cannot reestablish lost connection. Then the program froze up on me. This all happened within a matter of minutes (no more than 5).
Earlier today, I had received a time out message.
I attached a print screen of the output messages.
I was able to connect using Oracle client instead of direct connection. Performance is a little better, but I am still having perfofrmance issues when I run the same SQL a
2nd or 3rd time.
For example, I ran the a simple SQL which returned 19 rows. It took 6 minutes before the tab for set 2 appeared and then quickly returned the results. Many times, I receive the error message that a query is still running (although all result rows have been returned) and I have to cancel it before it will run.
Do you have an object palette or Navigation Manager opened or minimized?
Please try using the editor without an object palette or navigation Manager. To do so you will need to use the configuration wizard and choose the Toad for Oracle configuration and then close the Object palette.
I really suspect an issue introduced in 2.7 that tries to refresh the object palette contents after every SQL execution. It shouldn’t be that way and we are fixing, but your issue sounds like that is what is happening.
You can confirm this by turning on Tools | Trace SQL | and displaying the output window. When doing so you will see SQL being executed after your editor SQL, only when having an Object palette open.
Debbie
