Object autocomplete performance problem

Hi

Toad 12.1.0.2 x64 on Win 7 x64 SP1.

I really want to know how to speed auto competition of object names (tables and packages especially) in Toad editor.
This is one of the most common question in mine classes and have no straight answer on that. People shows SQL developer and immediately respond and this is really frustrating.
I know it has something with speed of PC but I’d like to get all possible hints in this area.

Please advice me what to do to make fast as it can be.

Brg

Damir Vadas

In the code assist options uncheck objects that you can live without. Unchecking public synonyms will speed it up a lot. Also use the cache results option. Executions 2-N will be very fast.

On Feb 1, 2014, at 2:39 AM, "damir.vadas_531" bounce-damirvadas_531@toadworld.com wrote:

Object autocomplete performance problem

Thread created by damir.vadas_531
Hi

Toad 11.2.0.1 x64 on Win 7 x64 SP1.

I really want to know how to speed auto competition of object names (tables and packages especially) in Toad editor.

This is one of the most common question in mine classes and have no straight answer on that. People shows SQL developer and immediately respond and this is really frustrating.

I know it has something with speed of PC but I'd like to get all possible hints in this area.

Please advice me what to do to make fast as it can be.

Brg

Damir Vadas

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

THX a lot.

Will play with these options.

P.S
It is amazing how few extracted sentences may help a lot and in the same time if you place them among 2000 others in help file-they just vanished.

[:(]

It's been quite a while since firing up SQL Developer, but I just did so for comparison. It uses a completely different approach for the object picklist.

I haven't played with options so I don't know how customizable it is, but as a new user using the default option set here's what I see. By default it only displays objects of the connected schema. I can see objects in another schema by starting to type out
the schema name. Similarly the picklist doesn't show PL/SQL by default, but if I know the name of what I'm looking for and start typing it then the PL/SQL object will appear. I must know the starting character. I also can't seem to get the picklist to invoke
after SELECT. I hit CTRL+Space, but nothing happens. It also doesn't show a complete list of objects. I tried viewing public synonyms starting with "ALL" and it shows the last 50 or so synonyms when sorted alphabetically. I couldn't get to all_objects without
starting to type it out. At some point I noticed the ellipsis in the list. If I double click that the list closes and reopens displaying all synonyms. It appears that objects are not cached as well. I can see queries executing every time I invoke the list.
This is not necessarily a bad thing though.

Toad, on the other hand, brings more of a sledgehammer approach and loads everything up front. It's better suited if you need to browse the list of objects without knowing the name of what you're looking for, but if you know the name and start typing it the
list is filtered quickly. Initial load time is longer, but the results are more complete. If the cache option is enabled then subsequent executions are immediate. It only takes milliseconds to populate and display the list. The list can be invoked anywhere
within your code as well.

My comparisons were done on my local XE database so performance was excellent in both tools. To throw a more real world test at it I connected to a large Oracle apps database we have that's on the other side of the continent. Using the default option set in
Toad the load time was ~13 seconds. SQL Developer never gave me results. I waited well over a minute and tried to close. I received a message about a connection being busy and asked if I wanted to abort or try again. I clicked abort. After another minute I
was able to close SQL Developer and never did get any results. I closed and reopened SQL Developer and tried again. This time I didn't have any problems when trying to close it, but after a minute or two I still never did get the picklist.

Of course I'm biased towards the Toad approach, but you could make an argument for each design.

If you want the fastest possible experience make sure stats are current on your database. Trim the results to only those object types of interest. If those in your class are primarily interested in writing SQL and are using tables and views mainly then enable
only those objects in Options|Editor|Code Assist. Add public synonyms if they want access to those as well, but this adds quite a bit of extra time. Toad 12.5 has a new option to exclude synonyms having a name starting with /. The default is checked. This
improves performance a lot. Also, enable the cache option if the objects in the schema they are working with are somewhat static. If objects are not being created/dropped regularly then caching would be wise. They can always refresh the cache by clicking the
refresh button within the popup.

Michael

On 02/01/2014 02:39 AM, damir.vadas_531 wrote:

Object autocomplete performance problem

Thread created by damir.vadas_531
Hi

Toad 11.2.0.1 x64 on Win 7 x64 SP1.

I really want to know how to speed auto competition of object names (tables and packages especially) in Toad editor.

This is one of the most common question in mine classes and have no straight answer on that. People shows SQL developer and immediately respond and this is really frustrating.

I know it has something with speed of PC but I'd like to get all possible hints in this area.

Please advice me what to do to make fast as it can be.

Brg

Damir Vadas

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


No virus found in this message.

Checked by AVG - www.avg.com

Version: 2014.0.4259 / Virus Database: 3684/7045 - Release Date: 01/30/14

I just want to add one extra tidbit to Michael’s excellent reply. Since Toad has to query the data dictionary (and not schema objects) - you need current stats on the data dictionary for optimal performance - so try these commands :slight_smile:

exec dbms_stats.gather_dictionary_stats;

exec dbms_stats.gather_fixed_objects_stats;

The first one is the critical one - but often the second command is worth collecting too.

@Bert,

This sounds more then reasonable … nice thoughts.

THX

@Michael,

In SQL developer place shema_name nad pulstop (.) and all object are instantly shown … works on slow computers as well. In Toad this is not so fast …

8(

However, THX for comprehensive reply. Appreciated a lot.

Damir

According mentioned instructions I’ve been playing and get really ultra responsive Toad.

Now really rocks.

Thx for hints to all

made these changes but did not help one bit.
i am on 12.6.0.53

still takes forever and nothing shows up if i wait long enough or i have to just terminate and reopen Toad

Is Toad hung completely or is it just not returning anything? Does this happen on all of your databases and all schemas? If Toad is hanging, enable SQL Spooling from the Database|Spool SQL menu and spool to screen. Try to load Code Insight and when it’s hanging look at the query that is shown in the spooled SQL. Post the query here. Also, make sure that your database stats are up to date as mentioned in previous reply.

Enabled Spool to screen but nothing showed in the output frame. Where do I look for it?

It usually does not go anywhere and I terminate the program. I left it to complete on purpose and after a long wait, got an error. Tried to attach the log from that message. The site says new users not allowed to upload. Pasting the entire log below.

Nothing was pulled up at the end.

This issue is in all databases and schemas.

EurekaLog 7.0.7.1

Application:

1.1 Start Date : Thu, 13 Sep 2018 10:57:55 -0500
1.2 Name/Description: Toad.exe - (Toad™ for Oracle®)
1.3 Version Number : 12.6.0.53
1.4 Parameters :
1.6 Up Time : 23 hour(s), 28 minute(s), 17 second(s)

Exception:

2.1 Date : Fri, 14 Sep 2018 10:26:13 -0500
2.2 Address : 06D369C9
2.3 Module Name: Toad.exe - (Toad™ for Oracle®)
2.5 Type : EAccessViolation
2.6 Message : Access violation at address 06D369C9 in module ‘Toad.exe’. Read of address 00000000
2.7 ID : 95CA0000
2.8 Count : 1
2.11 Sent : 0

User:

3.2 Name : HP6300
3.3 Email:

Active Controls:

4.1 Form Class : rctrl_renwnd32
4.2 Form Text : Calendar -
4.3 Control Class:
4.4 Control Text :

Computer:

5.2 Total Memory : 17081679872 (15.91 Gb)
5.3 Free Memory : 9923244032 (9.24 Gb)
5.4 Total Disk : 500000903168 (465.66 Gb)
5.5 Free Disk : 321192452096 (299.13 Gb)
5.6 System Up Time : 6 day(s), 5 hour(s), 57 minute(s), 25 second(s)
5.7 Processor : Intel® Core™ i5-4590 CPU @ 3.30GHz
5.12 Virtual Machine: VirtualPC

Operating System:

6.1 Type : Microsoft Windows 7 (64 bit)
6.2 Build #: 7600

Steps to reproduce:

8.1 Text:

Call Stack Information:

|Methods |Details|Stack |Address |Module |Offset |Unit |Class |Procedure/Method |Line |

|*Exception Thread: ID=167532; Parent=0; Priority=-2 |
|Class=; Name=MAIN |
|DeadLock=0; Wait Chain= |

Comment=
7FFFFFFE
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
00000040
--------------------------------------------------------------------------------------------------------------------------------------------------------

You can try to reset your user files.

  1. Open Copy User Settings from the Utilities menu
  2. Choose the option to Reset to a clean set
  3. Click OK
  4. Choose Yes and then OK on the next two dialogs

This will restore Toad to a clean state as if it were freshly installed. Your existing user files will be backed up to %APPDATA%\Dell\Toad for Oracle\12.6_bak.

Now, start Toad, make connection, and try Code Insight again. If it works you can close Toad and copy any configuration files over from the backed up set to the new (%APPDATA%\Dell\Toad for Oracle\12.6) . I'd recommend copying only what is absolutely necessary. For example...

  1. CONNECTION*.INI (all of your saved connections)
  2. SavedSQL.dat (SQL Recall)
  3. ToadActions.dat (Automation Designer apps and actions)
  4. Workspaces.dat (saved Workspaces)
  5. *.tpr (Project Manager projects)

If resetting your user files does not work I'd try downloading the latest Toad trial and seeing if it works.

Similar kind of behavior with the latest trial version as well. Takes too long to show the table records, while switching between the tables. Quite abnormal.

Am using version another older version 9.7.2.5, and have no issues at all with it, except for the AR unicode character display.

image

Please try "reset user files" as I suggested in the other thread.

Could you please send me further details to 'Reset', as I could not locate the 'Copy User Settings' option in the View menu of TOAD.

image

It's under "Utilities", not "View". If you don't see it there either, right-click on the toolbar and choose "Restore Toolbar Defaults"

Still no luck, and switching between tables take longer times in newer versions. Where can I look for toad log files..any idea?

Please be specific about which version you are talking about.

Try Database -> Spool SQL -> Spool to Screen. Then do whatever is slow in Toad. That will put all of the SQL that Toad runs, with timestamps, at the bottom of Toad. Copy/paste that here and then maybe we can tell what is slow.

Am using Toad for Oracle version 11.6.0.43, and also tried it on the latest Toad Trial version 13.