Oracle Cite-iusejava

nikoo

　　Get current session>　　SELECT sid, serial#, username, machine
　　FROM 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
　　2  FROM user_tab_statistics
　　3  WHERE 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"
　　10  FROM user_tab_col_statistics
　　11  WHERE 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'));