This content has been marked as final. Show 4 replies
What happens if you don't set memory_target and switch back to using the SGA and PGA parameters?
Worth a try to see if it's that parameter causing the issue.
I made the following changes:
alter system set sga_max_size=12288M scope=spfile;
alter system set sga_target=10240M scope=spfile;
alter system set pga_aggregate_target=10240M scope=spfile;
alter system reset memory_target scope=spfile;
alter system reset memory_max_target scope=spfile;
And restarted the instance. We do still run into the same problem. After about 10-20 minutes of reporting activity we start massively paging. I was watching nmon, iostat, and EM and have the following observations:
94MB free RAM (w/out file system buffers... in other words, consumed active memory). The breakdown according to nmon is:
SGA allocation from EM:
Shared Pool: 1568MB
Buffer Cache: 8032MB
Large Pool: 512MB
Java Pool: 32MB
Current PGA allocated: 6414MB
Maximum PGA Allocated: 17340MB
So PGA seems to have grown to 1.7 times the amount I gave it as a target. This is likely the issue.
When you did the import, have all of the stats been set correctly? Perhaps there are tables without stats and it's ful scanning them into PGA to do the sorting parts of your reporting queries?
I'd check that the import was OK and there were no errors.
The import finished successfully. I manually gathered stats and manually created all the indexes.