This discussion is archived
1 2 3 Previous Next 41 Replies Latest reply: Jan 23, 2013 3:20 AM by Rob_J Go to original post RSS
  • 15. Re: Database Memory Usage
    jgarry Guru
    Currently Being Moderated
    You might want to grep Huge /proc/meminfo just to be sure you aren't using Hugepages.
  • 16. Re: Database Memory Usage
    User328666 Newbie
    Currently Being Moderated
    Oracle is not running out of memory unless you see something in the database alert log. If you don't see anything in the alert log, then relax. Oracle might "want" more memory, but that's a performance tuning thing and not a "we are going to crash" thing. In statspack look at the Buffer Pool Advisory and it will tell you if more memory is wanted. The same information is available in OEM (it's been five years since the last time I saw an Oracle 9i database, but I think the OEM 8i and higher included the SGA and PGA memory advisors).

    The thing that runs out of memory is the host operating system. On Linux look at the file /proc/meminfo. For example:

    cat /proc/meminfo | grep -i free
    cat /proc/meminfo | grep -i huge

    As a general rule your SGA + PGA should not exceed 60% of installed RAM. There are several reasons for this:
    1. At run time Oracle will allocate as much memory to the PGA spaces as it needs and is not bound by pga_aggregate_target, so you need a lot of free memory.
    2. The o/s and any apps on the server need memory too.
    3. The less free memory you have, the more agressive the paging daemon gets and it will swap large chunks of your db buffer cache to disk. You'lll still see cache hits in statspack but performance will be poor because the cache hits are actually occurring in the swap file.

    Given you are using 64-bit RHEL 5.6 you should have HugePages configured as this prevents all shared memory segments from swapping (pins the SGA in memory, but does not help PGA spaces). Then you could increase your SGA + PGA settings to 80% of installed RAM.
  • 17. Re: Database Memory Usage
    jgarry Guru
    Currently Being Moderated
    I've gotten a bit lost among all this, are we sure it's 64-bit?
  • 18. Re: Database Memory Usage
    yxes2013 Newbie
    Currently Being Moderated
    Thanks all,

    Given:
    Since memory tuning consist small effect to performance issue since we have 12Gb physical RAM and 12 core CPU.
    So let us discard cpu and memory as the cause of performance issue.

    Just for the sake of argument. That you are in my place and the user complains that the their process is very slow.
    Given also is the statspack report and vmstat report.

    What are other things you will do?

    check I/O bottleneck?
    check table locking?
    check poor sql?

    We can discard poor sql bacause there are days that with the same processes the performance is okay.

    So what is left is I/O bottleneck and table locking? ( I can see this in vmstat and v$locks)

    But why is that even if we shutdown the servers to release locks, sometimes it still hangging after its started. I seems there are also jobs being revived when the server is bounced, so weird the behavior. Maybe the rollbacks of aborted transactions? Or some hidden packages
    created by the application. How can I find hidden process the has been revived along with the server start?

    Are there other things I missed?

    Edited by: yxes2013 on Jan 16, 2013 10:40 AM
  • 19. Re: Database Memory Usage
    yxes2013 Newbie
    Currently Being Moderated
    I've gotten a bit lost among all this, are we sure it's 64-bit?
    Its is not 64 bit but the memory is sufficient because the is only 20 users. They even halt just to give way to the reports and invoice processing. Besides the statspack says no memory issue and the alert log does not show of memory problems too.
  • 20. Re: Database Memory Usage
    sb92075 Guru
    Currently Being Moderated
    ALTER SESSION SET SQL_TRACE=TRUE;

    if the SQL above is issued at the start of any session, the resultant trace file will show where time is being spent.

    you can also produce EXPLAIN PLAN for all SQL executed by this session.

    when you don't know where time is being spent, you can only guess at what needs to be changed.
  • 21. Re: Database Memory Usage
    Justin Cave Oracle ACE
    Currently Being Moderated
    yxes2013 wrote:
    Thanks all,

    Given:
    Since memory tuning consist small effect to performance issue since we have 12Gb physical RAM and 12 core CPU.
    So let us discard cpu and memory as the cause of performance issue.
    Well, if we're assuming that this is a continuation of your previous thread, you have 12 GB of physical RAM but you are somehow running a 32-bit database with an SGA that, if memory serves, is only a couple hundred MB and a PGA that is not large enough for your sorts. The fact that your server has 12 GB of physical RAM is irrelevant to determining whether you have a memory bottleneck.
    Just for the sake of argument. That you are in my place and the user complains that the their process is very slow.
    Given also is the statspack report and vmstat report.

    What are other things you will do?
    Look at the Statspack report. Determine what is slow. Determine why it is slow. Determine how to make it less slow.
    check I/O bottleneck?
    If the Statspack report indicates an I/O bottleneck,
    check table locking?
    If the Statspack report indicates lock contention
    check poor sql?
    If the Statspack report indicates poor SQL

    We can discard poor sql bacause there are days the with the same process the performance is okay.
    Again, if we assume that this is continuing your prior thread, it has been pointed out that the "good" and "bad" days have very different sets of SQL. That "bad" report appeared to have a bunch of ad hoc SQL statements coming from Excel in addition to whatever your application is doing.
    So what is left is I/O bottleneck and table locking? ( I can see this in vmstat and v$locks)
    It makes no sense to discard all the options that you have discarded. Particularly when you've been told in the earlier thread all this information.
    But why is that even if we shutdown the servers to release locks, sometimes it still hangging after its started. I seems there are also jobs being restarted when the server is bounced, so weird the behavior.
    How can I find hidden process the has been revived along with the server start?

    Are there other things I missed?
    It seems likely that the system is slow, not hung. If the system is slow after a restart, it could be that people are simply restarting their ad hoc Excel queries once the system is back online causing the system to be slow. It could be a bunch of other things as well, though. You would need a Statspack report to tell you what was actually taking all the time.

    Justin
  • 22. Re: Database Memory Usage
    yxes2013 Newbie
    Currently Being Moderated
    Thanks all,

    I will try that since that is the one not yet been performed tracing. But I think I can only do it on database level because there are lots of programs being run in background where you can not set at sqlplus session level.
  • 23. Re: Database Memory Usage
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    Thanks all,

    I will try that since that is the one not yet been performed tracing. But I think I can only do it on database level because there are lots of programs being run in background where you can not set at sqlplus session level.
    An intelligently written LOGON trigger can issue the ALTER SESSION command for only specific sessions.
  • 24. Re: Database Memory Usage
    Rob_J Journeyer
    Currently Being Moderated
    yxes2013,

    I could be completely wrong here, but you've started three threads in the past few weeks which are pretty much all about the same topic but worded slightly differently. They all come back to the same point - your performance is "slow" and you want to know how to fix it. You've had so many people giving you good advice and it doesn't seem like you've implemented most of it?

    I'd suggest you work through all of the information in the threads that are many pages long and look to try/test the suggestions people have made. You've had advice from some very experienced and reputable sources, too.

    As was pointed out again in this thread, you're system based on previous statspack reports had a db_cache_size of a couple of hundred MB, there were many different and PGA consuming queries running on a bad day of performance and I'm not sure that you've resolved those issues yet?

    Rob
  • 25. Re: Database Memory Usage
    jgarry Guru
    Currently Being Moderated
    We're trying to help you here, but you have to help us help you by answering our questions and giving feedback on what you've tried and how it has made a difference.

    For more general tuning advice there are some good books. Cary Millsap's book is a good start (though it does assume the system is somewhat close to configured right - google Method-R). I always thought Lawson's Art & Science book was a good intro for newbies. MOS has some docs on the Oracle Performance Tuning Method or some such thing. There are other books good and bad, and things have changed a lot in modern versions. The performance tuning guide for your version is useful, no reason you can't dive into that right away, but it has limitations too, including the fact that just because something is in there, doesn't mean it's good for your situation.

    You may also have a situation where the tuning for the general apps needs to be different than the tuning for reports. Having everyone else stop while reports and invoicing is run may actually be a good idea if the programs aren't written to properly use Oracle's features, but that's a business judgement.

    Don't forget, if you restart the system, Oracle has to roll forward and then rollback uncommitted transactions. You can control that somewhat ahead of time, that's what the MTTR advisory is for.
  • 26. Re: Database Memory Usage
    yxes2013 Newbie
    Currently Being Moderated
    Hi jq and all,
    We're trying to help you here, but you have to help us help you by answering our questions and giving feedback on what you've tried and how it has made a difference.
    Thanks for all the support your time, It is just that I do not know what to do. I just wish I can evade from this "nightmare". With all the advises, I do not know which one to take. Everyday the issue is getting worst. Still I dont have any concrete steps on how to find the issue.
    Please dont be mad at me for being redundant. I sent the trace to the support and still he can not find the problem.
  • 27. Re: Database Memory Usage
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    I understand that you are having problems, so how about re-reading all the information in the previous three threads, and compiling a list of possible actions. Just a bullet point list. Also, compile a list of your problems and then compare the risk to reward for each and see which ones to test.

    Based on what I can remember, making sure that you have a large enough SGA and PGA are the first steps because you were only using 288MB of db_cache and a similar amount for the PGA, which is tiny, and you mentioned that you had 1.7GB to use so it might not be being used correctly. Sizing the memory correctly is a good first step.

    Find out if SGA_TARGET and PGA_AGGREGATE_TARGET can be used in 9i, first.

    Have you made any changes yet based on the recommendations that people have given you?
  • 28. Re: Database Memory Usage
    yxes2013 Newbie
    Currently Being Moderated
    Hi Rob,

    I think those are for parameters are for 11g and not in 9i.

    I check all the occurrences of the word "SGA" in our init.ora and I found these:
    pga_aggregate_target=4G
    olap_page_pool_size     =          4194304
    sga_max_size = 1600M
    _kghdsidx_count=1
    I also observe that if I run "ps -ef" I see lots of process even if the there is no one
    oraprod   6620     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6622     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6624     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6626     1  0 10:47 ?        00:00:04 oracleOAPROD (LOCAL=NO)
    oraprod   6628     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6630     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6632     1  0 10:47 ?        00:00:03 oracleOAPROD (LOCAL=NO)
    oraprod   6634     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6640     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6642     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6644     1  0 10:47 ?        00:00:02 oracleOAPROD (LOCAL=NO)
    oraprod   6648     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6650     1  0 10:47 ?        00:00:19 oracleOAPROD (LOCAL=NO)
    oraprod   6716     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6718     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6720     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6722     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6724     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6726     1  0 10:47 ?        00:00:03 oracleOAPROD (LOCAL=NO)
    oraprod   6728     1  0 10:47 ?        00:00:04 oracleOAPROD (LOCAL=NO)
    oraprod   6730     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6732     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6734     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6736     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6738     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6740     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6742     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6744     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6746     1  0 10:47 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6752     1  0 10:48 ?        00:00:05 oracleOAPROD (LOCAL=NO)
    oraprod   6762     1  0 10:49 ?        00:00:32 oracleOAPROD (LOCAL=NO)
    oraprod   6764     1  0 10:49 ?        00:00:02 oracleOAPROD (LOCAL=NO)
    oraprod   6766     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6768     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6770     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6772     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6774     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6776     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6780     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6782     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6784     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6786     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6788     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6790     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6792     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6794     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6796     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6798     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6800     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6802     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6804     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6806     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6808     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6810     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6812     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6822     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6836     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6838     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6842     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6856     1  0 10:49 ?        00:00:01 oracleOAPROD (LOCAL=NO)
    oraprod   6858     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6860     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6864     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6866     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6868     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6872     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6874     1  0 10:49 ?        00:00:02 oracleOAPROD (LOCAL=NO)
    oraprod   6880     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6888     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6890     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6892     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6894     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6896     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6898     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6900     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6902     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6904     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6906     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6908     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6910     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6912     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6914     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6916     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6918     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6920     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6938     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6942     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6960     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6962     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6964     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6974     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6982     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6986     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6988     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6990     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6992     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6994     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6996     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   6998     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   7000     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   7002     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    oraprod   7006     1  0 10:49 ?        00:00:00 oracleOAPROD (LOCAL=NO)
    139 process. will these contribute to performace problem?

    Thanks,
  • 29. Re: Database Memory Usage
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:

    139 process. will these contribute to performace problem?
    yes.

    based upon OS PID it appears that some batch job just started 100+ processes which access the DB;
    OS PIDs are within one of the previous process.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points