Toad World® Forums

SB Tablespace node is EXTREMELY slow when displaying lots of tablespaces.

I have already unchecked “Include usage info” since I thought that would improve response. Did not.

I turned on Spooling SQL to screen to see what it was running every time I click on the Tablespace node. I clicked on Roles, then back to Tablespaces - it took over 5 MINUTES to display the list with NOTHING showing up in the Spool window. So it looks like something internal to Toad is causing this delay. We have 3,543 tablespaces in this particular database but when I do a refresh of the Tablespace node and run the queries the Spool SQL window shows, they take less than 1 second.

We don’t run any query when you just click on the tablespace node. It’s when you expand the node that we run a query. Also, spool SQL doesn’t spool until the query is finished.

If Toad is doing something internally that’s causing the delay, you should see your CPU spike during that wait period. Is that what you’re seeing? If so, I’ll build a database with a bunch of tablespaces to investigate.

Toad CPU does spike during the entire 5 minute wait.

I’m using Treeview and had already expanded the Tablespace node - it show Tablespaces (3543). Toad is waiting to paint the RHS when I click back on the Tablespace node.

I realize SQL does not display until query is finished, but when Toad finally paints the RHS pane, there is still nothing in the SQL window.

I have not timed the response time for smaller databases, but they are usable, versus the tablespace node on our big one which is not (we avoid it most of the time but it sure would be nice to be able to use it efficiently :slight_smile:

OK. The problem is the autosizing of the columns in the RHS after the list is loaded. I’ve made a change so that if there are lots of items in the tree, we’ll just look at the nodes in the visible area. It should come back quickly for you in the next beta.

Also, make sure you are not experiencing issues described in MOS Document 1292253.1 titled “Query Against Dictionary Views Extracting Tablespace Information is Slow or Appears to Hang”. I have seen the same thing you have every single day with Toad SB Tablespace tab where there are a large number of tablespaces and the RECYCLEBIN contains objects. As of (until 12c, that is), purging the RECYCLEBIN makes Toads SB Tablespace tab run much quicker. That led to a whole exploration of collecting fixed object statistics and how much DBA_FREE_SPACE is performance-impacted by RECYCLEBIN objects. FWIW, 12c uses Adaptive Plans to ferret out a badly-performing plan the first execution, and switches to another plan on subsequent runs, so Oracle didn’t actually fix the issue that we are describing here, they just made the optimizer “smarter” at dealing with objects that have no stats collected (including fixed objects), and exclude “bad plans” from future executions.

Hope this provides some insight…

John, thanks so much for this fix. Tablespace display list is now quite fast in SB, even after re-enabling “Include usage info”. Ian, thanks for the suggestion. Since we determined the major slowness was not the queries, I have not looked into RECYCLEBIN objects, but will keep that in mind.