1 2 Previous Next 25 Replies Latest reply on Oct 11, 2013 10:32 AM by yxes2013 Go to original post
      • 15. Re: Weird Db behavior

        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,
             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

          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

            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

              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

                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

                  Quit and let them find a real DBA?

                  • 21. Re: Weird Db behavior
                    Jonathan Lewis

                    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 ?



                    Jonathan Lewis

                    • 22. Re: Weird Db behavior

                      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?



                      • 23. Re: Weird Db behavior

                        >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

                          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.



                          • 25. Re: Weird Db behavior

                            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


                            Is there something need to change?

                            How can I upgrade to connection pooling?


                            1 2 Previous Next