FW: insert into partitioned table
- From: "Yasin Baskan" <yasbs@xxxxxxxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Sun, 3 Apr 2005 22:58:11 +0300
Hi,
I have a question about partitioned tables. I am testing insert
performance on an ordinary table and a partitioned version of the same
table. Both tables are in the same tablespace with local management and
auto space management.
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
BLOCKS EXTENTS
EVENT TABLE
EVENT_DATA 360576 229
EVENT_PAR EVENT2005_1 TABLE PARTITION
EVENT_DATA 212992 209
EVENT_PAR EVENT2005_2 TABLE PARTITION
EVENT_DATA 119296 188
EVENT_PAR EVENT2005_3 TABLE PARTITION
EVENT_DATA 34816 105
I am inserting 10000 rows to both. I could not understand the big
difference between logical reads of these two tables.
Below i included the block usage patterns of two tables and a tkprof
output for the inserts.
EVENT is the original table and EVENT_PAR is the copy of it with three
partitions (EVENT2005_1,EVENT2005_2,EVENT2005_3).
I am inserting all the rows to the third partition.
Both tables are initially loaded with direct insert and then i am
inserting 10000 rows in conventional insert.
What may be the explanation for the big difference in logical reads?
EVENT
Free Blocks.............................
Total Blocks............................360576
Total Bytes.............................2953838592
Total MBytes............................2817
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................39
Last Used Ext BlockId...................355464
Last Used Block.........................8192
Unformatted Blocks .....................0
FS1 Blocks (0-25) .....................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................0
FS4 Blocks (75-100).....................162
Full Blocks .....................359577
EVENT_PAR 2005_1
Free Blocks.............................
Total Blocks............................212992
Total Bytes.............................1744830464
Total MBytes............................1664
Unused Blocks...........................4986
Unused Bytes............................40845312
Last Used Ext FileId....................41
Last Used Ext BlockId...................375944
Last Used Block.........................3206
Unformatted Blocks .....................0
FS1 Blocks (0-25) .....................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................0
FS4 Blocks (75-100).....................0
Full Blocks .....................207314
EVENT_PAR 2005_2
Free Blocks.............................
Total Blocks............................119296
Total Bytes.............................977272832
Total MBytes............................932
Unused Blocks...........................580
Unused Bytes............................4751360
Last Used Ext FileId....................59
Last Used Ext BlockId...................379016
Last Used Block.........................444
Unformatted Blocks .....................0
FS1 Blocks (0-25) .....................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................0
FS4 Blocks (75-100).....................0
Full Blocks .....................118147
EVENT_PAR 2005_3
Free Blocks.............................
Total Blocks............................34816
Total Bytes.............................285212672
Total MBytes............................272
Unused Blocks...........................256
Unused Bytes............................2097152
Last Used Ext FileId....................39
Last Used Ext BlockId...................401544
Last Used Block.........................768
Unformatted Blocks .....................0
FS1 Blocks (0-25) .....................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................0
FS4 Blocks (75-100).....................204
Full Blocks .....................34117
INSERT INTO EVENT (ISPEC, TRANNO, INPUT_DATE,ACTMTH, CA_GL_IND,
INPUTDATE,
ORIG_CN_NO,SUP_IND,VAL_DATE,REL_DAY,DEPT,POST_NO,BAL_UPDATD,
INPUT_REL,
BRANCH, AUD_SEQNO, KW_CAAC, POST_NARR, CLIENT_NO, AVA_DATE, AVA_RDN,
USERCODE, AMOUNT,AVA_UPDATD, BATCH_NO, OLD_DATE)
VALUES
('POST', 0, '03MAR05',403, 'C', 030305, :B2, 'N', 030305, 17593,
'GNMDLK',
:B1, 'Y', 17593, ' ', 1, '00000221 YTL', 'PERFORMANS DENEME', 1,
030305,
17593, '4000', 1000, 'Y' ,0, 410)
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 10000 7.81 8.93 714 2107 135080
10000
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 10001 7.81 8.94 714 2107 135080
10000
INSERT INTO EVENT_PAR (ISPEC, TRANNO, INPUT_DATE,ACTMTH, CA_GL_IND,
INPUTDATE,
ORIG_CN_NO,SUP_IND,VAL_DATE,REL_DAY,DEPT,POST_NO,BAL_UPDATD,
INPUT_REL,
BRANCH, AUD_SEQNO, KW_CAAC, POST_NARR, CLIENT_NO, AVA_DATE, AVA_RDN,
USERCODE, AMOUNT,AVA_UPDATD, BATCH_NO, OLD_DATE)
VALUES
('POST', 0, '03MAR05',403, 'C', 030305, :B2, 'N', 030305, 17593,
'GNMDLK',
:B1, 'Y', 17593, ' ', 1, '00000221 YTL', 'PERFORMANS DENEME', 1,
030305,
17593, '4000', 1000, 'Y' ,0, 410)
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 10000 8.55 9.24 794 12058 156198
10000
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 10001 8.55 9.24 794 12058 156198
10000
--
//www.freelists.org/webpage/oracle-l
Other related posts:
