What are the best suggestion to handle when we implement performance tuning in oracle? Below are the scenario:
Scenario # 1 : App team is running some test cases from front end. and there was a performace degradation while running the SQL statements in the transaction. it means 39 round trip to the database.
Of the 39 SQL statements – 21 inserts, 16 selects, 3 update. and the AWR report says that there were few most expensive SQL statements(some insert queries).
Scenario # 2 : There was a small percentage (<1%) of “buffer busy wait” which could be elongating individual SQL statements by an average of 27ms – need to do tuning by reducing data and index contention
So what is the best option available in oracle performance tuning other than Explain plan, AWR report, ORacle partitioning and SQL trace for the above 2 scenarios?? also implemented Oracle huge page by increasing the memory.. Performance was slighly improved but no use again it went back to the same state.
IS there any other way I can use Index_ffs_scan and RICHS_SECRET_HINT or caching a table in a memory options to make SQL statements much faster?? any other new methods available in ORacle performance tuning?? suggestions please.. also Pl let me know if you want more details..