Why SELECT * Can Kill Your Oracle Performance: Real-World Cases

Case 1

Two SQL statements with identical parameters showed a >10× runtime difference. Monitoring reports revealed that SQL1 ran for 8 seconds and consumed 403 MB I/O, while SQL2 ran for 2.1 minutes with 15 GB I/O. The slower query accessed a shadow table lacking LOB columns, causing a TEMP TABLE TRANSFORMATION step and repeated scans of the IM_HISMESSAGE table. The faster query avoided this transformation.

Shadow‑Table Difference

The original table contained LOB fields; the shadow table did not. Because the WITH clause could not materialize a temporary table when LOBs were present, the optimizer performed a costly temporary‑table transformation. The developer’s convenience of writing SELECT * pulled unnecessary LOB columns, inflating I/O and runtime.

Case 2

A top‑SQL in a production AWR report consumed 84 % of DBTIME. The statement was a simple SELECT * FROM test_a WHERE object_id = 11. The execution plan showed an unusually high COST for TABLE ACCESS BY INDEX ROWID. The table was a wide table with over 400 columns; selecting all columns caused the optimizer to generate a high‑cost plan, whereas selecting only needed columns would have been far cheaper.

Case 3

Two test tables t1 and t2 were created from dba_objects. Two queries were run: one selecting a single column, the other selecting all columns. Using a USE_MERGE join, the SORT JOIN for the all‑column query consumed 1810 KB memory, while the single‑column query used only 424 KB. A HASH JOIN reduced the impact, but the memory difference remained noticeable.

Case 4

When tables are large and many columns are selected, the * wildcard adds unnecessary columns, increasing network transfer and I/O. On EXADATA systems, the SMART SCAN feature still shows a clear performance gap between SELECT * and a column‑list query.

Conclusion

These real‑world cases demonstrate that the “avoid SELECT *” rule is far from trivial. Selecting all columns can hide costly operations such as LOB handling, temporary‑table transformations, and excessive I/O, leading to hidden performance problems that are difficult to diagnose after deployment.