| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> insert into partitioned table
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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 01 2005 - 03:18:17 CST
 |
 |