Most of Oracle products and technologies heavily rely on Oracle database to function.

So when you need to improve the performance of your Oracle Fusion ERP / EBS / SOA / ADF application you need to start by looking at what’s going on in your Oracle Database.

Since Oracle Database 10g, the best way to analyze your database performance while your application is running is to retrieve an AWR report (Automatic Workload Repository).

The AWR is a great tool to retrieve all data related to :

  • Active sessions history and System statistics
  • Object usage statistics
  • Wait events
  • Process spending times
  • SQL query statistics

You can run this report between two AWR snapshot usually 1 or 2 hours away from each other to maximize results.

Ask your users to execute in the real condition the tasks that could need performance improvement then, later, run the following SQL queries to retrieve the report.

Note: you need to run this queries with a user owning the administrator Role or at least select grant on SYS.V_$DATABASE, select grant on SYS.DBA_HIST_SNAPSHOT, select grant on SYS.V_$INSTANCE, select grant on SYS.DBA_HIST_DATABASE_INSTANCE and execute grant on SYS.DBMS_WORKLOAD_REPOSITORY

Note 2: This functionality requires Enterprise Edition and the Diagnostics and Tuning option. Make sure you have the correct licensing before using this functionality.