Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Inserts become slow as partition of table grows
Alan Smithsons
post May 17 2017, 08:03 AM
Post #1


Newbie
*

Group: Members
Posts: 9
Joined: 10-July 14
Member No.: 49,769



Hi!
My DB on Oracle 10g 64bit. I have one partition table. Inserting rows in empty partition is fast (10000 rows per minute), but when table has over 10 million rows insert rate has slowed (1000 rows per minute).
I never deleted rows from this table after create it, for inserting i use construction INSERT /*+APPEND*/ INTO MYTABLE SELECT....

What can cause a slowdown in insert speed as a partition table grows?

CODE
CREATE TABLE MYTable
(
dpk DATE,
roi VARCHAR2(8),
daot DATE,
pul VARCHAR2(6),
mes VARCHAR2(32),
lic VARCHAR2(20),
rolr VARCHAR2(20),
otr VARCHAR2(20),
ser VARCHAR2(20),
ots VARCHAR2(2),
avk VARCHAR2(2),
po1 VARCHAR2(3),
po2 VARCHAR2(3),
sig VARCHAR2(10),
crc NUMBER(12,0),
vot VARCHAR2(5)
)
PCTFREE 0
INITRANS 1
MAXTRANS 255
TABLESPACE parts
STORAGE (
INITIAL 524288000
NEXT 524288000
PCTINCREASE 0
MINEXTENTS 1
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (DPK)
(
PARTITION parts27 VALUES LESS THAN (TO_DATE(' 2017-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 0
INITRANS 1
MAXTRANS 255,
PARTITION parts271 VALUES LESS THAN (TO_DATE(' 2017-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 0
INITRANS 1
MAXTRANS 255,
PARTITION parts30 VALUES LESS THAN (MAXVALUE)
PCTFREE 0
INITRANS 1
MAXTRANS 255
)
/

-- Indexes for MYTable

CREATE INDEX na_crc ON MYTable
(
crc ASC
)
PCTFREE 0
INITRANS 2
MAXTRANS 255
TABLESPACE idparts
STORAGE (
INITIAL 104857600
NEXT 104857600
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
LOCAL (
PARTITION parts27
PCTFREE 0
INITRANS 2
MAXTRANS 255,
PARTITION parts271
PCTFREE 0
INITRANS 2
MAXTRANS 255,
PARTITION parts30
PCTFREE 0
INITRANS 2
MAXTRANS 255
)
/

CREATE INDEX na_lic ON MYTable
(
sig ASC,
lic ASC
)
PCTFREE 0
INITRANS 2
MAXTRANS 255
TABLESPACE idparts
STORAGE (
INITIAL 104857600
NEXT 104857600
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
PARTITION parts27
PCTFREE 0
INITRANS 2
MAXTRANS 255,
PARTITION parts271
PCTFREE 0
INITRANS 2
MAXTRANS 255,
PARTITION parts30
PCTFREE 0
INITRANS 2
MAXTRANS 255
)
/

CREATE INDEX na_roi ON MYTable
(
daot ASC,
roi ASC,
avk ASC
)
PCTFREE 0
INITRANS 2
MAXTRANS 255
TABLESPACE idparts
STORAGE (
INITIAL 104857600
NEXT 104857600
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
LOCAL (
PARTITION parts27
PCTFREE 0
INITRANS 2
MAXTRANS 255,
PARTITION parts271
PCTFREE 0
INITRANS 2
MAXTRANS 255,
PARTITION parts30
PCTFREE 0
INITRANS 2
MAXTRANS 255
)
/
Go to the top of the page
 
+Quote Post
burleson
post May 22 2017, 10:02 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,495
Joined: 26-January 04
Member No.: 13



Hi Alan,

Sorry about the delay in responding!

>> for inserting i use construction INSERT /*+APPEND*/ INTO MYTABLE SELECT....

The append hit (by definition) grabs fresh block from the end of the table:

By using the APPEND hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don't need to specify an APPEND hint.

This may help:

http://dbaforums.org/oracle/index.php?showtopic=17072

***********************************************************************

>> What can cause a slowdown in insert speed as a partition table grows?

Check for row chaining:

http://www.dba-oracle.com/t_identify_chained_rows.htm

Good Luck!






--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Alan Smithsons
post May 31 2017, 03:12 AM
Post #3


Newbie
*

Group: Members
Posts: 9
Joined: 10-July 14
Member No.: 49,769



Thanks for answer, but i don't use parallel in dml yet. rolleyes.gif
I got trace file for inserting 25000 rows in my partition table.

CODE
INSERT /*+ APPEND */ INTO MAINTABLE SELECT DISTINCT * FROM TEMP0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      7.87    2568.66      51994       5259     223887       18124
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      7.87    2568.66      51994       5259     223887       18124

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     25330        5.30       1012.27
  log file switch completion                      3        0.61          0.89
  log buffer space                                3        0.12          0.33
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00


All waitings link with file_id - 118,119,120. This is files contein local index blocks (dba_extents tables).
I have not been deleted or modified data from my table.
Why this waitings was appeared?
How i can resolve this problem?
Go to the top of the page
 
+Quote Post
burleson
post May 31 2017, 02:36 PM
Post #4


Advanced Member
***

Group: Members
Posts: 13,495
Joined: 26-January 04
Member No.: 13



Hi Alan,

>> All waitings link with file_id - 118,119,120. This is files contein local index blocks (dba_extents tables).

Excellent clue!

This sounds like segment header contention:

http://dba-oracle.com/googlesearchsite_pro...5191j1256389j25

Ensure that the index is defined with "segment space management auto" or multiple freelists:

http://dba-oracle.com/googlesearchsite_pro...5010j1617568j21

Good Luck!




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Alan Smithsons
post May 31 2017, 07:09 PM
Post #5


Newbie
*

Group: Members
Posts: 9
Joined: 10-July 14
Member No.: 49,769



Hi again!!!
I saw v$system_event

Attached File  44.png ( 21.43K ) Number of downloads: 3


and v$waitstat

CLASS COUNT TIME
data block 3619851 4090595
undo header 7427 93512
undo block 3992 6060
file header block 618 32266
segment header 9 10
1st level bmb 3 3
2nd level bmb 1 3

Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd August 2017 - 01:00 AM