Does Oracle allocate different PGA's for the same user when connected from multiple PCs?-CodePudding

Assume that I've connected 3 times to the database with the same user from different PCs. Does Oracle create separate PGA areas for each of them, or just one? If one, how it handles multiple queries coming from different sessions connected by the same user, and executed at the same time?

CodePudding user response：

Each session (assuming you're using dedicated sessions) allocates separate memory in PGA for things like sorts. It doesn't matter whether those sessions come from 1 user or 100 users. Each session gets its own memory.

CodePudding user response：

Answering your questions

Does Oracle create separate PGA areas for each of them, or just one?

The Program Global Area or PGA is an area of memory allocated and private for one process. The configuration of the PGA depends on the connection configuration of the Oracle database: either shared server or dedicated.

In a shared server configuration, multiple users share a connection to the database, minimizing memory usage on the server, but potentially affecting response time for user requests. In a shared server environment, the SGA holds the session information for a user instead of the PGA. Shared server environments are ideal for a large number of simultaneous connections to the database with infrequent or short-lived requests. In a dedicated server environment, each user process gets its own connection to the database; the PGA contains the session memory for this configuration. The PGA also includes a sort area. The sort area is used whenever a user request requires a sort, bitmap merge, or hash join operation.

Therefore, the answer is yes, assuming you are not using shared server configuration.

If one, how it handles multiple queries coming from different sessions connected by the same user, and executed at the same time?

In a SHARED SERVER configuration, the SGA holds the session information for a user instead of the PGA. That is precisely the point for handling multiple connections using the same server process. Shared server tasks have to keep these working areas in the SGA, because all the dispatcher processes handle requests from any user process.