5 Replies Latest reply: Sep 23, 2013 9:23 PM by ***Anuj*** RSS

    set container execution time

    Andrey Goryunov

      Hi,

       

      I created cdb with 252 container databases, set threaded_execution=true and allocated 8Gb of memory to SGA

       

      When I am switching between containers in sqlplus it is taking minutes to execute alter session

       

      Is it expected? Or something can be tuned to make it faster

       

      cdb> alter session set container=pdb252;

       

      Session altered.

       

      Elapsed: 00:03:16.71

      cdb> alter session set container=pdb1;

       

      Session altered.

       

      Elapsed: 00:12:57.39

      cdb>

      cdb> alter session set container=pdb100;

       

      Session altered.

       

      Elapsed: 00:07:31.42

      cdb> alter session set container=pdb252;

       

      Session altered.

       

      Elapsed: 00:11:20.17

       

       

      Thanks,

      Andrey

        • 1. Re: set container execution time
          Andrey Goryunov

          I tried to gather stats on fixed tables, but it stuck on shared pool latch, tried to flush shared pool but got the same event for the session and it did not go anywhere.

           

          I set _enable_shared_pool_durations to false, bounced database and opened all 252 pluggable dbs and time of switching between container became just several seconds.

          • 2. Re: set container execution time
            krajaman

            We are setting up the same scenario in our test system hoping to reproduce the problem. If we can't then we may need more info from you.

             

            Thanks,

             

            - Kumar

            • 3. Re: set container execution time
              Javier Francisco Ruiz

              What OS verison are you using

              • 5. Re: set container execution time
                ***Anuj***

                Hi Andrey

                 

                I created same number of PDB's but for me  switching between containers is very fast. I am using windows .  I think issue is somewhere else.

                 

                SQL> show parameter threaded_execution

                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- -----------
                threaded_execution                   boolean     TRUE
                SQL> show parameter sga

                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- -----------
                lock_sga                             boolean     FALSE
                pre_page_sga                         boolean     TRUE
                sga_max_size                         big integer 8000M
                sga_target                           big integer 8000M
                SQL> set time on
                22:06:37 SQL> set timi on
                22:06:41 SQL>  select count(*) from v$pdbs;

                  COUNT(*)
                ----------
                       253

                Elapsed: 00:00:00.00
                22:06:43 SQL> alter session set container=Anuj_cl248;

                Session altered.

                Elapsed: 00:00:00.00
                22:06:58 SQL> alter session set container=Anuj_cl241
                22:07:03   2  ;

                Session altered.

                Elapsed: 00:00:00.00

                Thanks

                Anuj Mohan

                Oracle12cSIG(IOUG)