========================================================================================================= STATISTICS ================================================================================================= drop table t; drop table t1; create table t as select object_id i, owner s from all_objects where rownum < 100000; create table t1 as select i,s from t where s = 'SYS' or s='MAR'; create index ti on t(i); create index ts on t(s); create index t1i on t1(i); create index t1s on t1(s); -------USER_TABLE select * from user_tables where table_name = 'T'; analyze table t delete statistics; select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, avg_space_freelist_blocks, num_freelist_blocks, sample_size, last_analyzed from user_tables where table_name = 'T'; analyze table t compute statistics for table; select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, avg_space_freelist_blocks, num_freelist_blocks, sample_size, last_analyzed from user_tables where table_name = 'T'; -------USER_INDEXES analyze table t delete statistics; select blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, num_rows, sample_size, last_analyzed from user_indexes where table_name = 'T' and index_name = 'TI'; analyze table t compute statistics for all indexes; select blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, num_rows, sample_size, last_analyzed from user_indexes where table_name = 'T' and index_name = 'TI';table; --------USER_TAB_COLUMNS select * from user_tab_columns where table_name = 'T' and column_name = 'I'; select * from user_tab_col_statistics where table_name = 'T' and column_name = 'I'; analyze table t delete statistics; select * from user_tab_col_statistics where table_name = 'T' and column_name = 'I'; analyze table t compute statistics for all columns; select * from user_tab_col_statistics where table_name = 'T' and column_name = 'I'; -------USER_TAB_HISTOGRAMS analyze table t compute statistics for columns s size 254; select endpoint_number, endpoint_value, substr(endpoint_actual_value, 1, 30) from user_tab_histograms where table_name = 'T' and column_name = 'S'; analyze table t compute statistics for columns s size 8; select endpoint_number, endpoint_value, substr(endpoint_actual_value, 1, 30) from user_tab_histograms where table_name = 'T' and column_name = 'S'; analyze table t compute statistics for columns s size 254; select endpoint_number, endpoint_value, substr(endpoint_actual_value, 1, 30) from user_tab_histograms where table_name = 'T' and column_name = 'S'; select s, count(*) from t group by s; ========================================================================================================= AUTOTRACE @C:\ORA92\sqlplus\admin\plustrce.sql COLUMN id_plus_exp FORMAT 990 HEADING i COLUMN parent_id_plus_exp FORMAT 990 HEADING p COLUMN plan_plus_exp FORMAT a60 COLUMN object_node_plus_exp FORMAT a8 COLUMN other_tag_plus_exp FORMAT a29 COLUMN other_plus_exp FORMAT a44 set autotrace off set autotrace on explain set autotrace on statistics set autotrace on set autotrace traceonly set autotrace traceonly explain stat ====================================================================== set autotrace traceonly; select * from t join t1 on t.i=t1.i where t1.s = 'SYS'; select/*+INDEX(t ti),INDEX(t1 t1i)*/ * from t join t1 on t.i=t1.i where t1.s = 'SYS'; select/*+INDEX(t ts),INDEX(t1 t1s)*/ * from t join t1 on t.i=t1.i where t1.s = 'SYS'; analyze table t compute statistics for table for all indexes for all columns; analyze table t1 compute statistics for table for all indexes for all columns; select * from t join t1 on t.i=t1.i where t1.s = 'SYS'; select * from t join t1 on t.i=t1.i where t1.s = 'MAR'; select/*+INDEX(t ti)*/ * from t join t1 on t.i=t1.i where t1.s = 'MAR'; set autotrace off =========================================== SQL TRACE ============================================= GRANT ALTER SESSION TO MAR; ============================================= alter session set timed_statistics=true; alter session set max_dump_file_size=1000; alter session set tracefile_identifier='My_trace'; ----запуск трассировки alter session set events '10046 trace name context forever, level 12' --включить максимальный уровень alter session set events '10046 trace name context forever, level 0' --отключить alter session set sql_trace=true; execute dbms_session.set_sql_trace(true); execute dbms_system.set_sql_trace_in_session(session_id, serial_id, true); alter session set sql_trace=false; ===================================== --Найти файл дампа select p.spid from v$process p, v$session s where p.addr = s.paddr and s.sid = SessionID; C:\ORACLE\.....\udump>tkprof my_trace_file.trc output = my_file.txt --prf