-
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
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|