ora-04301 Fault Handling in Oracle

The last time the database was reported ora-04301, the shared pool size was adjusted a little bit, and recently this error has occurred. So determined to make a good application, mainly the debris is too serious ah.

The code is as follows

Copy Code

Select Ksmchidx, (case When Ksmchsiz >= 65560 then 254 When Ksmchsiz >= 32792 then 253 When Ksmchsiz >= Then 1 When Ksmchsiz >= Then 0 End) bucket#, COUNT (*) Free_chunks, SUM (ksmchsiz) free_space, Trunc (avg (ksmchsiz)) avg_chunk_size From X$ksmsp where ksmchcls = ' free ' Group BY KSMCHIDX, (case When Ksmchsiz >= 65560 then 254 When Ksmchsiz >= 32792 then 253 When Ksmchsiz >= Then 1 When Ksmchsiz >= Then 0 End);

This is the SQL that observes the shared pool fragmentation situation.

The code is as follows	Copy Code
Select Name,value from V$sysstat where name like '%parse% ';

See the resolution, a lot of hard parsing, and soft parsing is not good!

The code is as follows	Copy Code
SELECT substr (Sql_text, 1,) "SQL", count (*), SUM (executions) "Totexecs", Max (sql_id) sql_id From V$sqlarea WHERE Executions < 5 GROUP by substr (Sql_text, 1, 40) Having count (*) > 30 Order by 2; SELECT * from V$sqlarea where sql_id= ' g2b789ppwxjx8 ';

Check for SQL that consumes more shared pool.

The code is as follows	Copy Code
Elect Ksmchcls CLASS, COUNT (ksmchcls) NUM, SUM (ksmchsiz) siz, To_char ((SUM (Ksmchsiz)/count (KSMCHCLS)/1024), ' 999,999.00 ') | | K ' "AVG SIZE" From X$ksmsp GROUP by KSMCHCLS;

Shared pool situation.