| SGA_MAX_SIZE and SGA_TARGET [message #650727] |
Mon, 02 May 2016 06:58  |
 |
ninan
Messages: 163
Registered: June 2011
Location: Noida
|
Senior Member |
|
|
Hi,
As per the AWRs and ADDMs analyzed for our production database it is high on I/O.
There are some SQLs which shows high I/O which needs tuning.
Apart from these the ADDMs report Undersized SGA at peak time as the Top ADDM findings for 30 minutes window.
We have 5 active sessions at this time . 8 CPU with 2 core.
The SGA MAX SIZE and SGA_TARGET both shows 7168MB.
The ADDM finding says :
Finding 1: Undersized SGA
Impact is .42 active sessions, 55.42% of total activity.
--------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "7168 M" during the analysis period.
Recommendation 1: Database Configuration
Estimated benefit is .19 active sessions, 25.24% of total activity.
-------------------------------------------------------------------
Action
Increase the size of the SGA by setting the parameter "sga_target" to
8064 M.
My question is since both SGA_MAX_SIZE and SGA_TARGET is 7168MB can we increase the SGA_TARGET beyond the SGA_MAX_SIZE of 7168MB without increasing the SGA ?
Can this be done without restarting the Database as it is a production.
Or are we overlooking at these figures is any other process causing the ADDM to show SGA as the issue ?
init.ora settings
Parameter Name
Begin value
End value (if different)
O7_DICTIONARY_ACCESSIBILITY FALSE
_use_single_log_writer TRUE
aq_tm_processes 5
audit_file_dest /u01/app/oracle/admin/mxx/adump
audit_sys_operations TRUE
audit_trail DB, EXTENDED
compatible 12.1.0
control_files /ora_1/oradata/mxx/data/mxx_control01.ctl, /ora_1/oradata/mxx/data/mxx_control02.ctl
db_block_size 8192
db_domain
db_name mirev
deferred_segment_creation FALSE
diagnostic_dest /u01/app/oracle
event
filesystemio_options SETALL
java_jit_enabled TRUE
job_queue_processes 100
local_listener (ADDRESS=(PROTOCOL=TCP)(HOST=dbora103.unix.csob.cz)(PORT=1526))
log_archive_dest_1 LOCATION=/ora_arch/oradata/mxxx/arch
log_archive_format mxx_%t_%s_%r.arc
log_archive_min_succeed_dest 1
nls_length_semantics CHAR
open_cursors 600
os_roles FALSE
parallel_max_servers 64
pga_aggregate_target 1073741824
processes 500
recyclebin OFF
remote_login_passwordfile EXCLUSIVE
remote_os_roles FALSE
sec_return_server_release_banner FALSE
sessions 776
sga_max_size 7516192768
sga_target 7516192768
sql92_security TRUE
undo_tablespace UNDOTBS1
Thanks,
Ninan.
[Updated on: Mon, 02 May 2016 07:05] Report message to a moderator |
|
|
|
| Re: SGA_MAX_SIZE and SGA_TARGET [message #650729 is a reply to message #650727] |
Mon, 02 May 2016 07:24  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
No, you cannot raise the sga_target beyond the sga_max_size. You'll have to adjust them in the spfile and restart.
However, there is probably little point in doing this. In my experience, raising SGA by that sort of amount is unlikely to have an effect. You would be better off tuning the problem SQLs.
Why have you changed _use_single_log_writer from the default of ADAPTIVE?
|
|
|
|
|
|
| Re: SGA_MAX_SIZE and SGA_TARGET [message #650735 is a reply to message #650733] |
Mon, 02 May 2016 08:56 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
If you are not the DBA for this database, I would hope that you do not have permission to change any parameters. What you may be able to do is tune the SQL. You say that you have identified the problem statements, so start working on them. How long do they take? How much faster do you need them to be? What are the execution plans?
|
|
|
|
|
|
|
|
|
|
| Re: SGA_MAX_SIZE and SGA_TARGET [message #650742 is a reply to message #650738] |
Mon, 02 May 2016 14:35 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
ninan wrote on Mon, 02 May 2016 16:36When you say tuning the SQL for I/O do we need to consider tuning the I/O first.
i.e, http://docs.oracle.com/cd/A87860_01/doc/server.817/a76992/ch20_io.htm
or rewrite the SQL based on the SQL execution plan.. No, you do not tune IO. You tune SQL. Ask yourself a simple question: "what are your users complaining about?" I do not think they are complaining that "the IO is too high". They are more probably complaining that "this report takes too long" or "this screen refresh is too slow" or "the overnight batch jobs don't finish until lunchtime". You have to focus on the business problem. Have you identified it yet?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SGA_MAX_SIZE and SGA_TARGET [message #650867 is a reply to message #650866] |
Thu, 05 May 2016 11:09 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Thu, 05 May 2016 17:50
Maybe you should have a look at MOS, this is the only place you will have sure information about them.
And if you don't know then the very first thing to do is to remove the parameter.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
