slow inserts

I have a heavy insert database - 3rd party application which checks for existance of a record, and if they are not there, it inserts them in 10 major tables. I'm running 36 instances of this application and have about 25,000 inserts per table/per minute. Unfortunately, this throuput is not enough for the business and I'm trying to optimize it. What wonders me is that if I add more application instances the insert rate doesn't improve. Bellow is a part of my statpack.
Please help me analyze what is the problem and what can I do to improve the performance.
Thanks a lot,mj

Code:

Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time ------------------------- ---------- -------- --------- db file sequential read 10,147,781 96,505 88.00 CPU time 7,286 6.64 db file parallel write 102,185 2,391 2.18 log file sync 165,362 1,649 1.50 log file parallel write 297,068 1,251 1.14

are you using ASSM?

are you seeing ITL waits on v$segment_statistics?

Increase FREELISTS on tables if you are not using ASSM.

Tamil

My system is too busy and ASSM doesn't handle it right. I have 45% better performance with manual freelists.
My table/index structure is PCTFREE 60 INITRANS 90 MAXTRANS 255 STORAGE (INITIAL 160M NEXT 160M FREELIST GROUPS 7 FREELISTS 20 )
for 5 heavy inserted tables.
How much more I could bumped it up?
Thanks a lot, mj

Can you trace one of the sessions that is performing this check\insert, so we can see what is going on? Also, please use the "[ code ]" tags when posting output (like the above statspack waits).

I added the CODE tags.

Quote:

Originally Posted by mjschwenger

My system is too busy and ASSM doesn't handle it right. I have 45% better performance with manual freelists.
My table/index structure is PCTFREE 60 INITRANS 90 MAXTRANS 255 STORAGE (INITIAL 160M NEXT 160M FREELIST GROUPS 7 FREELISTS 20 )
for 5 heavy inserted tables.
How much more I could bumped it up?
Thanks a lot, mj

Ridiculous settings.
Tamil

Quote:

Originally Posted by tamilselvan

Ridiculous settings.
Tamil

Well, I'm not in love with them either but this brings me better insert rate. Even with so many freelist groups I still have free lists contention and buffer busy waits. I would appreciate if you suggest something that makes more sense. Assusme good hardware, all big tables with 160M extents in a separate tblspace, 4 huge array 10 row devices, and indexes separate as well.
Let me put some more details for the app - this is 3rd party products - I cannot touch the code or make any changes rather then database. It all into the logic - it starts with the select from 1 table - then if the data is there - performs some logic - select all info, compare and reinserts the new data. If the data is new - straight insert into the same tables where the select are.When there's 1 instance of this app - it's not too bad. But when there are multiple session - like 50-100 then the performance is terrible. The problem is that I cannot change anything, but I need to obtain a better performance. My stat pack doesn't look so bad but I have no throughput - the insert rate is low. There are about 25,000 inserts a minute in a single table in the beginning of the app run - later on, they get down to 10,000.
Please, suggest

What is your block size?

What are the BIG reasons for setting PCTFREE 60 and INITRANS 90 ?

Remember each INITRANS takes 24 bytes in a block.

Assume your block size = 8192.

The PCTFREE 60 will simply take away 4843 bytes ((8192 -120)*0.60). Note approximately 120 bytes is needed for block header info.

Then INITRANS 90 will consume 2160 bytes (90*24).

Available space for new insert = 8192 - 4843 - 2160 = 1189.

That's why your INSERT is running slow. Most of the space within a block will never be used.

What you must consider:
1 Set FREELIST GROUPS to 1. Higher value is needed only if you use RAC.

2. Reduce FREELISTS to 17 (or 13) if you are going to run more than 34 concurrent sessions that insert rows.

3. Set PCTFREE to 10. If appln updates the rows that leads to row expansion in future, then you may try with 15. If you donot know right now, then set it to 10.

4. Set INITRANS to 3 (I would consider 1, if only one sesion is going to update/delete in future). The ITL will be used only when delete/update takes place on the table/index. Right now, your main concern is "INSERT". More over, when there is a shortage in ITL, Oracle will use the space reserved by PCTFREE. Hence, very very high value for INITRANS is not needed.

5. Experiment TABLE PARTITION with GLOBAL INDEXES as a last option.

Tamil

Thanks a lot, Tamil. Here's where I'm at.
The insert rate is what the business measures and they want to see at about 60,000 inserts per min for Oracle (the second system is running on DB2 and already does that with almost no tuning). So they consider Oracle a bad system because of it's bad performance for this app. I'm trying to tune it up so I could get better insert rate - whatever is required.
The reason for PCT free=60 is that I need to keep less data in the block. I can see how 5-6 (sometimes 12-13 sessions) are going after the same data on selects/updates - that causes my big contention on db sequential reads. I use 4K page size for the same reason - less data in the block. The data is selected very randomly - the possibility of not finding it in the cache is pretty big... especially where there are 100,000 mln of records in the 10 base tables.
I started with ASSM - the insert rate I'm getting is the same like now. Then I tried using manual + freelists like 48 per table - and my troughput went a little bit up but had waits. Adding of 1 freelist group did not help a lot but ncreasing them to 5-7 did. I know this will cause me space though.
My wait events are buffer busy waits and enq: TX - index contention. Oracle advices to increase initran on this indexes/tables to eliminate these waits.
After I went to 7freelist groups/20 freelist my undo table space start getting slammed - I guess there's more activity now. Here are my waits by class:

PHP Code:

data block 921559 47037 segment header 311 18 free list 4440 1734 1st level bmb 14 2 2nd level bmb 2 0 undo header 107972 9673 undo block 237835 3804

The data blocks waits reported include the freelist waits as well.
I still use auto undo handling - may be I have to go to manual one if the auto doesn't handle it.
I must consider between 72 to 96 concurent sessions - app instances running together. The problems with Oracle is that I get the same insert rate no matter how many processes I run. Let's say I run 25 processes - I get 10,000 inserts a min. You would expect if I run 50 processes to get may be no 20,000 (because of the contention) but at least 15-18,000. No, this is not the case... I get the same 10,000 inserts no matter what tuning I do (ASSM, ASM, freelist or no, freelist groups, etc.)
I'm starting new testing today - I'll try single freelist group and different % of Pctfree = and I'll post the results.
I cannot use partitioning for the moment - still waiting for approval per license agreement.
Thanks a lot for the help - really appreciated.
mj

You cannot compare DB2 performance with Oracle.

Also, an application developed against one particular RDBMS should NOT be deployed against with another RDBMS.

If the application commits after every row insert, then definitely Oracle is going to run slowly.

Finally, if you use 9i, You can increase _bump_highwater_mark_count to higher value in your test system. I think it defaults to 5. This parameter tells Oracle how many blocks should be allocated by advancing HW per freelist.
Also, consult with Oracle support before you change in PROD env.

Tamil

Changing pctfree = 15 brought me a little bit lower insert rate - 4%, but may be it's not related only to pctfree - just such stage after 10mln of records inserted.
Tested also initran = although I do not have too many updates, the number of enq: TX - index contention increases if I drop them to 40.
Calculating hwm and changing it up will allocate a lot of space per segment - I have 140 freelists x 50 for hwm = 7000 blocks...
Let me try and I'll post my results.
Please, advise what other changes I could test.
Thanks a lot,mj

Quote:

Originally Posted by mjschwenger

Changing pctfree = 15 brought me a little bit lower insert rate - 4%, but may be it's not related only to pctfree - just such stage after 10mln of records inserted.
Tested also initran = although I do not have too many updates, the number of enq: TX - index contention increases if I drop them to 40.
Calculating hwm and changing it up will allocate a lot of space per segment - I have 140 freelists x 50 for hwm = 7000 blocks...
Let me try and I'll post my results.
Please, advise what other changes I could test.
Thanks a lot,mj

Post the full statspack report taken 5 min interval here.

Tamil

1st part

Thanks a lot for your help - here is the snap - about 10 min long, mj
[PHP]
STATSPACK report for

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
ORAPERF 1029455018 oraperf 1 10.1.0.4.0 NO aixperfdb

Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 720 11-Jan-06 15:40:54 65 679.0
End Snap: 721 11-Jan-06 15:49:54 65 683.1
Elapsed: 9.00 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 5,312M Std Block Size: 4K
Shared Pool Size: 960M Log Buffer: 195,313K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,562,471.84 36,845.92
Logical reads: 28,916.59 681.91
Block changes: 10,014.51 236.16
Physical reads: 2,762.25 65.14
Physical writes: 1,298.34 30.62
User calls: 7,950.90 187.50
Parses: 702.14 16.56
Hard parses: 0.01 0.00
Sorts: 3.25 0.08
Logons: 0.03 0.00
Executes: 4,119.29 97.14
Transactions: 42.41

% Blocks changed per Read: 34.63 Recursive Call %: 9.94
Rollback per transaction %: 0.00 Rows per Sort: 22.07

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.95 Redo NoWait %: 100.00
Buffer Hit %: 90.45 In-memory Sort %: 100.00
Library Hit %: 100.01 Soft Parse %: 100.00
Execute to Parse %: 82.95 Latch Hit %: 99.81
Parse CPU to Parse Elapsd %: 92.63 % Non-Parse CPU: 96.82

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 40.08 40.10
% SQL with executions>1: 53.63 53.71
% Memory for SQL w/exec>1: 51.69 51.93

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
db file sequential read 1,491,521 15,412 86.02
CPU time 1,079 6.02
db file parallel write 47,953 946 5.28
log file sync 23,929 231 1.29
log file parallel write 43,225 177 .99
-------------------------------------------------------------
Wait Events DB/Inst: ORAPERF/oraperf Snaps: 720-721
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 1,491,521 0 15,412 10 65.1
db file parallel write 47,953 0 946 20 2.1
log file sync 23,929 0 231 10 1.0
log file parallel write 43,225 0 177 4 1.9
SQL*Net break/reset to clien 97,200 0 25 0 4.2
control file sequential read 2,323 0 10 4 0.1
class slave wait 2 2 10 4883 0.0
Queue Monitor Task Wait 2 0 10 4883 0.0
control file parallel write 230 0 6 24 0.0
enq: TX - index contention 1,296 0 5 4 0.1
latch: cache buffers chains 1,032 1,028 2 2 0.0
buffer busy waits 7,045 0 1 0 0.3
enq: TX - row lock contentio 1 0 1 1282 0.0
latch free 204 183 1 3 0.0
process startup 8 0 0 53 0.0
latch: library cache 224 0 0 2 0.0
read by other session 52 0 0 6 0.0
control file single write 17 0 0 9 0.0
db file scattered read 22 0 0 4 0.0
rdbms ipc reply 121 0 0 1 0.0
LGWR wait for redo copy 947 0 0 0 0.0
latch: shared pool 35 0 0 1 0.0
latch: library cache pin 27 0 0 1 0.0
SQL*Net more data to client 198 0 0 0 0.0
latch: row cache objects 7 0 0 1 0.0
enq: SQ - contention 4 0 0 1 0.0
latch: redo allocation 5 0 0 1 0.0
enq: HW - contention 4 0 0 1 0.0
latch: In memory undo latch 3 0 0 1 0.0
latch: cache buffers lru cha 3 0 0 0 0.0
buffer deadlock 165 165 0 0 0.0
latch: enqueue hash chains 1 0 0 0 0.0
SQL*Net message from client 2,188,940 0 4,012 2 95.6
queue messages 109 109 532 4883 0.0
virtual circuit status 18 18 527 29297 0.0
Queue Monitor Wait 18 18 527 29297 0.0
wakeup time manager 2 0 527 ###### 0.0
jobq slave wait 173 170 500 2891 0.0
SQL*Net message to client 2,188,933 0 4 0 95.6
SQL*Net more data from clien 5 0 0 17 0.0
-------------------------------------------------------------
Background Wait Events DB/Inst: ORAPERF/oraperf Snaps: 720-721
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 47,954 0 946 20 2.1
log file parallel write 43,225 0 177 4 1.9
Queue Monitor Task Wait 2 0 10 4883 0.0
control file parallel write 234 0 6 24 0.0
control file sequential read 90 0 1 7 0.0
process startup 8 0 0 53 0.0
db file sequential read 98 0 0 2 0.0
control file single write 18 0 0 9 0.0
db file scattered read 17 0 0 5 0.0
rdbms ipc reply 121 0 0 1 0.0
LGWR wait for redo copy 949 0 0 0 0.0
latch: cache buffers chains 4 4 0 5 0.0
latch: redo allocation 4 0 0 0 0.0
latch: cache buffers lru cha 4 0 0 0 0.0
buffer busy waits 2 0 0 0 0.0
rdbms ipc message 23,385 2,612 6,292 269 1.0
smon timer 54 0 635 11762 0.0
Queue Monitor Wait 18 18 527 29297 0.0
-------------------------------------------------------------

PHP]

part2

PHP Code:

wakeup time manager 2 0 527 ###### 0.0 jobq slave wait 173 170 500 2891 0.0 SQL*Net message to client 2,188,933 0 4 0 95.6 SQL*Net more data from clien 5 0 0 17 0.0 ------------------------------------------------------------- Background Wait Events DB/Inst: ORAPERF/oraperf Snaps: 720-721 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- db file parallel write 47,954 0 946 20 2.1 log file parallel write 43,225 0 177 4 1.9 Queue Monitor Task Wait 2 0 10 4883 0.0 control file parallel write 234 0 6 24 0.0 control file sequential read 90 0 1 7 0.0 process startup 8 0 0 53 0.0 db file sequential read 98 0 0 2 0.0 control file single write 18 0 0 9 0.0 db file scattered read 17 0 0 5 0.0 rdbms ipc reply 121 0 0 1 0.0 LGWR wait for redo copy 949 0 0 0 0.0 latch: cache buffers chains 4 4 0 5 0.0 latch: redo allocation 4 0 0 0 0.0 latch: cache buffers lru cha 4 0 0 0 0.0 buffer busy waits 2 0 0 0 0.0 rdbms ipc message 23,385 2,612 6,292 269 1.0 smon timer 54 0 635 11762 0.0 Queue Monitor Wait 18 18 527 29297 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: ORAPERF/oraperf Snaps: 720-721 -> ordered by event (idle events last) Event -------------------------------------------------- 0 - 1 ms 1 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ LGWR wait for redo copy 944 3 0 0 0 0 Queue Monitor Task Wait 0 0 0 0 0 2 SQL*Net break/reset to client 95,954 1,202 16 3 0 0 SQL*Net more data to client 198 0 0 0 0 0 buffer busy waits 6,969 30 17 19 5 5 buffer deadlock 165 0 0 0 0 0 class slave wait 0 0 0 0 0 2 control file parallel write 0 0 0 16 212 2 control file sequential read 611 813 864 34 1 0 control file single write 0 0 6 11 0 0 db file parallel write 7,356 4,102 6,199 11,964 10,434 7,895 db file scattered read 3 6 13 0 0 0 db file sequential read 10,503 258,045 761,357 279,182 101,814 80,164 enq: HW - contention 3 1 0 0 0 0 enq: SQ - contention 2 2 0 0 0 0 enq: TX - index contention 909 140 104 67 43 30 enq: TX - row lock contention 0 0 0 0 0 1 latch free 141 9 0 54 0 0 latch: In memory undo latch 3 0 0 0 0 0 latch: cache buffers chains 809 0 0 223 0 0 latch: cache buffers lru chain 3 0 0 0 0 0 latch: enqueue hash chains 1 0 0 0 0 0 latch: library cache 126 69 27 2 0 0 latch: library cache pin 19 8 0 0 0 0 latch: redo allocation 4 1 0 0 0 0 latch: row cache objects 2 5 0 0 0 0 latch: shared pool Wait Event Histogram DB/Inst: ORAPERF/oraperf Snaps: 720-721 -> ordered by event (idle events last)

PHP Code:

Event -------------------------------------------------- 0 - 1 ms 1 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ 22 12 1 0 0 0 log file parallel write 21,611 636 11,463 9,485 23 7 log file sync 399 329 7,015 15,460 682 19 process startup 0 0 0 0 0 8 rdbms ipc reply 121 0 0 0 0 0 read by other session 9 13 22 5 3 0 Queue Monitor Wait 0 0 0 0 0 18 SQL*Net message from client 1,643,818 501,854 16,306 17,491 7,499 866 SQL*Net message to client 2,185,854 4 0 0 0 0 SQL*Net more data from client 4 0 0 0 0 1 dispatcher timer 0 0 0 0 0 9 jobq slave wait 0 0 0 0 0 173 pmon timer 2 0 0 0 0 182 queue messages 0 0 0 0 0 109 rdbms ipc message 5,809 1,895 2,026 3,018 3,202 7,436 smon timer 0 0 0 2 1 51 virtual circuit status 0 0 0 0 0 18 wakeup time manager 0 0 0 0 0 2 ------------------------------------------------------------- SQL ordered by Gets DB/Inst: ORAPERF/oraperf Snaps: 720-721 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 15,614,958 -> Captured SQL accounts for 101.5% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 1,716,548 168,609 10.2 11.0 371.10 1192.95 2573386356 Module: pipe04ac INSERT INTO SEP_CONFLICT_REL(CONFLICT_ID, MATCH_ID, PARENT_ENTIT Y_ID, CHILD_ENTITY_ID, DEGREE_OF_SEP, LIKE_CONF, REL_CONF) VALUE S(:1,:2,:3,:4,:5,:6,:7) 1,234,473 128,800 9.6 7.9 338.01 826.63 3352792302 Module: pipe04ac INSERT INTO ER_DETAIL ( ER_ID, MATCHED_ENTITY_ID, MATCHED_TYPE, MATCHED_ACCT_ID, MATCHED_KEY_ID, INBOUND_KEY_ID, SCORE_ADJUS TMENT, MATCH_PRECISION, PRECISION_DESC ) VALUES ( :1, :2, :3, : 4, :5, :6, :7, :8, :9 ) 836,101 84,352 9.9 5.4 92.10 233.17 4101062403 Module: pipe02ac INSERT INTO SEP_CONFLICT(CONFLICT_ID, SEP_TEST_ID, INBOUND_ENTIT Y_ID, CONFLICT_RULE_ID, CONF_ENTITY1, CONF_ENTITY2, DEGREE_OF_SE P, CONFLICT_STAT, WEAK_LIKE_SCORE, WEAK_REL_SCORE, CONFLICT_DT) VALUES(:1, :2,:3,:4,:5,:6,:7,:"SYS_B_0",:8,:9,:10) 687,347 112,027 6.1 4.4 162.46 447.97 1712256954 Module: pipe04ac SELECT MAX(ROLE_ID) FROM SEP_ROLES WHERE DSRC_ACCT_ID = :1 664,889 112,006 5.9 4.3 28.36 29.64 659324044 Module: pipe04ac INSERT INTO ER_ENTITY_STATE(ER_ID, ENTITY_ID, DSRC_ACCT_ID, MAX_ ADDR_ID, MAX_ATTR_ID, MAX_EMAIL_ID, MAX_NAME_ID, MAX_NUM_ID, MAX _ROLE_ID) VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9) 618,954 112,046 5.5 4.0 238.26 681.01 2878966175 Module: pipe04ac SELECT MAX(ADDR_ID) FROM ADDRESS WHERE DSRC_ACCT_ID = :1 606,310 112,028 5.4 3.9 227.12 712.55 1507829111 Module: pipe04ac SELECT MAX(NAME_ID) FROM NAME WHERE DSRC_ACCT_ID = :1 601,716 33,515 18.0 3.9 92.53 274.08 444121366 Module: pipe03ac INSERT INTO NUMS(NUM_ID, DSRC_ACCT_ID, ENTITY_ID, HIST_STAT, NUM _HASH, NUM_LOCATION, NUM_STAT, NUM_STAT_DT, NUM_TYPE_ID, NUM_VAL UE, QC_STAT, SYS_CREATE_DT, SYS_DELETE_DT, SYS_LSTUPD_DT, VALID_ FROM_DT, VALID_THRU_DT) VALUES(:1, :2, :3, :4, :5, NULL, NULL, N ULL, :6, :7, :8, :9, NULL, :10, NULL, NULL) 598,296 112,014 5.3 3.8 275.85 815.74 728889134 Module: pipe04ac SELECT MAX(NUM_ID) FROM NUMS WHERE DSRC_ACCT_ID = :1 SQL ordered by Gets DB/Inst: ORAPERF/oraperf Snaps: 720-721 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 15,614,958 -> Captured SQL accounts for 101.5% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 584,401 112,036 5.2 3.7 158.69 450.57 3193297599 Module: pipe04ac SELECT MAX(ATTR_ID) FROM ATTRIBUTE WHERE DSRC_ACCT_ID = :1 547,839 42,847 12.8 3.5 94.48 1023.18 1739503172 Module: pipe01ac INSERT INTO SEP_RELATIONS(RELATION_ID, ENTITY_ID, RELTO_ID, LIKE _CONF, REL_CONF, DISCLOSED, MATCH_ID) VALUES(:1, :2,:3,:4,:5,:"S YS_B_0",:6) 419,157 23,625 17.7 2.7 60.98 372.92 3253887853 Module: pipe03ac INSERT INTO ADDRESS(ADDR_ID, ADDR1, ADDR2, ADDR3, ADDR_HASH, ADD R_LEFTOVRS, ADDR_STAT, ADDR_STAT_DT, ADDR_TYPE, CARRIER_ROUTE, C ITY, COMPANY_NAME, COUNTRY, COUNTRY_CODE, DLV_PROB, DPBC, DSRC_A CCT_ID, ENTITY_ID, HIST_STAT, HOUSEHOLD_ID, JOB_TITLE, LATITUDE, LONGITUDE, LOT_CODE, POSTAL_CODE, QC_STAT, STATE, STATE_CODE, S 394,454 12,768 30.9 2.5 53.51 180.44 3965780448 Module: pipe03ac UPDATE DSRC_ACCT SET ENTITY_ID = :1 ,ACCT_LOADED = :"SYS_B _0" WHERE DSRC_ACCT_ID = :2 359,915 17,501 20.6 2.3 20.47 385.33 2711563501 Module: pipe04ac INSERT INTO NAME(NAME_ID, CULTURE, DSRC_ACCT_ID, ENTITY_ID, FIRS T_NAME, HIST_STAT, LAST_NAME, LFN_HASH, LMN_HASH, MID_NAME, NAME _GEN, NAME_PFX, NAME_SFX, NAME_TYPE, QC_STAT, SYS_CREATE_DT, SYS _DELETE_DT, SYS_LSTUPD_DT, VALID_FROM_DT, VALID_THRU_DT) VALUES( :1, NULL, :2, :3, :4, :5, :6, :7, :8, :9, NULL, NULL, NULL, :10, 337,392 48,569 6.9 2.2 15.29 15.32 3286148528 select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co n# = cd.con# and cd.enabled = :1 and c.owner# = u.user# 337,112 21,341 15.8 2.2 83.19 344.04 3078809437 Module: pipe02ab INSERT INTO SEP_ROLES(ROLE_ID, DISCLOSED, DSRC_ACCT_ID, ENTITY_I D, ROLE_CODE_ID, ROLE_STAT, SYS_DELETE_DT) VALUES(:1, :2, :3, :4 , :5, :6, NULL) 325,591 17,920 18.2 2.1 48.99 168.17 1848403279 Module: pipe03ac INSERT INTO ATTRIBUTE(ATTR_ID, ATTR_DT, ATTR_TYPE_ID, ATTR_VALUE , DSRC_ACCT_ID, ENTITY_ID, HIST_STAT, QC_STAT, SYS_CREATE_DT, SY S_DELETE_DT, SYS_LSTUPD_DT, VALID_FROM_DT, VALID_THRU_DT) VALUES (:1, NULL, :2, :3, :4, :5, :6, :7, :8, NULL, :9, NULL, NULL) 302,332 17,116 17.7 1.9 32.17 417.41 2888989485

Attach the statspack report instead of pasting it part by part.
I wanted to see complete init.ora parameters.

Tamil

I'm sorry I did not see the attachment option.
Thanks a lot, mj

Not sure but use insert /* +Append */ Or use hints

Quote:

Originally Posted by srt

Not sure but use insert /* +Append */ Or use hints

I cannot touch the code - it's in the app (3rd party product). Unfortunatelly there's not even a single database object other then tables and indexes. Not proc, no functions, nothing...
I asked the development but they are using soem special OCI calls and cannot change it to include "append".
Thanks a lot,mj

append is kinda useless with indexes anyway

p.s to attach something you need to be in the advanced reply section - then its at the bottom

1 Attachment(s)

The file is uploaded.
My insert rate in this file is like 1900 inserts a minute - very bad according to the business. If I had a way to minimize the sequential reads should be grate!
Thanks a lot for the help,mj

append if for insert select, for a heavy insert system even append is possible this will make your segment "HUGE"

Quote:

Originally Posted by mjschwenger

The file is uploaded.
My insert rate in this file is like 1900 inserts a minute - very bad according to the business. If I had a way to minimize the sequential reads should be grate!
Thanks a lot for the help,mj

I think the attached file is not complete.
I do not see init parameters.

Tamil

Sorry about that - the site excepts only up to 100mb - even the file for 5 minutes is bigger.

Hhere init.ora Parameters:

PHP Code:

End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -- background_dump_dest /opt/oracle/ora10g/admin/oraperf/ compatible 10.1.0.4.0 control_files /dev/rlv2_ctl1, /dev/rlv2_ctl2, / core_dump_dest /opt/oracle/ora10g/admin/oraperf/ cursor_sharing SIMILAR db_block_size 4096 db_domain usnv.ibm.com db_file_multiblock_read_count 16 db_name oraperf db_recycle_cache_size 536870912 db_writer_processes 8 dispatchers (PROTOCOL=TCP) (SERVICE=oraperfXD dml_locks 2500 event 10359 trace name context forever, job_queue_processes 10 log_buffer 200000000 open_cursors 2000 optimizer_dynamic_sampling 0 optimizer_index_caching 90 optimizer_index_cost_adj 10 optimizer_mode FIRST_ROWS pga_aggregate_target 2097152000 processes 150 remote_login_passwordfile EXCLUSIVE session_cached_cursors 200 sga_max_size 6828326912 sga_target 6828326912 undo_management AUTO undo_retention 60 undo_tablespace UNDOTBS1 user_dump_dest /opt/oracle/ora10g/admin/oraperf/ -----------------------------------------------------------

Thanks a lot,mj

1 Attachment(s)

This is the part which is missing in the previuos statpack - from the place where it stopped. (I could've zip it...)
Sorry and thanks again,mj

Just following the discussion, very interesting. This is probably beyond me, but have you also done a sql trace for a couple of minutes on one of these app sessions? Are there any triggers, or too many indexes involved on the inserted table? Are the inserts the only (or main) activity going on in the database?

The situation is also way above me - I have never seen Oracle behaves this way... For me this was always the most scalable RDBMS!
I have traces, of course. Bellow is the summary of the last one done for a single app instance. No triggers, no FKs, a couple of indexes on the big insert tables as every session also selects from them.
Both selects and inserts are intensive. It'a heavy OLTP (huge insert rate, small multiple selects, etc.) but imagine that the Db is treated as a huge repository - for all records inserted and evaluated. Every new records is compared towards the old entries. If existsthen the re-evaluation is performed, If not - it is inserted as new.
Thanks a lot,mj

mjschwenger,

Have a couple of questions for you:

Quoting from your previous reply:

(1)
"a couple of indexes on the big insert tables as every session also selects from them"

- What type of indexes are these? Bitmap? B*tree?
- Are they unique indexes?

(2)
"Every new records is compared towards the old entries. If exists then the re-evaluation is performed, If not - it is inserted as new"

- The queries submitted by the application for comparison, do they do index range scans or unique scans?

(3)
"a couple of indexes on the big insert tables"

What values get to these indexes? Are they monotonic values ? Values that are consistently increasing and never decreasing ?

http://www.dbaxchange.com

I went through your statspack report.
My observations are given below:

1. db_block_size = 4KB. I feel 4KB is very small. This value might be good 10 years ago when we had slow performance UNIX servers. But today, most of the hardwires are well designed to take care of 8KB block size, particularly if you use Veritas Volume Manager or IBM volume manager ( I have been using both as well as IBM servers). My suggestion to use 8 KB block size. Do not set it to more than 8 KB on IBM server, you will see worst performance (this is my personal experience).

2. The log_buffer is set to 200MB. This is huge. I have never seen a database with such a huge value since 1987. Since, 1.73 MB redo is generated per second; I would suggest you could decrease the log_buffer value to 4 MB.

3. log_parallelism default value in 10g is 2. You can increase to 8 or 12 so that different concurrent sessions would start using different log buffer. This will increase transaction through put. The transaction per second is 34.29 from your statspack report. I would expect this should be around 1000 on a well-tuned / configured IBM P series server with 8 CPUs. I had a similar experience what you are doing now 2 years ago. There were 120 concurrent sessions (through MQ series) pumped data into 400 GB big database. I am still using log_parallelism 8 in my 9i production server.

4. Raw device and File system. I believe you are using RAW devices (the data file name shows that). Did you enable async_io in your server? This is very important in IBM server with AIX OS. Alternatively, you can experiment FILE SYSTEM for oracle data files.

5. Stop Using MTS. There is no need to configure MTS because memory is cheap today. You can add memory to your server any time whenever you want. MTS will definitely slow down your transaction through put.

6. If the application is running on the same DB server, then do not use service name for connecting to Database. Alternatively, if the data is sent from the app server, then tune tnsnames.ora file. Consider adding SDU/TDU parameters.

7. Consider disabling PGA. May be for your workload, UGA may work well.

8. Divide the buffer cache into 2 parts – keep and default. Use “keep” for indexes and”default” for table data.

9. As I said earlier, I still believe PCTFREE, FREELISTS and INITRANS play major role for insert operation. You need to increase slowly those values for each table and indexes (FREELIST is not used in indexes even if it is set).

Where are you located?

I am expecting some inputs from Modic, Dapi & Slimdave. Guys, are you there?

Tamil

Since this is 10g how about read what does ADDM say? AWR reports? (Ok it similar to statspack but then since this is 10g lets use the features)

have you tried used reverse indexes? it has quite a few limitations, if you can bypass those you might be able to reduce your ITL waits

http://dba.ipbhost.com/index.php?showtopic=3076
http://dba.ipbhost.com/index.php?showtopic=3283
http://dba.ipbhost.com/index.php?showtopic=3278
http://dba.ipbhost.com/index.php?showtopic=3208

Hi Tamil, I don't think I can help.

AFAICS, it's stuffed by the one-row-at-a-time approach. The SQL in statspack would seem to show that much of the db sequential read wait time is due to the "does this row exist?" part of the logic.

On top of that, I'm worried by the queries like.
"SELECT MAX(ROLE_ID) FROM SEP_ROLES WHERE DSRC_ACCT_ID = :1"
That smells like disaster for concurrent updates.

Thanks a lot, Tamil. I'll go number by number:
#1 - my first try was with 8K pages and I saw more contention - on 8K pages db sequential reads are 4% higher. I can ceratinly switch to 8k again and re test once I have a beter throughtput.
#2 - The smallest log buffer which works for me is 10m - bellow that I have waits on redo requests and redo logs. I'll decrease the buffer log and re tests it with #3. I did not think about it - let me try it.
#4 - I use row devices. According to the networking aio server is properly configured and Oracle suppose to use it - unfortunatelly I do not have the impression that it works right but I do not know how to check. Fileio system is set to aio per IBM doc from september 2005. Before I used setall. What is your experience with the param?
#5 - I'll disable MTS - I did not configure it myself - may some of my colleagues did in the last days...
#6 - the app is very CPU intensive itself and cannot run on the DB server - I have alreadychanged SDU and TDU to 15000(10xMTS-1500)
#7 - you are right - UGA has high numbers in the statpack I saw them - I'll try to use it instead of PGA - one of the most seeing recommendations of ADDM is to increase PGA to 6G which I cannot do...
#8 - I tried to put all indexes and data for "only insert tables" into the recycle buffer pool with the size of 1G - the performance went much down - 12.5% and I stopped the test. I have 12G RAM on the server. Currently 6.8G are allocated to SGA, and I still have contetion on libray and shared pool. I may try to devide the total SGA to 3 parts - 3G for keep BP and to allocate there all "select" indexes, 1.5G for default BP and 2G for recycle - I'll keep there the insert only tables, and 2G for UGA.
#9 - I'll drop the freelists to 10 with 3 groups. In my testing I saw that changing the freelist for indexes helps - it relieves the enqueues-TX - are you sure they are not in use? What about freelist groups for indexes?
All indexes are Btree indexes, nothing is clustered, some of them are unique, but almost all queries are using ranges. A lot of the indexes are already reversed but the contention is still there.
I'm starting another big series of tests tonight and I'll post my results. It'll include compressing of some indexes as well.
Thanks really a lot for the help.mj