Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
You can create queries by associating v$sql, v$sql_text views and sys.all_users. The sample query is as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | SELECT module, sql_text, username, disk_reads_per_exec, buffer_gets, disk_reads, parse_calls, sorts, executions, rows_processed, hit_ratio, first_load_time, sharable_mem, persistent_mem, runtime_mem, cpu_time, elapsed_time, address, hash_value FROM (SELECT module, sql_text , u.username , round((s.disk_reads/decode(s.executions,0,1, s.executions)),2) disk_reads_per_exec, s.disk_reads , s.buffer_gets , s.parse_calls , s.sorts , s.executions , s.rows_processed , 100 - round(100 * s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio, s.first_load_time , sharable_mem , persistent_mem , runtime_mem, cpu_time, elapsed_time, address, hash_value FROM sys.v_$sql s, sys.all_users u WHERE s.parsing_user_id=u.user_id and UPPER(u.username) not in ('SYS','SYSTEM') ORDER BY 4 desc) WHERE rownum <= 20; |
perfect