When the “the database is slow” complaint comes in, the first reflex is often to upgrade the hardware. Yet most of the slowdowns we see in the field come from a handful of basic points being overlooked. Before spending money on servers, work through them in this order.
Measure first, then act
You don’t optimise by guesswork. Any change made without seeing where the slowness is amounts to a coincidence you’ll mistake for a fix.
If your licence covers them, AWR and ASH reports are the fastest route; otherwise you can work from v$session, v$sql and v$session_wait. You are looking for the answers to two questions: which SQL consumes the most resources and what are the sessions waiting for.
-- queries with the highest total elapsed time
SELECT sql_id, executions, elapsed_time/1e6 AS total_sec,
elapsed_time/NULLIF(executions,0)/1e6 AS avg_sec, sql_text
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;A query with a low average time but a very high execution count can be a bigger problem than a query that looks slow on its own.
Are the statistics up to date?
Oracle’s cost-based optimiser chooses plans based on table and index statistics. If the statistics are stale, the optimiser decides with the wrong information — and usually decides badly.
SELECT table_name, num_rows, last_analyzed
FROM user_tables
WHERE last_analyzed IS NULL
OR last_analyzed < SYSDATE - 7;On tables you bulk-load, gather statistics manually when the load finishes; waiting for the automatic collection window is often too late.
Actually read the execution plan
The estimated plan and the plan that actually runs can differ. To see the real plan and row counts:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '&sql_id',
format => 'ALLSTATS LAST'));The most important thing to look for in the plan is the gap between the estimated row count (E-Rows) and the actual row count (A-Rows). If the gap is large, the optimiser is wrong; the root cause is usually the previous point.
Use bind variables
Queries sent with a different literal value each time are a new query for Oracle every time: the shared pool bloats, and hard-parse cost and latch waits increase.
-- bad: a separate SQL statement for every customer
SELECT * FROM orders WHERE customer_id = 4711;
-- good: one SQL statement, different bind values
SELECT * FROM orders WHERE customer_id = :customer_id;If it can’t be fixed in the application, cursor_sharing can be a temporary workaround, but the permanent fix is to use binds in the application.
Avoid patterns that disable indexes
If an index exists but isn’t used, the way the query is written is usually to blame.
Wrapping the column in a function: WHERE UPPER(name) = 'AHMET' cannot use a normal index on name. Either change the query or create a function-based index.
Implicit type conversion: passing a number to a VARCHAR2 column (WHERE code = 12345) forces Oracle to convert the column and disables the index. Send the value as text: WHERE code = '12345'.
Leading wildcard: WHERE name LIKE '%company' is not suitable for an index scan; consider text search indexes for this kind of search.
Index strategy: fewer but right
Adding an index for every slow query is a common mistake. Every index speeds up reads but adds cost to every INSERT/UPDATE/DELETE.
In composite indexes, column order is decisive: columns filtered by equality and with high selectivity should come first. Also identify and clean up unused indexes — index usage monitoring helps here.
Don’t fetch data you don’t need
The SELECT * habit isn’t only a network and memory cost: if all the columns you need are in the index, Oracle can finish the query without touching the table at all (a covering index). Using the asterisk rules that possibility out from the start.
Likewise, instead of fetching every row to filter in the application, leave the filtering to the database.
Stop row-by-row processing
Doing individual INSERT/UPDATE statements inside a loop in PL/SQL is the most common performance killer we meet on large data sets. Use BULK COLLECT and FORALL for bulk operations; where possible, turn the work into a single set operation (INSERT ... SELECT, MERGE).
-- a single set operation instead of row by row
MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount);Then the hardware
If there is still a bottleneck after working through these eight points, you can now discuss hardware or configuration with a measured justification — memory, storage latency, parallelism settings. Reversing the order is expensive and usually fruitless.
When adapting this note to your own environment, check for version differences: syntax and view names can change between versions. Validate in a test environment before making changes in production.