Useful shortcuts for vi editor

Wednesday, 12 April 2017

How to see TOP 10 SQL that consuming system resources highly?

e.g.
select * from
(
select * from v$sql
order by DISK_READS desc
)
where rownum < 11

options for order by:
RUNTIME_MEM, EXECUTIONS, DISK_READS, SORTS, ELAPSED_TIME

for old querries:
select * from dba_hist_sqltext where sql_id = '8j2hv3c6wskdy'

for SQL plan:
select * from V$SQL_PLAN where sql_id = '8j2hv3c6wskdy'

find the object location of SQL
select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'