Small DB table (Oracle) still huge execution time for SELECT * FROM

Question

I have a quite small table in Oracle 12c. It has around 45K records and size is 12MB (after recent gather stats). But it takes 30 sec to 1 min 30 secs to run

SELECT * FROM table_name;

Additionally if I run

SELECT * FROM TABLE WHERE ID = 123

(where id is an indexed column) it too takes around 45 secs.

What could be wrong?

UPDATE: Explain plan as requested.

SELECT * FROM {table_name}

SELECT STATEMENT  ALL_ROWSCost: 410  Bytes: 14,733,600  Cardinality: 43,850      
    1 TABLE ACCESS FULL TABLE {table_name} Cost: 410  Bytes: 14,733,600  Cardinality: 43,850  


SELECT * FROM {table_name} WHERE id = 123

SELECT STATEMENT  ALL_ROWSCost: 2  Bytes: 672  Cardinality: 2          
    2 TABLE ACCESS BY INDEX ROWID BATCHED TABLE {table_name} Cost: 2  Bytes: 672  Cardinality: 2      
        1 INDEX RANGE SCAN INDEX {index_name} Cost: 1  Cardinality: 2 

Sorry for hiding the object name to comply with organization policy

Answer 1

After liaising with DBA, we found above query was mostly waiting on library cache lock and library cache pin wait events. The table had more than one hundred thousand views (with dynamic names like VW_TABLE_12345, VW_TABLE_12346 etc.) created on it as dependent objects which was apparently a very bad design. When we changed code and cleared all the views, the table was as fast as it should be