Re: lots of waiting on 'db file parallel write'

This isn't an OLTP system although we do have lots of single record/short dml transactions being processed on a regular basis. Our system is mostly read and those reads select lots of data .. queries that pull anywhere from 10K to 6MM records...these happen all day long.  Most of our queries do lots of hash joins and sorts...hence the CPU usage...

I suspect that what we are writing so intensely is these inserts/updates coming from the web when users register or subscribe to online retailers... these dml's are not batched so throughtout the day they hit our system. Our short transactions do use bind variables, but our large selects dont' because the WHERE clause of these selects can be anything the customers want it to be .... so we use dynamic_sampling=4 (when customers user MOD for example) and use histograms because data distributions are always changing... our customer upload data (large data sets ..millions of records) on a regular/daily basis too...to update their customer base.... this could also be the reason we are writting so much more.. we've recently switched to linux application servers (apache on linux) and we are now able to upload data much faster... (more writing).

I guess I can always turn off async io .. but I didn't want to go there yet.
thanks for the feedback.
-peter Received on Mon Feb 27 2006 - 08:14:36 CST