toad 11 - explain plan columns size is not saved

Hi,

I recently upgraded to TOAD for Oracle v11.0.0, and when asking for an explain plan in query editor, the columns’ size always revert back to some default setting which is not appropriate. In previous versions, when I resized one or more columns, the next time I asked for an explain plan, columns’ size were kept.

explain plan settings :

  • tree mode
  • columns selected : lots of columns (cardinality, cost, io cost, cpu cost and so on)

Is there a new option somewhere to control this behavior or is this a defect ?

thanks
sebastien

This is working fine for me. The path to your files will differ, but these 3
files control how the Explain plan looks.

C:\Users\jdorlon\AppData\Roaming\Quest Software\Toad for Oracle\11.0\User
Files\ExplainPlancolumnDefs.xml

C:\Users\jdorlon\AppData\Roaming\Quest Software\Toad for Oracle\11.0\User
Files\ExplainPlanGlobalPrefs.XML

C:\Users\jdorlon\AppData\Roaming\Quest Software\Toad for Oracle\11.0\User
Files\ExplainPlanUserPrefs.XML

The ExplainPlanColumnDefs.XML file does not change with your settings. It is
used to control what is available for settings.

The ExplainPlanGlobalPrefs.XML file contains information about your font
selections.

The ExplainPlanUserPrefs.XML file contains information about column visibility,
widths, and order.

It sounds like ExplainPlanUserPrefs.XML isn’t being read or written for
some reason. The file hasn’t accidentally been made read only, has it?

Hi,

Thanks for the information, this is interesting …

ExplainPlanUserPrefs.XML is R/W, and NTFS information is weird : created on October 4th (that must be when I installed toad 11), but modified on April 25th (toad v11 build date ?).
Furthermore, if this is the same file that stores column visibility, order and size, then it stores the first two attributes (visibility and order) properly, but not size.

sebastien

With Toad not running, try deleting ExplainPlanGlobalPrefs.XML and
ExplainPlanUserPrefs.XML. They should get recreated next time you do an explain
plan. Just don’t delete ExplainPlanColumnDefs.XML.

I just tested changing of width, order, and visibility. It’s all working
fine here and stored in ExplainPlanUserPrefs.XML. The “Shown”
attribute is visibility (0 means not visible, 1 means visible in tree, 2 means
visible as a column), “Width” is size, and “pos” is
position (it’ll be if Shown = 0 or 1)

I did what you suggested : deleted the two files, then opened toad again, asked for an explain plan, then customized the display to get my columns back. Both files are then recreated.

However, the behavior is really strange … it looks like the total width of all the columns I have selected is fixed.
I have 10 columns displayed, my file content is :
























If I change any column width, then next time I call explain plan, the first column will be resized so that to total width remains the same as when I called explain plan the previous time. In other words, if I widen column 2, then column 1 will be shortened by the same amount I widened column 1.
Of course, if I resize column 1, it does not have any effect because it’s always column 1 that is adjusted so that the total length remains constant, so it reverts back to old setting.

Is this clear or do you want me to rephrase it ?

sebastien

I understand what you are saying. I just can’t make it happen here. :slight_smile:

Maybe there is something about your combination of settings that is causing the
problem. Will you send all 3 of those files to me? John.dorlon@quest.com .

Thanks.

I’m not native English speaker, so I’m not sure I use the correct terms to describe what is happening (hence my worries).

I’ve made few more tests : no matter what I select as visible columns, and which order, it will always try to resize the column containing the steps details (the one that says which operation on which object, let’s call it the “main” column) so that the total length of all columns is roughly equal to the size of TOAD windows minus a small part on the right of the window :

  • if I resize my main TOAD window, or the editor window only, the total length is modified, and guess what, the main column is resized too,
  • if I move the main column right in the middle of all columns, then it resizes it (not the first column)
  • etc.
  • if I resize any column, it will resize the main column only,
  • if I remove or add one column, then again the main column will be resized accordingly.
    So whatever happens (resize of window or any column modification), it will always adjust the main column size so that the total length is equal to the window length minus a small part on the right.

I’ll send you the 3 files.

sebastien

Oh, you are talking about the main column! Yes, that one resizes automatically
because when you have a lot of steps, it needs to be wider since there is lots
of indention in the lower steps. When you have only a few steps, there is less
indention and less width is needed. The other columns should remember their
widths as you set them.

I could make the auto-sizing of main column an option if you find it annoying. I
find it less noticeable if the main column is on the right.

Yes, the main column. I should have made some more tests before opening this thread, because I finally figured out what is happening … but it took some time (and now I can spend time n it, my day is over).

For me autosize is not a good thing because I usually have explain plan much wider than toad window size and use scroll bar. That’s because I have around 10 columns, and some of them are very wide : as you said main column itself can be very wide if the query is complex because of indentation, and also filter predicates and access predicates. Just these 3 columns require sometime to be much wider than toad window size, and with toad 11 it’s not possible any more.

My 2 cents, after a 1 minute think time : there should be 2 modes

  1. disable autosize (like toad 10 and before, you set column width, it remembers it, that’s all),
  2. enable autosize, then the behavior should be
    a) autosize (extend) main column width when total column width is smaller than editor window,
    b) don’t autosize when the total width is greater than total length, let the user play with scroll bar.

It’s probably not a perfect solution, but at least, it could allow users to get witdh larger than editor window, something impossible right now (unless you manually resize each time again and again).

For the moment, this is the one feature that makes me think of downgrading to toad 10.

sebastien

Oh no! We don’t like to hear the D-Word around here! At least, I
don’t.

I’ll add an option into the next beta. You can go to explain plan ->
rt-click -> display, and check/uncheck “autosize main column in tree
mode”.

If unchecked, the width will just be remembered like every other column.

-John

ah ooops, sorry for the d-word … but these times I do 10s or 100s of explain plans every day, so this is driving me crazy …
I’ll check the next beta, in the meantime, I’ll install the most up to date beta.

thanks,
sebastien

Fixed in beta release, waiting for the new stable release …

many thanks,
sebastien