Forum Stats

  • 3,733,968 Users
  • 2,246,850 Discussions
  • 7,856,955 Comments

Discussions

Application is getting timeouts after certain no of execution : 12c

3255403
3255403 Member Posts: 8

Hello friends,

We are getting timeouts on application from database (RAC - 12.1.0.2.0 two machines on Linux 2.6.32-431.el6.x86_64) after certain number of execution having sql and pl/sql. Whether we run application on 500 tx/sec or 800 tx/sec, we get timeouts relatively. After few timeouts application recovers. If we restart database the scenario occurs after around 2 -3 million transactions after that it can occur randomly.

Assuming there was some problem in SGA, Initially SGA was auto tuned so we tried it to set manual but we are still facing the issue. Currently SGA is manual and v$sgainfo stat is following:

NAME                                            BYTES           RES   CON_ID

-------------------------------- ---------- --- ----------

Fixed SGA Size7659928No0
Redo Buffers126554112No0
Buffer Cache Size50600083456Yes0
In-Memory Area Size0No0
Shared Pool Size5502926848Yes0
Large Pool Size939524096Yes0
Java Pool Size939524096Yes0
Streams Pool Size671088640Yes0
Shared IO Pool Size536870912Yes0
Data Transfer Cache Size0Yes0
Granule Size134217728No0
Maximum SGA Size58787364864No0
Startup overhead in Shared Pool3914611248No0
Free SGA Memory Available00

Output from the query

select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",

   last_oper_type "TYPE" from v$memory_dynamic_components;

component current_size MIN_SIZE USER_SPECIFIED_SIZE TYPE

shared pool524847365248GROW
large pool896896896STATIC
java pool896896896STATIC
streams pool640640640STATIC
SGA Target000STATIC
DEFAULT buffer cache477444774447744SHRINK
KEEP buffer cache000STATIC
RECYCLE buffer cache000STATIC
DEFAULT 2K buffer cache000STATIC
DEFAULT 4K buffer cache000STATIC
DEFAULT 8K buffer cache000STATIC
DEFAULT 16K buffer cache000STATIC
DEFAULT 32K buffer cache000STATIC
Shared IO Pool5120512GROW
Data Transfer Cache000STATIC
In-Memory Area000STATIC
PGA Target112641126411264STATIC
ASM Buffer Cache0048768STATIC

In the above output we see the shared pool is growing.we checked v$sga_resize_ops for the same too.

Please provide your valuable suggestions/feedback.

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited August 2016

    Why assume the problem has anything to do with SGA? There could be any number of reasons why your application doesn't scale.

    Run application with apparent maximum load. Trace additional load - where does the time go? If you dont know this you are just guessing and opening yourself up to the ready-fire-aim approach to performance.

This discussion has been closed.