| tuning insert [message #128065] |
Thu, 14 July 2005 17:46  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
One of my statements has the following tkprof:
The table is as you can see from the insert - most columns are numbers. I just run statistics for the whole table.
3 indexes -1 on PK(CONFLICT_ID,CONFLICT_RULE_ID),1 on CONF_ENTITY1 and 1 on CONF_ENTITY2.
INSERT INTO SEP_CONFLICT(CONFLICT_ID, SEP_TEST_ID,INBOUND_ENTITY_ID, CONF_ENTITY1, CONF_ENTITY2, DEGREE_OF_SEP, CONFLICT_STAT,WEAK_LIKE_SCORE, WEAK_REL_SCORE, CONFLICT_DT)VALUES (:1, :2,:3,:4,:5,:6,:7,'P',:8,:9,:10)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3420 2.77 40.52 6881 129 35533 3420
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3421 2.77 40.52 6881 129 35533 3420
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 67
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 6881 1.23 38.13
SQL*Net message to client 3420 0.00 0.00
SQL*Net message from client 3420 0.00 2.06
********************************************************************************
If I need to troubleshoot this - what I need to look at first?
Why the db file sequential read on insert are so high?
Thanks a lot,mj
|
|
|
|
| Re: tuning insert [message #128068 is a reply to message #128065] |
Thu, 14 July 2005 17:57  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have similar insert worse the the previous:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net break/reset to client 806 0.01 0.12
SQL*Net message to client 477 0.00 0.00
SQL*Net message from client 477 0.00 0.39
db file sequential read 239 0.00 1.14
********************************************************************************
what does the SQL*Net break/reset to client waits depends on?
Thanks a lot,mj
|
|
|
|
| Re: tuning insert [message #128266 is a reply to message #128065] |
Sat, 16 July 2005 11:29 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
It looks like in your first case, you ran this insert statement 3420 times which took a total of 41 seconds. That is only .012 seconds per insert. That seems fairly fast.
What is your query, what is your code?
Why are you executing this insert statement, which does only one row at a time, over and over and over? Try to rewrite it so that you are using set/bulk operations instead of row at a time processing.
|
|
|
|
|
|
|
|
| Re: tuning insert [message #128472 is a reply to message #128065] |
Mon, 18 July 2005 16:48 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Thanks a lot, guys.
First - I cannot touch the application. May there'll be changes but not early then 5-6 months and in the same time, I have to make it work. There's no option for batching. The app gets 1 records from external source and inserts different parts of it into different tables. Then compare this product to all other products already in the tables and populates the report tables. The trans/sec are very high - like 3500/sec. The inserts start at 18,000/min and gradualy drop with the data inserted, but I still have to keep the speed as high as possible.
I know that the application we are running has logic issues - inserting and selecting from the same tables, but there's nothing I can do... except to try to tune up Oracle side as much as I can.
For this reason the parallel inserts cannot be specify in the code. Can I specify them out of the query - like table level... I already set the parallel server to 3 although this is OLTP, not DW, and in table storage MAXTRAN - 60... How else I could cotrol the parallel processing? Is parallel goign to be beneficial to me as this is a high OLTP?
My redo logs are 8 by 2G each. They are changing evey 30 min.
What else I can do to tune them up?
Log Synch evewn is in my top 5 in statpack - any ideas for that?
MY buffer log is 10M as Oracle adviced for huge applications.
any other ideas?
Thanks in advince,
mj
|
|
|
|
|
|
| Re: tuning insert [message #128564 is a reply to message #128065] |
Tue, 19 July 2005 07:35 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Yes, please provide more information as Ziggy has mentioned, hardware, memory, disk system, and major memory parameters.
Also more about your application and its code / logic. Perhaps there is something you can do with the physical storage and structure of your tables (like iot or partition) but we don't know enough to know.
Also I don't see how parallel will help, as that is useful for a long running operation but in your case you have multiple short running operations.
Binding would help (don't know if application does or not?) but if you can't change the code maybe can investigate cursor sharing parameter. Test any changes first though.
And are you sure you literally can't change anything, not even create some stored procedures or something for the existing app to call or interface with? How does it work now, you just install some precreated scripts and users and stored procs?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
