Oracle SQLArea Most Expensive SQLs.

From v$sqlarea view you can check most expensive SQL clauses in your Oracle database. You can for example make checks about CPU time, Elapsed time, Executions, Buffer gets, Disk reads etc...

Here is the example SQL for CPU time (seconds) per execution fetching (run this as user who have permission to select from v$sqlarea) (this will fetch other values too but the order is made by CPU time per execution):
----

SQL> set pages 120
SQL> set lines 120

SQL> spool CPU_Time_seconds_per_exec.txt

SQL> select rownum as sorting, a.* from (
SELECT sqlst.parsing_schema_name, sqlst.sql_id, sqlst.last_active_time,
(sqlst.buffer_gets/sqlst.executions) "buffer_gets per exec",
(sqlst.disk_reads/sqlst.executions) "disk_reads per exec",
(sqlst.cpu_time/1000000) "cpu Time (s)",
(sqlst.elapsed_time/1000000) "Elapsed Time (s)",
(sqlst.user_io_wait_time/sqlst.executions) "iowait per exec",
sqlst.executions "ex",
sqlst.module,
substr(sqlst.sql_text,1,250) || '|' FROM
v$sqlarea sqlst
WHERE
sqlst.executions > 0 AND sqlst.parsing_schema_name <> 'ANONYMOUS' AND sqlst.parsing_schema_name <> 'APEX_030200'
AND sqlst.parsing_schema_name <> 'APEX_PUBLIC_USER' AND sqlst.parsing_schema_name <> 'APPQOSSYS'
AND sqlst.parsing_schema_name <> 'DBSNMP' AND sqlst.parsing_schema_name <> 'DIP' AND sqlst.parsing_schema_name <> 'EXFSYS'
AND sqlst.parsing_schema_name <> 'FLOWS_FILES' AND sqlst.parsing_schema_name <> 'MDSYS' AND sqlst.parsing_schema_name <> 'ORACCT'
AND sqlst.parsing_schema_name <> 'ORACLE_OCM' AND  sqlst.parsing_schema_name <> 'ORDDATA' AND sqlst.parsing_schema_name <> 'ORDPLUGINS'
AND sqlst.parsing_schema_name <> 'ORDSYS' AND sqlst.parsing_schema_name <> 'OUTLN' AND sqlst.parsing_schema_name <> 'PUBLIC'
AND sqlst.parsing_schema_name <> 'SI_INFORMTN_SCHEMA' AND sqlst.parsing_schema_name <> 'SYS' AND sqlst.parsing_schema_name <> 'SYSTEM'
AND sqlst.parsing_schema_name <> 'WMSYS' AND sqlst.parsing_schema_name <> 'XDB' AND sqlst.parsing_schema_name <> 'XS$NULL' AND
TO_CHAR(sqlst.last_active_time, 'YYYYMMDD')=TO_CHAR(SYSDATE, 'YYYYMMDD')
ORDER BY "cpu Time (s)" desc ) a
where rownum < 11



SQL> spool off;

----

In this example SQL fetch only this day rows. You can make more accurate fetch if you make changes where clause "last_active_time" section. And with rownum you can limit number of rows that fetch will list.
And changing sort (ORDER BY) you'll get different most expensive lists like sqls that used most time (elapsed_time) . parsing_schema_name clauses in where will exclude Oracle own schemas from result set so that you can see only user made schemas.