There’s no equivalent to the MySQL slow query log in Oracle but these queries and the statspack reports are helpful when trying to find slow queries:

   1  -- Order queries by how many cpu seconds they consume
   2  SELECT   hash_value, executions, ROUND (elapsed_time / 1000000, 2) total_time,
   3           ROUND (cpu_time / 1000000, 2) cpu_seconds
   4      FROM (SELECT   *
   5                FROM v$sql
   6            ORDER BY elapsed_time DESC)
   7  ORDER BY cpu_seconds DESC;
   8  
   9  -- Find the total cpu seconds consumed
  10  SELECT SUM (cpu_seconds)
  11    FROM (SELECT   hash_value, executions,
  12                   ROUND (elapsed_time / 1000000, 2) total_time,
  13                   ROUND (cpu_time / 1000000, 2) cpu_seconds
  14              FROM (SELECT   *
  15                        FROM v$sql
  16                    ORDER BY elapsed_time DESC)
  17          ORDER BY cpu_seconds DESC);
  18  
  19  
  20  -- Find the total time queries have taken
  21  SELECT SUM (total_time)
  22    FROM (SELECT   hash_value, executions,
  23                   ROUND (elapsed_time / 1000000, 2) total_time,
  24                   ROUND (cpu_time / 1000000, 2) cpu_seconds
  25              FROM (SELECT   *
  26                        FROM v$sql
  27                    ORDER BY elapsed_time DESC)
  28          ORDER BY cpu_seconds DESC);
  29  
  30  -- Find execution plan for the damned query you should fix
  31  SELECT *
  32    FROM v$sql_plan
  33   WHERE hash_value = 2967942512;