I truly believe that database myths are the scourge of the database world.  With that said, I have to blame myself for not being specific enough in one of my own blog posts and propagating one myself!  As it was brought to my attention by Tanel Poder and Greg Rahn, I hadn’t put a post out here until now, so apologies! I have flipped back to an almost exclusively 10g environment the last three months, but even though I’m back to my old stomping grounds, now fully staffed with almost double the DBA’s that were present when I left, have…

This is another blog post about how TEMP can kill you in performance. The Program Global Area, aka PGA, is a memory region that allows Oracle to perform many processes that once fell to static calculations in parameters that had to be managed by a DBA. As frustrating as it might be to a DBA to not have enough memory to allocate to the performance enhancing feature, I found it even more frustrating to find NO PGA allocation to window sorts.  We were experiencing poor performance in one of our environments, but I noted it was only during heavy workloads,…

The introduction of the temp tablespace was a crucial step in the RDBMS to separating  the “work area” for sorting and specific join operations from permanent segments.   To eliminate the performance challenges of sorts and hash against the I/O threshold involved with performing this word in the temp tablespace, Oracle 9i introduced the Program Global Area, (PGA) to complete sorts and hashes within memory. DBA’s spent considerable time tuning the PGA, attempting to continually ensure that as more complex sorting and join features emerged, that these processes would complete their work within the PGA and not require such a large…