The result set of the select statement may be stored in the UGA area of pga and

The result set of the select statement may be stored in the UGA area of pga. Q: Where is the returned result set stored when oracle performs the select Operation? Is the rowid set or real data saved in the result set? Is the returned result set in the PGA region? If real data is returned, Will memory overflow be caused if the data volume is too large? High Level

The result set of the select statement may be stored in the UGA area of pga. Q: Where is the returned result set stored when oracle performs the select Operation? Is the rowid set or real data saved in the result set? Is the returned result set in the PGA region? If real data is returned, Will memory overflow be caused if the data volume is too large? High Level

The result set of the select statement may be stored in the UGA area of pga.

Q:Where is the returned result set stored when oracle executes the select Operation? Is the rowid set or real data saved in the result set? Is the returned result set in the PGA region? If real data is returned, Will memory overflow be caused if the data volume is too large? Answers from experts

A:

If it's a simple select * from table, the data goes straight to the client. if the SQL has some intermediate steps for data processing, such as select count (*) from table, UGA will be used. in dedicated config, UGA is part of PGA.

Of course the data blocks will be scanned into SGA (buffer cache specifically), unless it's a direct path read. your original question is whether the data will be in PGA. I already answered that. but no matter whether it's select *... or select count (*)..., the table data must go to the buffer cache first(Partition t for parallel read, and a few other special cases of direct path read ).

Yong Huang

That is, there is no need to store the data directly obtained. The data is already there (buffer cache, logical read/disk, phisical read logical read)
ForIntermediate resultFor example, in the Huang version, or,Sorting result, array, and so onShould be in PGA or disk

Refer:

Http://www.itpub.net/thread-1505871-1-1.html

Google select statement result set pga

========================================================== ========================================================== ========================================================== ============

Understanding of db block gets direct in DML statements

Q:

From v $ sysstat, we can see that db block gets consists of db block gets from cache and db block gets direct.

The former is easy to understand, and the latter means: "Number of times a CURRENT block was requested bypassing the buffer cache"

So why bypassing the buffer cache?

Is it because it cannot be found in buffer cache? If it is not found in the buffer cache, so the bypassing buffer cache is used to find it on the disk, then the number of db block gets direct + the number of consistent gets direct, it should be equal to the number of physical reads, but it does not. Why? Thank you.

A:

What is the purpose of buffer cache?
If the block you want to read is not suitable, the purpose of setting the buffer cache even violates its original intention.
Should it be bypassing buffer cache?

That is to say, in general, to read a block, you should first find it in the buffer cache, and then find it from the disk if it cannot be found, and put it in the buffer cache first, for example, you can find it directly from the buffer cache next time you need it.
However, sometimes the data is read into PGA, that is, if a large amount of data is to be read and not reused, it is not appropriate to read the buffer cache.

Q:

This bypassing buffer cache refers to the direct bypassing buffer cache, which is directly found on the disk; or is it forced to find it on the disk if it is not found in the buffer cache first, what if the block is not stored in the buffer cache?
A: bypassing buffer cache is directly found on the disk.

Question:

About db block gets direct
Db block gets(From cache)Is the current mode gets in buffer cache,

So db block gets direct should be the current mode gets in pga, right?

(DML statement execution occursDb block gets direct, The data block on the data file is directly stored on the pga, so it should be so)
No formal statement found

Refer:

Http://www.itpub.net/forum.php? Mod = viewthread & tid = 963118 & highlight =

Baidu db block gets direct

Http://blog.itpub.net/7608831/viewspace-693190/ =

Http://oracle.zwcoom.com/Oracle/Oracleguanli/20100312/143435.html

Additional

1.3 CGARelated Concepts

CGA is also a memory area,It is dynamic, that is (Is created at the beginning of the call, and exists throughout the call process until the call ends..It stores the data required during the call process..

We know that,Calls mainly include parsing (parse) calls, execution (executive) calls, obtaining (fetch) calls, recursive SQL calls, and PL/SQL calls.. We can see from the types of calls that the data actually needs in the call process, suchSQL area, PL/SQL AREA, and SORT AREA are all stored in UGA, because the data must always exist and be available between calls. In CGA, only the data temporarily required during the call process is stored, such as Direct I/O Buffer)And stack space and other data structures. Therefore, the call cannot be completed without the data structure in CGA.

Note that CGA cannot be located in SGA (connected in Shared Server mode ),CGA must be in PGA.If the current process is running, there is only one CGA in each PGA.If the current process is not running, there is no CGA In the PGA of the process.

The preceding description indicates thatDirect path read is the cga area on the pga that reads data from the data file.

An SQL operation is not an SQL statement, but an operation. For example, you need SQL operations in the SQL workspace.For hash connections, sorting, and so on.