6 Replies Latest reply on Sep 26, 2014 2:23 PM by thatJeffSmith-Oracle

    Number of sessions created from sql developer for single schema

    1013593

      Hi all,

      11.2.0.3

      Redhat 5

       

      I am going through architecture stuff and what i know is for one session connecting to database one dedicated server process is allocated.hence for each session there will be one process id on OS and one SID on oracle

      But when i establish single connection from sql developer i see two process id and two sid. Can somebody through light on this.

      oracle    5847     1  0 Sep25 ?        00:00:02 ora_i104_dev

      oracle    5851     1  0 Sep25 ?        00:00:02 ora_i105_dev

      oracle    5855     1  0 Sep25 ?        00:00:02 ora_i106_dev

      oracle    5859     1  0 Sep25 ?        00:00:01 ora_i107_dev

      oracle    5863     1  0 Sep25 ?        00:00:01 ora_i108_dev

      oracle    5867     1  0 Sep25 ?        00:00:01 ora_i109_dev

      oracle    5871     1  0 Sep25 ?        00:00:01 ora_i10a_dev

      oracle    5875     1  0 Sep25 ?        00:00:01 ora_i10b_dev

      oracle    5879     1  0 Sep25 ?        00:00:01 ora_i10c_dev

      oracle    5883     1  0 Sep25 ?        00:00:01 ora_i10d_dev

      oracle    5887     1  0 Sep25 ?        00:00:01 ora_i10e_dev

      oracle    5891     1  0 Sep25 ?        00:00:01 ora_i10f_dev

      oracle    5895     1  0 Sep25 ?        00:00:01 ora_i10g_dev

      oracle    5962     1  0 Sep25 ?        00:00:02 ora_smco_dev

      oracle   22034     1  0 01:33 ?        00:00:00 /u01/app/oracle/product/11.2.0.3/db1/bin/tnslsnr listener -inherit

      root     22132  3202  0 01:37 ?        00:00:00 sshd: oracle [priv]

      oracle   22136 22132  0 01:38 ?        00:00:00 sshd: oracle@pts/1

      oracle   22137 22136  0 01:38 pts/1    00:00:00 -bash

      root     22162  3202  0 01:38 ?        00:00:00 sshd: oracle [priv]

      oracle   22166 22162  0 01:38 ?        00:00:00 sshd: oracle@pts/2

      oracle   22167 22166  0 01:38 pts/2    00:00:00 -bash

      oracle   22189 22167  0 01:38 pts/2    00:00:00 sqlplus   as sysdba

      oracle   22192 22189  0 01:38 ?        00:00:00 oracledev (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

      oracle   22228     1  0 01:40 ?        00:00:01 oracledev (LOCAL=NO)

      oracle   22234     1  0 01:40 ?        00:00:04 oracledev (LOCAL=NO)            (*****  This is from the sqldeveloper********)

      root     22697  3202  0 02:19 ?        00:00:00 sshd: oracle [priv]

      oracle   22702 22697  0 02:19 ?        00:00:00 sshd: oracle@pts/3

      oracle   22703 22702  0 02:19 pts/3    00:00:00 -bash

      oracle   22795     1  0 02:23 ?        00:00:00 ora_w000_dev

      oracle   22865     1  0 02:29 ?        00:00:00 ora_j000_dev

      oracle   22869     1  0 02:29 ?        00:00:00 ora_j001_dev

      oracle   22872 22703  0 02:30 pts/3    00:00:00 ps -eaf

      oracle   22873 22703  0 02:30 pts/3    00:00:00 grep ora

       

      As from above there are two process id for oracledev after i connected from sqldeveloper.While local connection for same schema from OS spawns only single Process.

        • 1. Re: Number of sessions created from sql developer for single schema
          Cobert

          Hi,

           

          You'll usually have a few PZ background processes for SQL Developer - these are background parallel query components.
          You can trace all your sessions for particular applications by checking the gv$session and gv$process views. Frequently you'll see one main process and two PZxx (e.g. PZ99) background processes.

           

          Thanks,

          Conor.

          • 2. Re: Number of sessions created from sql developer for single schema
            tvCa-Oracle

            Two connections would be one more than necessary, but if that is the behaviour ... that is how it is. Maybe the SQL Developer uses a pool system, or a test connection, or something like that.

            • 3. Re: Number of sessions created from sql developer for single schema
              Cobert

              this will list your processes - you'll be able to see those OS processes spawned - the direct processes & any background PZ / PQ processes

               

              select * from gv$process

              where spid in (select process

                                from gv$session

                                where lower(machine) like '%mypcname%');

               

              just change mypcname to a bind or whatever your client PC is

              • 4. Re: Number of sessions created from sql developer for single schema
                1013593

                Hi thanks for your attention i fired your query it doesn't returned any rows i checked by connecting  another schema as well and still i see two processe on OS side and when i check them on DB with process id it is showing me sid and status for that user too.Is this the behavior of sql developer

                 

                SQL> SELECT  PROGRAM,MACHINE,USERNAME FROM V$SESSION WHERE PROGRAM  LIKE '%SQL%';

                 

                PROGRAM               MACHINE             USERNAME

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

                SQL Developer        WMOPNQ01LTP33       PROD_TEST

                 

                 

                SQL Developer        WMOPNQ01LTP33        TAB

                 

                 

                SQL Developer        WMOPNQ01LTP33       PROD_TEST

                 

                 

                SQL Developer        WMOPNQ01LTP33         TAB

                 

                I dont know whether SQL developer uses Pool connection?

                • 5. Re: Number of sessions created from sql developer for single schema
                  Cobert

                  This might be a little more informative - run in SQL developer and scroll down to see your own session:

                   

                  select u.username, u.machine, u.logon_time, p.program, p.pname

                  from gv$session u, gv$process p

                  where p.addr = u.paddr

                  and u.username is not null

                  order by username, machine;

                   

                  If you combine this against the v$process view you should get the processes.


                  As mentioned above - if you log straight into Oracle with a dedicated session you're not using background processes (you can see these & the process IDs from gv$session). Background processes will be PZ..

                   

                  SQL Developer shouldn't use connection pooling - that's typically at an application tier or using Oracles UCP (Universal Connection Pooling) jar / or set up through Weblogic or any other app server.

                  • 6. Re: Number of sessions created from sql developer for single schema
                    thatJeffSmith-Oracle

                    We create two

                     

                    1. the main connection for the tree, worksheet, reports, etc
                    2. secondary connection for the code insight feature - so while you're typing in a worksheet, we query for object names, etc

                     

                    You can have more than two though. If you open an unshared worksheet, this will create a dedicated, new connection for that worksheet.

                     

                    Also, if you open a repository, like for Migrations or Unit Testing, again, that's a new connection.