Shared pool understanding.
At my site one performance tuning guys said that when query enter into shared pool it searches complete shared pool during parsing to find its hash value. Since 70% percent queries which resides in shared pool have execution =1, so he recommended to flush the shared pool every night. So that queries has to search less shared pool area till other tuning activities(like bind variable,cursor_sharing) gets implemented. Our dba started doing that and we see gain in performance. Earlier simple "sqlplus sys as sysdba" use to take lot of time after database crosses 24hrs run window. Now such things not happening. Infact 'latch: library cache' wait event also gone down.
Now my question is:
1. Is he correct with his statement that query searches complete shared pool area to find it hash value presence in shared pool? If yes then how we can verify that query is searching 40G of shared pool area(in our env sharedpool_size=40G).
2. Hash value is managed by link list(guessing) so whether shared_pool_size=1G or shared_pool_size=100G how it matter.
3. How to decide when to reduce shared pool? Any awr event we need to monitor?
4. Which event of awr report we should check other than 'latch: library cache' to see the shared pool usage?
Oracle Version : 10.2.0.5
Sga_target=110G
shared_pool_size=5G
sharedpool_size=40G
db_cahe_size=14G
dbcache_size=60G
Thanks and Regards,
Rajib Sutradhar