For Toad 12.12.0.39, Windows 7 Professional 64bit during each query execution I have got a ~5 seconds delay.
I found out in spool anonymous block which is executed after each query.
My colleagues do not have this anonymouis block in the spool, they haven't got this delay.
Response time at the bottom of toad screen for "select 1 from dual" is ~12msecs but I'm waiting 5 seconds for displaying result in grid.
In other tools like "Oracle SQL Developer", "SQLPlus" I do not have similar effect.
Could you give me any advice what the source of my problem is or how to turn off invocation of this anonymous block?
-- Session: xxxx
-- Timestamp: 08:00:25.340
select * from dual;
-- Session: xxxx
-- Timestamp: 08:00:30.882
DECLARE
v_lines SYS.DBMS_OUTPUT.chararr;
v_line VARCHAR2 (32767);
v_clob CLOB;
v_cnt NUMBER;
v_output VARCHAR2 (32767);
PROCEDURE write_to_clob (p_clob IN OUT CLOB, p_str IN OUT VARCHAR2)
AS
BEGIN
BEGIN
DBMS_LOB.writeappend (p_clob, LENGTH (p_str), p_str);
EXCEPTION
WHEN VALUE_ERROR
THEN
NULL;
END;
p_str := NULL;
END;
BEGIN
v_cnt := :inOutCnt;
SYS.DBMS_OUTPUT.get_lines (v_lines, v_cnt);
DBMS_LOB.createtemporary (v_clob, FALSE, DBMS_LOB.session);
DBMS_LOB.open (v_clob, DBMS_LOB.lob_readwrite);
v_output := NULL;
FOR line IN 1 .. v_lines.COUNT
LOOP
IF :trim_line = 1 THEN
v_line := TRIM (v_lines (line));
ELSE
v_line := v_lines (line);
END IF;
IF v_line IS NOT NULL
THEN
v_line := REPLACE (v_line, CHR (0));
IF LENGTHB (v_output) + LENGTHB (v_line) > 32767
THEN
write_to_clob (v_clob, v_output);
END IF;
v_output := v_output || v_line;
IF line < v_lines.COUNT
THEN
IF LENGTHB (v_output) + 2 > 32767
THEN
write_to_clob (v_clob, v_output);
END IF;
v_output := v_output || CHR (13) || CHR (10);
END IF;
END IF;
END LOOP;
write_to_clob (v_clob, v_output);
if v_cnt > 0 then
:outLines := v_clob;
end if;
:inOutCnt := v_cnt;
DBMS_LOB.freetemporary (v_clob);
END;
:inOutCnt(INTEGER,IN/OUT)=500
:trim_line(INTEGER,IN)=1
:outLines(CLOB,OUT)=
That block is to fetch DBMS Output. To disable it set your DBMS Output options in the Editor as shown and try your query again. I can't recall when the queries in spooled sql are shown, but it looks like the delay is occurring prior to running that block.
You are right. I turned off DBMS output but this delay still occurs. I installed version 13 of Toad and spool loks excatly the same:
-- Session: xxxx
-- Timestamp: hh:mi:ss
select * from dual;
and delay occurs.
Never mind which query I execute easy or complicated results are always presented with a few seconds delay. Real time of execution is presented properly. It is similar to time in v$sql. Other functionalities like Schema Browser, Session monitor works fine, all staff is refreshed without any delay.
I tried some versions of Oracle clients and it does not solve my problem. I have remove Toad, install again and still problems occurs. I'm not able to find any useful advice.
How are your SQL Recall options configured? In Options navigate to the Editor|Code Assist page on the left and let me know how these options on the right are set.
To test SQL Recall causing, with Toad closed...
Rename your SavedSQL.xml file. It is in your User Files location which by default is located here... %APPDATA%\Quest Software\Toad for Oracle\12.12\User Files.
Start Toad and run your query again. Is it fast?
You can now restore your backed up SQL Recall file after closing Toad.
I have got the same configuration of SQL Recall like you. The experiment with SavedSQL.xml gave nothing.
I have done another test. I add 12 "select 1 from dual" statements into one script and I executed it as "Execute as script" and waited about 1 minute for the end. In the meantime I was starting second Toad instance and I observed sessions on DB on Session browser. Toad session for the executed script was in inactive sessions with 12 executions and 12 fetch (Current statement tab for the session). That shows that something wrong is with Toad because communication with DB is OK, statements were executed like lightning, session was ended but Toad because of these presentation lags still was working on this script.
Perhaps this is a hint which helps you solve my problem.
what solved it was deleting all files in
C:\Users\user\AppData\Local\Temp
first i tried to delete only toad files (begin with TOAD*. but that only helped for a short while. I then emptied the whole folder and problem was solved.
Thank you very much for your hint. I had more than 200 000 TOA* files in this directory. After removal TOAD works properly. I do not know what is the reason of such a big number of TOA* files there. I must observe it (my colleagues have ~200 files).
@jacpro1976 200,000 files! Yikes! Toad is supposed to clean up after itself when it closes, but if Toad undergoes a "forced" shutdown, that won't happen. Another possibility is that we have some code somewhere that is creating some files the "wrong" way, causing them not to get cleaned up even if you shut down Toad normally.
So, couple of questions:
which version of Toad are you using?
how do you usually shut down Toad?
maybe it's too late now to answer this one...but did you happen to notice the date on the oldest of those 200,000 files? I'm just wondering how long it took to build up that many.
if you see some more of these files build up over time....can you send a few to me? You can check them out with notepad first if you are concerned that they may have personal info that you don't want to send. john.dorlon@quest.com
I usually shut down Toad in normal way but sometimes I'm forced to kill it from Task Manager. Toad is not perfect piece of software. I usually (if it is possible) use Toad for a few days without shutting down. I have got a few connections, plenty of scripts, windows, tabs opened.
I had files from 2016, 17, 18, 19.
Right now I have got 117 TOA files from 23.09 to 27.09. About 20 per day. I was forced to kill Toad manually (from task manager) once last week. I remeber this situation very well because it made me very angry:)
Thanks, I got your files. They are grid settings files. They are created when you run a SELECT in the editor or go to Tables-Data tab in the Schema Browser. Like I said, they should get deleted when Toad is shutdown normally, but if you have to kill Toad manually, the file cleanup process doesn't happen and the files get left behind. I'm thinking about how to fix this.
My experience of Toad 13.2 was the same. All queries ran quickly but even one against dual would take about two seconds to display in the grid.
I had 242 TOA-files in temp but I had SQL Recall - Statements to save set to 50000 and the SavedSQL.xml was some 53 MB. Renaming it and restarting made results display instantly.
