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\u0142ego schematu wraz z indeksami<\/p>\n
TIMED_STATISTICS = TRUE<\/p>\n
SELECT last_analysed FROM DBA_TABLES – data zbierania statystyk<\/p>\n
TRACE w\u0142\u0105czenie: wy\u0142\u0105czenie: w\u0142\u0105czenie dla innego usera; in 10g is available package DBMS_MONITOR: <\/b> TKPROF<\/b> 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]<\/p>\n options: <\/p>\n
<\/span>TIMED_STATISTICS TRUE
<\/span>ALTER SESSION SET SQL_TRACE = TRUE – generacja pliku \u015bladu dla zapyta\u0144(obci\u0105\u017ca baz\u0119),
kt\u00f3ry b\u0119dzie znajdowa\u0142 si\u0119 w katalogu;
show parameter UDUMP; (USER DUMP DEST) #uruchomiony przez usera
show parameter BDUMP; (BACKGROUND DUMP DEST) #uruchomiony przez proces t\u0142a
MAX_DUMP_FILE_SIZE #max rozmiar pliku
[instance_name]_[process_name]_[process_id].trc #tracefile format <\/p>\n
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8′<\/p>\n\n
ALTER SESSION SET EVENTS '10046 trace name context off’<\/p>\n
ORADEBUG SETOSPID [nr z v$process];
ORADEBUG UNLIMIT;
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL8;
wy\u0142\u0105czenie dla innego usera:
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;<\/p>\n
– SESSION LEVEL<\/b>
– 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<\/b>
– 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<\/b>
– 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)<\/b>
– turn on :<\/b>
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;<\/p>\n
! tkprof czyta pliki do 2GB<\/span>
je\u017celi plik trace jest wi\u0119kszy ni\u017c 2GB nale\u017cy utworzy\u0107 plik fifo:
mkfifo [plik_fifo]
wywo\u0142a\u0107 komend\u0119 tkprof na pliku fifo:
TKPROF [plik_fifo] [output_file] …
przej\u015b\u0107 do innej konsoli i wywo\u0142a\u0107:
cat [plik_trace] > [plik_fifo]<\/p>\n