Toad World® Forums

Slow response for each query (a few seconds delay)

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.

image

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.

What more can I try to solve this problem?

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

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

It sounds like you are executing using F5 (Execute as Script). Do you experience the same delay executing a single statement using F9?