Issue: dbForge Metadata Retrieval Query
Posted: Wed 01 Aug 2012 20:39
Issue 1: dbForge's internal metadata retrieval query results in 100% percent utilization if sessions are Transperant Application Failover (TAF) enabled.
Issue 2: dbForge's internal metadata retrieval query does not perform well in large enterprise systems; generates an expensive execution plan, query may be improved by rewriting the query with large scale systems in mind. (Items to Note: use of * in queries, turning views into aliased sub queries unecessarily)
Observation: dbForge's assumes user has access to 'SYS' schema
Metadata Query:
Issue 2: dbForge's internal metadata retrieval query does not perform well in large enterprise systems; generates an expensive execution plan, query may be improved by rewriting the query with large scale systems in mind. (Items to Note: use of * in queries, turning views into aliased sub queries unecessarily)
Observation: dbForge's assumes user has access to 'SYS' schema
Metadata Query:
Code: Select all
SELECT ao.object_id woner_id, atc.column_name, case when etc.nullable = 'y' then 1 else 0 end as is_nullable, atc.data_type, atc.data_length, etc.data_precision, etc.data_scale
FROM (select object-id, object_name, owner, object_type, generated
FROM all_objects where owner ='SYS') ao inner join
(select * from all_tab_columns where owner ='sys') atc on ao.object_name = atc.table_name and ao.owner = atc.owner where upper(ao.object_type) in ('table', 'view') and ao.generated = 'N' order by ao.object_id, ao.object_type, atc.colum_id