When wait events like LIBRARY CACHE or LATCH FREE surges in business hours and the database seems to hang, you can check the free memory of shared pool are to down to a lower value. This is caused by a few similar statements generating too many unshared cursors and make library cache full.
To solve these surges on sight, you can try to flush the shared pool.
SQL> alter system flush shared_pool;
Flushing shared pool won't harm you system, but it would keep this performance only for a while, maybe 3 to 5 hours. Therefore, it's just a workaround. A better solution is to find out statements that produced a lot of trivial cursors that consumed shared_pool. Let's see how we find them out.
SQL> column inst_id format 9;
SQL> column SQL format a30;
SQL> column COUNT format 99,999;
SQL> column EXECS format 99,999;
SQL> column username format a8;
SQL> SELECT a.inst_id,
2 SUBSTR(a.sql_text,1,25) "SQL",
3 COUNT(*) COUNT,
4 SUM(a.executions) EXECS,
6 FROM gv$sqlarea a,
7 dba_users b
8 WHERE a.executions < 5
9 AND a.parsing_user_id = b.user_id
10 GROUP BY a.inst_id,
13 HAVING COUNT(*) > 999
14 ORDER BY 1,3 DESC ;
INST_ID SQL COUNT EXECS USERNAME
------- ------------------------------ ------- ------- --------
1 INSERT INTO SH.SH01_SALES10EAG 7,249 7,249 SH
1 INSERT INTO SH.SH01_SALES20EDA 4,416 4,416 SH
1 DECLARE COUNT_MONTH 2,223 2,393 HR
1 SELECT ORDER_ID, SHIPPING_DATE 2,002 2,002 SH
1 INSERT INTO SH.SH01_SALES20EAK 1,307 1,307 SH
2 SELECT BOOK_ID, BAR_CODE_ID, C 8,378 10,146 SH
2 DECLARE SUM_MARKET_ 2,367 2,684 HR
2 SELECT ORDER_ID, CUSTOMER_ID, 2,327 2,783 SH
2 SELECT BOFORE_TAX, AFTER_TAX, 1,466 2,280 HR
2 SELECT COUNT(*) FROM HR.TARGET 1,459 2,952 HR
10 rows selected.
You can see that some statements are similar, but they forced database to do hard parses and produced a lot of trivial cursors. A better way is to make them share and reuse the same cursor by rewriting the statementing with bind variables.
These similar statements are parsed too many times and executed too little times per statement. An ideal cursor sharing should be parsed one time and executed many times in order to save the memory of library cache.
According to the results, we can conclude them into a common feature, that is, they are not using bind variables. Similar INSERT could generates a lot of cursors if they are different in values. Therefore, the first step to tune these statements is to change local variables into bind variables in the program units to reduce literal parsing and make them to share same cursors.