For managing and helping to tune long-running SQL statements, Enterprise Manager Cloud Control 12c provides a very useful utility called SQL Monitoring, which is enabled out of the box with no performance impact.
This will automatically monitor any long-running SQL statement that has gone beyond 5 seconds of either CPU or I/O time, as well as any parallel statements. However, in a very busy system with lots of such queries, SQL monitoring may not take place for all the queries. You can use the
/*+MONITOR*/ hint in your SQL statements to explicitly ask for SQL monitoring in this case.
You can access the SQL monitoring page by going to Performance | SQL Monitoring. Any SQL statements that are long-running or parallel will appear on ...