Home » RDBMS Server » Server Administration » Sorting
Sorting [message #262808] Tue, 28 August 2007 05:34 Go to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi,

Is sorting in PGA done by copying blocks from SGA to PGA?
Can anyone explain how it's happening?

Thanks...
Re: Sorting [message #262864 is a reply to message #262808] Tue, 28 August 2007 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First block in a buffer area, then data in sort area.

Regards
Michel
Re: Sorting [message #262980 is a reply to message #262808] Tue, 28 August 2007 12:52 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Definitely one of the best so far
SQL Memory Management in Oracle9i by Benoît Dageville, Mohamed Zait
http://www.cs.ust.hk/vldb2002/VLDB2002-proceedings/papers/S29P03.pdf

Gints Plivna
http://www.gplivna.eu
Re: Sorting [message #263485 is a reply to message #262808] Thu, 30 August 2007 03:05 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi,

In Oracle doc,it's given that PGA consists of,

1. Stack space (contains session's variables, arrays, and other information)
2. UGA (session-specific data)

in case of Dedicated Server mode.

But in case of MTS,PGA consists of only Stack space & UGA is moved to shared pool or large pool.

My question is,
1. Why UGA is placed in shared memory in case of MTS?
2. If UGA is placed in shared memory since it is shareable among shared server processes,why not stack space placed in shared memory region because it also contains session-specific info?


Can anyone please explain what is the exact difference between contents of Stack space & UGA that makes onething to be placed in shared memory region & other in PGA itself in MTS mode?

Thanks...


Re: Sorting [message #263493 is a reply to message #263485] Thu, 30 August 2007 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. UGA is placed in shared memory because it contains data that is persistent accross calls like package variables, opened cursor information, session parameters...
2. It is placed in shared because when a call is completed you likely not have the same shared server to execute the next call.
Stack is not in shared memory because between each call there is no more stack, you have completed your work.

Regards
Michel
Re: Sorting [message #263530 is a reply to message #262808] Thu, 30 August 2007 04:35 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member

Thanks Michel for your explanation,

I didn't understand why stack space is not shared.Can you please explain it with an example?

Thanks a lot...

Re: Sorting [message #263533 is a reply to message #263530] Thu, 30 August 2007 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Stack is not shared because it is used only during calls (piece of code execution). During a call you stay with the same shared server, so it can use its own memory and does not need shared memory for stacks.
Between calls there are no more stack.

For instance, you launch a select, a shared server is allocated for you, when it executes your query it allocates (private) memory for stack, when it completes the query it returns you the result and frees space allocated for stack (and forget you).

Regards
Michel

[Updated on: Thu, 30 August 2007 04:45]

Report message to a moderator

Re: Sorting [message #263553 is a reply to message #262808] Thu, 30 August 2007 05:59 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member


Michel,a lot of thanks for your good explanation..

Can you please explain me the answer given by you for my first question little briefly,if possible with an example i.e.,

Is sorting in PGA done by copying blocks from SGA to PGA?
Can anyone explain how it's happening?


Soon,with your help & this forum members help I hope i'll learn Oracle DBA well and i'll also start to give solution for others queries...

Thanks Michel & all members of this forum...


Re: Sorting [message #263584 is a reply to message #263553] Thu, 30 August 2007 08:01 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you please explain me the answer given by you for my first question little briefly

Quote:
First block in a buffer area, then data in sort area.

Is this not briefly?
Process read a block (buffer) in SGA, put it in buffer in PGA then extract data to sort from this buffer and put it in sort area.

Quote:
Soon,with your help & this forum members help I hope i'll learn Oracle DBA well and i'll also start to give solution for others queries...

Please first read Database Concepts.

Regards
Michel

[Updated on: Thu, 30 August 2007 08:02]

Report message to a moderator

Previous Topic: Migration
Next Topic: Table or LOG for Procedure name param val and time of execution
Goto Forum:
  


Current Time: Sat Dec 10 17:02:33 CST 2016

Total time taken to generate the page: 0.13403 seconds