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: 7
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 Today, 10:02 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,409
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

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: 22nd May 2017 - 06:18 PM