OraFAQ Forum: Performance Tuning » Waits During Inserts .

Home » RDBMS Server » Performance Tuning » Waits During Inserts .

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Waits During Inserts . [message #306316]

Thu, 13 March 2008 11:34

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi,

We are seeing some waits for this insert Statement.
This table and Blob is in a different Tablespace seperated from rest of table/Indexes of the APP.

All these below waits were for 10-60 secs time, This is a Image table where we store images, and IMAGE_BL column is of BLOB dataype.

direct path write
direct path read temp
SQL*Net message from client

INSERT INTO image (image_id, creation_dt, height_nb, width_nb, format_tx, side_cd,size_nb, image_bl, source_image_id_tx, rotation_nb, process_dt)
     VALUES (image_seq.NEXTVAL, SYSDATE, :b9, :b8, TRIM (:b7), :b6,:b5, :b4, :b3, :b2, :b1 ) 
  RETURNING image_id
       INTO :o0

TEMP Tablespace is > 32,0000 MB

It's an OLTP system, Insert happen at a time continously for 5-10 min then searches, ,
say like Remote Sites people they do keying of Data
so in the Application if we take an Hour of work for example,
First 5 min lot of inserts when people start keying the data ,
then 10 min idle with some short searches and regular update of Work Status table,
then 5 min heavy inserts again when ever Batch come, like that and in a batch there could be 1 image or a max of 15 images,
so in 5 min there could be 50-100 batches,
The time it takes for each batch now is approx 15-20 sec what we have seen is with 1 image it takes around 15 sec.

Any suggestion where to look and tune what for these type of waits.

Thanks

Report message to a moderator

Re: Waits During Inserts . [message #306321 is a reply to message #306316]

Thu, 13 March 2008 11:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/84315/74940/

which of the suggestion in URL above have you done & what were the results?

Is the tablespace DMT or LMT?

Report message to a moderator

Re: Waits During Inserts . [message #306334 is a reply to message #306321]

Thu, 13 March 2008 13:52

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi,

Tablespace is LMT,
As far as suggestions from that link, Yes i have monitored the DB where this was happening,

STATS are Updated.
> No Locks / Deadlocks Detected.
>Other than these waits there was nothing wrong, Select,Searches Are all fine, No complaints for Performance excepts waits on Inserts
>This Insert is a Part of Package /Proc , every thing from this package gets passed but delay is only on this insert Statement and i see those waits.
>Only 3 Btree Index and 1 Blob column Index are there on this table, All the indexes are used in select, There is no FTS on this table when it is called in Searches.
> All indexes are Analyzed upto date.

This behaviour is same in TEST DB also, even though there is no activity execept this Inserts, What we are seeing is only this porition which involves BLOB column is slow,
> Default Block Size is 8 k
> PGA_AGGREGATE_TARGET parameter in PROD is 1073741824
> workarea_size_policy is AUTO

Please tell me if i could provide more info,
Is there any to look more into tuning objects involving BLOB's

Thanks

[Updated on: Thu, 13 March 2008 13:52]

Report message to a moderator

Previous Topic:	How to get recordcount & elapsed time without get result set while run proc in sql*plus?
Next Topic:	Help - Stored Outline

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 17:51:32 CST 2024