Oracle Cite-iusejava
Get current session> SELECT sid, serial#, username, machineFROM v$session
WHERE type != 'BACKGROUND'
Enable & Disable sql trace:
dbms_monitor.session_trace_enable(session_id => 127,
serial_num => 29,
waits => TRUE,
binds => FALSE)
dbms_monitor.session_trace_disable(session_id => 127,
serial_num => 29)
Gather time statistics
ALTER SESSION SET timed_statistics = TRUE
Get trace file
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
General trprof usage
tkprof {input trace file} {output file} sys=no sort=prsela,exeela,fchela
prsela: elapsed time parsing.
Gather table statistics:
BEGIN
dbms_stats.gather_table_stats(ownname => user,
tabname => 'T',
estimate_percent => 100,
method_opt => 'for all columns> cascade => TRUE);
END;
/
Get statistic on table:
SQL> SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
2FROM user_tab_statistics
3WHERE table_name = 'T';
Get statistic on column:
SQL> SELECT column_name AS "NAME",
2 num_distinct AS "#DST",
3 low_value,
4 high_value,
5 density AS "DENS",
6 num_nulls AS "#NULL",
7 avg_col_len AS "AVGLEN",
8 histogram,
9 num_buckets AS "#BKT"
10FROM user_tab_col_statistics
11WHERE table_name = 'T';
Gain explain plan:
SQL> EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno = 10 ORDER BY ename;
SQL> SELECT * FROM table(dbms_xplan.display);
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'typical'));
页:
[1]