Sql Commads

Find the objects that are currently being accessed.

select distinct sp. object_name from gv$sql_plan sp,gv$session gs
 where sp.sql_id =gs.sql_id
 and gs.status='ACTIVE'
 and sp.object_name is not null
 and gs.username not in ('SYS','SYSTEM')
 and sp.object_name not like '%:%'

Find the SQL and its duration that is currently running 

select distinct a.sid,a.serial#,a.logon_time,a.username,a.sql_id,a.sql_exec_start,sysdate,a.status,
substr(to_char(NUMTODSINTERVAL(a.sql_exec_start-sysdate, 'day')),5,15) duration ,a.event,a.program,a.module,b.sql_text
from gv$session a, gv$sql b
where a.status='ACTIVE'
and a.username not in ('SYS','SYSTEM')
and b.sql_id=a.sql_id
and b.parsing_schema_name=a.username
and a.program not like 'orac%'
and b.parsing_schema_id=a.user#
order by a.sql_exec_start;
 

Find the Top SQLs order by executing duration for a particular Period.

with qduration as ( select ash.sql_id,ash.user_id,ash.sql_exec_start,min(ash.sample_time) qmin ,max(ash.sample_time) qmax  ,
EXTRACT (hour FROM (max(ash.sample_time)-min(ash.sql_exec_start))) HH,
EXTRACT (MINUTE FROM (max(ash.sample_time)-min(ash.sql_exec_start)))  MI ,
EXTRACT (SECOND FROM (max(ash.sample_time)-min(ash.sql_exec_start))) SS
from dba_hist_active_sess_history ash
where ash.sample_time  BETWEEN    to_date('07-DEC-2017 10:00:00','DD-MON-YYYY HH24:MI:SS')
and to_date('07-DEC-2017 13:23:00','DD-MON-YYYY HH24:MI:SS')
and sql_id is not null
and sql_exec_start is not null
 --and ash.user_id=71
group  by ash.sql_exec_start,ash.sql_id,ash.user_id)
select qd.sql_id,qd.user_id,qd.sql_exec_start,qd.qmax,qd.qmin,qd.HH,qd.mi,qd.ss,dbms_lob.substr(dh.sql_text,4000,1)  from qduration qd,dba_hist_sqltext dh
where qd.sql_id=dh.sql_id
order by 6 desc , 7  desc,8 desc;

SQL to find the tables are analyzed.

select owner,table_name,num_rows,last_analyzed from dba_tables where table_name=&tablename and owner=&owner;

From delveloper perspective, use 

select table_name,num_rows,last_analyzed from dba_tables where table_name=&tablename ;

Find temp usage 

 select s.username,s.osuser,u.tablespace,s.sql_id,substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
 sum(round(((u.blocks*p.value)/1024/1024),2)) size_mb
 from v$sort_usage u, v$session s, v$sqlarea a,v$parameter p
 where s.saddr = u.session_addr
 and a.address (+) = s.sql_address
 and a.hash_value (+) = s.sql_hash_value
 and p.name = 'db_block_size'
 group by  s.username,s.osuser,u.tablespace,s.sql_id,
 substr(a.sql_text, 1, (instr (a.sql_text, ' ')-1))