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

The main content of this article is "how to solve the problem of database insert", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the problem of database insert.

In the morning, several large tables with more than 10 gigabytes and hundreds of millions of meters were partitioned and everything was normal. There was a problem inserting a small table with 100000 data before lunch. I simply inserted the statement and did not finish running for an hour.

The insert statement is as follows. Due to confidentiality, the relevant information has been modified:

Insert into SGS.PROD_NEW select * from SGS.PROD where CREATE_TIME > = to_date ('2019-01-01-01,' yyyy-mm-dd') and CREATE_TIME

< to_date('2020-07-01', 'yyyy-mm-dd'); 这张表大概10万行数据，没有主键，没有索引。 set linesize 200;col owner for a20;col table_name for a40;col tablespace_name for a20;select owner, table_name, num_rows,TABLESPACE_NAME from dba_tables where table_name = 'PROD'; OWNER TABLE_NAME NUM_ROWS TABLESPACE_NAME-------------------- ---------------------------------------- ---------- --------------------SGS PROD 107220 GC_TS 表的大小60M左右: SQL>

Select bytes/1024/1024 MB from dba_segments where segment_name='PROD'; MB- 59

When the first table was inserted, I didn't finish running for an hour. I took a look at the waiting event, it was always direct path read, and there was no blocking. Why is it so slow? take a look at the full table scan in which the execution plan is to go. There is no problem with the execution plan:

-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time |-| -| 0 | INSERT STATEMENT | | 2027 | 1 | LOAD TABLE CONVENTIONAL | PRODNEW | | * 2 | TABLE ACCESS FULL | PROD | 106k | 67m | 2027 (1) | 00:00:01 | |-

Just stop and run in parallel. Opened 16 parallel runs to insert, ran for half an hour or got stuck there, the same waiting event. Puzzled, thought of whether the statistical information is out of date, but this table does not have an index, statistical information is not out of date is a full table scan. I thought that the buffer cache is too small, but I can plug in hundreds of millions of data in front of me, which is theoretically impossible. Thinking of whether the temp table space is too small, check, 32G, the utilization rate of 0.01%.

After fishing in troubled waters for an hour, I wondered if it could be caused by a large field. Check it out, and sure enough, MMP, there is a CLOB type field 100G in size.

SQL > select bytes/1024/1024/1024 GB from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='PROD'); GB-.000061035104.680664

The final solution:

Open 16 parallel, split the time period into one month and one month. The last thing to note is that the tablespace should be large enough.

At this point, I believe you have a deeper understanding of "how to solve the problem of database insert". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.