I have a large Query (about 1206 Rows), as soon as I add a bind parameter to the query Toad freezes for about one minute before showing the input request for the bind parameter.
The Query is very simple, it's just a bunch of SELECT FROM DUAL witch I need to create some fake Data and the query is executed in under 1 second, but Toad needs a long time to start it.
During the freezing of Toad, I can't see any activity from this session on the database.
I am using Toad for Oracle 13.3.0.181
Does anyone else have the same problem?
Hi Benjamin,
By any chance, if you go to Options -> Sensitive Data -> General, do you have the "Enable Sensitive Data Awareness" enabled? If so, try unchecking that option and seeing if the performance improves. We recently discovered that an issue with MS XML parsing can potentially cause this feature to slow the editor's response time on very large queries.
Let us know if that fixes the issue you're seeing. If not, we'll continue to dig a bit deeper to see if we can locate the issue.
Thanks,
-John
Hello John
Thanks for the quick answer. Sadly I can't find this Settings. If I open the Toad Options I don't see the point Snesitive Data and I also don't find it if I use the search function in the Options.
Benjamin
Hi Benjamin,
I'm also running 13.3.0.181. If I go to view->Toad Options, I see "Sensitive Options" on the list down the left side of the dialogue. Do you not see this?
You did put "Snesitive data" in your post above, I'm wondering if that was a typo in the post, or if you searched the options for "Snesitive", which is indeed, not found.
HTH
Cheers,
Norm. [TeamT]
Hi Benjamin,
Ahh, that feature is only available in Toad for Oracle Professional and higher. Since you're not seeing it listed in the Options window, I'm guessing you're working in Toad for Oracle Base Edition. If so, then something else is causing this issue.
You mentioned your script was a bunch of SELECT FROM DUAL to create fake data. If there's nothing sensitive or proprietary in it, would you be able to send me the script you're trying to use offline at john[dot]bowman[at]quest.com?
-John
Hello John
Yes this is correct wa are using Toad for Oracle Base Edition, I forgot to mention that in my post.
The Script I am using is attached in this post here.
Freezing-Example.sql (184.9 KB)
For the record, I can re-produce the freeze-up on my Toad Xpert 13.3 version, and the SDP option enabled or not has no effect. I did turn spooling on to see if Toad was performing something extra...
Toad is executing the statement almost as is... last lines of what Toad executed (I used ":1" for the bind variable instead of ROW_ID):
----->... 1000+ rows snipped ...<-----
SELECT '000000001199' PRODUCT_NR, '60' PRODUCT_VERSION, '3800000' PRODUCT_OWNER, 24 QUANTITY, DATE'2020-07-27' DELIVERY_DATE, 1199 ROW_ID FROM DUAL UNION ALL
SELECT '000000001200' PRODUCT_NR, '55' PRODUCT_VERSION, '3840000' PRODUCT_OWNER, 66 QUANTITY, DATE'2020-07-27' DELIVERY_DATE, 1200 ROW_ID FROM DUAL
)
SELECT * --SUM(QUANTITY)
FROM PROD_LIST
WHERE ROW_ID < :1;
:1(INTEGER,IN)=7
Hi Benjamin,
John pointed me to some problematic code here that's causing the slowness. In short, Toad is hashing your SQL internally after formatting it so that we can maintain variable values per statement where formatting differences are taken out of play. This code takes some time with large SQL and as I look at the code I question its usefulness. I think the only option for you with that sample statement is to remove the bind variable and instead set its value in the SQL directly.
Thanks,
Michael
Hello Michael
Thanks for the Information. I know that the sample code dosen't realy make sense, the real one is a bit more complex. I just tried to reproduce the same behavior in a simple example, that can be run without any real Data from us.
The workaround with removing the bind variable and using the value directly, works for the runtime, but makes our process to use this query more complicated.
Benjamin