- Check all tables must have joins in the where clause.
- If possible you can put more joins too.
- Check all the columns what id using in where clause for joins or filters has indexes. If not try to create indexes.
- Try to reduce the in class with exists.
- Check all sub queries has joins with main tables.
- Then get the explain plan and check which table has full scan. If the table has look ups that has full join not a problem. If other table like millions of records and going through full join then try to crate a index on that table.
- You can use Hints finally to improve the performance. By hint you are forcefully run the query using that hint.
/* Leading(LINES, HEADS) */
/* Index(lined linx_id)*/