 Problem ORA-04031 was solved and spfile changed. [message #166841] |
Mon, 10 April 2006 00:01  |
|
|
The problem was ora-04031 large_pool_size with sort and session heap. For the mentioned problem I gone through the following approach.
1) Made the workarea_size_poilcy =Auto.
2) set the Pga_aggregate_target=120M
Which solved the problem again but as pga_aggreagate_target was set high for the problem I again reset it to small size but problem rises due to which I gone through following.
3) Resized sort_area_size which automatically resized the heap-area_size. Which again was not sufficient for the solution.
4) As the final solutino for the problem I have resize Shared_pool_size which requires sga_max_size to be reset because limit was already full and shared pool size and large pool size can be changed upto to the maximum size of sga max size.
5) restarted the server.
But, when I checked the changed parameter they are as it is which were set earlier. Shutdown and restart of the machine rollbacked the changes.
Pre specification of the Oracle server -
workarea_size_policy=False
pga_aggreagate_target not set
sort_area_size = 524288
heap_area_size=1048576
sga_max_size=118255568
Shared_pool_size=46137344
Post server Configuration -
workarea_size_policy=auto
pga_aggreagate_target=120M
Rest are same as before.
I have used the following command to alter the field.
alter system set parameter=value scope=spfile;
for the ach parameter change.
I am using spfile but unable to locate the location of the spfile, as it is known to me default loacation is oracle_home/database/. And there isnt any location of spfile was spcified apart from that.
Can any body tell me what is the reason for such behavior. Is such type of problem can be solved by periodic restart of the Oracle server machine.
waiting for all your replys.
regds
Amit
|
|
|
|
| Re: Problem ORA-04031 was solved and spfile changed. [message #166938 is a reply to message #166841] |
Mon, 10 April 2006 07:37  |
|
|
Hi, I again checked the problem and found that pfile was used for the database.
And problem was rises due to the parameter parallel_automatic_tuning was set to false which in result forcing the server to use shared pool even if large pool was available.
After that I have made parallel_automatic_tuning to true and increased the size of large pool by 10MB and shared pool by 20MB.
This made a great change in performance at my end. I am running a development sort of database.
regds
Amit
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
