Zbieranie statystyk dla tabeli:

exec dbms_stats.gather_table_stats('',upper('&tab'), method_opt=>'for all columns size auto');

Statystyki zebrane dla kolumny:

select num_distinct, low_value, high_value,
       num_nulls, histogram
from   user_tab_columns
where  table_name=upper('&tab') and column_name=upper('&col');

Histogram dla kolumny:

select endpoint_number, endpoint_value,
       endpoint_actual_value, endpoint_repeat_count
from   user_tab_histograms
where  table_name=upper('&tab') and column_name=upper('&col');

Plan wykonania:

alter session set statistics_level=all;

...zapytanie...
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST'));

Podstawowe statystyki bieżącej sesji:

select name, value from v$mystat natural join v$statname
where name in ('physical reads direct', 'physical reads', 
               'session logical reads', 'CPU used by this session',
               'DB time', 'consistent gets', 'db block gets', 
               'db block changes','table scans (long tables)',
               'table scans (short tables)', 'redo size',  
               'table scans (direct read)', 'table fetch by rowid');

Plan Directives:

select o.object_name, o.subobject_name col_name,
       o.object_type, d.type, d.reason
from   dba_sql_plan_directives d, dba_sql_plan_dir_objects o
where  o.directive_id=d.directive_id
  and  o.object_name=upper('&tab');

Zapytania o dużej liczbie wersji (Child Cursors):

select sql_id, version_count, sql_text
from   v$sqlarea
where  version_count>10;

Child Cursors:

select sql_id, hash_value, child_number, sharable_mem, sql_text
from v$sql
order by sql_id, child_number;

Obiekty w Buffer Cache:

select b.owner||'.'||b.object_name object,
       count(*) num_blocks
from   x$bh a, dba_objects b
where  a.obj=b.object_id
group by owner, object_name
order by count(*) desc;

Skumulowane zdarzenia oczekiwania w systemie:

select s.event, s.total_waits, s.time_waited, s.average_wait
from   v$system_event s, v$event_name n, v$system_wait_class c
where  s.event_id=n.event_id and n.wait_class#=c.wait_class#
order by s.event;

Aktualne zużycie PGA z podziałem na sesje:

select ss.sid, ss.value
from   v$sesstat ss join v$statname sn
  on   (ss.statistic# = sn.statistic#)
 where sn.name = 'session pga memory';

Polecenia SQL, które nie mieszczą się w SQL Work Areas:

select s.sql_text, wa.operation_type, wa.last_memory_used
from   v$sql s join v$sql_workarea wa on (s.address = wa.address)
where  onepass_executions+multipasses_executions>0;

Podział pamięci PGA sesji na obszary:

select s.sid, p.spid, pm.category, pm.allocated
from   v$process_memory pm join v$process p on (pm.pid=p.pid)
join   v$session s on (p.addr=s.paddr)
where  sid=&identyfikator_sesji;

Efektywność wykorzystania SQL Work Area w PGA:

select name, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
  from (select name, value cnt, (sum(value) over ()) total
  from v$sysstat
 where name like 'workarea exec%');

Kiedy ostatnio zbierano statystyki dla tabeli, czy są aktualne:

select table_name, to_char(last_analyzed,'YYYY-MM-DD HH24:MI') when,
        stale_stats
from user_tab_statistics
where table_name=upper('&tab')