Getting a Slow performance in Toad for Oracle 12.10 when I am trying to open Schema Browser | Script Tab.

Toad for Oracle - Beta
1

Getting a slow response in Toad for Oracle 12.5.1, 12.10 and now with Beta Version 12.11.- When I am trying to open Schema Browser | Script Tab| It takes a lot of time to show the DDL. in some cases almost 2 minutes.
I’m using 64 bit environment (Windows, Database Server 12c, Toad for Oracle and Oracle Client 12.1)

2

This is likely due to a slow performing query. From the main menu, click Database -> Spool SQL -> Spool to Screen.

Then load the script tab. The output window at the bottom of Toad will capture every SQL that Toad executes. Post the output here and I’ll take a look at it. If you don’t want to post it online, you can email it to me at john.dorlon@quest.com

3

Rod, we are experiencing the same issue…i.e. withing SCHEMA BROWSER you click on say the MATERIALIZED VIEWS…then SCRIPT…it takes 20seconds or 2min to pull up the ddl.

Works fine in 12.8…works fine in 10.x toad. We have only a few db’s this is an issue on…did you figure it out? tks

4

John, see my reply. tks

5

Gammy,

See my original reply. There are a lot of queries executed and unless someone shows me the spool SQL output, I don’t know which one is causing problems. Show me the spool SQL and then I can try to help.

-John

6

thanks John…it works for my version of toad but not the customer…so, I need to get the customer to do this? i.e. from the BAD toad?

7

Right - reproduce the problem with Spool SQL turned on, then send me the spool sql output.

8

John, I just sent you two spool files (GOOD and BAD) from my @yahoo email. Let me know what you think. I am guessing this is a toad version issue.

9

This resolved the issue for us that time: “ALTER SESSION set optimizer_adaptive_reporting_only=TRUE;”

10

Thanks…thats a 12c parameter and this db is 11.2.0.4 Setting optimizer_mode=RULE seems to help somewhat.

I think this is a toad 12.1 issue as 10.x and 12.8 are fine

11

Hello John,

I am experiencing the same issue here. Please find attached spool for the 11g database for which issue does not occur and for 12c database (when loading script can take > 30s).

From my own comparison, it seems that more queries are run by for the 12c database for some reason since the spool for the 11g database stops at

Session: KNL@PRD2B.COMMUNICATION1.NTLD
Timestamp: 13:47:13.619
Select *
from sys.ALL_IND_COLUMNS
where index_owner = :iown
and index_name = :iname
order by column_position
:iown(VARCHAR[3],IN/OUT)=‘KNL’
:iname(VARCHAR[25],IN/OUT)=‘PK_T_SENTENCES_TRANSLATED’

Whereas it is followed by many other queries on the 12c database. If that is correct, then why does Toad run more queries to load the same data for the 12c version? Thank you
SpoolPRD2B_12c.txt (17.3 KB)