Queries

============================================
Display time waited for each wait class.
============================================


SELECT a.wait_class, sum(b.time_waited)/1000000 time_waited
FROM   v$event_name a
       JOIN v$system_event b ON a.name = b.event
GROUP BY a.wait_class;


===============================================
Display session wait information by wait class.
===============================================


SELECT *
FROM   v$session_wait_class
WHERE  sid = &enter_sid;



===============================================
Statistics (delete lock unlock)
===============================================


BEGIN
  DBMS_STATS.delete_table_stats('MY_SCHEMA','LOAD_TABLE');
  DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');
  DBMS_STATS.unlock_table_stats('MY_SCHEMA','LOAD_TABLE');

END;


=============================================
Retrieve SAMPLE Date (%)
============================================

SELECT e.empno, e.ename, d.dname
FROM   emp SAMPLE (10) e
       JOIN dept d ON e.deptno = d.deptno;




===============================================
past 30 minutes waits
===============================================

select
ash.event,
        sum(ash.wait_time +ash.time_waited) ttl_wait_time
      from v$active_session_history ash
where ash.sample_time between sysdate - 30/1440 and sysdate
group by ash.event
order by 2
/


=================================================
What user is waiting the most(last_hour) ?
=================================================


select sesion.sid,
           sesion.username,
           sum(active_session_history.wait_time +
               active_session_history.time_waited) ttl_wait_time
     from v$active_session_history active_session_history,
           v$session sesion
     where active_session_history.sample_time between sysdate - 1/24 and sysdate
       and active_session_history.session_id = sesion.sid
    group by sesion.sid, sesion.username
  order by 3






==============================================
What SQL is currently using the most resources? (lasthour)
==============================================

select active_session_history.user_id,
           dba_users.username,
           sqlarea.sql_text,
           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 - 1/24 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
  order by 4
/



==============================================
What object is currently causing the highest resource waits? (lasthour)
==============================================


 select dba_objects.object_name,
           dba_objects.object_type,
           active_session_history.event,
           sum(active_session_history.wait_time +
               active_session_history.time_waited) ttl_wait_time
      from v$active_session_history active_session_history,
           dba_objects
     where active_session_history.sample_time between sysdate - 1/24 and sysdate
       and active_session_history.current_obj# = dba_objects.object_id
   group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
  order by 4

===========================================

script to gather database statistics

==========================================

begin

DBMS_STATS.GATHER_DATABASE_STATS (

estimate_percent =>100,

block_sample=>FALSE,

method_opt=>'for all columns size auto',

degree=>null,

cascade=>true,

no_invalidate=>false,

options=>'GATHER STALE',

gather_sys=>FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (

estimate_percent =>100,

block_sample=>FALSE,

method_opt=>'for all columns size auto',

degree=>null,

cascade=>true,

no_invalidate=>false,

options=>'GATHER EMPTY',

gather_sys=>FALSE);

end;

=========================================

script to gather dictionary statistics

========================================

begin

DBMS_STATS.GATHER_DICTIONARY_STATS (

estimate_percent =>100,

block_sample=>FALSE,

method_opt=>'for all columns size auto',

degree=>null,

cascade=>true,

no_invalidate=>false,

options=>'GATHER STALE'

);

DBMS_STATS.GATHER_DICTIONARY_STATS (

estimate_percent =>100,

block_sample=>FALSE,

method_opt=>'for all columns size auto',

degree=>null,

cascade=>true,

no_invalidate=>false,

options=>'GATHER EMPTY'

);

end;

========================================

schedule a job (statistics)

=======================================

begin

sys.dbms_scheduler.create_job(job_name => '"SYS"."ESTIMATE100_GATHERAUTO"',

job_type => 'PLSQL_BLOCK',

job_action => 

'begin

DBMS_STATS.GATHER_DATABASE_STATS (

estimate_percent =>100,

block_sample=>FALSE,

method_opt=>''for all columns size auto'',

degree=>null,

cascade=>true,

no_invalidate=>false,

options=>''GATHER STALE'',

gather_sys=>FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (

estimate_percent =>100,

block_sample=>FALSE,

method_opt=>''for all columns size auto'',

degree=>null,

cascade=>true,

no_invalidate=>false,

options=>''GATHER EMPTY'',

gather_sys=>FALSE);

end;',

repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',

start_date => trunc(sysdate+1) + 2/24,

job_class => 'DEFAULT_JOB_CLASS',

comments => 'Gather auto stats on every table with 100% sampling',

auto_drop => FALSE,

enabled => FALSE);

sys.dbms_scheduler.set_attribute(name => '"SYS"."ESTIMATE100_GATHERAUTO"',

attribute => 'job_priority',

value => 4);

sys.dbms_scheduler.enable('"SYS"."ESTIMATE100_GATHERAUTO"');

end;