Hi,
I inherited an Oracle 9iR2 database on a Win2k advanced server platform, w/ 3 cpus and 4 GB ram. Previously, there was job that was running, which supposedly took less than an hour. What the job did was gather statistics on tables, and then did some data mining, and data manipulation, writing to summary tables. The data "loaded/inserted" into the "master" table was about 9 mil records daily. The job would kick off, @ an off-peak time, to roll this data. Once this data was "rolled up", then the data that was in the master table was deleted, not truncated, b/c there are points when data is still being inserted, while this job is running.
Now I had to install the new patchset 3, b/c there were some issues w/ the 4GB limit. Now after some time, it seems to take about 4 hours. At first I thought the main reason was because there were high deletes and inserts that there was a very high watermark on the table, which was true. I was able to reset it, by truncating the table, and rebuilding it's indexes. I also did a move table, and rebuild index, to make sure.
But that didn't do much, after I ran the process once after truncating table, and moving table, still was running about 4 hours. There were no changes to the init.ora or spfile for that matter. I ran a statspack (w/ timing statistics set to true of course), and sorted out that I needed to increase my buffer cache, and PGA parameters(sort_area_size,
etc..), but again,didn't do a whole lot. I saw that my undo header was quite high...and that surprised me considering that I am using Automatic Undo mgmt. w/ undo tablespace. And the tablespace is an permanent LMT.
The tablespace which contains all the data is an LMT, using ASSM. Also showed that I needed to increase the Log Buffers, which I set to max to(512k ram). I read somewhere that oracle will either use 512k or 128*cpu count which ever is greater.
And I know, yes, I should run a tkprof, to check out the sql stmts, but my question is, should I check on sql stmts if it hasn't changed, and the data in master table is usually constant on a daily basis? Is there something else I could be missing? There's nothing wrong on the o/s or hardware, that was checked. No other processes are running in background that would cause degradation(ie anti-virus, system process that kicked off, etc...)
Any feedback/help/suggestions/
advice would be greatly appreciated.
Start Free Trial
