|
Sort
|
I start this issue with a question with regard to the last issue.
Question:
When (In which connection) does Oracle allocate shared memory to the shared pool?
Preconditions are as follows:
1. There is no sufficient space in memory to increase shared_pool_size.
2. It is not possible to add physical memory.
3. ORA-04031 occurs mostly when a large amount of shared memory is allocated to the shared pool.
Answer:
First, I check the session information.
*Check the session information.
SQL> select sid,serial#,server from v$session
where username = 'XPRT';
SID SERIAL# SERVER
---------- ---------- ------------------
10 2105 SHARED
|
SERVER column is SHARED, which indicates the shared server mode.
Now, I will show you possible solutions to prevent ORA-04031 from occurring.
(Solution 1)
Set large_pool_size.
When large_pool_size is set in the shared server mode, the shared memory is allocated
to the large pool. However, as there is no sufficient space in memory, I can't increase
large_pool_size any more.
Suppose, for example, I set large_pool_size to 100MB as follows.
SQL> alter system set large_pool_size=100M;
System altered.
SQL> select id,sum(cost) from code group by id;
ORA-04031: Unable to allocate 8192 bytes of shared memory ("large pool",
"unknown object","sort subheap","sort key")
|
It is clear that large_pool_size = 100MB is not a big help.
It is of course possible to reduce shared_pool_size and increase large_pool_size instead,
but I wouldn't recommend you to do so for the purpose of summarizing the data only.
(Solution 2)
Switch the server mode to DEDICATED.
ORA-04031 occurs when the shared memory is allocated to the shared pool or the large pool (i.e. SGA).
In other words, ORA-04031 can be avoided by allocating the shared memory to PGA.
Shared memory can be allocated to PGA by switching the server mode to dedicated.
You can switch to the dedicated server mode by creating and using a service describing [ (SERVER=DEDICATED) ] in
CONNECT_DATA column of tnsnames.ora.
Now, I summarize the data in the dedicated server mode.
SQL> select sid,serial#,server from v$session
where username = 'XPRT';
SID SERIAL# SERVER
---------- ---------- ------------------
10 14 DEDICATED
SQL> select id,sum(cost) from code where rownum<=1000000 group by id;
1000000 rows selected
|
ORA-04031 does not occur.
Actually, there is another method of allocating the shared memory to PGA.
I will talk about that next week.
That's it for today.
Takuya Kishimoto
|
|
