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.
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.
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.
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.
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.
