ORACLE STATISTICS

exec DBMS_STATS.GATHER_SCHEMA_STATS('[username]', cascade=>TRUE);
exec #dla tabeli wraz z indeksami DBMS_STATS.GATHER_TABLE_STATS('[username','[table_name]',cascade=>TRUE); #dla całego schematu wraz z indeksami

TIMED_STATISTICS = TRUE

---

SELECT last_analysed FROM DBA_TABLES - data zbierania statystyk

---

EXPLAIN PLAN
$oracle_home/rdbms/admin/utlxplan.sql - tworzy tabelę PLAN_TABLE dla generowania planów poprzedzonych:
EXPLAIN PLAN SET STATEMENT_ID = '[nazwa]' FOR SELECT ...

---

TRACE
TIMED_STATISTICS TRUE
ALTER SESSION SET SQL_TRACE = TRUE - generacja pliku śladu dla zapytań(obciąża bazę),
który będzie znajdował się w katalogu;
show parameter UDUMP; (USER DUMP DEST) #uruchomiony przez usera
show parameter BDUMP; (BACKGROUND DUMP DEST) #uruchomiony przez proces tła
MAX_DUMP_FILE_SIZE #max rozmiar pliku
[instance_name]_[process_name]_[process_id].trc #tracefile format
TKPROF
! tkprof czyta pliki do 2GB
jeżeli plik trace jest większy niż 2GB należy utworzyć plik fifo:
mkfifo [plik_fifo]
wywołać komendę tkprof na pliku fifo:
TKPROF [plik_fifo] [output_file] ...
przejść do innej konsoli i wywołać:
cat [plik_trace] > [plik_fifo]

TKPROF [trace_file] [output_file] SORT=([option] if option sort is not set, order is similar as tracefile, many options means that TKPROF sums the value of the options) PRINT=[only with sort opition - limit number of SQL in output file] SYS=[no - dont list SQL executed by SYS user]


options:

PARSE	EXEC	FETCH
PRSCNT	EXECNT	FCHCNT	number of times
PRSCPU	EXECPU	FCHCPU	CPU time spending
PRSELA	EXEELA	FCHELA	elapsed time spent
PRSDSK	EXEDSK	FCHDSK	number of disk physical reads
PRSQRY	EXEQRY	FCHQRY	number of buffers for consistent read
PRSCU	EXECU	FCHCU	number of buffers for current read
	EXEROW	FCHROW	number of rows processed, fetched
PRSMIS	EXEMIS	FCHMIS	number of library cache misses

example:
tkprof ../db_ora_10196.trc db_ora_10196-1.txt sys=no sort=prsela,exeela,fchela

dobre wyjaśnienie na blogu
podane za Oracle® Database Performance Tuning Guide
10g Release 2 (10.2) Part Number B14211-03:

CALL Value	Meaning
PARSE	Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
EXECUTE	Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.
FETCH	Retrieves rows returned by a query. Fetches are only performed for SELECT statements.

SQL Trace Statistic	Meaning
COUNT	Number of times a statement was parsed, executed, or fetched.
CPU	Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
ELAPSED	Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
DISK	Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.
QUERY	Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries. Consistent mode czyli spójny tryb wszystkich danych dla SCN z którego pochodzi statement, bufory pochodzące z tego trybu obejmują te z undo, konieczne dla utrzymania stanu z tego samego SCN
CURRENT	Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

SQL Trace Statistic	Meaning
ROWS	Total number of rows processed by the SQL statement. This total does not include rows processed by subqueries of the SQL statement.

---

AUTOTRACE
[oracle_home]/sqlplus/admin/plustrce.sql - skrypt do ustawienia roli PLUSTRACE potrzebnej do korzystania z AUTOTRACE
SET AUTOTRACE TRACEONLY | ON EXPLAIN | ON STATISTICS | ON EXPLAIN STATISTICS #bez wyników zapytań
SET AUTOTRACE ON EXPLAIN | ON STATISTICS | ON EXPLAIN STATISTICS #wraz z wynikami zapytań
SET AUTOTRACE OFF

---

ZBIERANIE STATYSTYK (przestarzałe)
utlbstat.sql - początek (usuwanie tabel zawierających kopie V$ i ponowne odtworzenie i zebranie tam nowych danych
utlestat.sql - koniec (generacja reportu report.txt

---

STATSPACK
$oracle_home/rdbms/admin/spcreate.sql - uruchamia poniższe skrypty:
$oracle_home/rdbms/admin/spcusr.sql - tworzenie usera PERFSTAT (log w spcusr.lis)
$oracle_home/rdbms/admin/spctab.sql - tworzy tabele (log w spctab.lis)
$oracle_home/rdbms/admin/spckg.sql - tworzy pakiety (log w spcpkg.sql)
EXECUTE STATSPACK.SNAP; - generacja migawki (punktu w którym zbierane są statystyki)
EXECUTE STATSPACEK.STATSPACK_MODIFY_PARAMETERS - zmiana parametrów zbierania migawek
$oracle_home/rdbms/admin/spreport.sql - generuje raport
$oracle_home/rdbms/admin/spauto.sql - generuje joba dla migawek gdzie interwał:
SYSDATE+1 - raz dziennie
SYSDATE+1/48 - raz na 1/2 h
SYSDATE+1/24 - raz na 1 h
$oracle_home/rdbms/admin/sptrunc.sql - usuwanie zawartości wszystkich tabel
$oracle_home/rdbms/admin/sppurge.sql - usuwanie w/g migawek
$oracle_home/rdbms/admin/spdrp.sql - uruchamia poniższe skrypty:
$oracle_home/rdbms/admin/spdtab.sql - usuwa tabele i pakiety
$oracle_home/rdbms/admin/spdusr.sql - usuwa usera PERFSTAT

---

DBMS_STATS
pakiet służy do zbierania statystyk dla optymalizatora kosztowego wyzwalany przez GATHER_STATS_JOB
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB'; $oracle_home/rdbms/admin/dbmsstat.sql - specyfikacja pakietu
$oracle_home/rdbms/admin/prvtstat.plb - ciało pakietu

---

select * from dba_hist_sql_plan where options='FULL SCAN' and operation='TABLE' and object_owner like '%[user]%';