Login into Oracle PDB (18c) taking longer

[krishp1]

Hi,

We have a 18c Container database with few PDBs. We are able to login into the PDB using SQL plus pretty quickly but it is taking 25+ seconds to login using Toad. Also using Schema browser is taking longer.

Any help you can provide is appreciated.

Parthiban

[JohnDorlon]

I use PDBs almost exclusively and they log in quickly. They shouldn't take any longer than normal DBs.

However, SQL*Plus does not run any queries when you start it. Toad does.

You can turn on spool sql (main menu: database-> spool sql->spool to screen) then make your connection. Toad will capture the Executed SQL and timestamp it, so we can see which query is slow. Post that output here, then hopefully I can tell you a setting to change.

-John

[krishp1]

---

Timestamp: 08:30:47.662
Connect: WILEST1@DCC1P

---

Session: WILEST1@DCC1P
Timestamp: 08:30:48.174
SELECT version, product, sysdate FROM sys.PRODUCT_COMPONENT_VERSION WHERE UPPER(PRODUCT) LIKE '%ORACLE%'

---

Session: WILEST1@DCC1P
Timestamp: 08:30:48.223
Declare
debug_connect_session number;
alter_session number;
alter_system number;
alter_database number;
execute_any_procedure number;
select_any_table number;
grant_any_object_priv 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 nls_database_parameters.value%type;
compatible_version varchar2(60);
sid number;
local_trans_id varchar2(60);
qry_str varchar2(4000);
has_dbms_java number;
has_dbms_debug number;
qu_version_owner varchar2(30);
CTRepositoryVersion number;
is_RAC number;
instance_id number;
instance_name varchar2(100);
server_name varchar2(100);
team_coding_schema varchar2(30);
team_coding_table varchar2(30);
team_coding_synonym varchar2(30);
team_coding_table_selectable number;
has_nav_tc number;
dbid varchar2(30);
authentication_type varchar2(30);
AmazonRDSPackage varchar2(30);
OraDBType number;
cx_table_count number;
Begin
-- Put Toad data into v$session columns
Begin
SYS.DBMS_APPLICATION_INFO.SET_MODULE(:IN_MDL, null);
Exception
When others then null; -- we can live without this.
End;

-- Retrieve specific system privileges
select
sum(decode(Privilege, 'DEBUG CONNECT SESSION', 1, 0)) debug_connect_session,
sum(decode(Privilege, 'ALTER SESSION', 1, 0)) alter_session,
sum(decode(Privilege, 'ALTER SYSTEM', 1, 0)) alter_system,
sum(decode(Privilege, 'ALTER DATABASE', 1, 0)) alter_database,
sum(decode(Privilege, 'EXECUTE ANY PROCEDURE', 1, 0)) execute_any_procedure,
sum(decode(Privilege, 'SELECT ANY TABLE', 1, 0)) select_any_table,
sum(decode(Privilege, 'UPDATE ANY TABLE', 1, 0)) update_any_table,
sum(decode(Privilege, 'GRANT ANY OBJECT PRIVILEGE', 1, 0)) grant_any_object_priv,
sum(decode(Privilege, 'SELECT ANY DICTIONARY', 1, 0)) select_any_dictionary,
sum(decode(Privilege, 'CREATE PUBLIC SYNONYM', 1, 0)) create_public_syn
into debug_connect_session, alter_session, alter_system, alter_database, execute_any_procedure, select_any_table, update_any_table, grant_any_object_priv, select_any_dictionary, create_public_syn
from sys.session_privs
where privilege in ('DEBUG CONNECT SESSION',
'ALTER SESSION',
'ALTER SYSTEM',
'ALTER DATABASE',
'EXECUTE ANY PROCEDURE',
'SELECT ANY TABLE',
'UPDATE ANY TABLE',
'GRANT ANY OBJECT PRIVILEGE',
'SELECT ANY DICTIONARY',
'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
begin
select sum(decode(table_name, 'DBA_SYNONYMS', 1, 0)) dba_syns,
sum(decode(table_name, 'DBA_OBJECTS', 1, 0)) dba_objs
into has_dba_synonyms, has_dba_objects
from sys.table_privileges
where owner = 'SYS'
and table_name in ('DBA_SYNONYMS', 'DBA_OBJECTS')
and select_priv in ('Y', 'G');
exception
when no_data_found then
has_dba_synonyms := 0;
has_dba_objects := 0;
end;
end if;

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

-- get compatible version
begin
qry_str := 'select value from v$parameter where name = ''compatible''';
execute immediate qry_str into compatible_version;
exception
when others then
compatible_version := null;
end;

-- check for privileges on sys.dbms_transaction
begin
execute immediate 'select sys.dbms_transaction.local_transaction_id from dual' into local_trans_id;
exception
when others then
local_trans_id := '';
end;

-- determine if certain packages are installed
qry_str := 'Select sum(decode(object_name, ''DBMS_JAVA'', 1, 0)) jv,' ||
' sum(decode(object_name, ''DBMS_DEBUG'', 1, 0)) dbg ';
if has_dba_objects > 0 then
qry_str := qry_str || 'from sys.dba_objects ';
else
qry_str := qry_str || 'from sys.all_objects ';
end if;
qry_str := qry_str || 'where owner = ''SYS'' ' ||
'and object_type = ''PACKAGE'' ' ||
'and object_name in (''DBMS_JAVA'', ''DBMS_DEBUG'')';
execute immediate qry_str into has_dbms_java, has_dbms_debug;

-- 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 no_data_found 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 ';
if has_dba_objects > 0 then
qry_str := qry_str || 'from sys.dba_objects ';
else
qry_str := qry_str || 'from sys.all_objects ';
end if;
qry_str := qry_str || 'where object_name = ''QU_VERSION'' ' ||
'and object_type in (''PACKAGE'',''SYNONYM'') and rownum = 1';
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
CTRepositoryVersion := 0;
if qu_version_owner is not null then
begin
qry_str := 'select (10 * QU_VERSION.major_number) + QU_VERSION.minor_number from dual';
execute immediate qry_str into CTRepositoryVersion;
exception
when others then
CTRepositoryVersion := 0;
end;
end if;

--
-- find the team coding schema, if any
begin
-- all_objects is generally faster than all_synonyms, so first look there to see if the synonym exists.
qry_str := 'Select object_name ';
if has_dba_objects > 0 then
qry_str := qry_str || 'from sys.dba_objects ';
else
qry_str := qry_str || 'from sys.all_objects ';
end if;
qry_str := qry_str || 'where owner = ''PUBLIC'' and object_name = ''QUEST_COM_TEAM_CODING'' and object_type = ''SYNONYM'' ';
execute immediate qry_str into team_coding_synonym;
exception
when others then
team_coding_synonym := null;
end;
-- now look to find team coding table name only if we know synonym exists.
if team_coding_synonym is not null then
begin
qry_str := 'Select table_owner, table_name ';
if has_dba_synonyms > 0 then
qry_str := qry_str || 'from sys.dba_synonyms ';
else
qry_str := qry_str || 'from sys.all_synonyms ';
end if;
qry_str := qry_str || 'where synonym_name = ''QUEST_COM_TEAM_CODING'' and owner = ''PUBLIC'' ';
execute immediate qry_str into team_coding_schema, team_coding_table;
exception
when others then
team_coding_schema := null;
team_coding_table := null;
end;
end if;
-- if we found a table, make sure we can select from it.
if team_coding_table is not null then
begin
qry_str := 'SELECT 1 FROM ' || team_coding_schema || '.' || team_coding_table || ' where rownum = 1';
execute immediate qry_str into team_coding_table_selectable;
exception
when no_data_found then
team_coding_table_selectable := 1;
when others then
team_coding_table_selectable := 0;
end;
end if;
if team_coding_table is null then
--
-- see if sql nav team coding table exists
team_coding_table_selectable := 0;
qry_str := 'Select 1 ';
if has_dba_objects > 0 then
qry_str := qry_str || 'from sys.dba_objects ';
else
qry_str := qry_str || 'from sys.all_objects ';
end if;
qry_str := qry_str || 'where object_type = ''TABLE'' ' ||
'and object_name = ''SQLNAV_PROGRAM_STATUS_LOG''';
begin
execute immediate qry_str into has_nav_tc;
exception
when no_data_found then
has_nav_tc := 0;
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;';

-- determine if this is a RAC DB
begin
execute immediate qry_str using out is_RAC;
exception
when others then
:isRAC := 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 DBID if possible
begin
qry_str := 'select dbid from v$database';
execute immediate qry_str into dbid;
exception
when others then
null;
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;

-- Is this an Amazon RDS Instance?
begin
execute immediate 'select object_name from sys.all_procedures where owner = ''RDSADMIN'' and object_name = ''RDSADMIN_UTIL'' and object_type = ''PACKAGE'' and subprogram_id = 0' into AmazonRDSPackage;
exception
when others then
AmazonRDSPackage := null;
end;

-- What kind of database is this? (pluggable, container, normal)
begin
qry_str := 'select' || CHR(13) || CHR(10) ||
' Case' || CHR(13) || CHR(10) ||
' when sys_context(''USERENV'',''CON_NAME'') = ''CDB$ROOT'' then 1 -- container,' || CHR(13) || CHR(10) ||
' when sys_context(''USERENV'',''CDB_NAME'') is null then 0 -- normal,' || CHR(13) || CHR(10) ||
' else 2 -- pluggable' || CHR(13) || CHR(10) ||
' end db_type' || CHR(13) || CHR(10) ||
'from dual';
execute immediate qry_str into OraDBType;
exception
when others then
OraDBType := 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');

-- Assign results to output variables
:OUT_DEBUG_CONNECT_SESSION := debug_connect_session;
:OUT_ALTER_SESSION := alter_session;
:OUT_ALTER_SYSTEM := alter_system;
:OUT_ALTER_DATABASE := alter_database;
:OUT_EXECUTE_ANY_PROCEDURE := execute_any_procedure;
:OUT_SELECT_ANY_TABLE := select_any_table;
:OUT_UPDATE_ANY_TABLE := update_any_table;
:OUT_GRANT_ANY_OBJECT_PRIV := grant_any_object_priv;
:OUT_SELECT_ANY_DICTIONARY := select_any_dictionary;
:OUT_CREATE_PUBLIC_SYN := create_public_syn;
:OUT_SELECT_CATALOG_ROLE := select_catalog_role;
:OUT_USER := user;
:OUT_DB_CHARSET := db_charset;
:OUT_CX_TABLE_COUNT := cx_table_count;
:OUT_COMPATIBLE_VERSION := compatible_version;
:OUT_TEAM_CODING_SCHEMA := team_coding_schema;
:OUT_TEAM_CODING_TABLE := team_coding_table;
:OUT_TC_TABLE_SELECTABLE := team_coding_table_selectable;
:OUT_HAS_NAV_TC := has_nav_tc;
:OUT_SID := sid;
:OUT_LOCAL_TRANS_ID := local_trans_id;
:OUT_HAS_DBMS_JAVA := has_dbms_java;
:OUT_HAS_DBMS_DEBUG := has_dbms_debug;
:OUT_QU_VERSION_OWNER := qu_version_owner;
:OUT_QU_VERSION := CTRepositoryVersion;
:OUT_IS_RAC := is_rac;
:OUT_INSTANCE_ID := instance_id;
:OUT_INSTANCE_NAME := instance_name;
:OUT_SERVER_NAME := server_name;
:OUT_DBID := dbid;
:OUT_AUTHENTICATION_TYPE := authentication_type;
:OUT_IS_AMAZON_RDS := AmazonRDSPackage;
:OUT_DB_TYPE := OraDBType;
end;
:IN_MDL(VARCHAR[14],IN)='TOAD 12.6.0.53'
:isRAC(Unknown,IN)=
:OUT_DEBUG_CONNECT_SESSION(INTEGER,OUT)=
:OUT_ALTER_SESSION(INTEGER,OUT)=
:OUT_ALTER_SYSTEM(INTEGER,OUT)=
:OUT_ALTER_DATABASE(INTEGER,OUT)=
:OUT_EXECUTE_ANY_PROCEDURE(INTEGER,OUT)=
:OUT_SELECT_ANY_TABLE(INTEGER,OUT)=
:OUT_UPDATE_ANY_TABLE(INTEGER,OUT)=
:OUT_GRANT_ANY_OBJECT_PRIV(INTEGER,OUT)=
:OUT_SELECT_ANY_DICTIONARY(INTEGER,OUT)=
:OUT_CREATE_PUBLIC_SYN(INTEGER,OUT)=
:OUT_SELECT_CATALOG_ROLE(INTEGER,OUT)=
:OUT_USER(VARCHAR[0],OUT)=
:OUT_DB_CHARSET(VARCHAR[0],OUT)=
:OUT_CX_TABLE_COUNT(INTEGER,OUT)=
:OUT_COMPATIBLE_VERSION(VARCHAR[0],OUT)=
:OUT_TEAM_CODING_SCHEMA(VARCHAR[0],OUT)=
:OUT_TEAM_CODING_TABLE(VARCHAR[0],OUT)=
:OUT_TC_TABLE_SELECTABLE(INTEGER,OUT)=
:OUT_HAS_NAV_TC(INTEGER,OUT)=
:OUT_SID(VARCHAR[0],OUT)=
:OUT_LOCAL_TRANS_ID(VARCHAR[0],OUT)=
:OUT_HAS_DBMS_JAVA(INTEGER,OUT)=
:OUT_HAS_DBMS_DEBUG(INTEGER,OUT)=
:OUT_QU_VERSION_OWNER(VARCHAR[0],OUT)=
:OUT_QU_VERSION(INTEGER,OUT)=
:OUT_IS_RAC(INTEGER,OUT)=
:OUT_INSTANCE_ID(INTEGER,OUT)=
:OUT_INSTANCE_NAME(VARCHAR[0],OUT)=
:OUT_SERVER_NAME(VARCHAR[0],OUT)=
:OUT_DBID(VARCHAR[0],OUT)=
:OUT_AUTHENTICATION_TYPE(VARCHAR[0],OUT)=
:OUT_IS_AMAZON_RDS(VARCHAR[0],OUT)=
:OUT_DB_TYPE(INTEGER,OUT)=

---

Session: WILEST1@DCC1P
Timestamp: 08:30:48.591
Select feature from toad.toad_restrictions where user_name=USER or user_name in ( select ROLE from sys.session_roles)

---

Session: WILEST1@DCC1P
Timestamp: 08:30:48.609
Error: ORA-00942: table or view does not exist

---

Session: WILEST1@DCC1P
Timestamp: 08:30:48.662
Error: ORA-01031: insufficient privileges

---

Session: WILEST1@DCC1P
Timestamp: 08:30:48.675
Select username
from sys.DBA_USERS
order by username

---

Session: WILEST1@DCC1P
Timestamp: 08:30:51.470
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 || '."152472098419032224601274679219"; 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)=
:out_toad_prof_valid(INTEGER,OUT)=

---

Session: WILEST1@DCC1P
Timestamp: 08:30:52.006
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 || '."16116083611392723421966436594"; 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)=
:out_toad_prof_valid(INTEGER,OUT)=

---

Timestamp: 08:30:52.530
Connect: WILEST1@DCC1P

---

Session: WILEST1@DCC1P
Timestamp: 08:30:53.682
begin sys.dbms_application_info.set_module('TOAD background query session', null); end;

---

Session: WILEST1@DCC1P
Timestamp: 08:30:54.569
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')

---

Session: WILEST1@DCC1P
Timestamp: 08:30:58.223
Rollback:

---

Timestamp: 08:30:58.227
Disconnect: WILEST1@DCC1P

[krishp1]

We use version 12.6.0.53

[JohnDorlon]

That looked fairly quick - 10 seconds, including opening an Editor.

The slowest query looks like it was:

Select username
from sys.DBA_USERS
order by username;

Just to be sure, will you try that in the Editor and see how long it takes?
If you have a lot of users in the database that do not have objects, this making this setting may speed things up. (right-click on Username dropdown in Schema Browser)

Also, if you don't care about colorization of table names in the Editor, go to Options -> Editor -> Display. Uncheck "Highlight table names", "Highlight view names", "Highlight stored procedure names"

[krishp1]

John,

select username from sys.DBA_USERS order by username is coming fast.

We did make the changes you recommended. It did not help.

Also, the issue is sporadic and that leads me to believe that the issue is on DB side.

Are there other settings at DB level that we should be looking at?

Thanks for your prompt response, I appreciate that.

Parthiban

[JohnDorlon]

All I can suggest is use Spool SQL to try to identify the slow running SQL. Once you identify it, let me know.

If you suspect Toad settings, you can reset user files. Here are some steps to do that. Note, for your install, it will be under "Dell" instead of "Quest Software".

Toad for Oracle not working after in place upgrade to Win 10

I've never heard of this problem, so I'm not sure what the easiest solution is, but maybe the following will fix it: Reset Toad's user files From Toad's main menu: Utilities -> Copy User Settings (In newer versions this is called Manage User Settings) Choose the option to Reset to a clean set In newer versions, you can choose to retain some settings like Connections, Workspaces, etc. If you don't see that, you can restore these things manually later. Keep reading. Click OK Choose Yes a…