How to find out the real free memory in shared pool?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to find out the real free memory in shared pool?

  1. #1
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29

    How to find out the real free memory in shared pool?

    Dear All,

    Every once a while, my database will get the ORA-04031 error. I would like to write a script to monitor the "real" free memory that is available in the shared pool. By saying "real" free memory, I meant the value of the free memory I got after doing "alter system flush shared_pool". I want to obtain that free memory value without running "alter system flush shared_pool". Is it possible to write any sql to do that?

    Thanks,

    -- Chris

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    what is the oracle version? If it is equal to or more than 10g then are you using ASMM or manual?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29
    We are using oracle 10g, but we are not using ASMM. We would like to monitor the behavior of the shared pool memory. That 's why we are looking for ways to find out the real free memory that is avaiable in the shared pool.

    Regards,

    -- Chris

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    use the below script at your own risk. honestly, i have no idea about the tables / views used in this query or i have never tested the script thoroughly. I have earlier collected it from one of the Burlisons' posts.

    select
    '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX,
    10*trunc(KSMCHSIZ/10) "From",
    count(*) "Count" ,
    max(KSMCHSIZ) "Biggest",
    trunc(avg(KSMCHSIZ)) "AvgSize",
    trunc(sum(KSMCHSIZ)) "Total"
    from
    x$ksmsp
    where
    KSMCHSIZ<140
    and
    KSMCHCLS='free'
    group by
    KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
    UNION ALL
    select
    '1 (140-267)' BUCKET,
    KSMCHCLS,
    KSMCHIDX,
    20*trunc(KSMCHSIZ/20) ,
    count(*) ,
    max(KSMCHSIZ) ,
    trunc(avg(KSMCHSIZ)) "AvgSize",
    trunc(sum(KSMCHSIZ)) "Total"
    from
    x$ksmsp
    where
    KSMCHSIZ between 140 and 267
    and
    KSMCHCLS='free'
    group by
    KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
    UNION ALL
    select
    '2 (268-523)' BUCKET,
    KSMCHCLS,
    KSMCHIDX,
    50*trunc(KSMCHSIZ/50) ,
    count(*) ,
    max(KSMCHSIZ) ,
    trunc(avg(KSMCHSIZ)) "AvgSize",
    trunc(sum(KSMCHSIZ)) "Total"
    from
    x$ksmsp
    where
    KSMCHSIZ between 268 and 523
    and
    KSMCHCLS='free'
    group by
    KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
    UNION ALL
    select
    '3-5 (524-4107)' BUCKET,
    KSMCHCLS,
    KSMCHIDX,
    500*trunc(KSMCHSIZ/500) ,
    count(*) ,
    max(KSMCHSIZ) ,
    trunc(avg(KSMCHSIZ)) "AvgSize",
    trunc(sum(KSMCHSIZ)) "Total"
    from
    x$ksmsp
    where
    KSMCHSIZ between 524 and 4107
    and
    KSMCHCLS='free'
    group by
    KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
    UNION ALL
    select
    '6+ (4108+)' BUCKET,
    KSMCHCLS,
    KSMCHIDX,
    1000*trunc(KSMCHSIZ/1000) ,
    count(*) ,
    max(KSMCHSIZ) ,
    trunc(avg(KSMCHSIZ)) "AvgSize",
    trunc(sum(KSMCHSIZ)) "Total"
    from
    x$ksmsp
    where
    KSMCHSIZ >= 4108
    and
    KSMCHCLS='free'
    group by
    KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);
    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29
    Dear Vijay,

    Thanks for your suggestion. I will try it out to see if it is helpful.

    Regards,

    -- Chris

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