Please check following points:
- When you open the dashboard page, first figure out whether Prompt or Report is taking more time.
- If it is prompt, check for any multi select prompts which are taking time to load all values.. (best practice is set default values).
- If Report is taking longer time, set some default filters and query for just couple of records and check the report performance.
- Take physical SQL from the session log (if SQL is not generated check for log level whether it is less than 2).
- Run this Physical SQL in the TOAD or any SQL Editor.
- Check Explain plan for the cost of the query.
- Hash Joins/Cartesian Joins are always kills performance.
- Some-times, Force Inner-join also helps to force the tables to have inner-join between two tables.
- Check are there any “Full Table Scans” happening instead of Index Scan.
- Consult your DBA for the Stats.
Thank you Deepika for your quick reply.
I have observed here, my reports are taking longer time. If i want to check the joins i am not able to find out which column is coming from which table?
there are lot of sub queries in my Physical Sql. please help me. If u want i can post my physical sql here.
1. To check table names from which data is fetched out, you can check the query log file nquery.log or simply go to Administration > Manage Sessions. There check your particular Physical SQL and Logical SQL too. (Set your LOG LEVEL to 2)
2. If your are aware about Aggregate tables, use it.
3. Use Cache management to improve your performance