High cpu time when showing columns list of a table with oracle 12c

2018-08-23T15:48:43.527000Z

Hello,

We used Tora for many years to manage our Oracle databases. We are currently migrating them from 11g to 12c. With Oracle 12 databases, we noticed now very bad response time when we want to display the column characteristics of a table.

We have this issue with any Tora release (included the last 3.2-133); but only with Oracle release 12. Maybe due to its new multi-tenant architecture?

I traced your SQL request when calling this command (show columns list of a table of a schema). It is something like :

SELECT DISTINCT cl.column_name as nom,
cl.data_type as type,
cl.data_length as longueur,
cl.data_precision as precision,
cl.data_scale as echelle,
(SELECT comments FROM all_col_comments cm WHERE cm.owner = cl.owner AND cm.table_name = cl.table_name AND cm.column_name = cl.column_name ) AS "Comment",
cl.column_id
FROM all_tab_columns cl
WHERE cl.owner = &1 AND cl.table_name = &2
ORDER BY cl.column_id;

Best cost and response time would be get with this :

SELECT cl.column_name as nom,
cl.data_type as type,
cl.data_length as longueur,
cl.data_precision as precision,
cl.data_scale as echelle,
cm.comments,
cl.column_id
FROM user_tab_cols cl
left join user_col_comments cm
on (cl.table_name = cm.table_name and cl.column_name = cm.column_name)
WHERE cl.table_name = &1
ORDER BY cl.column_id;

What do you think about this alternative?

Thanks in advance for your response

Sylvain

Recent changes to 905: High cpu time when showing columns list of a table with oracle 12c

High cpu time when showing columns list of a table with oracle 12c