Printable View
Library cache miss ratio is very high ,95% of shared pool is in use if I want to flush shared pool without shuting down database and (while activity going on) what is the effact.
Will it create problems ?
Is there any way to find out problem queries or which queries is running what time.
Or Which session is creating contentation ??
It's not good value:
May be you shold:
1. increase OPEN_CURSORS parameter
if it will has not effect then:
2. increase SHARED_POOL_SIZE
and check count of sql statments :
select substr(sql_text,1,40) txt, count(*)
from v$sqlarea
group by substr(sql_text,1,40)
having count(*) > 20;
yes I found some queries whose count is > 20, but what it implies. Is it implies hard parse ??
These sql reload many times and each statment :
- should be reparse
- use shared memory
Your programmers should rewrite some application and use bind variables if it possible.
--------------------------------------------------------
You may set parameter
CURSOR_SPACE_FER_TIME = FALSE (if it set to TRUE)
PS: and check OPEN_CURSORS - this is first.
Application is written in pyhton and it is generating dynamics queries. All queries use bind variobles only.
still re-load and hard parse is high.
Increase first the value of your shared pool size, and open cursors.
Bounce back you db, execute the application again and
query the v$sesstat joined with v$session_event to see the specific session's query waits if its still high.
You might have to query the v$sqlarea to see how much buffer gets during parsing the sql consumes.