Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We recently increased sga_max_size to 4g from 2g but didnt increase sga_target, is it ok not to increase sga_target?? reason we wanted to increase sga is to reduce wait time on sequential reads in our db..should we increase sga_target too?? please advice.

Below is what we have currently..

NAME TYPE VALUE DISPLAY_VALUE sga_max_size 6 4194304000 4000M sga_target 6 1996488704 1904M

share|improve this question
    
Specifying the Oracle version would also be helpful. If you're on 11g, for example, you may be better served setting MEMORY_TARGET rather than the separate SGA_TARGET and PGA_TARGET. –  Justin Cave Mar 2 '11 at 21:58
    
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production..Thanks. –  Sree Mar 3 '11 at 17:03

2 Answers 2

It is perfectly valid to increase the SGA_MAX_SIZE without changing the SGA_TARGET though it probably isn't going to change your performance. Setting the SGA_MAX_SIZE to a larger value allows you to adjust the SGA_TARGET upward without restarting the database. It won't affect the actual amount of RAM allocated to the SGA, that's controlled by the SGA_TARGET. If your intention was to increase the size of the SGA from 2 GB to 4 GB, you need to adjust the SGA_TARGET.

share|improve this answer

Oracle introduce automatic SGA management since Oracle 9i. This mean you do not have to size the following components: DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE. The size of memory utilized for automatic SGA management is specified by SGA_TARGET which can be adjusted dynamically up to the maximum size of SGA which is specified by SGA_MAX_SIZE that can be only changed by restarting the instance.

You want to minimize db sequential read of your query. I'm afraid by just changing SGA might not increase your query performance. I suggest you check out Ask Tom - db sequential read.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.