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')