Extremely long delay before (initializing) and after (executing) query execution

G

Gustavo

Member³
Hi!

I was running v9 and I thought that was the problem, and I decided to finally upgrade to the latest version. But the issue remains. There is a VERY long "hang time" before and after running queries. Each of the delays (before and after query) might take up to several minutes. I've sent the zipped debug.txt file through e-mail.

Regards,
Gustavo
 
Hi,

I reported slow search performance a while back. Maybe search has the same issue?

Regards,

Jan
 
Can you go to Help > Support Info, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail reply?
 
Sorry, it had been filtered into a phishing folder.

There's no sqlnet.ora file; tnsnames.ora is the only file in that path.

Regards,
Gustavo
 
Okay. To obtain some more diagnostic information, can you modify the shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory or in the %APPDATA%\PLSQL Developer directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer).
 
Hi, Marco!

I had sent this in the 1st e-mail message, but I've just forwarded it again. The file is too large, so it is zipped.

Regards,
Gustavo
 
Hi!

I noticed the e-mail was too large due to the tnsnames.ora file, which had been pasted before. As you've confirmed you had received it, I trimmed most of its content, in order to reduce the message size and see if you can get the debug file now.

Best regards,
Gustavo
 
Hi, Marco!

Performance is normal from SQL*Plus. It also behaves fine from PLSD right after connecting, but, then, all of a sudden, I start getting 3-minute "initialize" lags. Sometimes I even spawn a new PLSD instance from Windows task bar, when I want to run a simple query. Many times it is faster to load a new instance and run the query than waiting for the former instance to "initialize". Also, my colleagues don't have the same problem, running PLSD 8, which is the company's last upgrade.

Regards,
Gustavo
 
Marco,

Please, can you take a look at this? I can generate whatever additional debug info you need, but I need a solution. I currently have to spawn new instances of PLSD to run queries, as my "main" instance takes up to 5 minutes "initializing", and even more than that after "executing". The other option is to run them in SQL*Plus, as the other available tools are not authorized here (except from Oracle's SQL Developer, which I personally hate).

Best regards,
Gustavo
 
You have to work with DBA and trace the session that PLD invokes when connecting.

99% the problem is not in PLD itself, but in your DB configuration combined with the queries that PLD issues.

I noticed the e-mail was too large due to the tnsnames.ora file
Click to expand...
tnsnames.ora should have connection descriptions only. Do you have thousands of databases?
That may be the case...
 
You got a point, IvanZ... We certainly have hundreds, and might even have thousands of instances, but I don't need more than 30 of them. In our environment, tnsnames is stored in the network, but I'll give PLSD's tns_admin parameter a try.

Regards,
Gustavo
 
Yesterday I disabled all plugins that included database-related functions: Reconnect, Execute Current and rrProject. I also checked the option to automatically disconnect unused connections. Now, when I load the program, it behaves normally. But today, when I came back to work (it was just locked, not sleeping), it was terribly slow again. I'm running out of ideas.
 
Have the issues with the performance been solved yet? It's exactly as described: long initialization before and wait afterwards.
Export user objects f.i. takes 2 minutes just to load the objects.
Search database source takes 10 minutes while Oracle SQL only takes 25 seconds for the same search.
 
Nope, still the same issue, here.

@Worker - I had generated the debug files and sent them to Marco, but he couldn't get to a diagnostic yet.

If I spawn a new instance, either from Windows' Task Bar or from PLSD File -> New Instance, the new instance will run the commands instantly, while the old one is still hung.
 
Gustavo: If I remember correctly, the debug file is a plain text timestamped log file. If you can reproduce the problem with a small debug file, maybe you can look at the file yourself, see what's going on during the delay, and get Marco 80% towards a solution just to help speed things along.
 
Hi Marco, Gustavo, Worker,

I created a new debug.txt. Problem is after a while the time part is omitted, so can't tell what takes time.
I also notice that first all relevant objects get listed based on my setting and then per object a search in the source occurs.
If I search dba_source with owner = user and object_type in ... (comparable search) then it takes like 3 seconds, compared to 10 minutes with the PL/SQL Dev object search situation...

Hope this helps. Good luck!

Regards,

Jan
 
You must log in or register to reply here.

Similar threads

C
  • Discussion Discussion
problem exporting long query to Excel
Replies
3
Views
6K
Marco Kalter
Marco Kalter
O
  • Discussion Discussion
Executing SQL extremely slow
Replies
5
Views
8K
Marco Kalter
Marco Kalter
I
  • Discussion Discussion
Unable to break queries after execution
Replies
7
Views
7K
NK
NK
G
  • Discussion Discussion
very slow closing of windows
Replies
10
Views
11K
gad
G
W
  • Discussion Discussion
Long "Initializing..." delay running SQL queries in Rel 9
2
Replies
37
Views
187K
Marco Kalter
Marco Kalter
Back
Top