This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Oct 11, 2013 3:32 AM by yxes2013 Go to original post RSS
  • 15. Re: Weird Db behavior
    chris_c Journeyer
    Currently Being Moderated

    You may be able to figure out what application is causing you pain by looking at the audit trail if it has been enabled e.g.

     

    SELECT os_username,
         username,
         terminal,
         returncode,
         TO_CHAR(timestamp,   'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
         TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME
    FROM dba_audit_session
    where timestamp >=to_date('07-OCT-2013 16:00:00','DD-MON-YYYY HH24:MI:SS') and
    timestamp <=to_date('07-OCT-2013 18:00:00','DD-MON-YYYY HH24:MI:SS');

     

    Pick a time starting before you get the errors and look for something or someone that is creating a lot of connections.

     

    If there is an application that is now using more connections/processes then you need to work with development either to reduce the number of sessions the application uses, calculate the correct number of processes required by the application or look into shared server/connection pooling all these need some input from the developers and users of the application.

  • 16. Re: Weird Db behavior
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Chris

     

     

    Does this mean that the app hits an unhandled exception for invalid data? which causing it to loop?

    Can this be diagnosed by the script?

  • 17. Re: Weird Db behavior
    chris_c Journeyer
    Currently Being Moderated

    Could be many things, unlikely to be an exception, most likely someone has made a change that means the application is creating additional connections, IF audit session is enabled you should be able to see what applications are logging in and out and when, this will either lead you to the problem application or eliminate one possible cause.

  • 18. Re: Weird Db behavior
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Chris,

     

    This is our 3rd party apps (outsourced), and as usual we have no control about their codes. I can not bluntly tell the developer that they apps has problem unless I can show them

    some log reports like ASH report or network log report? Maybe  the file that made the ORACLE_HOME full was the hint. But usually this happens when during weekends when I am not able to access the server. I need to be granted VPN access first so I can check remotely when this happens next time.

  • 19. Re: Weird Db behavior
    yxes2013 Newbie
    Currently Being Moderated

    Hi Chris,

     

    I run your script below:

    1. SELECT os_username, 
    2.      username, 
    3.      terminal, 
    4.      returncode, 
    5.      TO_CHAR(timestamp,   'DD-MON-YYYY HH24:MI:SS') LOGON_TIME, 
    6.      TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME 
    7. FROM dba_audit_session 
    8. where timestamp >=to_date('07-OCT-2013 16:00:00','DD-MON-YYYY HH24:MI:SS') and 
    9. timestamp <=to_date('07-OCT-2013 18:00:00','DD-MON-YYYY HH24:MI:SS'); 

    And i got :


                                                                                                                                                                                                                                                                    ORASMS                                                                                                                                                                                                                                                                                                  0 07-OCT-2013 16:00:17

                                                                                                                                                                                                                                                                    ORASMS                                                                                                                                                                                                                                                                                                  0 07-OCT-2013 16:00:18

                                                                                                                                                                                                                                                                    ORASMS                                                                                                                                                                                                                                                                                                  0 07-OCT-2013 16:00:19

                                                                                                                                                                                                                                                                    ORASMS                                                                                                                                                                                                                                                                                                  0 07-OCT-2013 16:00:20

                                                                                                                                                                                                                                                                    ORASMS                                                                                                                                                                                                                                                                                                  0 07-OCT-2013 16:00:21

                                                                                                                                                                                                                                                                    ORASMS                                                                                                                                                                                                                                                                                                  0 07-OCT-2013 16:00:22

                                                                                                                                                                                                                                                                    ORASMS                                                                                                                                                                                                                                                                                                  0 07-OCT-2013 16:00:24

     

     

    5949 rows selected.

     

    So what I am going to do?

  • 20. Re: Weird Db behavior
    Gamblesk Explorer
    Currently Being Moderated

    Quit and let them find a real DBA?

  • 21. Re: Weird Db behavior
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    If that's really what the output looked like then user ORASMS was logging on successfully once per second for the best part of two hours without logging off - which suggests the session was crashing or was being killed - because it never seems to log off. What is your setting for sessions ? Are you running with shared servers enabled ? What do you know about the ORASMS user ?

     

    Regards

    Jonathan Lewis


  • 22. Re: Weird Db behavior
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Jon,

     

    I checked on some other times when process was normal but they same the and the connection per hour is around 3000. but during problematic times it is only around half of it

    like 1500.

     

    What is your setting for sesssion > What command should I run ti check ths?

    Are you running on shared servers enable > What command should I run to check this?

    What do you know about orasms? Its an app that continuously checking the table if there is sms message to be process(read and send) to users.

     

    I am thinking if AWR,ASH,ADDM will help?

     

    Thanks

  • 23. Re: Weird Db behavior
    sb92075 Guru
    Currently Being Moderated

    >I am thinking if AWR,ASH,ADDM will help?

    You have repeatedly demonstrated that you are eminently unqualified to do any "thinking" when it comes to Oracle DB

     

    It appears all you know how to do is ask others to do your job  for you.

  • 24. Re: Weird Db behavior
    chris_c Journeyer
    Currently Being Moderated

    to check the session and processes parameter use (in sqlplus)

     

    show parameter processes

    show parameter sessions

     

    From the description of the application and what is happening the application if creating a new connection every second, selecting the rows to process from the database doing "stuff" then closing the thread/process without closing the database connection. Oracle will then cleanup the server process however this may not be instantaneous. I suspect what is happening is that during the time you are having this issue either the response from the database is slower due to higher load/more records or the cleanup is taking longer than normal.

    The proper fix for this is an application change to use a single connection or a connection pool, there are ways to mitigate the impact by using dead connection detection or resource profiles to reduce the time taken to detect dead sessions and/or disconnect idle sessions but neither of these should be implemented without extensive testing.

    An awr report of the system under normal load and when the issue occurs may narrow the range of possibilities for what is wrong here it looks like an application design/architecture problem but you do seem to have a lot of problems with the day to day management of the infrastructure as well.

     

    Chris

  • 25. Re: Weird Db behavior
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Chris,


    SQL> show parameter process

     

     

    NAME                                 TYPE        VALUE

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

    aq_tm_processes                      integer     1

    cell_offload_processing              boolean     TRUE

    db_writer_processes                  integer     1

    gcs_server_processes                 integer     0

    global_txn_processes                 integer     1

    job_queue_processes                  integer     1000

    log_archive_max_processes            integer     4

    processes                            integer     200

    SQL> show parameter session

     

    NAME                                 TYPE        VALUE

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

    java_max_sessionspace_size           integer     0

    java_soft_sessionspace_limit         integer     0

    license_max_sessions                 integer     0

    license_sessions_warning             integer     0

    session_cached_cursors               integer     50

    session_max_open_files               integer     10

    sessions                             integer     324

    shared_server_sessions               integer

    SQL>



    Is there something need to change?

    How can I upgrade to connection pooling?


    Thanks..

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points