Hi all,
Been noticing this error in my alert log for a while. Then I got a call from one user complaining of a failing query..this is a scheduled query that runs early in the mornings daily.
Solution to my case:
1. I looked through my alert log to see if I will find any special incidents around the period which these errors were encountered. I didn't find any!
2. I looked at AWR and ADDM reports for the periods around this occurrence. No serious observation except that I found "Undersized PGA". This I did not take seriously.
3. I immediately proceeded to increase the size of my TEMP tablespace by resizing the one of the datafiles. This had no effect whatsoever because the error continued to show up in the alert logs and the query kept failing.
4. I had them schedule the query to a period when there's less transaction on the database (weekend). It still failed. This morning, I came in a looked at my alert logs only to find several incidents of ORA-1652 all through the weekend! This was a period when activity on the DB is not anywhere near optimal.
6. All the while, "Undersized PGA" kept showing up in my ADDM reports.So I decided to increase my pga_aggregate_target. We ran the query again and it worked!
Lesson:
Pay attention to seemingly unimportant alerts especially from your ADDM reports. I must add that all the while that I had "Undersized PGA", it stated that this had less than 2% impact on my peformance.
