Toad Not Responding During Login

I've installed Toad for Oracle 16.2 on a windows 10 machine with an Oracle Client 19c.

During login, the toad is not responding for a minute or longer before being able to connect to the database (11gR2). Any idea what causes it? When using SQL developer, I can connect immediately.

Right after a connection is made, Toad runs a big PL/SQL block to get determine your privileges, etc. Usually it runs very fast, but with some databases, it is slow. You can see if this is the problem like this:

  1. Start Toad but don't connect yet. Exit out of the login window.
  2. Go to main menu -> database -> spool sql -> spool to screen. This opens a window that records all of the SQL that Toad executes.
  3. In the spool sql window, enable timings like this:
    image
  4. Now make your connection, and you'll be able to see from the Spool SQL output if it's the big pl/sql block that is slow, or something else. For me, that block runs in 0.2 seconds on 19c.

You can post the whole output here if you want.

Hi John,

Thank you for the response. Please see the spool below:


----------------------------------
-- Timestamp: 17:27:04.581
Connect: HR@HRMS

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 17:27:05.706
SELECT version, product, sysdate, null FROM sys.PRODUCT_COMPONENT_VERSION WHERE UPPER(PRODUCT) LIKE '%ORACLE%';
-- Elapsed Time: 0.015 seconds
-- Rows fetched: 1

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 17:27:05.721
Declare
  Toad_Security_Exists number;
  debug_connect_session number;
  alter_any_table number;
  alter_session number;
  alter_system number;
  alter_database number;
  alter_tablespace number;
  alter_user number;
  create_table number;
  create_role number;
  create_trigger number;
  create_sequence number;
  administer_database_trigger number;
  administer_resource_manager number;
  create_any_index number;
  create_any_sequence number;
  create_any_table number;
  create_any_trigger number;
  debug_any_procedure number;
  drop_any_sequence number;
  execute_any_procedure number;
  select_any_table number;
  delete_any_table number;
  insert_any_table number;
  grant_any_object_priv number;
  grant_any_role number;
  grant_any_privilege number;
  select_any_dictionary number;
  update_any_table number;
  create_public_syn number;
  has_dba_synonyms number;
  has_dba_objects number;
  select_catalog_role number;
  db_charset varchar2 (128);
  utplsql_ver varchar2 (60);
  utplsql_own varchar2 (128);
  e_inherit_privs EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_inherit_privs, -6598);
  compatible_version varchar2(60);
  cant_access_vparameter integer;
  sid number;
  qry_str varchar2(4000);
  has_dbms_java number;
  has_dbms_debug number;
  local_trans_id varchar2(60);
  qu_version_owner varchar2(128);
  CTRepositoryVersionMajor number;
  CTRepositoryVersionMinor number;
  CTRepositoryVersionRevision number;
  CTRepositoryVersionBuild number;
  Is_RAC number;
  instance_id number;
  instance_name varchar2(100);
  server_name varchar2(100);
  OS_USERNAME varchar2(60);
  radix varchar2(1);
  dbid varchar2(30);
  dbname varchar2(9);
  authentication_type varchar2(30);
  IsExadata number;
  AmazonRDSPackage varchar2(128);
  cx_table_count number;
  -- 
  -- swap out ALL_ views for DBA_ views if possible
  function check_for_dba(in_str in varchar2) return varchar2 is
      result varchar2(4000);
    begin
      result := in_str;
      if has_dba_objects > 0 then
        result := REPLACE (result, 'all_objects', 'dba_objects');
      end if;

      if has_dba_synonyms > 0 then
        result := REPLACE (result, 'all_synonyms', 'dba_synonyms');
      end if;
      return result;
    end;

Begin
  -- Put Toad data into v$session columns
  Begin
    SYS.DBMS_APPLICATION_INFO.SET_MODULE(:IN_MDL, :IN_ID);
  Exception
    When others then null; -- we can live without this.
  End;
  -- 
  -- Retrieve specific system privileges
  select
    sum(decode(Privilege, 'ADMINISTER DATABASE TRIGGER', 1, 0)) administer_database_trigger,
    sum(decode(Privilege, 'ADMINISTER RESOURCE MANAGER', 1, 0)) administer_resource_manager,
    sum(decode(Privilege, 'ALTER ANY TABLE', 1, 0)) alter_any_table,
    sum(decode(Privilege, 'ALTER DATABASE', 1, 0)) alter_database,
    sum(decode(Privilege, 'ALTER TABLESPACE', 1, 0)) alter_tablespace,
    sum(decode(Privilege, 'ALTER SESSION', 1, 0)) alter_session,
    sum(decode(Privilege, 'ALTER SYSTEM', 1, 0)) alter_system,
    sum(decode(Privilege, 'ALTER USER', 1, 0)) alter_user,
    sum(decode(Privilege, 'CREATE ANY INDEX', 1, 0)) create_any_ind,
    sum(decode(Privilege, 'CREATE ANY SEQUENCE', 1, 0)) create_any_seq,
    sum(decode(Privilege, 'CREATE ANY TABLE', 1, 0)) create_any_table,
    sum(decode(Privilege, 'CREATE ANY TRIGGER', 1, 0)) create_any_trig,
    sum(decode(Privilege, 'CREATE PUBLIC SYNONYM', 1, 0)) create_public_syn,
    sum(decode(Privilege, 'CREATE ROLE', 1, 0)) create_role,
    sum(decode(Privilege, 'CREATE SEQUENCE', 1, 0)) create_sequence,
    sum(decode(Privilege, 'CREATE TABLE', 1, 0)) create_table,
    sum(decode(Privilege, 'CREATE TRIGGER', 1, 0)) create_trigger,
    sum(decode(Privilege, 'DEBUG ANY PROCEDURE', 1, 0)) debug_any_proc,
    sum(decode(Privilege, 'DEBUG CONNECT SESSION', 1, 0)) debug_connect_session,
    sum(decode(Privilege, 'DROP ANY SEQUENCE', 1, 0)) drop_any_seq,
    sum(decode(Privilege, 'EXECUTE ANY PROCEDURE', 1, 0)) execute_any_procedure,
    sum(decode(Privilege, 'GRANT ANY OBJECT PRIVILEGE', 1, 0)) grant_any_object_priv,
    sum(decode(Privilege, 'GRANT ANY ROLE', 1, 0)) grant_any_role,
    sum(decode(Privilege, 'GRANT ANY PRIVILEGE', 1, 0)) grant_any_privilege,
    sum(decode(Privilege, 'SELECT ANY DICTIONARY', 1, 0)) select_any_dictionary,
    sum(decode(Privilege, 'SELECT ANY TABLE', 1, 0)) select_any_table,
    sum(decode(Privilege, 'DELETE ANY TABLE', 1, 0)) delete_any_table,
    sum(decode(Privilege, 'INSERT ANY TABLE', 1, 0)) insert_table,
    sum(decode(Privilege, 'UPDATE ANY TABLE', 1, 0)) update_any_table
  into administer_database_trigger, administer_resource_manager, alter_any_table, alter_database, alter_tablespace,
       alter_session, alter_system, alter_user, create_any_index, create_any_sequence, create_any_table,
       create_any_trigger, create_public_syn, create_role, create_sequence, create_table, create_trigger,
       debug_any_procedure, debug_connect_session, drop_any_sequence, execute_any_procedure, grant_any_object_priv,
       grant_any_role, grant_any_privilege, select_any_dictionary, select_any_table, delete_any_table,
       insert_any_table, update_any_table
  from sys.session_privs
  where privilege in ('DEBUG CONNECT SESSION',
                      'ALTER ANY TABLE',
                      'ALTER SESSION',
                      'ALTER SYSTEM',
                      'ALTER DATABASE',
                      'ALTER TABLESPACE',
                      'ALTER USER',
                      'ADMINISTER DATABASE TRIGGER',
                      'ADMINISTER RESOURCE MANAGER',
                      'CREATE TABLE',
                      'CREATE ROLE',
                      'CREATE TRIGGER',
                      'CREATE SEQUENCE',
                      'EXECUTE ANY PROCEDURE',
                      'SELECT ANY TABLE',
                      'DELETE ANY TABLE',
                      'INSERT ANY TABLE',
                      'UPDATE ANY TABLE',
                      'GRANT ANY OBJECT PRIVILEGE',
                      'GRANT ANY ROLE',
                      'GRANT ANY PRIVILEGE',
                      'SELECT ANY DICTIONARY',
                      'CREATE ANY INDEX',
                      'CREATE ANY SEQUENCE',
                      'CREATE ANY TABLE',
                      'CREATE ANY TRIGGER',
                      'DEBUG ANY PROCEDURE',
                      'DROP ANY SEQUENCE',
                      'CREATE PUBLIC SYNONYM');
  -- 
  -- Check for SELECT_CATALOG_ROLE.  It must be a default role to appear in the result of this query.
  Select sum(decode(role, 'SELECT_CATALOG_ROLE', 1, 0)) select_catalog_role
  into   select_catalog_role
  from   sys.session_roles
  where  role = 'SELECT_CATALOG_ROLE';
  -- 
  -- determine database character set
  select value into db_charset
  from   sys.nls_database_parameters
  where  parameter = 'NLS_CHARACTERSET';
  -- 
  -- check for other ways dba_objects and dba_synonyms could be granted
 if (select_any_dictionary = 1) or (select_catalog_role = 1) then
   has_dba_synonyms := 1;
   has_dba_objects := 1;
 else
    --
    -- just try to select from dba_synonyms and dba_objects as a privilege check
    begin
      qry_str := 'select 1 from DBA_SYNONYMS where rownum = 1';
      execute immediate qry_str into has_dba_synonyms;
    exception
      when others then
        has_dba_synonyms := 0;
    end;

    begin
      qry_str := 'select 1 from DBA_OBJECTS where rownum = 1';
      execute immediate qry_str into has_dba_objects;
    exception
      when others then
        has_dba_objects := 0;
    end;
  end if;
  -- 
  -- Is Toad Security in effect?
  begin
    qry_str := 'Select 1
                 from toad.toad_restrictions
                 where (user_name = USER or user_name in ( select ROLE from sys.session_roles))
                 and   rownum = 1';
    execute immediate qry_str into Toad_Security_Exists;
  exception
    when others then
      Toad_Security_Exists := 0;
  end;
  -- 
  -- retrieve OS Username
  begin
    qry_str := 'select SYS_CONTEXT(''USERENV'',''OS_USER'') from dual';
    execute immediate qry_str into OS_USERNAME;
  exception
    when others then
      OS_USERNAME := null;
  end;
  -- 
  -- retrieve SID if possible
  begin
    qry_str := 'select SYS_CONTEXT(''USERENV'',''SID'') from dual';
    execute immediate qry_str into sid;
  exception
    when others then
      sid := -1;
  end;
  --
  -- get compatible version
    begin
      cant_access_vparameter := 0;
      qry_str := 'select value from v$parameter where name = ''compatible''';
      execute immediate qry_str into compatible_version;
    exception
      when others then
        compatible_version := null;
        cant_access_vparameter := 1;
    end;
  -- 
  -- check for privileges on sys.dbms_transaction
  begin
    qry_str := 'select sys.dbms_transaction.local_transaction_id from dual';
    execute immediate qry_str into local_trans_id;
  exception
    when others then
      local_trans_id := '<error>';
  end;
  -- 
  -- determine if dbms_java and dbms_debug are installed
  qry_str := 'select sum(decode(object_name, ''DBMS_JAVA'', 1, 0)) jv,
                     sum(decode(object_name, ''DBMS_DEBUG'', 1, 0)) dbg
              from sys.all_objects
              where owner = ''SYS''
              and object_type = ''PACKAGE''
              and object_name in (''DBMS_JAVA'', ''DBMS_DEBUG'')';
  qry_str := check_for_dba(qry_str);
  begin
    execute immediate qry_str into has_dbms_java, has_dbms_debug;
  exception
    when others then
      has_dbms_java := 0;
      has_dbms_debug := 0;
  end;
  -- 
  -- determine if code tester repository is installed
  begin
    Select user into qu_version_owner
    FROM   sys.user_objects
    WHERE  object_name = 'QU_VERSION'
    AND    object_type in ('PACKAGE', 'SYNONYM')
    AND    rownum = 1;
  exception
    when others then
      qu_version_owner := null;
  end;
  -- if not found then look in other schemas
  if qu_version_owner is null then
    begin
      qry_str := 'select owner
                  from  sys.all_objects
                  where object_name = ''QU_VERSION''
                  and   object_type in (''PACKAGE'',''SYNONYM'')
                  and rownum = 1';
      qry_str := check_for_dba(qry_str);
      execute immediate qry_str into qu_version_owner;
    exception
      when others then
        qu_version_owner := null;
    end;
  end if;
  -- 
  -- if it is installed, determine code tester version
  CTRepositoryVersionMajor := -1;
  CTRepositoryVersionMinor := -1;
  CTRepositoryVersionRevision := -1;
  CTRepositoryVersionBuild := -1;
  if qu_version_owner is not null then
    begin
      qry_str := 'select QU_VERSION.major_number,
                         QU_VERSION.minor_number,
                         QU_VERSION.revision_number,
                         QU_VERSION.build_number
                  from dual';
      execute immediate qry_str into CTRepositoryVersionMajor, CTRepositoryVersionMinor, CTRepositoryVersionRevision, CTRepositoryVersionBuild;
    exception
      when others then
        null;
    end;
  end if;
  -- 
  -- determine if this is a RAC instance
  qry_str := 'begin if dbms_utility.is_cluster_database';
  qry_str := qry_str || ' then :isRAC := 1; else :isRAC := 0; end if; end;';
  begin
    execute immediate qry_str using out is_RAC;
  exception
    when others then
      is_RAC := 0;
  end;
  -- 
  -- determine instance id
  begin
    execute immediate 'select dbms_utility.current_instance from dual' into instance_id;
  exception
    when others then
      instance_id := 1;
  end;
  -- 
  -- determine instance name and server name if possible
  qry_str := 'select sys_context(''USERENV'',''INSTANCE_NAME''),
                     sys_context(''USERENV'',''SERVER_HOST'')
              from dual';
  begin
    execute immediate qry_str into instance_name, server_name;
  exception
    -- if we don't have privs on v$instance and not rac, then try to get db name
    when others then
      if is_RAC = 0 then
        begin
          qry_str := 'select sys_context(''USERENV'',''DB_NAME'') from dual';
          execute immediate qry_str into instance_name;
        exception
          when others then
             instance_name := null;
        end;
      end if;
  end;
  --
  -- get timestamp radix
  begin
    qry_str := 'select to_char(systimestamp, ''X'' ) from dual';
    execute immediate qry_str into radix;
  exception
    when others then
      radix := '.';
  end;
  -- 
  -- determine DBID, DBName if possible
  begin
    qry_str := 'select dbid, name from v$database';
    execute immediate qry_str into dbid, dbname;
  exception
    when others then
      null;
  end;
  --
  -- get authentication type
  begin
    qry_str := 'Select SYS_CONTEXT(''USERENV'', ''IDENTIFICATION_TYPE'') from dual';
    execute immediate qry_str into authentication_type;
  exception
    when others then
      authentication_type := null;
  end;
  -- 
  -- Is this an Amazon RDS Instance?
  begin
    qry_str := 'select object_name
                from sys.all_procedures
                where owner = ''RDSADMIN''
                and object_name = ''RDSADMIN_UTIL''
                and object_type = ''PACKAGE''
                and subprogram_id = 0';
    execute immediate qry_str into AmazonRDSPackage;
  exception
    when others then
      AmazonRDSPackage := null;
  end;
  -- 
  -- Is this an Exadata Instance?
  begin
    qry_str := 'select 1 from v$cell where rownum = 1';
    execute immediate qry_str into IsExadata;
  exception
    when others then
      IsExaData := 0;
  end;
  -- 
  -- check for existence of Codexpert tables
  Select count('x')
  into cx_table_count
  from sys.all_tables
  where owner in ('TOAD', USER)
  and table_name in ('CX_XPERTRUN', 'CX_XPERTLINES', 'CX_XPERTITEMS', 'CX_TYPES', 'CX_SEVERITYS', 'CX_SETRULES', 'CX_RULESETS', 'CX_METRICS', 'CX_METRICRANGES', 'CX_CATEGORYS', 'CX_BASERULES');
  -- 
  -- determine if utPLSQL is installed
  utplsql_ver := NULL;
  BEGIN
    -- Fetch version using the public API
    EXECUTE IMMEDIATE 'SELECT SUBSTR (ut.version (), 1, 60) FROM DUAL'
      INTO utplsql_ver;
  EXCEPTION
    -- Attempt direct access on ORA-06598
    WHEN e_inherit_privs THEN
    BEGIN
      qry_str := 'SELECT do.owner
                  FROM   sys.all_objects  do
                         JOIN sys.all_synonyms ds
                             ON     (do.owner = ds.table_owner)
                                AND (do.object_name = ds.table_name)
                  WHERE      ds.owner = ''PUBLIC''
                         AND ds.table_name = ''UT''
                         AND ds.synonym_name = ''UT''
                         AND do.object_type = ''PACKAGE''';

      qry_str := check_for_dba(qry_str);
      EXECUTE IMMEDIATE qry_str INTO utplsql_own;

      qry_str := 'begin :utplsql_ver := ' || utplsql_own || '.ut_utils.gc_version; end;';
      EXECUTE IMMEDIATE qry_str USING OUT utplsql_ver;

    EXCEPTION
      WHEN OTHERS THEN
        utplsql_ver := SQLCODE;
    END;
  WHEN OTHERS THEN
    utplsql_ver := SQLCODE;
  END;
  -- 
  -- Done with SQL. Assign results to output variables
  :OUT_DEBUG_CONNECT_SESSION := debug_connect_session;
  :OUT_ALTER_ANY_TABLE := alter_any_table;
  :OUT_ALTER_SESSION := alter_session;
  :OUT_ALTER_SYSTEM := alter_system;
  :OUT_ALTER_DATABASE := alter_database;
  :OUT_ALTER_TABLESPACE := alter_tablespace;
  :OUT_ALTER_USER := alter_user;
  :OUT_CREATE_TABLE := create_table;
  :OUT_CREATE_ROLE := create_role;
  :OUT_CREATE_TRIGGER := create_trigger;
  :OUT_CREATE_SEQUENCE := create_sequence;
  :OUT_ADMIN_DB_TRIGGER := administer_database_trigger;
  :OUT_ADMIN_RS_MAN := administer_resource_manager;
  :OUT_EXECUTE_ANY_PROCEDURE := execute_any_procedure;
  :OUT_CREATE_ANY_INDEX := create_any_index;
  :OUT_CREATE_ANY_SEQUENCE := create_any_sequence;
  :OUT_CREATE_ANY_TABLE := create_any_table;
  :OUT_CREATE_ANY_TRIGGER := create_any_trigger;
  :OUT_DROP_ANY_SEQUENCE := drop_any_sequence;
  :OUT_DEBUG_ANY_PROCEDURE := debug_any_procedure;
  :OUT_SELECT_ANY_TABLE := select_any_table;
  :OUT_DELETE_ANY_TABLE := delete_any_table;
  :OUT_INSERT_ANY_TABLE := insert_any_table;
  :OUT_UPDATE_ANY_TABLE := update_any_table;
  :OUT_GRANT_ANY_OBJECT_PRIV := grant_any_object_priv;
  :OUT_GRANT_ANY_ROLE := grant_any_role;
  :OUT_GRANT_ANY_PRIVILEGE := grant_any_privilege;
  :OUT_SELECT_ANY_DICTIONARY := select_any_dictionary;
  :OUT_CREATE_PUBLIC_SYN := create_public_syn;
  :OUT_SELECT_CATALOG_ROLE := select_catalog_role;
  :OUT_DB_CHARSET := db_charset;
  :OUT_USER := user;
  :OUT_CX_TABLE_COUNT := cx_table_count;
  :OUT_OSUSERNAME := OS_USERNAME;
  :OUT_COMPATIBLE_VERSION := compatible_version;
  :OUT_CANT_ACCESS_PARAMETER := cant_access_vparameter;
  :OUT_SID := sid;
  :OUT_TOAD_SEC_EXISTS := Toad_Security_Exists;
  :OUT_UTPLSQL_VER := utplsql_ver;
  :OUT_HAS_DBMS_JAVA := has_dbms_java;
  :OUT_HAS_DBMS_DEBUG := has_dbms_debug;
  :OUT_LOCAL_TRANS_ID := local_trans_id;
  :OUT_QU_VERSION_OWNER := qu_version_owner;
  :OUT_QU_VERSION_MAJOR := CTRepositoryVersionMajor;
  :OUT_QU_VERSION_MINOR := CTRepositoryVersionMinor;
  :OUT_QU_VERSION_REVISION := CTRepositoryVersionRevision;
  :OUT_QU_VERSION_BUILD := CTRepositoryVersionBuild;
  :OUT_IS_RAC := Is_RAC;
  :OUT_INSTANCE_ID := instance_id;
  :OUT_INSTANCE_NAME := instance_name;
  :OUT_SERVER_NAME := server_name;
  :OUT_DBID := dbid;
  :OUT_DBName := dbname;
  :OUT_RADIX := radix;
  :OUT_AUTHENTICATION_TYPE := authentication_type;
  :OUT_IS_EXADATA := IsExadata;
  :OUT_IS_AMAZON_RDS := AmazonRDSPackage;
end;
:IN_MDL(VARCHAR[17],IN)='TOAD 16.2.98.1741' 
:IN_ID(VARCHAR[27],IN)='62639376,411746480,62419216' 
:OUT_DEBUG_CONNECT_SESSION(INTEGER,OUT)=1 
:OUT_ALTER_ANY_TABLE(INTEGER,OUT)=1 
:OUT_ALTER_SESSION(INTEGER,OUT)=1 
:OUT_ALTER_SYSTEM(INTEGER,OUT)=1 
:OUT_ALTER_DATABASE(INTEGER,OUT)=1 
:OUT_ALTER_TABLESPACE(INTEGER,OUT)=1 
:OUT_ALTER_USER(INTEGER,OUT)=1 
:OUT_CREATE_TABLE(INTEGER,OUT)=1 
:OUT_CREATE_ROLE(INTEGER,OUT)=1 
:OUT_CREATE_TRIGGER(INTEGER,OUT)=1 
:OUT_CREATE_SEQUENCE(INTEGER,OUT)=1 
:OUT_ADMIN_DB_TRIGGER(INTEGER,OUT)=1 
:OUT_ADMIN_RS_MAN(INTEGER,OUT)=1 
:OUT_EXECUTE_ANY_PROCEDURE(INTEGER,OUT)=1 
:OUT_CREATE_ANY_INDEX(INTEGER,OUT)=1 
:OUT_CREATE_ANY_SEQUENCE(INTEGER,OUT)=1 
:OUT_CREATE_ANY_TABLE(INTEGER,OUT)=1 
:OUT_CREATE_ANY_TRIGGER(INTEGER,OUT)=1 
:OUT_DROP_ANY_SEQUENCE(INTEGER,OUT)=1 
:OUT_DEBUG_ANY_PROCEDURE(INTEGER,OUT)=1 
:OUT_SELECT_ANY_TABLE(INTEGER,OUT)=1 
:OUT_DELETE_ANY_TABLE(INTEGER,OUT)=1 
:OUT_INSERT_ANY_TABLE(INTEGER,OUT)=1 
:OUT_UPDATE_ANY_TABLE(INTEGER,OUT)=1 
:OUT_GRANT_ANY_OBJECT_PRIV(INTEGER,OUT)=1 
:OUT_GRANT_ANY_ROLE(INTEGER,OUT)=1 
:OUT_GRANT_ANY_PRIVILEGE(INTEGER,OUT)=1 
:OUT_SELECT_ANY_DICTIONARY(INTEGER,OUT)=1 
:OUT_CREATE_PUBLIC_SYN(INTEGER,OUT)=1 
:OUT_SELECT_CATALOG_ROLE(INTEGER,OUT)=1 
:OUT_DB_CHARSET(VARCHAR[12],OUT)='AR8ISO8859P6' 
:OUT_USER(VARCHAR[6],OUT)='HR' 
:OUT_CX_TABLE_COUNT(INTEGER,OUT)=0 
:OUT_OSUSERNAME(VARCHAR[4],OUT)='musr' 
:OUT_COMPATIBLE_VERSION(VARCHAR[10],OUT)='11.2.0.4.0' 
:OUT_CANT_ACCESS_PARAMETER(INTEGER,OUT)=0 
:OUT_SID(VARCHAR[4],OUT)='1900' 
:OUT_TOAD_SEC_EXISTS(INTEGER,OUT)=0 
:OUT_UTPLSQL_VER(VARCHAR[4],OUT)='-904' 
:OUT_HAS_DBMS_JAVA(INTEGER,OUT)=1 
:OUT_HAS_DBMS_DEBUG(INTEGER,OUT)=1 
:OUT_LOCAL_TRANS_ID(VARCHAR[0],OUT)=<NULL> 
:OUT_QU_VERSION_OWNER(VARCHAR[0],OUT)=<NULL> 
:OUT_QU_VERSION_MAJOR(INTEGER,OUT)=-1 
:OUT_QU_VERSION_MINOR(INTEGER,OUT)=-1 
:OUT_QU_VERSION_REVISION(INTEGER,OUT)=-1 
:OUT_QU_VERSION_BUILD(INTEGER,OUT)=-1 
:OUT_IS_RAC(INTEGER,OUT)=0 
:OUT_INSTANCE_ID(INTEGER,OUT)=1 
:OUT_INSTANCE_NAME(VARCHAR[4],OUT)='HRMS' 
:OUT_SERVER_NAME(VARCHAR[11],OUT)='hq-ppdbakis' 
:OUT_DBID(VARCHAR[10],OUT)='2107418215' 
:OUT_DBName(VARCHAR[4],OUT)='HRMS' 
:OUT_RADIX(VARCHAR[1],OUT)='.' 
:OUT_AUTHENTICATION_TYPE(VARCHAR[5],OUT)='LOCAL' 
:OUT_IS_EXADATA(INTEGER,OUT)=0 
:OUT_IS_AMAZON_RDS(VARCHAR[0],OUT)=<NULL> 
-- Elapsed Time: 2 minutes 36 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 17:29:42.095
SELECT u.NAME
FROM sys.USER$ u
WHERE u.TYPE# = 1
order by 1;
-- Elapsed Time: 0 seconds
-- Rows fetched: 38

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 17:29:42.360
DECLARE 
  TYPE tc_syn_type IS VARRAY (4) OF VARCHAR2 (30); 

  syns   CONSTANT tc_syn_type := tc_syn_type ('QUEST_TEAM_CODING', 'DELL_COM_TEAM_CODING', 'QUEST_COM_TEAM_CODING', 'SQLNAV4_VCS_CONFIG'); 
  local_result    INTEGER; 
  local_temp      INTEGER; 
BEGIN 
  local_result := 0; 

  -- Look for Team Coding synonym 
  FOR i IN syns.FIRST .. syns.LAST 
  LOOP 
    BEGIN 
      BEGIN 
        -- Query by the synonym name to see if the underlying table exists 
        EXECUTE IMMEDIATE 'SELECT 1 FROM ' || syns(i) 
          INTO local_temp; 
      EXCEPTION 
        WHEN NO_DATA_FOUND THEN
          NULL; 
      END; 

      -- If successful, set the output value 
      local_result := 1; 

      EXIT; 
    EXCEPTION 
      WHEN OTHERS THEN 
        NULL; -- we still want to check other values. 
    END; 
  END LOOP; 

  -- Populate out var 
  :out_res := local_result; 
END;
:out_res(INTEGER,OUT)=0 
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 17:29:43.157
select banner from v$version;
-- Elapsed Time: 0.016 seconds
-- Rows fetched: 5

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 17:29:45.250
DECLARE
  TYPE table_names IS VARRAY (3) OF VARCHAR2 (30);
  currval_not_defined          EXCEPTION;
  PRAGMA EXCEPTION_INIT (currval_not_defined, -8002);
  sys_prof_valid               NUMBER;
  toad_prof_valid              NUMBER;
  rst                          NUMBER;
  sys_pack                     VARCHAR2 (17);
  prof_seq                     VARCHAR2 (24);
  tables                       table_names;
  dbms_prof_tables    CONSTANT table_names
    := table_names ('plsql_profiler_data',
                    'plsql_profiler_runs',
                    'plsql_profiler_units') ;
  dbms_hprof_tables   CONSTANT table_names
    := table_names ('dbmshp_runs',
                    'dbmshp_function_info',
                    'dbmshp_parent_child_info') ;
  /*---------------------------------------------------------------------------
  -- Verify that the specified package exists
  ---------------------------------------------------------------------------*/
  FUNCTION CheckPackage (ObjectName IN VARCHAR2)
    RETURN NUMBER
  AS
    valid     NUMBER;
    err_msg   VARCHAR2 (2000);
  BEGIN
    valid := 1;
    BEGIN
      EXECUTE IMMEDIATE 'BEGIN ' || ObjectName || '."3948941111051215261438227990"; END;';
    EXCEPTION
      WHEN OTHERS
      THEN
        err_msg := SQLERRM;
        -- If error is for missing package member then we are OK
        IF INSTR (err_msg, 'PLS-00302') > 0
        THEN
          valid := 1;
        ELSE
          valid := 0;
        END IF;
    END;
    RETURN valid;
  END;
BEGIN
  IF :check_hierarchical = 0
  THEN
    tables := dbms_prof_tables;
    sys_pack := 'sys.dbms_profiler';
    prof_seq := 'plsql_profiler_runnumber';
  ELSE
    tables := dbms_hprof_tables;
    sys_pack := 'sys.dbms_hprof';
    prof_seq := 'dbmshp_runnumber';
  END IF;
  -- Check for the SYS.DBMS_PROFILER package
  sys_prof_valid := CheckPackage (sys_pack);
  -- If it exists, check for the other profiler objects
  IF sys_prof_valid = 1
  THEN
    toad_prof_valid := 1;
    BEGIN
      -- Check for all tables
      FOR i IN tables.FIRST .. tables.LAST
      LOOP
        BEGIN
          EXECUTE IMMEDIATE 'SELECT 1 FROM ' || tables (i) || ' WHERE 0 = 1'
            INTO rst;
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
            toad_prof_valid := 1;
          WHEN OTHERS
          THEN
            RAISE;
        END;
      END LOOP;
      -- Check for the sequence
      BEGIN
        EXECUTE IMMEDIATE 'SELECT ' || prof_seq || '.currval FROM DUAL';
      EXCEPTION
        WHEN currval_not_defined
        THEN
          toad_prof_valid := 1;
        WHEN OTHERS
        THEN
          RAISE;
      END;
      -- Check for the TOAD_PROFILER package
      IF :check_hierarchical <> 1
      THEN
        toad_prof_valid := CheckPackage ('toad_profiler');
      END IF;
    EXCEPTION
      WHEN OTHERS
      THEN
        toad_prof_valid := 0;
    END;
  ELSE
    toad_prof_valid := 0;
  END IF;
  :out_sys_prof_valid := sys_prof_valid;
  :out_toad_prof_valid := toad_prof_valid;
END;
:check_hierarchical(INTEGER,IN)=0 
:out_sys_prof_valid(INTEGER,OUT)=<NULL> 
:out_toad_prof_valid(INTEGER,OUT)=<NULL> 

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 17:29:46.984
select object_name nam, Decode(object_type, 'TABLE', 1, 'VIEW', 2, 3) typ
from   sys.user_objects
where  object_type in ('TABLE','PROCEDURE', 'PACKAGE', 'FUNCTION')
order by nam asc;
-- Elapsed Time: 0.827 seconds
-- Rows fetched: 1396

Yeah looks like that PL/SQL block is to blame. 2:36 is not acceptable.

If you want, you can take the individual SQLs out of it and try running them separately in the Editor to see which one is slow. You'll have to edit out the "into" part, of course.

Usually it can be resolved by updating statistics on the database.

Thanks John. I'll have a look.

When trying to re-run the block takes more than 2mins and is just completed in less than a second.


----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:56:21.961
Declare
  Toad_Security_Exists number;
  debug_connect_session number;
  alter_any_table number;
  alter_session number;
  alter_system number;
  alter_database number;
  alter_tablespace number;
  alter_user number;
  create_table number;
  create_role number;
  create_trigger number;
  create_sequence number;
  administer_database_trigger number;
  administer_resource_manager number;
  create_any_index number;
  create_any_sequence number;
  create_any_table number;
  create_any_trigger number;
  debug_any_procedure number;
  drop_any_sequence number;
  execute_any_procedure number;
  select_any_table number;
  delete_any_table number;
  insert_any_table number;
  grant_any_object_priv number;
  grant_any_role number;
  grant_any_privilege number;
  select_any_dictionary number;
  update_any_table number;
  create_public_syn number;
  has_dba_synonyms number;
  has_dba_objects number;
  select_catalog_role number;
  db_charset varchar2 (128);
  utplsql_ver varchar2 (60);
  utplsql_own varchar2 (128);
  e_inherit_privs EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_inherit_privs, -6598);
  compatible_version varchar2(60);
  cant_access_vparameter integer;
  sid number;
  qry_str varchar2(4000);
  has_dbms_java number;
  has_dbms_debug number;
  local_trans_id varchar2(60);
  qu_version_owner varchar2(128);
  CTRepositoryVersionMajor number;
  CTRepositoryVersionMinor number;
  CTRepositoryVersionRevision number;
  CTRepositoryVersionBuild number;
  Is_RAC number;
  instance_id number;
  instance_name varchar2(100);
  server_name varchar2(100);
  OS_USERNAME varchar2(60);
  radix varchar2(1);
  dbid varchar2(30);
  dbname varchar2(9);
  authentication_type varchar2(30);
  IsExadata number;
  AmazonRDSPackage varchar2(128);
  cx_table_count number;
  -- 
  -- swap out ALL_ views for DBA_ views if possible
  function check_for_dba(in_str in varchar2) return varchar2 is
      result varchar2(4000);
    begin
      result := in_str;
      if has_dba_objects > 0 then
        result := REPLACE (result, 'all_objects', 'dba_objects');
      end if;

      if has_dba_synonyms > 0 then
        result := REPLACE (result, 'all_synonyms', 'dba_synonyms');
      end if;
      return result;
    end;

Begin
  -- Put Toad data into v$session columns
  Begin
    SYS.DBMS_APPLICATION_INFO.SET_MODULE('TOAD 16.2.98.1741', '62639376,411746480,62419216');
  Exception
    When others then null; -- we can live without this.
  End;
  -- 
  -- Retrieve specific system privileges
  select
    sum(decode(Privilege, 'ADMINISTER DATABASE TRIGGER', 1, 0)) administer_database_trigger,
    sum(decode(Privilege, 'ADMINISTER RESOURCE MANAGER', 1, 0)) administer_resource_manager,
    sum(decode(Privilege, 'ALTER ANY TABLE', 1, 0)) alter_any_table,
    sum(decode(Privilege, 'ALTER DATABASE', 1, 0)) alter_database,
    sum(decode(Privilege, 'ALTER TABLESPACE', 1, 0)) alter_tablespace,
    sum(decode(Privilege, 'ALTER SESSION', 1, 0)) alter_session,
    sum(decode(Privilege, 'ALTER SYSTEM', 1, 0)) alter_system,
    sum(decode(Privilege, 'ALTER USER', 1, 0)) alter_user,
    sum(decode(Privilege, 'CREATE ANY INDEX', 1, 0)) create_any_ind,
    sum(decode(Privilege, 'CREATE ANY SEQUENCE', 1, 0)) create_any_seq,
    sum(decode(Privilege, 'CREATE ANY TABLE', 1, 0)) create_any_table,
    sum(decode(Privilege, 'CREATE ANY TRIGGER', 1, 0)) create_any_trig,
    sum(decode(Privilege, 'CREATE PUBLIC SYNONYM', 1, 0)) create_public_syn,
    sum(decode(Privilege, 'CREATE ROLE', 1, 0)) create_role,
    sum(decode(Privilege, 'CREATE SEQUENCE', 1, 0)) create_sequence,
    sum(decode(Privilege, 'CREATE TABLE', 1, 0)) create_table,
    sum(decode(Privilege, 'CREATE TRIGGER', 1, 0)) create_trigger,
    sum(decode(Privilege, 'DEBUG ANY PROCEDURE', 1, 0)) debug_any_proc,
    sum(decode(Privilege, 'DEBUG CONNECT SESSION', 1, 0)) debug_connect_session,
    sum(decode(Privilege, 'DROP ANY SEQUENCE', 1, 0)) drop_any_seq,
    sum(decode(Privilege, 'EXECUTE ANY PROCEDURE', 1, 0)) execute_any_procedure,
    sum(decode(Privilege, 'GRANT ANY OBJECT PRIVILEGE', 1, 0)) grant_any_object_priv,
    sum(decode(Privilege, 'GRANT ANY ROLE', 1, 0)) grant_any_role,
    sum(decode(Privilege, 'GRANT ANY PRIVILEGE', 1, 0)) grant_any_privilege,
    sum(decode(Privilege, 'SELECT ANY DICTIONARY', 1, 0)) select_any_dictionary,
    sum(decode(Privilege, 'SELECT ANY TABLE', 1, 0)) select_any_table,
    sum(decode(Privilege, 'DELETE ANY TABLE', 1, 0)) delete_any_table,
    sum(decode(Privilege, 'INSERT ANY TABLE', 1, 0)) insert_table,
    sum(decode(Privilege, 'UPDATE ANY TABLE', 1, 0)) update_any_table
  into administer_database_trigger, administer_resource_manager, alter_any_table, alter_database, alter_tablespace,
       alter_session, alter_system, alter_user, create_any_index, create_any_sequence, create_any_table,
       create_any_trigger, create_public_syn, create_role, create_sequence, create_table, create_trigger,
       debug_any_procedure, debug_connect_session, drop_any_sequence, execute_any_procedure, grant_any_object_priv,
       grant_any_role, grant_any_privilege, select_any_dictionary, select_any_table, delete_any_table,
       insert_any_table, update_any_table
  from sys.session_privs
  where privilege in ('DEBUG CONNECT SESSION',
                      'ALTER ANY TABLE',
                      'ALTER SESSION',
                      'ALTER SYSTEM',
                      'ALTER DATABASE',
                      'ALTER TABLESPACE',
                      'ALTER USER',
                      'ADMINISTER DATABASE TRIGGER',
                      'ADMINISTER RESOURCE MANAGER',
                      'CREATE TABLE',
                      'CREATE ROLE',
                      'CREATE TRIGGER',
                      'CREATE SEQUENCE',
                      'EXECUTE ANY PROCEDURE',
                      'SELECT ANY TABLE',
                      'DELETE ANY TABLE',
                      'INSERT ANY TABLE',
                      'UPDATE ANY TABLE',
                      'GRANT ANY OBJECT PRIVILEGE',
                      'GRANT ANY ROLE',
                      'GRANT ANY PRIVILEGE',
                      'SELECT ANY DICTIONARY',
                      'CREATE ANY INDEX',
                      'CREATE ANY SEQUENCE',
                      'CREATE ANY TABLE',
                      'CREATE ANY TRIGGER',
                      'DEBUG ANY PROCEDURE',
                      'DROP ANY SEQUENCE',
                      'CREATE PUBLIC SYNONYM');
  -- 
  -- Check for SELECT_CATALOG_ROLE.  It must be a default role to appear in the result of this query.
  Select sum(decode(role, 'SELECT_CATALOG_ROLE', 1, 0)) select_catalog_role
  into   select_catalog_role
  from   sys.session_roles
  where  role = 'SELECT_CATALOG_ROLE';
  -- 
  -- determine database character set
  select value into db_charset
  from   sys.nls_database_parameters
  where  parameter = 'NLS_CHARACTERSET';
  -- 
  -- check for other ways dba_objects and dba_synonyms could be granted
 if (select_any_dictionary = 1) or (select_catalog_role = 1) then
   has_dba_synonyms := 1;
   has_dba_objects := 1;
 else
    --
    -- just try to select from dba_synonyms and dba_objects as a privilege check
    begin
      qry_str := 'select 1 from DBA_SYNONYMS where rownum = 1';
      execute immediate qry_str into has_dba_synonyms;
    exception
      when others then
        has_dba_synonyms := 0;
    end;

    begin
      qry_str := 'select 1 from DBA_OBJECTS where rownum = 1';
      execute immediate qry_str into has_dba_objects;
    exception
      when others then
        has_dba_objects := 0;
    end;
  end if;
  -- 
  -- Is Toad Security in effect?
  begin
    qry_str := 'Select 1
                 from toad.toad_restrictions
                 where (user_name = USER or user_name in ( select ROLE from sys.session_roles))
                 and   rownum = 1';
    execute immediate qry_str into Toad_Security_Exists;
  exception
    when others then
      Toad_Security_Exists := 0;
  end;
  -- 
  -- retrieve OS Username
  begin
    qry_str := 'select SYS_CONTEXT(''USERENV'',''OS_USER'') from dual';
    execute immediate qry_str into OS_USERNAME;
  exception
    when others then
      OS_USERNAME := null;
  end;
  -- 
  -- retrieve SID if possible
  begin
    qry_str := 'select SYS_CONTEXT(''USERENV'',''SID'') from dual';
    execute immediate qry_str into sid;
  exception
    when others then
      sid := -1;
  end;
  --
  -- get compatible version
    begin
      cant_access_vparameter := 0;
      qry_str := 'select value from v$parameter where name = ''compatible''';
      execute immediate qry_str into compatible_version;
    exception
      when others then
        compatible_version := null;
        cant_access_vparameter := 1;
    end;
  -- 
  -- check for privileges on sys.dbms_transaction
  begin
    qry_str := 'select sys.dbms_transaction.local_transaction_id from dual';
    execute immediate qry_str into local_trans_id;
  exception
    when others then
      local_trans_id := '<error>';
  end;
  -- 
  -- determine if dbms_java and dbms_debug are installed
  qry_str := 'select sum(decode(object_name, ''DBMS_JAVA'', 1, 0)) jv,
                     sum(decode(object_name, ''DBMS_DEBUG'', 1, 0)) dbg
              from sys.all_objects
              where owner = ''SYS''
              and object_type = ''PACKAGE''
              and object_name in (''DBMS_JAVA'', ''DBMS_DEBUG'')';
  qry_str := check_for_dba(qry_str);
  begin
    execute immediate qry_str into has_dbms_java, has_dbms_debug;
  exception
    when others then
      has_dbms_java := 0;
      has_dbms_debug := 0;
  end;
  -- 
  -- determine if code tester repository is installed
  begin
    Select user into qu_version_owner
    FROM   sys.user_objects
    WHERE  object_name = 'QU_VERSION'
    AND    object_type in ('PACKAGE', 'SYNONYM')
    AND    rownum = 1;
  exception
    when others then
      qu_version_owner := null;
  end;
  -- if not found then look in other schemas
  if qu_version_owner is null then
    begin
      qry_str := 'select owner
                  from  sys.all_objects
                  where object_name = ''QU_VERSION''
                  and   object_type in (''PACKAGE'',''SYNONYM'')
                  and rownum = 1';
      qry_str := check_for_dba(qry_str);
      execute immediate qry_str into qu_version_owner;
    exception
      when others then
        qu_version_owner := null;
    end;
  end if;
  -- 
  -- if it is installed, determine code tester version
  CTRepositoryVersionMajor := -1;
  CTRepositoryVersionMinor := -1;
  CTRepositoryVersionRevision := -1;
  CTRepositoryVersionBuild := -1;
  if qu_version_owner is not null then
    begin
      qry_str := 'select QU_VERSION.major_number,
                         QU_VERSION.minor_number,
                         QU_VERSION.revision_number,
                         QU_VERSION.build_number
                  from dual';
      execute immediate qry_str into CTRepositoryVersionMajor, CTRepositoryVersionMinor, CTRepositoryVersionRevision, CTRepositoryVersionBuild;
    exception
      when others then
        null;
    end;
  end if;
  -- 
  -- determine if this is a RAC instance
  qry_str := 'begin if dbms_utility.is_cluster_database';
  qry_str := qry_str || ' then :isRAC := 1; else :isRAC := 0; end if; end;';
  begin
    execute immediate qry_str using out is_RAC;
  exception
    when others then
      is_RAC := 0;
  end;
  -- 
  -- determine instance id
  begin
    execute immediate 'select dbms_utility.current_instance from dual' into instance_id;
  exception
    when others then
      instance_id := 1;
  end;
  -- 
  -- determine instance name and server name if possible
  qry_str := 'select sys_context(''USERENV'',''INSTANCE_NAME''),
                     sys_context(''USERENV'',''SERVER_HOST'')
              from dual';
  begin
    execute immediate qry_str into instance_name, server_name;
  exception
    -- if we don't have privs on v$instance and not rac, then try to get db name
    when others then
      if is_RAC = 0 then
        begin
          qry_str := 'select sys_context(''USERENV'',''DB_NAME'') from dual';
          execute immediate qry_str into instance_name;
        exception
          when others then
             instance_name := null;
        end;
      end if;
  end;
  --
  -- get timestamp radix
  begin
    qry_str := 'select to_char(systimestamp, ''X'' ) from dual';
    execute immediate qry_str into radix;
  exception
    when others then
      radix := '.';
  end;
  -- 
  -- determine DBID, DBName if possible
  begin
    qry_str := 'select dbid, name from v$database';
    execute immediate qry_str into dbid, dbname;
  exception
    when others then
      null;
  end;
  --
  -- get authentication type
  begin
    qry_str := 'Select SYS_CONTEXT(''USERENV'', ''IDENTIFICATION_TYPE'') from dual';
    execute immediate qry_str into authentication_type;
  exception
    when others then
      authentication_type := null;
  end;
  -- 
  -- Is this an Amazon RDS Instance?
  begin
    qry_str := 'select object_name
                from sys.all_procedures
                where owner = ''RDSADMIN''
                and object_name = ''RDSADMIN_UTIL''
                and object_type = ''PACKAGE''
                and subprogram_id = 0';
    execute immediate qry_str into AmazonRDSPackage;
  exception
    when others then
      AmazonRDSPackage := null;
  end;
  -- 
  -- Is this an Exadata Instance?
  begin
    qry_str := 'select 1 from v$cell where rownum = 1';
    execute immediate qry_str into IsExadata;
  exception
    when others then
      IsExaData := 0;
  end;
  -- 
  -- check for existence of Codexpert tables
  Select count('x')
  into cx_table_count
  from sys.all_tables
  where owner in ('TOAD', USER)
  and table_name in ('CX_XPERTRUN', 'CX_XPERTLINES', 'CX_XPERTITEMS', 'CX_TYPES', 'CX_SEVERITYS', 'CX_SETRULES', 'CX_RULESETS', 'CX_METRICS', 'CX_METRICRANGES', 'CX_CATEGORYS', 'CX_BASERULES');
  -- 
  -- determine if utPLSQL is installed
  utplsql_ver := NULL;
  BEGIN
    -- Fetch version using the public API
    EXECUTE IMMEDIATE 'SELECT SUBSTR (ut.version (), 1, 60) FROM DUAL'
      INTO utplsql_ver;
  EXCEPTION
    -- Attempt direct access on ORA-06598
    WHEN e_inherit_privs THEN
    BEGIN
      qry_str := 'SELECT do.owner
                  FROM   sys.all_objects  do
                         JOIN sys.all_synonyms ds
                             ON     (do.owner = ds.table_owner)
                                AND (do.object_name = ds.table_name)
                  WHERE      ds.owner = ''PUBLIC''
                         AND ds.table_name = ''UT''
                         AND ds.synonym_name = ''UT''
                         AND do.object_type = ''PACKAGE''';

      qry_str := check_for_dba(qry_str);
      EXECUTE IMMEDIATE qry_str INTO utplsql_own;

      qry_str := 'begin :utplsql_ver := ' || utplsql_own || '.ut_utils.gc_version; end;';
      EXECUTE IMMEDIATE qry_str USING OUT utplsql_ver;

    EXCEPTION
      WHEN OTHERS THEN
        utplsql_ver := SQLCODE;
    END;
  WHEN OTHERS THEN
    utplsql_ver := SQLCODE;
  END;
end;
-- Elapsed Time: 0.016 seconds

I'm not sure that I understand. Does it take more than 2 minutes or less than 1 second when you re-run it?

If it takes 2 minutes, try running this script instead. I've broken it into pieces so can identify which part of it is slow.

Begin
   SYS.DBMS_APPLICATION_INFO.SET_MODULE('TOAD 16.2.98.1741', '62639376,411746480,62419216');
   Exception
   When others then null; -- we can live without this.
End;
/

select
sum(decode(Privilege, 'ADMINISTER DATABASE TRIGGER', 1, 0)) administer_database_trigger,
sum(decode(Privilege, 'ADMINISTER RESOURCE MANAGER', 1, 0)) administer_resource_manager,
sum(decode(Privilege, 'ALTER ANY TABLE', 1, 0)) alter_any_table,
sum(decode(Privilege, 'ALTER DATABASE', 1, 0)) alter_database,
sum(decode(Privilege, 'ALTER TABLESPACE', 1, 0)) alter_tablespace,
sum(decode(Privilege, 'ALTER SESSION', 1, 0)) alter_session,
sum(decode(Privilege, 'ALTER SYSTEM', 1, 0)) alter_system,
sum(decode(Privilege, 'ALTER USER', 1, 0)) alter_user,
sum(decode(Privilege, 'CREATE ANY INDEX', 1, 0)) create_any_ind,
sum(decode(Privilege, 'CREATE ANY SEQUENCE', 1, 0)) create_any_seq,
sum(decode(Privilege, 'CREATE ANY TABLE', 1, 0)) create_any_table,
sum(decode(Privilege, 'CREATE ANY TRIGGER', 1, 0)) create_any_trig,
sum(decode(Privilege, 'CREATE PUBLIC SYNONYM', 1, 0)) create_public_syn,
sum(decode(Privilege, 'CREATE ROLE', 1, 0)) create_role,
sum(decode(Privilege, 'CREATE SEQUENCE', 1, 0)) create_sequence,
sum(decode(Privilege, 'CREATE TABLE', 1, 0)) create_table,
sum(decode(Privilege, 'CREATE TRIGGER', 1, 0)) create_trigger,
sum(decode(Privilege, 'DEBUG ANY PROCEDURE', 1, 0)) debug_any_proc,
sum(decode(Privilege, 'DEBUG CONNECT SESSION', 1, 0)) debug_connect_session,
sum(decode(Privilege, 'DROP ANY SEQUENCE', 1, 0)) drop_any_seq,
sum(decode(Privilege, 'EXECUTE ANY PROCEDURE', 1, 0)) execute_any_procedure,
sum(decode(Privilege, 'GRANT ANY OBJECT PRIVILEGE', 1, 0)) grant_any_object_priv,
sum(decode(Privilege, 'GRANT ANY ROLE', 1, 0)) grant_any_role,
sum(decode(Privilege, 'GRANT ANY PRIVILEGE', 1, 0)) grant_any_privilege,
sum(decode(Privilege, 'SELECT ANY DICTIONARY', 1, 0)) select_any_dictionary,
sum(decode(Privilege, 'SELECT ANY TABLE', 1, 0)) select_any_table,
sum(decode(Privilege, 'DELETE ANY TABLE', 1, 0)) delete_any_table,
sum(decode(Privilege, 'INSERT ANY TABLE', 1, 0)) insert_table,
sum(decode(Privilege, 'UPDATE ANY TABLE', 1, 0)) update_any_table
from sys.session_privs
where privilege in ('DEBUG CONNECT SESSION',
                  'ALTER ANY TABLE',
                  'ALTER SESSION',
                  'ALTER SYSTEM',
                  'ALTER DATABASE',
                  'ALTER TABLESPACE',
                  'ALTER USER',
                  'ADMINISTER DATABASE TRIGGER',
                  'ADMINISTER RESOURCE MANAGER',
                  'CREATE TABLE',
                  'CREATE ROLE',
                  'CREATE TRIGGER',
                  'CREATE SEQUENCE',
                  'EXECUTE ANY PROCEDURE',
                  'SELECT ANY TABLE',
                  'DELETE ANY TABLE',
                  'INSERT ANY TABLE',
                  'UPDATE ANY TABLE',
                  'GRANT ANY OBJECT PRIVILEGE',
                  'GRANT ANY ROLE',
                  'GRANT ANY PRIVILEGE',
                  'SELECT ANY DICTIONARY',
                  'CREATE ANY INDEX',
                  'CREATE ANY SEQUENCE',
                  'CREATE ANY TABLE',
                  'CREATE ANY TRIGGER',
                  'DEBUG ANY PROCEDURE',
                  'DROP ANY SEQUENCE',
                  'CREATE PUBLIC SYNONYM');
                  
-- 
-- Check for SELECT_CATALOG_ROLE.  It must be a default role to appear in the result of this query.
Select sum(decode(role, 'SELECT_CATALOG_ROLE', 1, 0)) select_catalog_role
from   sys.session_roles
where  role = 'SELECT_CATALOG_ROLE';
-- 
-- determine database character set
select value 
from   sys.nls_database_parameters
where  parameter = 'NLS_CHARACTERSET';

-- 
-- Is Toad Security in effect?
declare
  qry_str varchar2(4000);
  Toad_Security_Exists number;
begin
  qry_str := 'Select 1
              from toad.toad_restrictions
              where (user_name = USER or user_name in ( select ROLE from sys.session_roles))
              and   rownum = 1';
  execute immediate qry_str into Toad_Security_Exists;
  exception
  when others then
    Toad_Security_Exists := 0;
end;
/

-- 
-- retrieve OS Username
declare
  qry_str varchar2(4000);
  OS_USERNAME varchar2(60);
begin
  qry_str := 'select SYS_CONTEXT(''USERENV'',''OS_USER'') from dual';
  execute immediate qry_str into OS_USERNAME;
  exception
  when others then
    OS_USERNAME := null;
end;

-- 
-- retrieve SID if possible
declare
  qry_str varchar2(4000);
  sid number;
begin
  qry_str := 'select SYS_CONTEXT(''USERENV'',''SID'') from dual';
  execute immediate qry_str into sid;
exception
  when others then
    sid := -1;
end;

--
-- get compatible version
declare
  qry_str varchar2(4000);
  compatible_version varchar2(60);
  cant_access_vparameter integer;
begin
  cant_access_vparameter := 0;
  qry_str := 'select value from v$parameter where name = ''compatible''';
  execute immediate qry_str into compatible_version;
exception
  when others then
    compatible_version := null;
    cant_access_vparameter := 1;
end;
-- 
-- check for privileges on sys.dbms_transaction
declare
  qry_str varchar2(4000);
  local_trans_id varchar2(60);
begin
qry_str := 'select sys.dbms_transaction.local_transaction_id from dual';
execute immediate qry_str into local_trans_id;
exception
when others then
  local_trans_id := '<error>';
end;

-- 
-- determine if dbms_java and dbms_debug are installed
declare
  qry_str varchar2(4000);
  has_dbms_java number;
  has_dbms_debug number;

  function check_for_dba(in_str in varchar2) return varchar2 is
     result varchar2(4000);
  begin
    result := in_str;
    result := REPLACE (result, 'all_objects', 'dba_objects');
    result := REPLACE (result, 'all_synonyms', 'dba_synonyms');
    return result;
  end;
  
begin
  qry_str := 'select sum(decode(object_name, ''DBMS_JAVA'', 1, 0)) jv,
                   sum(decode(object_name, ''DBMS_DEBUG'', 1, 0)) dbg
            from sys.all_objects
            where owner = ''SYS''
            and object_type = ''PACKAGE''
            and object_name in (''DBMS_JAVA'', ''DBMS_DEBUG'')';
  qry_str := check_for_dba(qry_str);

  begin
    execute immediate qry_str into has_dbms_java, has_dbms_debug;
  exception
  when others then
    has_dbms_java := 0;
    has_dbms_debug := 0;
  end;
end;
/

-- 
-- determine if code tester repository is installed
declare
  qu_version_owner varchar2(128);
begin
  begin
    Select user into qu_version_owner
    FROM   sys.user_objects
    WHERE  object_name = 'QU_VERSION'
    AND    object_type in ('PACKAGE', 'SYNONYM')
    AND    rownum = 1;
  exception
    when others then
      qu_version_owner := null;
  end;
end;
/

  
declare
  qry_str varchar2(4000);
  qu_version_owner varchar2(128);
  
  function check_for_dba(in_str in varchar2) return varchar2 is
     result varchar2(4000);
  begin
    result := in_str;
    result := REPLACE (result, 'all_objects', 'dba_objects');
    result := REPLACE (result, 'all_synonyms', 'dba_synonyms');
    return result;
  end;
  
begin
  qry_str := 'select owner
              from  sys.all_objects
              where object_name = ''QU_VERSION''
              and   object_type in (''PACKAGE'',''SYNONYM'')
              and rownum = 1';
  qry_str := check_for_dba(qry_str);
  execute immediate qry_str into qu_version_owner;
exception
  when others then
    qu_version_owner := null;
end;
/


declare
  qry_str varchar2(4000);
  Is_RAC number;
begin
  qry_str := qry_str || ' then :isRAC := 1; else :isRAC := 0; end if; end;';
  begin
    execute immediate qry_str using out is_RAC;
  exception
    when others then
      is_RAC := 0;
  end;
end;
/

declare
  instance_id number;
begin
  execute immediate 'select dbms_utility.current_instance from dual' into instance_id;
  exception
  when others then
    instance_id := 1;
end;
/


declare
  qry_str varchar2(4000);
  Is_RAC number;
  instance_name varchar2(100);
  server_name varchar2(100);
begin
  -- 
  -- determine instance name and server name if possible
  qry_str := 'select sys_context(''USERENV'',''INSTANCE_NAME''),
                   sys_context(''USERENV'',''SERVER_HOST'')
            from dual';
  begin
    execute immediate qry_str into instance_name, server_name;
    exception
      -- if we don't have privs on v$instance and not rac, then try to get db name
      when others then
        if is_RAC = 0 then
          begin
            qry_str := 'select sys_context(''USERENV'',''DB_NAME'') from dual';
            execute immediate qry_str into instance_name;
          exception
            when others then
               instance_name := null;
          end;
        end if;
    end;
end;
/
  
--
-- get timestamp radix
declare
  qry_str varchar2(4000);
  radix varchar2(1);
begin
  qry_str := 'select to_char(systimestamp, ''X'' ) from dual';
  execute immediate qry_str into radix;
  exception
  when others then
    radix := '.';
end;
/

declare
  qry_str varchar2(4000);
  dbid varchar2(30);
  dbname varchar2(9);
begin
  qry_str := 'select dbid, name from v$database';
  execute immediate qry_str into dbid, dbname;
exception
  when others then
    null;
end;
/

declare
  qry_str varchar2(4000);
  authentication_type varchar2(30);
begin
  qry_str := 'Select SYS_CONTEXT(''USERENV'', ''IDENTIFICATION_TYPE'') from dual';
  execute immediate qry_str into authentication_type;
exception
  when others then
    authentication_type := null;
end;
/

declare
  qry_str varchar2(4000);
  AmazonRDSPackage varchar2(128);
begin
  qry_str := 'select object_name
              from sys.all_procedures
              where owner = ''RDSADMIN''
              and object_name = ''RDSADMIN_UTIL''
              and object_type = ''PACKAGE''
              and subprogram_id = 0';
  execute immediate qry_str into AmazonRDSPackage;
exception
  when others then
    AmazonRDSPackage := null;
end;
/

declare
  qry_str varchar2(4000);
  IsExadata number;
begin
  qry_str := 'select 1 from v$cell where rownum = 1';
  execute immediate qry_str into IsExadata;
exception
  when others then
    IsExaData := 0;
end;
/

-- 
-- check for existence of Codexpert tables
declare
  cx_table_count number;
begin
  Select count('x')
  into cx_table_count
  from sys.all_tables
  where owner in ('TOAD', USER)
  and table_name in ('CX_XPERTRUN', 'CX_XPERTLINES', 'CX_XPERTITEMS', 'CX_TYPES', 'CX_SEVERITYS', 'CX_SETRULES', 'CX_RULESETS', 'CX_METRICS', 'CX_METRICRANGES', 'CX_CATEGORYS', 'CX_BASERULES');
end;
/

declare
  qry_str varchar2(4000);
  utplsql_ver varchar2 (60);
  utplsql_own varchar2 (128);
  e_inherit_privs EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_inherit_privs, -6598);
  
  function check_for_dba(in_str in varchar2) return varchar2 is
     result varchar2(4000);
  begin
    result := in_str;
    result := REPLACE (result, 'all_objects', 'dba_objects');
    result := REPLACE (result, 'all_synonyms', 'dba_synonyms');
    return result;
  end;
begin
  utplsql_ver := NULL;
  BEGIN
  -- Fetch version using the public API
  EXECUTE IMMEDIATE 'SELECT SUBSTR (ut.version (), 1, 60) FROM DUAL'
    INTO utplsql_ver;
  EXCEPTION
  -- Attempt direct access on ORA-06598
  WHEN e_inherit_privs THEN
  BEGIN
    qry_str := 'SELECT do.owner
                FROM   sys.all_objects  do
                       JOIN sys.all_synonyms ds
                           ON     (do.owner = ds.table_owner)
                              AND (do.object_name = ds.table_name)
                WHERE      ds.owner = ''PUBLIC''
                       AND ds.table_name = ''UT''
                       AND ds.synonym_name = ''UT''
                       AND do.object_type = ''PACKAGE''';
  
    qry_str := check_for_dba(qry_str);
    EXECUTE IMMEDIATE qry_str INTO utplsql_own;

    qry_str := 'begin :utplsql_ver := ' || utplsql_own || '.ut_utils.gc_version; end;';
    EXECUTE IMMEDIATE qry_str USING OUT utplsql_ver;
  
  EXCEPTION
    WHEN OTHERS THEN
      utplsql_ver := SQLCODE;
  END;
  WHEN OTHERS THEN
    utplsql_ver := SQLCODE;
  END;
end;
/

Thanks John.

I mean when re-running the block manually, it only took less than a sec.

I've tried to run the script you provided, seems no issue:


----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:42:51.817
Begin
   SYS.DBMS_APPLICATION_INFO.SET_MODULE('TOAD 16.2.98.1741', '62639376,411746480,62419216');
   Exception
   When others then null; -- we can live without this.
End;
-- Elapsed Time: 0.016 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:43:03.033
select
sum(decode(Privilege, 'ADMINISTER DATABASE TRIGGER', 1, 0)) administer_database_trigger,
sum(decode(Privilege, 'ADMINISTER RESOURCE MANAGER', 1, 0)) administer_resource_manager,
sum(decode(Privilege, 'ALTER ANY TABLE', 1, 0)) alter_any_table,
sum(decode(Privilege, 'ALTER DATABASE', 1, 0)) alter_database,
sum(decode(Privilege, 'ALTER TABLESPACE', 1, 0)) alter_tablespace,
sum(decode(Privilege, 'ALTER SESSION', 1, 0)) alter_session,
sum(decode(Privilege, 'ALTER SYSTEM', 1, 0)) alter_system,
sum(decode(Privilege, 'ALTER USER', 1, 0)) alter_user,
sum(decode(Privilege, 'CREATE ANY INDEX', 1, 0)) create_any_ind,
sum(decode(Privilege, 'CREATE ANY SEQUENCE', 1, 0)) create_any_seq,
sum(decode(Privilege, 'CREATE ANY TABLE', 1, 0)) create_any_table,
sum(decode(Privilege, 'CREATE ANY TRIGGER', 1, 0)) create_any_trig,
sum(decode(Privilege, 'CREATE PUBLIC SYNONYM', 1, 0)) create_public_syn,
sum(decode(Privilege, 'CREATE ROLE', 1, 0)) create_role,
sum(decode(Privilege, 'CREATE SEQUENCE', 1, 0)) create_sequence,
sum(decode(Privilege, 'CREATE TABLE', 1, 0)) create_table,
sum(decode(Privilege, 'CREATE TRIGGER', 1, 0)) create_trigger,
sum(decode(Privilege, 'DEBUG ANY PROCEDURE', 1, 0)) debug_any_proc,
sum(decode(Privilege, 'DEBUG CONNECT SESSION', 1, 0)) debug_connect_session,
sum(decode(Privilege, 'DROP ANY SEQUENCE', 1, 0)) drop_any_seq,
sum(decode(Privilege, 'EXECUTE ANY PROCEDURE', 1, 0)) execute_any_procedure,
sum(decode(Privilege, 'GRANT ANY OBJECT PRIVILEGE', 1, 0)) grant_any_object_priv,
sum(decode(Privilege, 'GRANT ANY ROLE', 1, 0)) grant_any_role,
sum(decode(Privilege, 'GRANT ANY PRIVILEGE', 1, 0)) grant_any_privilege,
sum(decode(Privilege, 'SELECT ANY DICTIONARY', 1, 0)) select_any_dictionary,
sum(decode(Privilege, 'SELECT ANY TABLE', 1, 0)) select_any_table,
sum(decode(Privilege, 'DELETE ANY TABLE', 1, 0)) delete_any_table,
sum(decode(Privilege, 'INSERT ANY TABLE', 1, 0)) insert_table,
sum(decode(Privilege, 'UPDATE ANY TABLE', 1, 0)) update_any_table
from sys.session_privs
where privilege in ('DEBUG CONNECT SESSION',
                  'ALTER ANY TABLE',
                  'ALTER SESSION',
                  'ALTER SYSTEM',
                  'ALTER DATABASE',
                  'ALTER TABLESPACE',
                  'ALTER USER',
                  'ADMINISTER DATABASE TRIGGER',
                  'ADMINISTER RESOURCE MANAGER',
                  'CREATE TABLE',
                  'CREATE ROLE',
                  'CREATE TRIGGER',
                  'CREATE SEQUENCE',
                  'EXECUTE ANY PROCEDURE',
                  'SELECT ANY TABLE',
                  'DELETE ANY TABLE',
                  'INSERT ANY TABLE',
                  'UPDATE ANY TABLE',
                  'GRANT ANY OBJECT PRIVILEGE',
                  'GRANT ANY ROLE',
                  'GRANT ANY PRIVILEGE',
                  'SELECT ANY DICTIONARY',
                  'CREATE ANY INDEX',
                  'CREATE ANY SEQUENCE',
                  'CREATE ANY TABLE',
                  'CREATE ANY TRIGGER',
                  'DEBUG ANY PROCEDURE',
                  'DROP ANY SEQUENCE',
                  'CREATE PUBLIC SYNONYM');
-- Elapsed Time: 0.343 seconds
-- Rows fetched: 1

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:43:12.672
-- 
-- Check for SELECT_CATALOG_ROLE.  It must be a default role to appear in the result of this query.
Select sum(decode(role, 'SELECT_CATALOG_ROLE', 1, 0)) select_catalog_role
from   sys.session_roles
where  role = 'SELECT_CATALOG_ROLE';
-- Elapsed Time: 0.015 seconds
-- Rows fetched: 1

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:43:23.794
-- 
-- determine database character set
select value 
from   sys.nls_database_parameters
where  parameter = 'NLS_CHARACTERSET';
-- Elapsed Time: 0.015 seconds
-- Rows fetched: 1

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:43:35.903
-- 
-- Is Toad Security in effect?
declare
  qry_str varchar2(4000);
  Toad_Security_Exists number;
begin
  qry_str := 'Select 1
              from toad.toad_restrictions
              where (user_name = USER or user_name in ( select ROLE from sys.session_roles))
              and   rownum = 1';
  execute immediate qry_str into Toad_Security_Exists;
  exception
  when others then
    Toad_Security_Exists := 0;
end;
-- Elapsed Time: 0.014 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:43:43.558
-- 
-- retrieve OS Username
declare
  qry_str varchar2(4000);
  OS_USERNAME varchar2(60);
begin
  qry_str := 'select SYS_CONTEXT(''USERENV'',''OS_USER'') from dual';
  execute immediate qry_str into OS_USERNAME;
  exception
  when others then
    OS_USERNAME := null;
end;
-- Elapsed Time: 0.015 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:43:53.586
-- 
-- retrieve SID if possible
declare
  qry_str varchar2(4000);
  sid number;
begin
  qry_str := 'select SYS_CONTEXT(''USERENV'',''SID'') from dual';
  execute immediate qry_str into sid;
exception
  when others then
    sid := -1;
end;
-- Elapsed Time: 0.016 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:44:05.615
--
-- get compatible version
declare
  qry_str varchar2(4000);
  compatible_version varchar2(60);
  cant_access_vparameter integer;
begin
  cant_access_vparameter := 0;
  qry_str := 'select value from v$parameter where name = ''compatible''';
  execute immediate qry_str into compatible_version;
exception
  when others then
    compatible_version := null;
    cant_access_vparameter := 1;
end;
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:44:17.566
-- 
-- check for privileges on sys.dbms_transaction
declare
  qry_str varchar2(4000);
  local_trans_id varchar2(60);
begin
qry_str := 'select sys.dbms_transaction.local_transaction_id from dual';
execute immediate qry_str into local_trans_id;
exception
when others then
  local_trans_id := '<error>';
end;
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:45:41.907
-- 
-- determine if dbms_java and dbms_debug are installed
declare
  qry_str varchar2(4000);
  has_dbms_java number;
  has_dbms_debug number;

  function check_for_dba(in_str in varchar2) return varchar2 is
     result varchar2(4000);
  begin
    result := in_str;
    result := REPLACE (result, 'all_objects', 'dba_objects');
    result := REPLACE (result, 'all_synonyms', 'dba_synonyms');
    return result;
  end;
  
begin
  qry_str := 'select sum(decode(object_name, ''DBMS_JAVA'', 1, 0)) jv,
                   sum(decode(object_name, ''DBMS_DEBUG'', 1, 0)) dbg
            from sys.all_objects
            where owner = ''SYS''
            and object_type = ''PACKAGE''
            and object_name in (''DBMS_JAVA'', ''DBMS_DEBUG'')';
  qry_str := check_for_dba(qry_str);

  begin
    execute immediate qry_str into has_dbms_java, has_dbms_debug;
  exception
  when others then
    has_dbms_java := 0;
    has_dbms_debug := 0;
  end;
end;
-- Elapsed Time: 0.015 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:45:56.997
-- 
-- determine if code tester repository is installed
declare
  qu_version_owner varchar2(128);
begin
  begin
    Select user into qu_version_owner
    FROM   sys.user_objects
    WHERE  object_name = 'QU_VERSION'
    AND    object_type in ('PACKAGE', 'SYNONYM')
    AND    rownum = 1;
  exception
    when others then
      qu_version_owner := null;
  end;
end;
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:46:06.808
declare
  qry_str varchar2(4000);
  qu_version_owner varchar2(128);
  
  function check_for_dba(in_str in varchar2) return varchar2 is
     result varchar2(4000);
  begin
    result := in_str;
    result := REPLACE (result, 'all_objects', 'dba_objects');
    result := REPLACE (result, 'all_synonyms', 'dba_synonyms');
    return result;
  end;
  
begin
  qry_str := 'select owner
              from  sys.all_objects
              where object_name = ''QU_VERSION''
              and   object_type in (''PACKAGE'',''SYNONYM'')
              and rownum = 1';
  qry_str := check_for_dba(qry_str);
  execute immediate qry_str into qu_version_owner;
exception
  when others then
    qu_version_owner := null;
end;
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:46:16.009
declare
  qry_str varchar2(4000);
  Is_RAC number;
begin
  qry_str := qry_str || ' then :isRAC := 1; else :isRAC := 0; end if; end;';
  begin
    execute immediate qry_str using out is_RAC;
  exception
    when others then
      is_RAC := 0;
  end;
end;
-- Elapsed Time: 0.015 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:46:24.663
declare
  instance_id number;
begin
  execute immediate 'select dbms_utility.current_instance from dual' into instance_id;
  exception
  when others then
    instance_id := 1;
end;
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:46:37.488
declare
  qry_str varchar2(4000);
  Is_RAC number;
  instance_name varchar2(100);
  server_name varchar2(100);
begin
  -- 
  -- determine instance name and server name if possible
  qry_str := 'select sys_context(''USERENV'',''INSTANCE_NAME''),
                   sys_context(''USERENV'',''SERVER_HOST'')
            from dual';
  begin
    execute immediate qry_str into instance_name, server_name;
    exception
      -- if we don't have privs on v$instance and not rac, then try to get db name
      when others then
        if is_RAC = 0 then
          begin
            qry_str := 'select sys_context(''USERENV'',''DB_NAME'') from dual';
            execute immediate qry_str into instance_name;
          exception
            when others then
               instance_name := null;
          end;
        end if;
    end;
end;
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:46:47.580
--
-- get timestamp radix
declare
  qry_str varchar2(4000);
  radix varchar2(1);
begin
  qry_str := 'select to_char(systimestamp, ''X'' ) from dual';
  execute immediate qry_str into radix;
  exception
  when others then
    radix := '.';
end;
-- Elapsed Time: 0.015 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:46:57.265
declare
  qry_str varchar2(4000);
  dbid varchar2(30);
  dbname varchar2(9);
begin
  qry_str := 'select dbid, name from v$database';
  execute immediate qry_str into dbid, dbname;
exception
  when others then
    null;
end;
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:47:07.263
declare
  qry_str varchar2(4000);
  authentication_type varchar2(30);
begin
  qry_str := 'Select SYS_CONTEXT(''USERENV'', ''IDENTIFICATION_TYPE'') from dual';
  execute immediate qry_str into authentication_type;
exception
  when others then
    authentication_type := null;
end;
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:47:16.901
declare
  qry_str varchar2(4000);
  AmazonRDSPackage varchar2(128);
begin
  qry_str := 'select object_name
              from sys.all_procedures
              where owner = ''RDSADMIN''
              and object_name = ''RDSADMIN_UTIL''
              and object_type = ''PACKAGE''
              and subprogram_id = 0';
  execute immediate qry_str into AmazonRDSPackage;
exception
  when others then
    AmazonRDSPackage := null;
end;
-- Elapsed Time: 0.078 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:47:27.711
declare
  qry_str varchar2(4000);
  IsExadata number;
begin
  qry_str := 'select 1 from v$cell where rownum = 1';
  execute immediate qry_str into IsExadata;
exception
  when others then
    IsExaData := 0;
end;
-- Elapsed Time: 0 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:47:36.678
-- 
-- check for existence of Codexpert tables
declare
  cx_table_count number;
begin
  Select count('x')
  into cx_table_count
  from sys.all_tables
  where owner in ('TOAD', USER)
  and table_name in ('CX_XPERTRUN', 'CX_XPERTLINES', 'CX_XPERTITEMS', 'CX_TYPES', 'CX_SEVERITYS', 'CX_SETRULES', 'CX_RULESETS', 'CX_METRICS', 'CX_METRICRANGES', 'CX_CATEGORYS', 'CX_BASERULES');
end;
-- Elapsed Time: 0.016 seconds

----------------------------------
-- Session: HR@HRMS
-- Timestamp: 09:47:54.221
declare
  qry_str varchar2(4000);
  utplsql_ver varchar2 (60);
  utplsql_own varchar2 (128);
  e_inherit_privs EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_inherit_privs, -6598);
  
  function check_for_dba(in_str in varchar2) return varchar2 is
     result varchar2(4000);
  begin
    result := in_str;
    result := REPLACE (result, 'all_objects', 'dba_objects');
    result := REPLACE (result, 'all_synonyms', 'dba_synonyms');
    return result;
  end;
begin
  utplsql_ver := NULL;
  BEGIN
  -- Fetch version using the public API
  EXECUTE IMMEDIATE 'SELECT SUBSTR (ut.version (), 1, 60) FROM DUAL'
    INTO utplsql_ver;
  EXCEPTION
  -- Attempt direct access on ORA-06598
  WHEN e_inherit_privs THEN
  BEGIN
    qry_str := 'SELECT do.owner
                FROM   sys.all_objects  do
                       JOIN sys.all_synonyms ds
                           ON     (do.owner = ds.table_owner)
                              AND (do.object_name = ds.table_name)
                WHERE      ds.owner = ''PUBLIC''
                       AND ds.table_name = ''UT''
                       AND ds.synonym_name = ''UT''
                       AND do.object_type = ''PACKAGE''';
  
    qry_str := check_for_dba(qry_str);
    EXECUTE IMMEDIATE qry_str INTO utplsql_own;

    qry_str := 'begin :utplsql_ver := ' || utplsql_own || '.ut_utils.gc_version; end;';
    EXECUTE IMMEDIATE qry_str USING OUT utplsql_ver;
  
  EXCEPTION
    WHEN OTHERS THEN
      utplsql_ver := SQLCODE;
  END;
  WHEN OTHERS THEN
    utplsql_ver := SQLCODE;
  END;
end;
-- Elapsed Time: 0.016 seconds

Hi Jay,

I don't know why those statements run slow on startup but fast later on.

I don't think these steps are relevant, but they won't hurt. Just to rule it out, please try the steps on this message.

-John

Hi John,

Tried to follow the steps mentioned, but can't see the "General". I'm using the 16.2 trial version.

Hi Jay,

You are on Files -> General.

Look down a few nodes. :slight_smile:

-John

Got it, found it after sending my reply :face_in_clouds:

I was able to update the file, but not working for me. Still, login takes time for this specific database. I tried to connect to another database the response is ok.

I'm really at a loss here why that block takes so long, and only on startup on that one DB.

I have an 11gR2 that I connect to often with a 19c client, and have not seen this before. :frowning:

We had similar case once where customer connected to a DB through VPN, and somehow the VPN was interfering with pl/sql blocks that had bind variables. They changed something on their network or VPN and problem went away. In that case though, the connection block caused disconnect instead of running slowly.

I'm directly connected to the DB without VPN but will try to check in network level for any issue