We recently increased sga_max_size from 2g to 4g but didn't increase sga_target. Is it OK not to increase sga_target? The reason we wanted to increase sga is to reduce wait time on sequential reads in our db. Should we increase sga_target too?
Below is what we currently have.
NAME TYPE VALUE DISPLAY_VALUE
sga_max_size 6 4194304000 4000M
sga_target 6 1996488704 1904M
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.
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.
