HP-UX

The Superdome is Itanium, sorry I did not specify that earlier. There are no other applications or databases running on this server.

I am trying to work with a DBA to find the cause. The database, server, and batch process have not changed in over a year. The uptime was more than 365 days. This is our pre-production database so, I am told, the data has not grown either.

The more I look at it, the more it looks like everything is running great from the OS perspective, really fast, in fact. It looks like all of the data is loaded up into the dbc and the query is repeatedly hitting it again and again and again. I cannot see the query but I am told that they are reading from several tables trying to build a new table. Again, they've done this nightly without issue until recently. In my book, something has changed but here I am, the Sys Admin, with all fingers pointing at me as if it has something to do with the server.

Thanks again folks, for all of your help. I'll keep digging and looking into your recommendations.

Oracle Database 10g runs I/O operations from both shared memory and process-private regions using the new HP-UX asynchronous driver. However, I/O operations through the asynchronous driver are not asynchronous in nature. This is because Oracle Database must perform a blocking wait to check the status of I/O operations submitted to the asynchronous driver. This causes some Oracle processes, such as the database writer process, to essentially process synchronous I/O.

This is an extract from this link

seems like asynchronous I/O on HP-UX with Oracle is only supported for raw FS

How many DB writer are currently set up on your DB ?

Few considerations about it you might want to prefer:

a) the FS type hosting the datafile can handle async I/O (oracle param DISK_ASYNCH_IO = true) so the DB writer can let the OS deal with the update (without having to do "wait" events)

b) if your FS type doesn't support async I/O you can setup more than 1 DBWR (DBWR_PROCESSES/db_writer_processes) but since the oracle buffer cache is divided in as much working sets, you might want to adjust the DB_BLOCK_LRU_LATCHES.

c) Last solution, activate the forking to simulate asynch I/O by activating the use of slave processes (DBWn_IO_SLAVES) which are in fact synchronous I/O (DISK_ASYNCH_IO = false). Risks of that last solution are buffer overhead, wait, memory overhead (IPCs & context switch)

As I recall waiting for cache in Glance means the process is waiting for the file system buffer cache to be updated, which is normal for buffered IO. Everytime when this was all I could see I and there was hardly any CPU usage and the system seemed to function normally, it turned out that someone had been making changes to a query anyway

Some product like ODM (Oracle disk manager) i think, can also help to avoid double buffering (OS+DB) and may help to save the some RAM space used by the buffer cache system so that the saved space can for example be reused to increase the database buffer cache.

Good news! The process always drops the indexes and rebuilds it every day before it starts. In our meeting right now the DBA said she just now rebuilt the indexes and the query finished immediately. At least now we have enough ammunition to point back to the database.

Thank you again everyone for all of your suggestions.

LoL

... Check that the query is correctly designed

That was the first sentence of my first post

Well, they've already admitted that the query should be modified to run in parallel mode but they do not want to make this change until they've finished their current testing over the next 4-6 weeks.