当前位置: 代码迷 >> 综合 >> 关于生产库上索引碎片收集的问题 整理 from askmaclean
  详细解决方案

关于生产库上索引碎片收集的问题 整理 from askmaclean

热度:80   发布时间:2023-12-20 09:53:39.0

问题描述:

环境:oracle10.2.0.5 RAC+HP-UX
用analyze index <index_name> validate structure这个语句可以验证索引的碎片程度,然后通过index_stats视图来查询,请问这个语句在生产库上对于比较大的索引使用这个语句,对系统的影响有多大?

FYI:

analyze index <index_name> validate structure


  1. SQL> create table ttab(t1 int);

  2. Table created.

  3. SQL> create index ind_tab on ttab(t1);

  4. Index created.

  5. SQL> oradebug setmypid
  6. Statement processed.
  7. SQL> oradebug event 10704 trace name context forever,level 10;
  8. Statement processed.


  9. SQL> analyze index ind_tab validate structure;

  10. Index analyzed.

  11. SQL> oradebug tracefile_name
  12. /s01/admin/G10R25/udump/g10r25_ora_25784.trc


  13. [root@vrh8 ~]# grep "ksqgtl \*\*\*" /s01/admin/G10R25/udump/g10r25_ora_25784.trc
  14. ksqgtl *** TX-00030007-000071e6 mode=6 flags=0x401 timeout=0 ***
  15. ksqgtl *** TM-00010183-00000000 mode=4 flags=0x401 timeout=0 ***


复制代码

analyze index <index_name> validate structure 要在table上加  TM mode=4的share lock,会block dml

 

 

second:


  1. Connected to:
  2. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
  3. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  4. SQL>  oradebug setmypid
  5. Statement processed.
  6. SQL>  oradebug event 10704 trace name context forever,level 10;
  7. Statement processed.
  8. SQL> analyze index ind_tab validate structure online;

  9. Index analyzed.

  10. SQL> oradebug tracefile_name
  11. /s01/admin/G10R25/udump/g10r25_ora_25830.trc

  12. [root@vrh8 ~]# grep "ksqgtl \*\*\*"  /s01/admin/G10R25/udump/g10r25_ora_25830.trc
  13. ksqgtl *** TX-0009002d-000072d7 mode=6 flags=0x401 timeout=0 ***


  14. SQL> select * from index_stats;

  15. no rows selected
复制代码

analyze index ind_tab validate structure online ==> 不要求 表上的 share lock,但是不填充index_stats 视图, 所以  validate structure online  只能用来验证索引上是否存在讹误