This discussion is archived
5 Replies Latest reply: Sep 23, 2013 7:23 PM by ***Anuj*** RSS

set container execution time

Andrey Goryunov Explorer
Currently Being Moderated

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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    JavierFranciscoRuiz Explorer
    Currently Being Moderated

    What OS verison are you using

  • 4. Re: set container execution time
    Andrey Goryunov Explorer
    Currently Being Moderated

    It is OEL 6.4

  • 5. Re: set container execution time
    ***Anuj*** Journeyer
    Currently Being Moderated

    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)

Legend

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