Toad World® Forums

Performance of Describe Window

Opening the describe window takes a noticeable amount of time, so I enabled Spool SQL (see below).

  • What's most interesting is the duplicate query to user_objects (MATERIALIZED VIEW).
  • I would guess that an initial query to user_objects without restricting to object_type should be able to determine if it is a materialized view or not (would return two rows) and also return [created, last_ddl_time, object_id, status]

What do you think?

----------------------------------
-- Session: 
-- Timestamp: 11:34:11.506
Select 1
from sys.USER_TABLES
where table_name =:nm;
:nm(VARCHAR[10],IN)='TEST_TABLE'

----------------------------------
-- Session: 
-- Timestamp: 11:34:11.519
Select 1
from sys.user_objects
where object_name =:nm
and object_type =:t;
:nm(VARCHAR[10],IN)='TEST_TABLE' 
:t(VARCHAR[17],IN)='MATERIALIZED VIEW'

----------------------------------
-- Session: 
-- Timestamp: 11:34:12.393
Select 1
from sys.user_objects
where object_name =:nm
and object_type =:t;
:nm(VARCHAR[10],IN)='TEST_TABLE' 
:t(VARCHAR[17],IN)='MATERIALIZED VIEW'

----------------------------------
-- Session: 
-- Timestamp: 11:34:12.397
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 t.table_name = :obj;
:obj(VARCHAR[10],IN)='TEST_TABLE'

----------------------------------
-- Session: 
-- Timestamp: 11:34:12.406
Select created, last_ddl_time, object_id, status
from sys.user_objects
where object_name = :nm
and object_type = :t;
:nm(VARCHAR[10],IN)='TEST_TABLE' 
:t(VARCHAR[5],IN)='TABLE'

----------------------------------
-- Session: 
-- Timestamp: 11:34:12.486
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
       ,cols.user_generated, cols.default_on_null, cols.identity_column
       ,coms.comments
FROM
      sys.user_col_comments coms, 
      sys.user_tab_cols cols
where 1=1
and   coms.table_name (+) = cols.Table_Name
and   coms.column_name (+) = cols.column_Name
and   coms.origin_con_id (+) = TO_NUMBER(sys_context('USERENV','CON_ID'))
and   cols.table_name = :TABNAME
order by column_id, internal_column_id;
:TABNAME(VARCHAR[10],IN)='TEST_TABLE'

----------------------------------
-- Session: 
-- Timestamp: 11:34:12.497
select column_name, encryption_alg, salt
from sys.user_encrypted_columns
where table_name = :tn;
:tn(VARCHAR[10],IN)='TEST_TABLE'

----------------------------------
-- Session: 
-- Timestamp: 11:34:12.505
Select comments
from sys.user_col_comments
where 1=1
and table_name = :obj
and origin_con_id = TO_NUMBER(sys_context('USERENV','CON_ID'))
and column_name = :col;
:obj(VARCHAR[10],IN)='TEST_TABLE' 
:col(VARCHAR[13],IN)='TEST_COLUMN_1'

----------------------------------
-- Session: 
-- Timestamp: 11:34:12.555
Select c1.column_name, c1.position, a1.constraint_name
from sys.user_cons_columns C1,  sys.user_constraints A1
where A1.table_name = C1.Table_name
and A1.constraint_name = C1.Constraint_Name
and A1.constraint_type = 'P'
and A1.table_name =:TabName
ORDER BY c1.position;
:TabName(VARCHAR[10],IN)='TEST_TABLE'

----------------------------------
-- Session: 
-- Timestamp: 11:34:12.690
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[6],IN)='KLASSX' 
:obj(VARCHAR[10],IN)='TEST_TABLE'

----------------------------------
-- Session: 
-- Timestamp: 11:34:12.952
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[6],IN)='KLASSX' 
:tname(VARCHAR[10],IN)='TEST_TABLE'

Hi Peter,

Thanks for bringing the duplicate query to my attention. I can't make any promises but I'll look at the code to see if there is something that I can do to eliminate one or more queries. When you see
it in spool sql output, it's easy to look and say "hey, just get rid of the 2nd one" but sometimes these queries come from different areas of code and serve multiple functions, so it may not be an easy fix.

-John

Yeah, there's not much I can do right now about those queries that you mentioned. I can think of some code changes to improve the situation (maybe reduce those 3 queries to 1 as you suggested) but it's a rather involved change so it'll have to wait.

Regarding the queries near the bottom of the spool, you can make some of those go away if you don't need all the info that you get in your table describe window.

Here are some options that you can disable to speed things up:

Uncheck these:

Also these:
image

Hi John,
I knew that this would be somewhere between easy and impossible :slight_smile:
Thank you for looking into it!