How to See Where Your Oracle SQL Query is Slow

If your query is slow, you might be wondering why, and what you can do about it.
In this video, I'll show you
- how to see what the Oracle database does when it runs your query
- what to look out for
- taking some steps that may (or may not) improve the performance of the query

⏱ TIMESTAMPS:
00:00 - Open the Execution Plan
01:06 - What do we see
07:16 - What to look for
09:52 - Add indexes

[farhansuno]

The optimizer will use the indexes if all the columns for a table used in the query are found in the index.
You need to create an index like - index idx1 on cust_order(order_id, order_date, shipping_method, customer_id) .
If one or more columns used in the query, whether in select or filter or joins, are not found in an index, the optimizer would rather choose to read the table with full table scan.
This is why it is discouraged to use select * on a table.
It is better to only mention those columns which are actually needed in a query and then check if an index on all those columns could be helpful in optimizing the query.
Every index comes with a maintenance cost and disk space consumed, so you need to compare the trade-offs here.

[budmonk]

The reason it wasn't using the added indexes was because the Primary Key is already indexed by default and of course - the optimizer felt that the full table scan is cheaper.

[zafar]

Excellent topic, please add more videos with more examples ..

[inesh-study]

great explanation, thank you very much. can we have the github repo for db