I have an 24×7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention. We use sequences for the primary key on this particular table and in order to reduce the index contention I want to repartition the primary key index to have 8 hash partitions (currently the index and table are not partitioned). Can I rebuild the index converting it to partitioned in an online fashion, i.e. not stopping DML on the table?
Repartition Primary Key Index online in Oracle
oracleoracle-11g-r2
Related Solutions
The optimal number of columns to compress depends on:
- The number of entries that will fit in each block (this depends on the number of compressed columns as they are only stored once per block)
- The average number of entries with same prefix
These factors can be estimated for the table
The aim is to maximize the size of the compressed prefix whilst minimizing the number of blocks needed to hold all rows with the same prefix.
Assuming that the data is uniform at least to a degree, and ignoring the small amount of overhead compression introduces, you could attempt to implement this approach like this:
helper functions:
create or replace function f_size( p_table_name in varchar,
p_column_name in varchar)
return number as
n number;
begin
execute immediate
'select avg(vsize('||p_column_name||'))+1 from '||p_table_name into n;
return n;
end;
/
create or replace function f_count( p_table_name in varchar,
p_column_names in varchar )
return integer as
n integer;
begin
execute immediate 'select count(*) '||
'from ( select '|| p_column_names ||
' from '||p_table_name||' '||
'group by '||p_column_names||' )'
into n;
return n;
end;
/
test IOT:
create table t ( k1, k2, k3, k4, k5, val,
constraint pk_t primary key(k1, k2, k3, k4, k5))
organization index as
select mod(k,10)||'_____',
mod(k,20)||'_____',
mod(k,30)||'_____',
mod(k,50)||'_____',
k||'_____',
lpad(' ',100)
from (select level as k from dual connect by level<=1000);
query:
with utc as (select table_name, column_name, f_size(table_name, column_name) as column_size from user_tab_columns where table_name='T'),
uic as (select table_name, column_name, column_position, column_size from user_ind_columns join utc using(table_name, column_name) where index_name='PK_T')
select z.*, (8192-prefix_size*prefixes_per_block)/remaining_size as rows_per_block
from( select z.*, greatest(1,8192/(prefix_size+rows_per_prefix*remaining_size)) as prefixes_per_block
from( select z.*, total_count/distinct_count as rows_per_prefix
from( select prefix_length, sum(column_size) as prefix_size, (select sum(column_size) from utc)-sum(column_size) as remaining_size, f_count(table_name, max(prefix_columns)) as distinct_count,
(select count(*) from t) as total_count
from( select table_name, connect_by_root column_position as prefix_length, column_size, substr(sys_connect_by_path(column_name, ','),2) as prefix_columns
from uic
connect by column_position=(prior column_position-1) )
group by table_name, prefix_length ) z ) z ) z
order by 1;
result:
PREFIX_LENGTH PREFIX_SIZE REMAINING_SIZE DISTINCT_COUNT TOTAL_COUNT ROWS_PER_PREFIX PREFIXES_PER_BLOCK ROWS_PER_BLOCK
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1 7 132.854 10 1000 100 1 61.608
2 14.5 125.354 20 1000 50 1.304 65.200
3 22.161 117.693 60 1000 16.666 4.129 68.827
4 29.961 109.893 300 1000 3.333 20.672 68.909
5 38.854 101 1000 1000 1 58.575 58.575
check:
analyze index pk_t validate structure;
select opt_cmpr_pctsave, opt_cmpr_count from index_stats;
OPT_CMPR_PCTSAVE OPT_CMPR_COUNT
---------------------- ----------------------
13 3
The check above roughly corresponds with the prefix length with the maximum rows_per_block
in the calculation - but I suggest you check my working carefully for yourself before trusting it :)
I am assuming the table is so large that you can't just take a copy and try out different prefix lengths. Another approach would be to do just that on a sample of the data - the sample should be chosen as a random selection of prefixes for a given compression candidate (rather than just a random selection of rows)
OBJ#=-1 means either means that the object number is missing or it's not related to an object. The best idea is to go into V$active_session_history and find the SQL_ID then get the SQL text via the SQL_ID and look at the object sin the SQL and what the SQL was doing.
As far as people suggesting INIT TRANS, that is unlikely as there is a separate wait event for INIT TRANS.
USN, SLOT and SEQUENCE map the UNDO entry for the transaction. They could be used to map back to activity in the UNDO but it's probably easier to get the transaction id the XID and use VERSIONS_XID on the objects involved.
see http://www.slideshare.net/khailey/ukoug-oracle-transaction-locks
Example ASH query
col object for A15
col otype for A10
select
substr(event,0,20) lock_name,
ash.session_id waiter,
mod(ash.p1,16) lmode,
--ash.p2 p2,
--ash.p3 p3,
o.object_name object,
o.object_type otype,
CURRENT_FILE# filen,
CURRENT_BLOCK# blockn,
ash.SQL_ID waiting_sql,
BLOCKING_SESSION blocker
--,ash.xid
from
v$active_session_history ash,
all_objects o
where
event like 'enq: TX - index contention'
and o.object_id (+)= ash.CURRENT_OBJ#
/
Related Question
- Oracle – How to Maintain Global Index on Large Table with Regular Partition Purges
- Oracle Ref Partitioning: Deadlock due to child table row migration
- Oracle Index – How to Create a Reverse Index Primary Key
- Oracle Primary Key Index – How to Make Usable from Unusable
- Local indexes vs Global indexes for partitioned tables in Oracle
- SAP Oracle Full Online Backup Failing
Best Answer
After much thought, I think I've managed it. Only drawback is that you'll need double the space of the underlying table to do so...
Don't think there's any other way to do it as you can't redefine PKs without dropping and recreating (correct me if I'm wrong).
Feel free to correct any mistakes I've made :)
Phil