SQL currently using the most resources

While investigating performance issue, we may need to find out the sql which is most resource intensive. Below is the script, this will produce the list on resource intensive sqls currently running in you database.

Here goes script as;

select active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
sqlarea.sql_id,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username,sqlarea.sql_id
order by ttl_wait_time desc;

Example,

The output you will be getting as

USER_ID	USERNAME	SQL_TEXT	SQL_ID	TTL_WAIT_TIME
0	SYS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	gm9t6yefb1yu6	2,802,675
331	ASHISH	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	cb7vqpgadycjp	492,902
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	8tyhx4x2x47ga	485,157
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	4d3dhvjvuf880	454,847
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	4fq3fkixrsp9d	432,484
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	3t3umn6pod3ds	142,767
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	rs0tel46zczna	98,390

The above output will provide you the sql which is most resource extensive. Also you get sql_id for your further investigation.