Object autocomplete performance problem

Toad for Oracle & DevOps Toolkit
22

Spool SQL:

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:34.190
SELECT VALUE FROM V$OPTION WHERE PARAMETER='Objects';

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:35.545
Select t.table_name, user as owner, t.cluster_name,
t.partitioned, t.iot_type, t.tablespace_name, t.last_analyzed, round(t.num_rows) num_rows,
t.initial_extent, t.degree, t.logging, t.cache, t.buffer_pool
, t.monitoring, t.temporary, t.table_type, t.table_type_owner, t.nested
, t.Compression, t.dropped
, t.compress_for, t.flash_cache, t.cell_flash_cache, t.segment_created
, decode(nvl(tablespace_name, 'x') || upper(partitioned) || nvl(iot_type, 'x') || to_char(pct_free), 'xNOx0', 'YES', 'NO') as External
from sys.user_all_tables t
where 1=1
order by table_name;

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:36.277
Select user, table_name from sys.user_snapshots
order by table_name;

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:36.300
Select user, object_name, created, last_ddl_time, object_id from sys.user_objects
Where object_type = 'TABLE'
order by object_name;

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:48.826
Select cols.column_id, cols.column_name as Name, cols.nullable,
cols.data_type as Type, cols.data_type_mod,
decode(cols.data_type, 'CHAR', cols.char_length,
'VARCHAR', cols.char_length,
'VARCHAR2', cols.char_length,
'NCHAR', cols.char_length,
'NVARCHAR', cols.char_length,
'NVARCHAR2', cols.char_length,
null) nchar_length,
decode(cols.data_type, 'NUMBER', cols.data_precision + cols.data_scale, cols.data_length) length,
cols.data_precision Precision, cols.data_scale Scale, cols.data_length dlength, cols.data_default
,cols.char_used
,cols.hidden_column, cols.internal_column_id
,cols.qualified_col_name
,InitCap(cols.histogram) histogram
,cols.virtual_column
,cols.num_distinct, cols.num_nulls, round(cols.density, 5) density
,null as comments
FROM
sys.user_tab_cols cols
where 1=1
and cols.table_name = :TABNAME
order by column_id, internal_column_id;
:TABNAME(VARCHAR[7],IN/OUT)='CHANNEL'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:48.905
select column_name, encryption_alg, salt
from sys.user_encrypted_columns
where table_name = :tn;
:tn(VARCHAR[7],IN/OUT)='CHANNEL'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:49.129
SELECT CN.NAME
FROM SYS.CDEF$ C, SYS.CON$ CN, SYS."_CURRENT_EDITION_OBJ" O, SYS.USER$ U
WHERE C.Type# = 2
AND C.CON# = CN.CON#
AND C.OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND U.NAME = :uname
AND O.NAME = :tname;
:uname(VARCHAR[8],IN/OUT)='PRTS_DEV'
:tname(VARCHAR[7],IN/OUT)='CHANNEL'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:49.148
SELECT c1.column_name, c1.position
FROM SYS.DBA_CONS_COLUMNS C1
WHERE C1.table_name = :tname
AND C1.constraint_name = :cname
AND C1.owner = :uname
ORDER BY 2;
:tname(VARCHAR[7],IN/OUT)='CHANNEL'
:cname(VARCHAR[12],IN/OUT)='SYS_C0021211'
:uname(VARCHAR[8],IN/OUT)='PRTS_DEV'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:49.346
Select t.trigger_name, t.trigger_type, t.triggering_event,
t.when_clause, t.status enabled, o.status, t.owner, o.object_id, t.trigger_body
from sys.DBA_OBJECTS o, sys.DBA_TRIGGERS t
where t.table_owner = :own
and t.table_name = :obj
and o.object_type = 'TRIGGER'
and o.object_name = t.trigger_name
and o.owner = t.owner;
:own(VARCHAR[8],IN/OUT)='PRTS_DEV'
:obj(VARCHAR[7],IN/OUT)='CHANNEL'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:49.953
Select owner, index_name, uniqueness, status
,INDEX_TYPE, TEMPORARY, PARTITIONED, LOGGING, DEGREE, funcidx_status, join_index
from sys.DBA_INDEXES
where table_owner = :town
and table_name = :tname
order by index_name;
:town(VARCHAR[8],IN/OUT)='PRTS_DEV'
:tname(VARCHAR[7],IN/OUT)='CHANNEL'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:49.994
Select *
from sys.DBA_IND_COLUMNS
where index_owner = :iown
and index_name = :iname
order by column_position;
:iown(VARCHAR[8],IN/OUT)='PRTS_DEV'
:iname(VARCHAR[12],IN/OUT)='SYS_C0021211'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:35:52.166
SELECT
ROWID, CHANNEL_CODE, CHANNEL_NAME
FROM PRTS_DEV.CHANNEL;

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:36:08.331
Select last_ddl_time
from sys.user_objects
where object_name = :nam
and object_type = :typ;
:nam(VARCHAR[7],IN/OUT)='CHANNEL'
:typ(VARCHAR[5],IN/OUT)='TABLE'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:36:08.348
Select CONSTRAINT_NAME, R_CONSTRAINT_NAME, R_OWNER
FROM SYS.DBA_CONSTRAINTS
WHERE OWNER = :own
AND TABLE_NAME = :tname
AND CONSTRAINT_TYPE = 'R';
:own(VARCHAR[8])='PRTS_DEV'
:tname(VARCHAR[7])='CHANNEL'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:00.802
Select cols.column_id, cols.column_name as Name, cols.nullable,
cols.data_type as Type, cols.data_type_mod,
decode(cols.data_type, 'CHAR', cols.char_length,
'VARCHAR', cols.char_length,
'VARCHAR2', cols.char_length,
'NCHAR', cols.char_length,
'NVARCHAR', cols.char_length,
'NVARCHAR2', cols.char_length,
null) nchar_length,
decode(cols.data_type, 'NUMBER', cols.data_precision + cols.data_scale, cols.data_length) length,
cols.data_precision Precision, cols.data_scale Scale, cols.data_length dlength, cols.data_default
,cols.char_used
,cols.hidden_column, cols.internal_column_id
,cols.qualified_col_name
,InitCap(cols.histogram) histogram
,cols.virtual_column
,cols.num_distinct, cols.num_nulls, round(cols.density, 5) density
,null as comments
FROM
sys.user_tab_cols cols
where 1=1
and cols.table_name = :TABNAME
order by column_id, internal_column_id;
:TABNAME(VARCHAR[4],IN/OUT)='BANK'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:00.812
select column_name, encryption_alg, salt
from sys.user_encrypted_columns
where table_name = :tn;
:tn(VARCHAR[4],IN/OUT)='BANK'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:00.836
SELECT CN.NAME
FROM SYS.CDEF$ C, SYS.CON$ CN, SYS."_CURRENT_EDITION_OBJ" O, SYS.USER$ U
WHERE C.Type# = 2
AND C.CON# = CN.CON#
AND C.OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND U.NAME = :uname
AND O.NAME = :tname;
:uname(VARCHAR[8],IN/OUT)='PRTS_DEV'
:tname(VARCHAR[4],IN/OUT)='BANK'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:00.841
SELECT c1.column_name, c1.position
FROM SYS.DBA_CONS_COLUMNS C1
WHERE C1.table_name = :tname
AND C1.constraint_name = :cname
AND C1.owner = :uname
ORDER BY 2;
:tname(VARCHAR[4],IN/OUT)='BANK'
:cname(VARCHAR[7],IN/OUT)='BANK_PK'
:uname(VARCHAR[8],IN/OUT)='PRTS_DEV'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:00.879
Select t.trigger_name, t.trigger_type, t.triggering_event,
t.when_clause, t.status enabled, o.status, t.owner, o.object_id, t.trigger_body
from sys.DBA_OBJECTS o, sys.DBA_TRIGGERS t
where t.table_owner = :own
and t.table_name = :obj
and o.object_type = 'TRIGGER'
and o.object_name = t.trigger_name
and o.owner = t.owner;
:own(VARCHAR[8],IN/OUT)='PRTS_DEV'
:obj(VARCHAR[4],IN/OUT)='BANK'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:00.906
Select owner, index_name, uniqueness, status
,INDEX_TYPE, TEMPORARY, PARTITIONED, LOGGING, DEGREE, funcidx_status, join_index
from sys.DBA_INDEXES
where table_owner = :town
and table_name = :tname
order by index_name;
:town(VARCHAR[8],IN/OUT)='PRTS_DEV'
:tname(VARCHAR[4],IN/OUT)='BANK'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:00.912
Select *
from sys.DBA_IND_COLUMNS
where index_owner = :iown
and index_name = :iname
order by column_position;
:iown(VARCHAR[8],IN/OUT)='PRTS_DEV'
:iname(VARCHAR[7],IN/OUT)='BANK_PK'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:01.077
SELECT
ROWID, BANK_CODE, FOREIGN_NAME, LOCALE_NAME
FROM PRTS_DEV.BANK;

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:15.807
Select last_ddl_time
from sys.user_objects
where object_name = :nam
and object_type = :typ;
:nam(VARCHAR[4],IN/OUT)='BANK'
:typ(VARCHAR[5],IN/OUT)='TABLE'

-- Session: PRTS_DEV@UATDB
-- Timestamp: 20:37:15.813
Select CONSTRAINT_NAME, R_CONSTRAINT_NAME, R_OWNER
FROM SYS.DBA_CONSTRAINTS
WHERE OWNER = :own
AND TABLE_NAME = :tname
AND CONSTRAINT_TYPE = 'R';
:own(VARCHAR[8])='PRTS_DEV'
:tname(VARCHAR[4])='BANK'

23

The background queries are executing quickly. I just see a delay after the query to load the grid is executed. Do you see the same delay if you run these queries in the Editor? (run with F9, not F5).

24

Yes, same is the case while query execution.