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.
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
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.
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)
% 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)
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)
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
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
Reply With Quote
Originally Posted by mjschwenger
Bookmarks