Hi All,
I was faced with a situation in which one of our dw reports plan changed on the last day and it was running slow. Report plans can change as data increases and good running sql can go bad. There are ways to handle this.
1) Creating data profiles by DBA is one of the solution
2) Using hints in the query is a quick fix
I was faced with a situation in which one of our dw reports plan changed on the last day and it was running slow. Report plans can change as data increases and good running sql can go bad. There are ways to handle this.
1) Creating data profiles by DBA is one of the solution
2) Using hints in the query is a quick fix
Things learned
- If you use in to join a table in where cluase it will go for nested loop and this might slow your query down
- If you see the row number out of sync beware this might indicate that there is an issue with your statisitics
- Indexes do not need to be analysed in Oracle 11g . In Oracle 10 it was compute statistics a statement that was always there after you build indexes
- Not using Quotes for varchar column will cause it not to use indexes which can have a major impact on the plan including non use of star transformation
- Outer joins takes more time so avoid outer joins
