Home » RDBMS Server » Performance Tuning » SGA_MAX_SIZE and SGA_TARGET (Unix, ORacle 12C)
SGA_MAX_SIZE and SGA_TARGET [message #650727] Mon, 02 May 2016 06:58 Go to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
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 Go to previous messageGo to next message
John Watson
Messages: 7212
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 #650733 is a reply to message #650729] Mon, 02 May 2016 08:25 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
_use_single_log_writer TRUE is the setting in init.ora . I am not very sure about this setting. What should I be asking to the production DBA . Do we need to change this setting.

Do you see any change in high I/O if we change it to ADAPTIVE.
Re: SGA_MAX_SIZE and SGA_TARGET [message #650735 is a reply to message #650733] Mon, 02 May 2016 08:56 Go to previous messageGo to next message
John Watson
Messages: 7212
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 #650738 is a reply to message #650735] Mon, 02 May 2016 10:36 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
When 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..
Re: SGA_MAX_SIZE and SGA_TARGET [message #650739 is a reply to message #650738] Mon, 02 May 2016 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As this very good book (too bad Oracle no more update it) says, tuning IO is step 8.
Start tuning from step 1.
And read chapter 9 Optimizing SQL Statements before chapter 20.

[Updated on: Mon, 02 May 2016 14:51]

Report message to a moderator

Re: SGA_MAX_SIZE and SGA_TARGET [message #650740 is a reply to message #650739] Mon, 02 May 2016 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 25792
Registered: January 2009
Location: SoCal
Senior Member
>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.

At what value does I/O go from being acceptable to being high? (please quantify)
Does every Oracle database have the exact same I/O threshold?

Re: SGA_MAX_SIZE and SGA_TARGET [message #650742 is a reply to message #650738] Mon, 02 May 2016 14:35 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
ninan wrote on Mon, 02 May 2016 16:36
When 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 #650754 is a reply to message #650742] Tue, 03 May 2016 02:33 Go to previous messageGo to next message
Roachcoach
Messages: 1508
Registered: May 2010
Location: UK
Senior Member
Impact is .42 active sessions

Do you actually have a problem?
Re: SGA_MAX_SIZE and SGA_TARGET [message #650859 is a reply to message #650729] Thu, 05 May 2016 09:50 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
Perhaps because of the well known bug causing redo corruption? Smile

[Updated on: Thu, 05 May 2016 09:53]

Report message to a moderator

Re: SGA_MAX_SIZE and SGA_TARGET [message #650860 is a reply to message #650859] Thu, 05 May 2016 10:28 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
What you mean by that?
Re: SGA_MAX_SIZE and SGA_TARGET [message #650862 is a reply to message #650860] Thu, 05 May 2016 10:35 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
It was the answer on John's question:

Quote:
Why have you changed _use_single_log_writer from the default of ADAPTIVE?
Re: SGA_MAX_SIZE and SGA_TARGET [message #650863 is a reply to message #650862] Thu, 05 May 2016 10:36 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
I mean what is this bug you mentioned and how is it related to _use_single_log_writer from the default of ADAPTIVE?
Re: SGA_MAX_SIZE and SGA_TARGET [message #650864 is a reply to message #650863] Thu, 05 May 2016 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.

Re: SGA_MAX_SIZE and SGA_TARGET [message #650865 is a reply to message #650863] Thu, 05 May 2016 10:54 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
Unfortunately I cannot refer to the bug number here. You can easily find it yourself in MOS, if you use "_use_single_log_writer deadlock" for searching.
Re: SGA_MAX_SIZE and SGA_TARGET [message #650866 is a reply to message #650865] Thu, 05 May 2016 11:07 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
But I find here

https://blogs.oracle.com/UPGRADE/entry/log_writer_slave_issues_in

that


Solution:

Turn off multiple logwriters on IBM AIX at the moment - in some cases this may solve issues on other platforms as well but please don't take this as a generic recommendation for all platforms. We are seeing the issues mainly on IBM AIX.

Set in your spfile:

_use_single_log_writer=true


and in the init.ora I shared this is set to TRUE. So is this still a issue?
Re: SGA_MAX_SIZE and SGA_TARGET [message #650867 is a reply to message #650866] Thu, 05 May 2016 11:09 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
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.

Previous Topic: Cluster table access IO is unexpected
Next Topic: ORA-03137
Goto Forum:
  


Current Time: Tue Jan 16 02:43:55 CST 2018

Total time taken to generate the page: 0.12173 seconds