Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the method of indexing and recording Oracle 4.1 billion data scale". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Background

Production system a pipeline table, 4.1 billion data, there is a column previously developed to establish bitmap index, because the table is a pipeline table, there are a large number of inserts, alert log has been reported

Dead lock, deadlock, due to the particularity of bitmap index, even without any constraints, because the column's distinction value is very low, 4.1 billion, only about 170,170 distinct value, resulting in a large number of dead lock, you need to delete bitmap index and change it to global normal index.

The table is partitioned by day.

Indexing statement alter session set workarea_size_policy=MANUAL; alter session set db_file_multiblock_read_count=512;alter session set events' 10351 trace name context forever, level 128 alter alter session set sort_area_size=2147483648;alter session set "_ sort_multiblock_read_count" = 128 alter session enable parallel ddl;alter session enable parallel dml;set timing oncreate index idx_data_02 on data (xx) parallel 8 nologging [local]

It takes about three hours.

Need to pay attention to

The original temp table space is 60g, but due to the opening of 16 parallelism at the beginning, the error report can not be expanded in temp. The temp table space is temporarily increased to 120g, and the index is established smoothly.

Referenc

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

[oracle@rh3 ~] $oerr ora 1035110351, 00000, "size of slots" / / * Cause:// * Action: sets the size of slots to use// * Comment: a slot is a unit of a slot is a unit of O and this factor controls the size// * Comment: of the IO.alter session set events' 10351 trace name context forever, level 128' Level 128-> direct path write max block 128I generated a new run of the big testcase with event 10357, Patch 4417285applied, manual workarea_size_policy, sort_area_size=50000000,db_file_multiblock_read_count=16 and event 10351 with level 128.I tried it with disk_asynch_io=TRUE and FALSE just to be certain this is notsomething related to the async.In the trace files I see something very peculiar. The slots size is 128 asexpected and I see many writes of 128 blocks but not all of them are and theylook like the they come in clusters. A few 128 writes, then a lot smaller ofdifferent sizes but mainly less than 16 blocks and then another cluster ofbig ones and so on.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=4butit is possible that there are other factor out of our control that forcesOracle to stop adding blocks to the slot and write small batches.In conclusion, in order to have the least ammount of direct operations andhave the maximum possible read/write batches these are the parameters to set:alter session set events' 10351 trace name context forever, level 128' This is the end of the introduction of alter session set workarea_size_policy=manual;alter session set sort_area_size=50000000; "what is the method of indexing and recording Oracle 4.1 billion data scale". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.