What queries are running in the database

What queries are running in the database

What are the queries that are running ?

select sesion.sid,
 sesion.username,
 optimizer_mode,
 hash_value,
 address,
 cpu_time,
 elapsed_time,
 sql_text
 from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
 and sesion.sql_address = sqlarea.address
 and sesion.username is not null;

Get the rows fetched, if there is difference it means processing is happening ?

select b.name, a.value vlu from v$sesstat a, v$statname b where a.statistic# = b.statistic# and sid =&sid and a.value != 0 and b.name like '%row%';

Get the sql_hash_value ?

select sql_hash_value from v$session where sid='&sid';
SQL> select sql_hash_value from v$session where sid='&sid';
Enter value for sid: 1075
old 1: select sql_hash_value from v$session where sid='&sid'
new 1: select sql_hash_value from v$session where sid='1075'
SQL_HASH_VALUE
--------------
 928832585

Get the sql_Text ?

SQL> select sql_text v$sql from v$sql where hash_value =&Enter_Hash_Value;
Enter value for enter_hash_value: 928832585

Get the explain_plan ?

set lines 190
col XMS_PLAN_STEP format a40
set pages 100
select
 case when access_predicates is not null then 'A' else ' ' end ||
 case when filter_predicates is not null then 'F' else ' ' end xms_pred,
 id xms_id,
 lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,
 object_name xms_object_name,
 cost xms_opt_cost,
 cardinality xms_opt_card,
 bytes xms_opt_bytes,
 optimizer xms_optimizer
from
 v$sql_plan
where
 hash_value in (&SQL_HASH_VALUE)
 and to_char(child_number) like '%';

Based the cost u can decide what to be done.
One of the solutions is to analyse the statistics