Skip to Main Content
  • Questions

  • What is difference between UGA and temp files in db

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ankit.

Asked: February 06, 2016 - 11:19 am UTC

Last updated: September 05, 2017 - 6:27 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

Hi,

I was reading about oracle DB architecture, I came across data files(I read about temp file).

Temp file --->>
when user do a sorting operation -->> the data will go to this file space -->> sorting is done and data is displayed to the end user and later on this space will get free.

I also read about UGA --->>> It contains user session information. I also read that this memory is also used for sorting purpose.

So my question is if both space are getting used for sorting purpose that what is the difference between temp file and UGA.

Thanks in advance.

and Connor said...

If I sort 50 rows, then they'll easily fit in memory, so I'll sort them in memory because that's the fastest.

If I sort 50,000,000,000 rows... they wont all fit in memory, so I'll sort as much as I can in memory and regularlt dump intermediate results out to disk (temp files) and keep going until I finally can get all the data sorted.

There are a couple of thresholds:

- An 'optimal' sort (we did the whole thing in memory)

- A one-pass sort (we passed through data and had to dump some results to disk, then read them all back to send the sorted entire set back)

- A multi-pass sort (we passed through data and had to dump some results to disk, then had to read them to do *more* sorting, then dumped *those* results back to disk, and so forth, and so forth, until we got the whole lot sorted)


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ankit Mongia, February 08, 2016 - 7:28 pm UTC

Thanks for the explanation. I need one more clarification -->> In dedicated server mode why UGA in PGA and in shared server UGA is in SGA
Chris Saxon
February 09, 2016 - 1:20 am UTC

In shared server, I'm repeatedly being allocated to *new* processes.

eg,

- I open a cursor, I get allocated to process 12. I do my stuff and I'm done.
- I come back 3 seconds later and fetch from that cursor, I now get allocated to process 15, I do my stuff and I'm done.

So since I might be allocated to *any* process, I need somewhere where they can *all* see my current memory state (UGA). So it sits in the SGA.

For dedicate server, I will *always* have the same process - its dedicated to me - so I can keep my state in the PGA

understand

A reader, September 04, 2017 - 8:11 am UTC

in the above explanation i had understand,the UGA not fetch or sort the huge records that's why instead of UGA we are using temp files is it right.........
Connor McDonald
September 05, 2017 - 6:27 am UTC

No, it works the same - it just determines whether the memory you are using sits in the PGA (your private process) or in the SGA (still private to you but in shared memory because you are using shared processes)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.