Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2016
    Posts
    2

    Unanswered: library cache hit

    Hi,
    please help with library cache hit ratio.
    What can I do to improve hit? re-size the shared_pool_size (in AMM increase memory_target parameter), change coursor_sharing on FORCE?

    DB1:
    Click image for larger version. 

Name:	db2.jpg 
Views:	0 
Size:	54.4 KB 
ID:	17008Click image for larger version. 

Name:	db2_2.jpg 
Views:	2 
Size:	98.0 KB 
ID:	17009Click image for larger version. 

Name:	db2_3.jpg 
Views:	0 
Size:	23.8 KB 
ID:	17010

    Code:
    select sum(pinhits)/sum(pins) from V$LIBRARYCACHE:
    0,9251
    Code:
    SQL> show parameter cursor
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -----------------------------
    cursor_sharing                       string      EXACT
    cursor_space_for_time                boolean     FALSE
    open_cursors                         integer     300
    session_cached_cursors               integer     20
    SQL>
     
    SQL> select POOL, NAME, round(BYTES/1024/1024) MB from v$sgastat  where pool = 'shared pool' and name='free memory' order by 3 desc;
     
    POOL         NAME                               MB
    ------------ -------------------------- ----------
    shared pool  free memory                        38
     
    SQL>
    DB2:
    Click image for larger version. 

Name:	db3.jpg 
Views:	0 
Size:	51.7 KB 
ID:	17011Click image for larger version. 

Name:	db32.jpg 
Views:	0 
Size:	103.7 KB 
ID:	17012Click image for larger version. 

Name:	db33.jpg 
Views:	1 
Size:	24.5 KB 
ID:	17013

    Code:
    select sum(pinhits)/sum(pins) from V$LIBRARYCACHE:
    0,8978
    Code:
    SQL> show parameter cursor;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    cursor_sharing                       string      EXACT
    cursor_space_for_time                boolean     FALSE
    open_cursors                         integer     300
    session_cached_cursors               integer     20
    SQL> select POOL, NAME, round(BYTES/1024/1024) MB from v$sgastat  where pool = 'shared pool' and name='free memory' order by 3 desc;
     
    POOL         NAME                               MB
    ------------ -------------------------- ----------
    shared pool  free memory                        15
     
    SQL>
    DB3:
    Click image for larger version. 

Name:	db4.jpg 
Views:	0 
Size:	40.4 KB 
ID:	17014Click image for larger version. 

Name:	db41.jpg 
Views:	0 
Size:	49.7 KB 
ID:	17015Click image for larger version. 

Name:	db43.png 
Views:	1 
Size:	23.2 KB 
ID:	17016

    Code:
    select sum(pinhits)/sum(pins) from V$LIBRARYCACHE:
    0,8957
    Code:
    SQL> show parameter cursor;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    cursor_bind_capture_destination      string      memory+disk
    cursor_sharing                       string      EXACT
    cursor_space_for_time                boolean     FALSE
    open_cursors                         integer     300
    session_cached_cursors               integer     50
    SQL>
     
    SQL> select POOL, NAME, round(BYTES/1024/1024) MB from v$sgastat  where pool = 'shared pool' and name='free memory' order by 3 desc;
     
    POOL         NAME                               MB
    ------------ -------------------------- ----------
    shared pool  free memory                       715
     
    SQL>
    Thanks for any sugestions.

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Without knowing what your database does (is it OLTP? Datawarehouse? DSS? etc) it's impossible for us to guess what you're trying to achieve with your database's overall performance. Bear in mind that tuning one thing doesn't neccesarily make everything work better and may lead to problems elsewhere. I suggest you read up on performance tuning, then you will have a better idea of why you want to tune your database & what bits of it need tuning to best suit your needs (and more importantly, how to check that you've not made things worse!).
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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