本篇内容介绍了“Oracle 41亿数据量表建立索引记录的方法是什么”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

背景

生产系统一个流水表,41亿数据,有一列原先开发建立了bitmap index,由于该表为流水表,有大量插入,alert日志中一直报

dead lock,死锁,由于位图索引特殊性,即使在没有任何约束情况下,由于该列的distinct值非常低,41亿,只有170左右的distinct value,所以造成大量的dead lock,需要删除bitmap index,改为global normal index。

该表为按天分区。

建索引语句

altersessionsetworkarea_size_policy=MANUAL;altersessionsetdb_file_multiblock_read_count=512;altersessionsetevents‘10351tracenamecontextforever,level128’;altersessionsetsort_area_size=2147483648;altersessionset“_sort_multiblock_read_count”=128;altersessionenableparallelddl;altersessionenableparalleldml;settimingoncreateindexidx_data_02ondata(xx)parallel8nologging[local];

大约耗时3个小时左右。

需要注意

temp表空间原先为60g,由于一开始开16个并行,所以导致报错无法在temp扩展,临时加大temp表空间到120g,顺利建立索引。

参考

https://www.askmaclean.com/archives/event-10357-and-10351.html

[oracle@rh3~]$oerrora1035110351,00000,"sizeofslots"//*Cause://*Action:setsthesizeofslotstouse//*Comment:aslotisaunitofI/Oandthisfactorcontrolsthesize//*Comment:oftheIO.altersessionsetevents'10351tracenamecontextforever,level128';level128->directpathwritemaxblock128Igeneratedanewrunofthebigtestcasewithevent10357,Patch4417285applied,manualworkarea_size_policy,sort_area_size=50000000,db_file_multiblock_read_count=16andevent10351withlevel128.Itrieditwithdisk_asynch_io=TRUEandFALSEjusttobecertainthisisnotsomethingrelatedtotheasync.InthetracefilesIseesomethingverypeculiar.Theslotssizeis128asexpectedandIseemanywritesof128blocksbutnotallofthemareandtheylooklikethetheycomeinclusters.Afew128writes,thenalotsmallerofdifferentsizesbutmainlylessthan16blocksandthenanotherclusterofbigonesandsoon.kcblcow:dba=100c91b,sz=128,blks=117,st=3,idx=14kcblcow:dba=100c91b,sz=128,blks=117,st=3,idx=14kcblcow:dba=100c991,sz=128,blks=1,st=3,idx=15kcblcow:dba=100c91b,sz=128,blks=117,st=3,idx=14kcblcow:dba=100c991,sz=128,blks=1,st=3,idx=15kcblcow:dba=100c990,sz=128,blks=1,st=3,idx=0kcblcow:dba=100c992,sz=128,blks=128,st=3,idx=1kcblcow:dba=100c992,sz=128,blks=128,st=3,idx=1kcblcow:dba=100ca12,sz=128,blks=39,st=3,idx=2kcblcow:dba=100c992,sz=128,blks=128,st=3,idx=1kcblcow:dba=100ca12,sz=128,blks=39,st=3,idx=2kcblcow:dba=100ca3a,sz=128,blks=1,st=3,idx=3kcblcow:dba=100ca12,sz=128,blks=39,st=3,idx=2kcblcow:dba=100ca3a,sz=128,blks=1,st=3,idx=3kcblcow:dba=100ca39,sz=128,blks=1,st=3,idx=4butitispossiblethatthereareotherfactoroutofourcontrolthatforcesOracletostopaddingblockstotheslotandwritesmallbatches.Inconclusion,inordertohavetheleastammountofdirectoperationsandhavethemaximumpossibleread/writebatchesthesearetheparameterstoset:altersessionsetevents'10351tracenamecontextforever,level128';altersessionsetworkarea_size_policy=manual;altersessionsetsort_area_size=50000000;

“Oracle 41亿数据量表建立索引记录的方法是什么”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!