|
Sort
|
In this issue, I will analyze the sort process again based on the following example.
When data are accumulated in a certain system, ORA-04031 error occurs frequently and the process
terminates with an error. As I read through the Oracle document to find the resolution,
it says that ORA-04031 can be avoided by increasing shared_pool_size.
I increase shared_pool_size from 200MB to 400MB then.
One month later:
After I increase shared_pool_size, I don't have ORA-04031 error for a while.
However, as the amount of data increases, ORA-4031 error starts occurring again.
This time, the system does not have sufficient memory to increase shared_pool_size, though.
I add the physical memory and then increase shared_pool_size to 800MB.
Three months later:
ORA-04031 error does not occur for a while, but as time goes by, the error occurs again.
I don't think I can add physical memory any more. Month-end operation will be a nightmare
unless I do something...
The following are excerpts from the documents:
ORA-04031: unable to allocate nnn bytes of shared memory
Cause:More shared memory is needed than was allocated in the shared pool.
Action:If the shared pool is out of memory, either use the DBMS_Shared_Pool package
to pin large packages, reduce your use of shared memory, or increase the amount of available
shared memory by increasing the value of the initialization parameters Shared_Pool_Reserved_Size
and Shared_Pool_Size. If the large pool is out of memory, increase the initialization parameter
Large_Pool_Size.
(Test environment)
OS:Red Hat Linux release7.1
Oracle:9.2.0.1
Step to reproduce the problem
I prepare the same environment as the one with ORA-04031 error.
Since it is difficult for my environment to have such large size of shared_pool_size,
I set shared_pool_size to smaller and reproduce ORA-04031 error.
Change shared_pool_size
SQL> alter system set shared_pool_size=12M;
System altered
Summarize the data. (1)
SQL> select id,sum(cost) from code group by id;
100000 rows selected
|
Data seem to be summarized properly.
Next, I increase the amount of data.
Summarize the data. (2)
Increase the data to 1000000.
SQL> select id,sum(cost) from code group by id;
Error at line 1.
ORA-04031: Unable to allocate 8192 bytes of shared memory ("shared pool",
"unknown object","sort subheap","sort key")
|
ORA-04031 error does occur. Now, I check if there is any free space in the shared pool.
Check the free space in shared pool
SQL> select pool,name,bytes/1024/1024 MB from v$sgastat
where name = 'free memory'
and pool='shared pool';
POOL NAME MB
----------- -------------------------- ----------
shared pool free memory 8.92718124
|
The shared pool has sufficient free space (9MB). However, if the operation continues
for a long time, unnecessary SQL statements are accumulated and sufficient memory
may not be allocated. This is called the shared pool fragmentation.
The following command will clear the shared pool.
SQL> alter system flush shared_pool;
System altered.
|
I perform the process to summarize the data again.
Summarize the data. (3)
After clearing the shared pool
SQL> select id,sum(cost) from code group by id;
Error at line 1.
ORA-04031: Unable to allocate 8192 bytes of shared memory ("shared pool",
"unknown object","sort subheap","sort key")
|
ORA-04031 is not yet resolved by clearing the shared pool.
I focus on four arguments above (i.e. shared pool, unknown object, sort subheap, and sort key).
Argument 1:shared pool: pool where memory attempts to be allocated
Argument 2:unknown object: details of an object to be allocated
Argument 3:sort subheap: type and location of the memory
Argument 4:sort key: memory tag
The problem may be the sort process itself. I will talk about the remedies next week.
That's it for today.
Takuya Kishimoto
|
|
