在索引激增2的结尾，我给你留下了三个问题：
如何在10g(及以上）的索引上设置maxtrans？当你实际面对这个问题时，有什么策略可以将潜在的损害降到最低？为什么我没有频繁地注意到空间浪费现象，尽管我看到了已经发生了问题的索引。
在这个贴子中，我将讨论maxtrans问题。如果您试图在10g中设置索引的maxtrans，它根本不起作用，下面是一个运行在10.2.0.3上的SQL*Plus的快速剪切粘贴，以演示这一点：
SQL> create index ord_placed on orders(date_placed)
2 tablespace test_8k
3 initrans 9
4 maxtrans 9
5 /
Index created.
SQL> select ini_trans, max_trans
2 from user_indexes
3 where index_name = 'ORD_PLACED'
4 /
INI_TRANS MAX_TRANS
---------- ----------
9 255
当然，如果您仍在运行9i（或8i）上，您将看到max_trans是显示您设置的值，但10g会忽略您设置maxtrans的尝试，并将其保留为255。如果您真的需要在10g中设置maxtrans，并且获得了Oracle支持部门的批准，您可以执行以下操作（同样，从SQL*Plus中剪切n-paste）：
SQL> define m_maxt = 9
SQL>
SQL> column object_id new_value m_objn
SQL> column data_object_id new_value m_objd
SQL>
SQL> select
2 object_id,
3 data_object_id
4 from
5 user_objects
6 where
7 object_name = 'ORD_PLACED'
8 and object_type = 'INDEX'
9 ;
OBJECT_ID DATA_OBJECT_ID
---------- --------------
100466 100466
1 row selected.
SQL>
SQL> execute sys.fix_index_maxtrans( &m_objn , &m_objd, &m_maxt )
PL/SQL procedure successfully completed.
SQL>
SQL> select ini_trans, max_trans
2 from user_indexes
3 where index_name = 'ORD_PLACED'
4 /
INI_TRANS MAX_TRANS
---------- ----------
9 9
1 row selected.
诀窍（需要与Oracle支持人员讨论）在sys.fix_index_maxtrans（）过程中，在sys中创建了该过程，如下所示：
create or replace procedure fix_index_maxtrans (
i_objn in number,
i_objd in number,
i_maxt in number
)
as
begin
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush shared_pool';
update
ind$
set
maxtrans = i_maxt
where
obj# = i_objn
and dataobj# = i_objd
;
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush shared_pool';
end;
/
这个过程有一个奇怪的地方——你会注意到我在更新数据字典之前和之后已经刷新了共享池两次。第一次的刷新，是因为Oracle有时会从字典缓存中更新数据字典；然后我重复这个刷新操作，因为有一些证据表明，在某些Oracle版本中，库缓存中的某些项在第一次刷新时没有正确刷新。刷新的全部次数可能是不必要的，但是非正常地修改maxtrans并不是你必须经常做的事情，所以你可能已经准备好接受这种破坏。
一旦设置了maxtrans，它看起来甚至在索引重建过程中都会持续存在（譬如，如果您觉得需要对索引使用rebuild命令），但是如果您想利用这种方法，我建议你自己要测试所有边界条件和特殊情况（例如分区索引等）。
我将在后面的文章中讨论另外两个问题，但是，作为对解决这个问题的其他选择的简要介绍，这里列出了四个（或者可能是五个，取决于您的观点）可能适合你的：
-
必要时使用coalesce-可能会经常定期做
-
将索引创建为反向键索引
-
将索引创建为全局哈希分区索引
-
去掉（或改变）索引
每个选项都有其优点、缺点、优势和局限性，这将是下一篇的讨论主题。
原文链接和内容如下：
https://jonathanlewis.wordpress.com/2009/08/26/index-explosion-3/
Index Explosion 3
Filed under: Index Explosion,Indexing,Infrastructure,Troubleshooting — Jonathan Lewis @ 6:53 pm BST Aug 26,2009
At the end of part 2 of Index Explosion I left you with three questions:
how can you set maxtrans on an index in 10g (and above)
what strategies exist for minimising the potential for damage if you actually have to face this problem
why haven’t I noticed the space-wasting phenomenon more often despite seeing indexes where it could have been happening
In this post I’ll address the maxtrans issue. If you try to set maxtrans on an index in 10g it simply doesn’t work, and here’s a quick cut-n-paste from SQL*Plus running 10.2.0.3 to demonstrate the point:
SQL> create index ord_placed on orders(date_placed)
2 tablespace test_8k
3 initrans 9
4 maxtrans 9
5 /
Index created.
SQL> select ini_trans, max_trans
2 from user_indexes
3 where index_name = 'ORD_PLACED'
4 /
INI_TRANS MAX_TRANS
---------- ----------
9 255
Of course if you’re still running 9i (or 8i), you’ll see max_trans showing the value you set, but 10g silently ignores your attempt to set maxtrans and leaves it at 255. If you really need to set maxtrans in 10g, and if you get approval from Oracle support, you can do something like the following (again, a cut-n-paste from SQL*Plus):
SQL> define m_maxt = 9
SQL>
SQL> column object_id new_value m_objn
SQL> column data_object_id new_value m_objd
SQL>
SQL> select
2 object_id,
3 data_object_id
4 from
5 user_objects
6 where
7 object_name = 'ORD_PLACED'
8 and object_type = 'INDEX'
9 ;
OBJECT_ID DATA_OBJECT_ID
---------- --------------
100466 100466
1 row selected.
SQL>
SQL> execute sys.fix_index_maxtrans( &m_objn , &m_objd, &m_maxt )
PL/SQL procedure successfully completed.
SQL>
SQL> select ini_trans, max_trans
2 from user_indexes
3 where index_name = 'ORD_PLACED'
4 /
INI_TRANS MAX_TRANS
---------- ----------
9 9
1 row selected.
The trick (and the requirement to talk to Oracle support) is in the procedure sys.fix_index_maxtrans(), which I created in the sys schema as follows:
create or replace procedure fix_index_maxtrans (
i_objn in number,
i_objd in number,
i_maxt in number
)
as
begin
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush shared_pool';
update
ind$
set
maxtrans = i_maxt
where
obj# = i_objn
and dataobj# = i_objd
;
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush shared_pool';
end;
/
There is an oddity in this procedure – you’ll notice I’ve flushed the shared_pool twice before and after updating the data dictionary. The flushes are there in the first case because Oracle sometimes updates the data dictionary from the dictionary cache; I then repeat them because there is some evidence that some items in the library cache don’t get flushed properly the first time around in some releases of Oracle. The total number of flushes may be unnecessary – but this hack for maxtrans isn’t something that you have to do often – so you may be prepared to live with the disruption.
Once the maxtrans is set it seems to persist even across an index rebuild (if you ever feel the need to use the rebuild command on an index, that is) – but if you want to take advantage of this approach, I’ll leave you to test all the boundary conditions and special cases (e.g. partitioned indexes, etc.) for yourselves.
I’ll address the other two questions in later posts but, as a brief introdution to other options for addressing the issue, here’s a list of four (or possibly five, depending on your point of view) which may be appropriate.
- Use a coalesce when necessary – possibly on a very regular basis
- Create the index as a reverse-key index
- Create the index as global hash partitioned index
- Get rid of (or change) the index
Each option has its good points, bad points, advantages and limitations – which will be the topic of discussion in the next installment.
