DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: library cache/ Shared pool flush/ Open Cursor

  1. #1
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    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 ??





  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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;

  3. #3
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    yes I found some queries whose count is > 20, but what it implies. Is it implies hard parse ??

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  5. #5
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    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.

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width