ORACLE STATISTICS

utworzone przez | mar 3, 2009 | Oracle

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

włączenie:
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8′

  • level 0 #wyłączone
  • level 1 #default
  • level 4 #default + bind variable values
  • level 8 #default + wait event information
  • level 12 #level 4 + level 8

wyłączenie:
ALTER SESSION SET EVENTS '10046 trace name context off’

włączenie dla innego usera;
ORADEBUG SETOSPID [nr z v$process];
ORADEBUG UNLIMIT;
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL8;
wyłączenie dla innego usera:
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

in 10g is available package DBMS_MONITOR:
– SESSION LEVEL
– turn on (level 8)
exec DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id => [sid],
serial_num => [serial],
waits => TRUE,
binds => FALSE);
– turn off:
exec DBMS_MONITOR.SESSION_TRACE_DISABLE(
session_id=> [sid],
serial_num=> [serial]);
– check:
SELECT sql_trace,sql_trace_waits,sql_trace_binds FROM v$session;
– DATABASE LEVEL
– turn on:
exec DBMS_MONITOR.DATABASE_TRACE_ENABLE(
waits => TRUE,
binds => TRUE,
instance_name > NULL);
– turn off:
exec DBMS_MONITOR.DATABASE_TRACE_ENABLE(
instance_name > NULL);
– check:
SELECT * FROM dba_enabled_traces;
– CLIENT LEVEL
– turn on:
exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(
client_id => 'client_name’,
waits => TRUE,
binds => FALSE);
– turn off:
exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'client_name’);
– check:
SELECT * FROM dba_enabled_traces;
– COMPONENT LEVEL (11g)
– turn on :
exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'serv_name’,
module_name => 'module’,
action_name => 'action’,
waits => TRUE,
binds => FALSE,
instance_name => NULL);
– turn off:
exec DBMS_MONITOR.SERV_MOD_TRACE_DISABLE(service_name => 'serv_name’,
module_name => 'module’,
action_name => 'action’,
instance_name => NULL)
– check:
SELECT * FROM dba_enabled_traces;

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]%’;