One fine day i logged in to one of my Production databases for a routine health check and found the database Physical size has grown almost 500GB in just 2 days. That was a biggg shock as the DB growth trend was never like that before. I knew he data itself cannot grow such to such a figure in just two days so why did the tablespaces autoextends with any check.
the first that comes to my mind the recent changes in the database environment and there was only one; the oracle database 11g upgrade from 10g.
The document i mentioned in my title comes to the rescue. It expalined the behaviuor i have just explained. Quoting the exact words
"There are a new feature in 11g that can do this behavior, SMCO (Space Management Coordinator) background process
SMCO coordinates the following space management tasks. It performs proactive space allocation and space reclamation.
The main advantage is that sessions need not wait for reactive space allocation/deallocation operations as this is proactively done, and hence a gain in performance."
I think the Oracle 11g thinks the DBAs are not wise enough to handle space management tasks. So i decided i won't let Oracle database to take control in my authority :) ; and disable the feature.
"This feature can be disabled by the following step:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0;
The feature can be turned on again any time by setting "_enable_space_preallocation"=3 which is the default value:
SQL> ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 3;
NOTE:
- The parameter is dynamic and the scope is ALTER SYSTEM.
- Please use the double quotes as indicated to prevent an ORA-911 because of the leading '_'
"
No comments:
Post a Comment