Thank you for the tips. Sorry for the delayed reply.
Correction: I am not experiencing differences between Oracle Clients (they are all equally slow). The reason for the previous incorrect assessment was that the first editor script execution (F5) of a brand new Toad for Oracle instance appears to be faster than subsequent executions. Previously, I didn’t close/re-start Toad for Oracle each time I changed the Oracle Client. However, this time I did restart Toad for Oracle with each test, and all Oracle Clients appear to behave roughly the same from a response time perspective.
Unchecking “Connect using Oracle Client” actually made things worse (slower).
Yes, “Execute scripts in Toad session” is checked.
Tracing is not enabled.
DBMS Output is disabled.
Toad Improvement Program was “Yes”. Changing it to “No” did not help.
The below is the output of the spool SQL. Please note the “Timestamp” seems to be more revealing than the “Elapsed Time” field.
There appears to be two culprits.
3rd to last step:
begin
sys.dbms_output.get_lines(:LINES, :NUMLINES);
end;
and 2nd to last step:
select ... from user_constraints...
The full spool SQL:
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:09.348
select 'Test connection' from dual;
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:09.502
BEGIN SYS.DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:09.765
select PARAMETER,VALUE from nls_session_parameters where PARAMETER in('NLS_NUMERIC_CHARACTERS','NLS_DATE_FORMAT','NLS_CURRENCY');
-- Elapsed Time: 0.069 seconds
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:09.950
select to_char(9,'9C') from dual;
-- Elapsed Time: 0.069 seconds
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:10.034
create table MY_TABLE(id number);
-- Elapsed Time: 0.069 seconds
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:10.166
drop table MY_TABLE;
-- Elapsed Time: 0.068 seconds
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:10.235
begin
sys.dbms_output.get_lines(:LINES, :NUMLINES);
end;
:LINES(VARCHAR[32767],)=<PL/SQL TABLE[10]>
:NUMLINES(INTEGER,)=0
-- Elapsed Time: 0.068 seconds
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:51.842
select user as owner, con.constraint_name
from user_constraints con
where owner = :own and con.table_name = :tn;
own(VARCHAR[11],IN)='MY_SCHEMA_NAME'
:tn(VARCHAR[18],IN)='MY_TABLE'
-- Elapsed Time: 0.069 seconds
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:33:09.016
Select created, last_ddl_time, object_id, status
from sys.user_objects
where object_name = :nm
and object_type = :t;
:nm(VARCHAR[18],IN)='MY_TABLE'
:t(VARCHAR[5],IN)='TABLE'
-- Elapsed Time: 0.063 seconds
Thank you in advance for your continued assistance in resolving this on-going issue.
