<\/p>\n
You can find the queries that are run in the database from v$sql, v$sql_text views, and who are running from sys.all_users.<\/p>\n
You can create queries by associating v$sql, v$sql_text views and sys.all_users. The sample query is as follows.<\/p>\n
SELECT\r\nmodule,\r\nsql_text,\r\nusername,\r\ndisk_reads_per_exec,\r\nbuffer_gets,\r\ndisk_reads,\r\nparse_calls,\r\nsorts,\r\nexecutions,\r\nrows_processed,\r\nhit_ratio,\r\nfirst_load_time,\r\nsharable_mem,\r\npersistent_mem,\r\nruntime_mem,\r\ncpu_time,\r\nelapsed_time,\r\naddress,\r\nhash_value\r\nFROM\r\n(SELECT\r\nmodule,\r\nsql_text ,\r\nu.username ,\r\nround((s.disk_reads\/decode(s.executions,0,1, s.executions)),2)\r\ndisk_reads_per_exec,\r\ns.disk_reads ,\r\ns.buffer_gets ,\r\ns.parse_calls ,\r\ns.sorts ,\r\ns.executions ,\r\ns.rows_processed ,\r\n100 - round(100 *\u00a0 s.disk_reads\/greatest(s.buffer_gets,1),2) hit_ratio,\r\ns.first_load_time ,\r\nsharable_mem ,\r\npersistent_mem ,\r\nruntime_mem,\r\ncpu_time,\r\nelapsed_time,\r\naddress,\r\nhash_value\r\nFROM\r\nsys.v_$sql s,\r\nsys.all_users u\r\nWHERE\r\ns.parsing_user_id=u.user_id\r\nand UPPER(u.username) not in ('SYS','SYSTEM')\r\nORDER BY\r\n4 desc)\r\nWHERE\r\nrownum <= 20;<\/pre>\n\n<\/div>\n