Performance Reference

Dynamic memory

select substr(COMPONENT, 0, 10) COMP, CURRENT_SIZE CS, USER_SPECIFIED_SIZE US from v$memory_dynamic_components where CURRENT_SIZE!=0;

select substr(COMPONENT, 0, 10), FINAL_SIZE, OPER_TYPE, OPER_MODE, status from v$memory_resize_ops order by START_TIME;

set echo off

Look up table, index, and colum statistics

select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len

from user_tables

where table_name = '&1';

select index_name, last_analyzed ANALYZE_TIME, num_rows,

       leaf_blocks, distinct_keys

from user_indexes

where table_name = '&1'

order by index_name;

select column_name, last_analyzed ANALYZE_TIME, num_distinct,

       num_nulls, density

from user_tab_columns

where table_name = '&1'

order by column_name;

set echo on

Explain Plan

--

explain plan for select * from mytab where my_sid_id > 0;

select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

Select * from table(dbms_xplan.display());

Show pending statistics

select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len

from user_tab_pending_stats

where table_name = '&1' and partition_name is null;

select index_name, last_analyzed "analyze time", num_rows,

       leaf_blocks, distinct_keys

from user_ind_pending_stats

where table_name = '&1' and partition_name is null

order by index_name;

select column_name, last_analyzed "analyze time", num_distinct,

       num_nulls, density

from user_col_pending_stats

where table_name = '&1' and partition_name is null

order by column_name;

col table_name format a10

col extension format a12

Stat extensions:

select * from user_stat_extensions;

-- Create extention on the 2 columns

select dbms_stats.create_extended_stats(null,’mytab’,’(col1,col2)’) from dual;

select * from user_stat_extensions;

-- Collect stats

begin

  dbms_stats.gather_table_stats(null, 'mytab',

    method_opt => 'for all columns size 1 for columns (col1,col2) size 254');

end;

/