what is stored in Process global area in case of Sql statement

>>is it stores some where in PGA and then transferred

Yes.  I know you've already mentioned the docs don't explain it properly but have you already read this analogy:

An analogy for a PGA is a temporary countertop workspace used by a file clerk. In this analogy, the file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/memory-architecture.html#GUID-859795E2-87CD-442B-B36F-584A77755F59

The buffer cache holds the "raw" data blocks.  That block likely holds extra row data your query may not need.  What is needed is pulled out of the buffer cache and held in the PGA.

For example, I need customer_id=1.  The data block that holds that is placed in the buffer cache but that block also holds data for customer_id 2 and 3.  Your query doesn't need that data so it reads customer1's data from that data block and uses it in the PGA.

is this  stored in Runtime area  i.e customer 1 data is stored in runtime area and what is stored in persistenet area for this requirement.

Example :
I create a cursor
cursor c1
is
select *
from employees
where employee_id=1;

When I create a cursor , cursor variable is created in client process and this points to persistent area where the cursor variable is declared by the server process and runtime stores the data retrieved by the query .

is my understanding correct

I'm afraid I've never cared enough about how the PGA works to dig into it at that level.

I can say that just creating the cursor doesn't process any data until the first fetch.

Hi Slighywv,

Sorry , I want to have better understanding of how things works  thats the reason iam focussing on each and every detail.

As you said when I open the cursor , oracle server process allocates the PGA memory i.e it allocates the run time area and persistent area to store the bind variable info in it and when I fetch the rows it is transferred from buffer cache to runtime area and from there to the client process.

Is it correct . Kindly guide me please.

Also for DML statement , all the modifcations happens in the buffer cache , what is stored in PGA.

Kindly help me understand these questions .  It is really appreciated.

I'm afraid the level of detail you are after is above my understanding.

You will have to wait for additional Experts to come around.

Thanks for suggestions Slightwv, Can you request any of colleagues who can help me with this information

Definitely above my pay grade as well.

I think you are starting to enter the area of "you don't need to know that".  Oracle has to keep some things to themselves.  If you cannot figure out what you need from the documentation, you may not be able to get a solid answer.

As an example, this was a while ago, I know someone that works at Oracle.  Brilliant DBA before he went there.  Started in training.  Went on to technical sales, I'm sure he is still there, but I don't know where now.  Because he is an employee, he has access to internal people we cannot get to.  He was trying to confirm something that we all know, that the pmon process wakes up every 5 minutes.  The internals people wouldn't confirm it.  Told him he didn't need to know that, essentially, it is "need to know and you don't".  This is a metric that we all know is correct, he was just trying to confirm that was what was really going on and they wouldn't.

I agree with slightwv’s comment about not caring about that level of internal details about how the Oracle SGA and PGA work.  I’ve worked with Oracle for almost 30 years and haven’t found a need to know that level of detail.

I find it more useful to pay attention to how many logical block reads or full-table scans the SQL statements in the application cause.  I look for ways to reduce those.

if you really want that level of detail you'll have to direct your attention to Tom Kyte or Jonathan Lewis
I'd suggest Patrick, our performance dba too, but he doesn't do stuff online and for free

And about johnsone remark:
>> He was trying to confirm something that we all know, that the pmon process wakes up every 5 minutes.

It is easy to see that PMON wakes up every 3 seconds. Just strace -Typ <pid of pmon> (on a lab of course - not prod):

) = -1 EAGAIN (Resource temporarily unavailable) <3.007649>
getrusage(0x1 /* RUSAGE_??? */, {ru_utime={3, 802389}, ru_stime={15, 906645}, ...}) = 0 <0.000033>
open("/proc/3865/stat", O_RDONLY)       = 9</proc/3865/stat> <0.000051>
read(9</proc/3865/stat>, "3865 (ora_vktm_cdb1a) S 1 3865 3"..., 999) = 319 <0.000060>
close(9</proc/3865/stat>)               = 0 <0.000041>
semtimedop(388366336, [{6, -1, 0}], 1, {2, 999999000}) = -1 EAGAIN (Resource temporarily unavailable) <3.007088>
getrusage(0x1 /* RUSAGE_??? */, {ru_utime={3, 802745}, ru_stime={15, 906645}, ...}) = 0 <0.000037>
open("/proc/3865/stat", O_RDONLY)       = 9</proc/3865/stat> <0.000048>
read(9</proc/3865/stat>, "3865 (ora_vktm_cdb1a) S 1 3865 3"..., 999) = 319 <0.000044>
close(9</proc/3865/stat>)               = 0 <0.000037>
semtimedop(388366336, [{6, -1, 0}], 1, {2, 999999000}) = -1 EAGAIN (Resource temporarily unavailable) <3.007385>
getrusage(0x1 /* RUSAGE_??? */, {ru_utime={3, 803157}, ru_stime={15, 906645}, ...}) = 0 <0.000042>
open("/proc/3865/stat", O_RDONLY)       = 9</proc/3865/stat> <0.000051>
read(9</proc/3865/stat>, "3865 (ora_vktm_cdb1a) S 1 3865 3"..., 999) = 319 <0.000049>
close(9</proc/3865/stat>)               = 0 <0.000042>
semtimedop(388366336, [{6, -1, 0}], 1, {2, 999999000}

Select allOpen in new window

And that's quite important to know. keeping resources allocated and rows locked for 5 minutes when a session crashes would be a big problem. Was also important to know when dynamic registration to listener was done by pmon in <12c

That's off topic, but IMO, those questions about internals are very important to understand the concepts. And this will be needed when troubleshooting issues or performance. Except if you are ready to spend days uploading irrelevant files to the support engineer ;)

Regards,
Franck.

Sorry, that was an old timing.  This was quite a while ago.  You simply look at the pmon timer statistic.  Number of waits divided by wait time.  At the time it was 5 minutes.  My point here is that Oracle internally would not confirm that something that was very simple and really not that important.  If they aren't going to confirm very simple not really a trade secret piece of information, they aren't going to be forthcoming with much else.

>> Sorry, that was an old timing.  This was quite a while ago.
Older than Oracle7 then:

Thank you all for your answers , really appreciate .