I'm sure that there is a way to tell, via querying some X$ table or other, but my purely-speculative and uninformed guess would be that "flush" simply means "deallocate inactive" and does not include defragmenting as well, which would involve relocating everything remaining to leave all free space in a contiguous area. Deallocating inactive data structures is relatively painless, as I imagine very little synchronization has to occur.
The reason I believe this is based on the my appraisal of what would have to happen to implement defragmentation functionality in a multiuser environment. The Shared Pool would have to be completely and exclusively locked for the relocation of data structures. If you look at all the X$ tables (and some of the V$ views), there are a lot of ADDR (address) columns -- I expect those would have to be updated, and that implies further locking of process- and session-specific data structures, some of which may be in the SGA and others of which might be totally out of reach, residing in the PGA or UGA which (on some platforms and configurations) in "private" process memory.
Just my $0.02... Quoting Douglas Cowles <dcowles@xxxxxxxxxx>:
The errors to appear to be citing the "shared pool" - which raises the question, doesn't flushing the shared pool defragment the shared pool? Tim Gorman <tim@xxxxxxxxx> To 07/15/2008 12:02 Douglas Cowles/Southbury/IBM@IBMUS AM cc oracle-l@xxxxxxxxxxxxx Subject Please respond to Re: rman Ora-4031 - how to flush tim@xxxxxxxxx large pool? Doug, The text following the ORA-04031 error message often states what part of the SGA has run out of space (i.e. shared pool, large pool, etc). The "Large Pool" essentially flushes itself all the time -- all data structures there are transient, and are deallocated/destroyed when no longer in active use. As a result, there is no ALTER SYSTEM FLUSH LARGE_POOL command. Query V$SGASTAT where POOL = 'large pool' to see what is present at any point in time. If the query returns no rows, then nothing is present there. By the way, RMAN doesn't use the Large Pool unless some kind of inter-process communication is going on between backup channels and I/O channels (i.e. duplexed backups, etc). Unless you've specified duplexed backups, RMAN probably isn't using the Large Pool. Since RMAN is largely implemented through PL/SQL packages, it is likely that your ORA-04031 is occurring in the Shared Pool. Read the error message a little more closely... Hope this helps... Tim Gorman consultant - Evergreen Database Technologies, Inc. P.O. Box 1802, Evergreen CO 80437-1802 website = http://www.EvDBT.com/ email = Tim@xxxxxxxxx mobile = +1-303-885-4526 fax = +1-303-484-3608 Yahoo IM = tim_evdbt Douglas Cowles wrote:We have a system that is throwing ORA-4031's in rman archive log backups after the system has been up a few days. To my understanding, RMAN tries to use the large pool for it's activities. When we alter system flush shared pool - the errors do not stop - the database has to be bounced or the large_pool has to be increased 3M or so. Does alter system flush shared_pool flush the large pool? If it does, then why would that not defragment the pool? If it does not, is there a way to do it? In general, will flushing the shared_pool defragment it - large pool or otherwise? Thanks, Doug C.
-- //www.freelists.org/webpage/oracle-l
