Google+ Followers

Pages

Thursday, July 28, 2011

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;

No comments:

Post a Comment