Toad World® Forums

Variable Bug/Issue

Not sure if anyone else has experienced this, but…

When setting up variables in an automation report that use an SQL select, TDA CPU usage will jump to 50% or higher and cause the application to go unresponsive for at least 5 minutes (currently looking at 10 for the latest instance) when adding another variable or moving between existing variables.

My variables are nothing complex - just variations on:

SELECT TO_CHAR(SYSDATE,‘MMDDYYYY’) FROM DUAL;

So that I can get various date information to fill in the email and file names. I’ve used the VB definitions in the past, but they aren’t flexible enough and tend to break at random times for unknown reasons.

I suspect that this is not an issue with Automation. Instead it is a peculiarity we have found with some Oracle servers. The sympton is a long delay when executing the second or more SQL statements. If this is the situation the best way to handle is to change your setting on your grids to be read-only. What this does is bypass building a command builder. It is in that process that the delay is occuring. I know that doesn't sound very related but give it a try. See option in screenshot.

Debbie

Hi Debbie,

I can’t check because it’s been going for about an hour now at 50%+ CPU and steadily increasing RAM usage, but I’m pretty sure I had already had that set at read-only because I hated getting prompted to commit accidental edits when clicking around on the data grid (despite setting the connection as “read only”).

I don’t think it’s automation either, specifically, but perhaps how it’s validating the SQL, if it performs any validation?

I’ve got six variables defined:

91DAYS-FILEN: SELECT TO_CHAR(SYSDATE-91,‘MMDDYYYY’) FROM DUAL;
91DAYS-EMAIL: SELECT TO_CHAR(SYSDATE-91,‘MM.DD.YYYY’) FROM DUAL;
1DAY-FILEN: SELECT TO_CHAR(SYSDATE-1,‘MMDDYYYY’) FROM DUAL;
1DAY-EMAIL: SELECT TO_CHAR(SYSDATE-1,‘MM.DD.YYYY’) FROM DUAL;
DATE-FILEN: SELECT TO_CHAR(SYSDATE,‘MMDDYY’) FROM DUAL;
TIME-FILEN: SELECT TO_CHAR(SYSDATE,‘HH24MI’) FROM DUAL;

It was after the addition of the last one (TIME-FILEN) that I’ve been waiting for over an hour. There had been long time periods & 50%+ CPU usage between the rest of them, but that one kicked off the hour-plus wait I’m having at the moment.

Edit: I was finally able to re-gain control of the TDA session and confirmed that the check-box in your screenshot was already checked in my setup.

Your descirption sounds like you turned on auto-commit. This is different than read-only. When you get a chance please check the read-only attribute.

Debbie

In the lower-left hand corner it shows “AutoCommit OFF” and the mouseover says “AutoCommit is disabled”.

Also, my connection to this Oracle Instance is read-only, so I would expect to see error messages if it were trying to commit changes.

I am afraid my discription of the solution is confusing. I am only referring to an option setting that based on it’s value we do something different in the code. It has nothing to do with your type of conneciton or how the left-hand corner is set.

Debbie

Ok, I’m guess I’m still not sure I understand.

I’m searching through the settings for every instance of Auto Commit to make sure they’re all turned off. Are you talking about a Oracle-side setting?

Beyond that, why would a SELECT trigger a commit? Especially when building a variable?

What is your “Show grid as Read-Only” option say? See screenshot posted earilier.
Options | Grid.

Debbie

It’s been checked since install.

TDA finally crashed on me for some reason, not sure why, but when it came back up I was able to add all 6 variables in - there were sudden CPU surges to 50% and non-responsiveness when moving between variables, but not the hour-long time that I saw earlier today.

Go figure Not sure what was occuring but seems better now.

Yeah, I’m just somewhat concerned that the delay between moving from one variable to another builds exponentially the more variables there are.

I should note that this only happens when using variables that are SQL selects - using the VB based ones don’t have that issue. Unfortunately I’ve had too many issues with getting the formatting right with the VB ones.

Debbie - just thought you should know that I can reproduce this behavior pretty consistently - I don’t know if it’s an issue with my system, or something that TDA is doing on the back-end, but it’s becoming a real problem.

Today’s example is a simple automation script with two SQL based variables - one to pull the date and one to the pull the time and format them.

TDA has been locked at 50-53% of CPU usage and steadily increasing RAM usage for about 15 minutes now - there were no queries running at the time I was setting up the variables.

I went back through your previous post and set every field to read-only.

Do you get the same excessive CPU usage when executing you SQL in the editor? I don't really know what else might be causing the issue.

There are two date functions in the expression variable editor that do not use VBA. They will take the two formats that you want.

Date('DDmmyyyy')
Date('DD.mm.yyyy')

See attached screenshot and see if this is a workable solution.

Debbie

P.S. The '-' error for variables will be fixed in our next Beat posting.