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

1
votes

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

2
sqloracleoracle11gquery-performance
There could be a lot wrong. I found a post with listed tools which might help you to check what is happening. (community.oracle.com/thread/503834) Have you already tried some of them, and if yes what were the output? – Malte Kölle
Does it happen only on this table or also in other tables (in the same database or even on other database)? It's may lead you to where you need to look at (table, database or server configuration). – Adirmola
Please edit your question and add the execution plan as formatted text not as a screenshot – a_horse_with_no_name
Are there many other columns in your table?. Does the table have clob/blob datatypes which are being retreived when doing SELECT * How much time does the following query take SELECT ID FROM TABLE WHERE ID = 123 – George Joseph
Does the disk the table is located have too much I/O – DesignerAnalyst

2 Answers

1
votes

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

0
votes

Multiple things can be wrong but two come to mind.

First is that your query with WHERE ID = 123 is returning a significant number of rows. Beyond a certain threshold, the database will decide that a full table scan is more efficient than using the index. This is related to the idea of cardinality of the values in the index.

Second, is that ID is not a number. If it is a string, then the column is being converted to a number and that could preclude the use of an index.

There are other possibilities, but these seem like the most likely.