Page 1 of 5 123 ... LastLast
Results 1 to 15 of 74
  1. #1
    Join Date
    Sep 2004
    Location
    Bayamon, Puerto Rico
    Posts
    37

    Question Unanswered: Problems with SGA

    Hi everybody!

    I've been trying to configure my Oracle to use more memory (RAM) from my operating system without success. My hardware configuration is: Dell, 4 Intel Xeon processors @ 800mhz, 8gb RAM, Windows 2000 Advance Server, Oracle 8.1.7, Raid 1 for operating system and Oracle and Raid 5 for Data.

    The operating system is already recognizing my 8gb of RAM with the /PAE flag. Apparently Windows 2000 has assigned to Oracle more than 2gb of RAM according to the imagecfg utility, but my SGA is always limited to near 2gb and when I try to configure more than 1.5gb to the SGA my database become un-stable and the performance is very low. Right now Oracle is configure with 1546Mb of SGA and my Processor utilization is 100% constantly. I have enough space of physical disk and RAM and it seems that Oracle is not able to handle more than 2gb of RAM.

    I have a lot of documents from Oralce but none of them has given me the correct information of how to handle and configure SGA. Any Ideas...

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What proof do you have that increasing the size of the SGA is necessary or desirable?
    IIRC, in the past the default maximum size for the SGA on Solaris was 1.75GB.
    To allow the SGA to be larger than that you had to modify some "config" file & re-link Oracle all of which is/was documented on MetaLink.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2004
    Location
    Bayamon, Puerto Rico
    Posts
    37

    Exclamation Sga!!!

    Quote Originally Posted by anacedent
    What proof do you have that increasing the size of the SGA is necessary or desirable?
    IIRC, in the past the default maximum size for the SGA on Solaris was 1.75GB.
    To allow the SGA to be larger than that you had to modify some "config" file & re-link Oracle all of which is/was documented on MetaLink.
    My application is a critical one. I work for the Electric Power Authority in Puerto Rico and the application is the one who handles readings from the client meters. It is always updating, requesting and inserting information to the database every time a meter is read. I'm focus on getting a bigger SGA because my page faults are too high and if I have a server with 8gb of RAM, why not to take advantage of this. No configuration has given me a satisfaction in performance. Sometimes I think that maybe lowering the sahred_pool_size and the buffers_cache will increase the performance but I'm not too sure.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Ready, Fire, AIM!
    Stop & think about your observations.
    Page faults happen at the OS level (Regardless of OS; Windoze or *nix).
    The SGA, by definition is FIXED size; regardless of what that size may be.
    The best you could do is trade off SGA size/space for OS filesystem "cache".
    By that I mean if you increase the size of the SGA, by definition you reduce the size of the OS filesystem cache. How do you benfit from that?
    I suspect that you are focusing on the symptom rather than the root cause.
    I suspect that your application suffers from too many full table scans.
    I suggest that you enable event 10046 at level=8 for some of your worst performing operations, run the results thru TKPROF.
    This will SHOW you where the time is being spent/wasted.
    With concrete proof of problem area(s), you then need to figure out a way to eliminate the bottleneck.
    I'm will to bet you that if you are successful at increasing the SGA size, you application will not see any noticable improvement.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2004
    Location
    Bayamon, Puerto Rico
    Posts
    37
    anacedent,
    I'm new to Oracle 8i. I was an administraor in Sybase, so I'm sorry to ask, what you mean by "I suggest that you enable event 10046 at level=8 for some of your worst performing operations, run the results thru TKPROF."

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    GOOGLE is your friend.
    Peter Finnegan has a fine website which details some How To's.
    http://asktom.oracle.com & do a keyword search on "10046"
    http://www.hotsos.com is the site with good info on this topic
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2004
    Location
    Bayamon, Puerto Rico
    Posts
    37

    Thumbs up Thanks!!!

    Quote Originally Posted by anacedent
    GOOGLE is your friend.
    Peter Finnegan has a fine website which details some How To's.
    http://asktom.oracle.com & do a keyword search on "10046"
    http://www.hotsos.com is the site with good info on this topic
    I will look closely to this recommendation, thanks.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Keep in mind that all your RAM really IS being used now ( and then some);
    otherwise you would not be seeing all the page faults.
    If you could hold all the neccesary data for the application in RAM,
    your system would NOT be paging itself to death.
    The real question which needs to be answered is,
    what changes can be made to reduce the flow of data from disk into RAM.
    The system which hosts the database should have no other applications running on it; none, zero; including anti virus s/w!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Sep 2004
    Location
    Bayamon, Puerto Rico
    Posts
    37

    Exclamation

    Quote Originally Posted by anacedent
    Keep in mind that all your RAM really IS being used now ( and then some);
    otherwise you would not be seeing all the page faults.
    If you could hold all the neccesary data for the application in RAM,
    your system would NOT be paging itself to death.
    The real question which needs to be answered is,
    what changes can be made to reduce the flow of data from disk into RAM.
    The system which hosts the database should have no other applications running on it; none, zero; including anti virus s/w!
    Running without antivirus is not permitted in my company as a regular rule because it might compromise other systems. Right now the operating system is free of other programs. Just several services that reads from the database are constantly interacting with my Oracle DB, but these services are running on another server.
    One thing I notice is that I'm running this server in a multipurpose server and don't know if this is the correct option for an application that needs rapid response from these services.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Stupid policy!
    There should NEVER be incoming email messages delivered to a system hosting a database.
    A database can't read incoming emails.
    The DBA should be reading email from a system other than the DB server.
    If no emails ever get delivered to the DB server system, there is no need for anti-virus s/w on it.
    Rhetorical questions -
    Is the anti-virus s/w "checking" the Oracle data as it comes off the disk into RAM?
    What happens to application performance if the AV s/w is shutdown (as a test)?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Sep 2004
    Location
    Bayamon, Puerto Rico
    Posts
    37

    Talking

    Quote Originally Posted by anacedent
    Stupid policy!
    There should NEVER be incoming email messages delivered to a system hosting a database.
    A database can't read incoming emails.
    The DBA should be reading email from a system other than the DB server.
    If no emails ever get delivered to the DB server system, there is no need for anti-virus s/w on it.
    Rhetorical questions -
    Is the anti-virus s/w "checking" the Oracle data as it comes off the disk into RAM?
    What happens to application performance if the AV s/w is shutdown (as a test)?
    That is a good question... Damn the network administrator is not here with me. I will try by myself to run without antivirus and see what happens.

    Do you think running in multipurpose mode DB and swithching the OS to run better for background services are good ideas?

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Do you think running in multipurpose mode DB and swithching the OS to run better for background services are good ideas?
    Why do you think Oracle knows, cares or behaves differently depending upon the mode in which it gets invoked?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Sep 2004
    Location
    Bayamon, Puerto Rico
    Posts
    37

    Exclamation How>>>...?

    Quote Originally Posted by anacedent
    >Do you think running in multipurpose mode DB and swithching the OS to run better for background services are good ideas?
    Why do you think Oracle knows, cares or behaves differently depending upon the mode in which it gets invoked?
    What setting did you recommend for a system that is always changing and every night the 'meter reading process' is executed to read about 150,000 clients per night? That means, that the system will not accept any user connection, just the service that reads electric power meters. Is there any way to change the nature of the Oracle DB, I mean, from a multipurpose database to any other setting?

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It depends.
    Can you bounce the DB twice a day?
    The parameters which have the greatest impact on performance typically only get applied when the DB starts up.
    You might benfit from running STATSPACK to gain hard performance numbers.
    It depends upon where the bottlenecks are in the system and application.
    You may be able to obtain acceptable performance with a single collection of initSID.ora values.
    Then again it may be optimal to have a one file which are "interactive" values & other for batch/night processing.
    Without having any actual performance number to base any possible change against, you'll be shooting in the dark & hoping to get lucky.
    Ideally you can set the event 10046 in in the initSID.ora file and collect complete performance values for a whole day.
    Once you have all the trace files, proecess then thru TKPROF, you'll be able to easily identify where the poorly performing SQL is.
    Hopefully at that point you can make changes to turn the "bad" SQL into good SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Sep 2004
    Location
    Bayamon, Puerto Rico
    Posts
    37
    Quote Originally Posted by anacedent
    It depends.
    Can you bounce the DB twice a day?
    The parameters which have the greatest impact on performance typically only get applied when the DB starts up.
    You might benfit from running STATSPACK to gain hard performance numbers.
    It depends upon where the bottlenecks are in the system and application.
    You may be able to obtain acceptable performance with a single collection of initSID.ora values.
    Then again it may be optimal to have a one file which are "interactive" values & other for batch/night processing.
    Without having any actual performance number to base any possible change against, you'll be shooting in the dark & hoping to get lucky.
    Ideally you can set the event 10046 in in the initSID.ora file and collect complete performance values for a whole day.
    Once you have all the trace files, proecess then thru TKPROF, you'll be able to easily identify where the poorly performing SQL is.
    Hopefully at that point you can make changes to turn the "bad" SQL into good SQL.
    My boss accept some of the recommendations and he will give me a few days to study the database stats output, but he is still having the open question, why oracle is limiting the database to 2.0gigs SGA if everything is set to use more than that. For example, use_indirect_data_buffers=true, /PAE is configure at the boot.ini, so Windows 2000 is recognizing 8gigs. The only thing I'm missing is a registry entry that I don't know how to enter it and it is about an AWE_WINDOW_SIZE that some experts talk about it and I really don't know if I need it. Everything is reduced to the point that when I try to define in my ora file to use db_block_buffers at a high value the database failed to start.
    Now while I'm focused on a performance study, my boss is still thinking why we bought a server with 8gigs of RAM if Oracle will never be capable to handle more than 2gigs, actually the best configuration was at a top of 1.5gigs.

Posting Permissions

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