Monitor SQL currently in shared pool using Toad

Question

Anyone have any idea? And any open source sofware which also seens to perform this kind of functionality?

Answer 1

Toad does it. It is under Tools|SGA Trace/optimization.

Here is your open source solution :-)

 select distinct
       vs.sql_text, vs.sharable_mem, vs.persistent_mem, vs.runtime_mem, vs.sorts, vs.executions,
       vs.parse_calls, vs.module, vs.action, vs.buffer_gets, vs.disk_reads, vs.version_count,
       vs.loaded_versions, vs.open_versions, vs.users_opening, vs.loads, vs.users_executing,
       vs.invalidations, vs.serializable_aborts, vs.command_type,
       to_char(to_date(vs.first_load_time,'YYYY-MM-DD/HH24:MI:SS'),'MM/DD  HH24:MI:SS') first_load_time,
       rawtohex(vs.address) address, vs.hash_value hash_value, vs.parsing_user_id
       ,rows_processed 
       ,optimizer_mode
       ,vs.is_obsolete, vs.elapsed_time, vs.cpu_time
       ,vs.Child_latch, vs.fetches
       ,vs.program_id,vs.java_exec_time,vs.plsql_exec_time,vs.user_io_wait_time,
       vs.cluster_wait_time, vs.concurrency_wait_time, vs.application_wait_time,
       vs.direct_writes,  vs.end_of_fetch_count
from   v$sqlarea vs