View shared pool usage
Select S.Pool Name, Round (TO_NUMBER (P.Value) / 1024, 2) || 'K' "Size", Round (S.Bytes / 1024, 2) || 'K' "Free", Round ((( S.BYTES / P.VALUE, 4) * 100 "Percentfree (%)" from v $ SGASTAT S, V $ Parameter P where s.name = 'free memory' and p.Name = 'shared_pool_size' and s.pool = 'Shared Pool' UnionSelect S.Pool Name, Round (TO_NUMBER (P.Value) / 1024, 2) || 'K' "Size", Round (S.Bytes / 1024, 2) || 'K' "Free ", Round ((s.bytes / p.value), 4) * 100" Percentfree (%) "from v $ SGASTAT S, V $ Parameter P where s.name = 'free memory' and p.name = 'large_pool_size 'and spool =' large pool '
Analysis of problems encountered, solve
A few days ago, the business host Alert log has a newspaper ORA-04031 error, but Shared_Pool_SIZE is already big enough, after analysis, what we want is because the program is not good, it is not possible to repeat the call, resulting in frequent Sexually SQL compile, so that there is too much fragment in Shared Pool, causing no continuous shared pool to be used, raising ORA-04031;
Our solution is to execute the alter system flush shared_pool; command to refresh Shared_Pool, which clear all content in Shared_Pool is cleared in the appropriate time of every day.
What needs to be explained is that this is not a good method, but we have no better way to solve such problems yet.
============ Alert_ora8.log error info ========================================1u Jan 2 14:52:17 2003 ErrorS in file /oracle/app/oracle/admin/ora8/udump/ora_2492.trc:ora-04031: Unable to allocate 4200 bytes of shared memory, "user_Objects", " SGA HEAP "," library cache ") ============ alert_ora8.log error info ======================== ======== Phoenix1> ORR ORA 403104031, 00000, "Unable to allocate% s bytes of shared memory (/"% s / ", /"% s / ", /"% s "// * 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 // INIT.ORA parameters "shared_pool_reserved_size" and // "shared_pool_size" .// If the large pool is out of memory, increase the INIT.ORA / / Parameter "large_pool_size".
Phoenix1> grep pool initora8.ora | grep -v "#" shared_pool_size = 2048000000
Phoenix1> Su - cyxphoenix1> su-phoenix1: / backup / cyx # crontab -l | more30 7,12 * * * su - oracle8 -c /backup/cyx/altersystem.sh> /backup/cyx/altersystem.log 2> > /backup/cyx/altersystem.log &
Phoenix1: / backup / cyx # cat * shssqlplus / nolog Observe your session, what do they do? Also, through select * from v $ db_Object_cache where share_mem> 10000 You can know which views use memory data more than 1000, Share_Poor_SZIE.
