Who's online

There are currently 1 user and 50 guests online.

Online users

Recent comments


Oakies Blog Aggregator

Oracle database operating system memory allocation management for PGA – part 3: Oracle and AMM: Quiz

This is a series of blogposts on how the Oracle database makes use of PGA. Earlier posts can be found here (PGA limiting for Oracle 12) and here (PGA limiting for Oracle 11.2).

Today a little wednesday fun: a quiz.

What do you think will happen in the following situation (leave a response as comment please!):

-Oracle Linux x86_64 6u6.
-Oracle database PSU 4
-Oracle database (single instance) with the following parameter set: memory_target=1G. No other memory related parameters set.

Run the pga_filler script (which can be found here (PGA limiting for Oracle 12)), with grow_until set to 2100000000 (approximately 2.1G).

I’ll try to create a blogpost on the outcome and an explanation on short notice!

Tagged: amm, fun, memory, oracle, pga, quiz

SLOB 2.2 Not Generating AWR reports? Testing Large User Counts With Think Time? Think Processes and SLOB_DEBUG.

I’ve gotten a lot of reports of folks branching out into SLOB 2.2 large user count testing with the SLOB 2.2 Think Time feature. I’m also getting reports that some of the same folks are not getting the resultant AWR reports one expects from a SLOB test.

If you are not getting your AWR reports there is the old issue I blogged about here (click here). That old issue was related to a Redhat bug.  However, if you have addressed that problem, and still are not getting your AWR reports from large user count testing, it might be something as simple as the processes initialization parameter. After all, most folks have been accustomed to generating massive amounts of physical I/O with SLOB at low session counts.

I’ve made a few changes to that will help future folks should they fall prey to the simple processes initialization parameter folly. The fixes will go into SLOB The following is a screen shot of these fixes and what one should expect to see in such situation in the future. In the meantime, do take note of SLOB_DEBUG as mentioned in the screenshot:



Filed under: oracle

RMOUG Training Days 2015!

OK, Oracle EM hat off, RMOUG Training Days hat on! :)

So as many of you know, RMOUG Board of Directors made the smart move after I joined Oracle, instead of losing a valuable member of the board, they moved me to being a non-voting board member emeritus and realized that I could still serve as the Training Days Conference Director.  The conference is by far, the most demanding position on the board and its a role that I relish and have the skills for.  Oracle is happy.  RMOUG is happy.  Membership is happy.  Training Days is taken care of… :)

This year I’m taking it up a notch and I wanted to talk about why RMOUG Training Days 2015 is the one conference you DON’T want to miss!

Project O.W.L.

The OWL is not just our mascot, it stands for Oracle Without Limits and Project O.W.L. is a new event at Training Days that will offer the attendee some great, new opportunities to learn, to interact with those in the industry and to immerse in the technology we love.  The event will center behind our great exhibition area and will have the following:

RAC Attack

RAC Attack will be back this year and better than ever!  Learn all the ins and outs of an Oracle RAC by building on on your laptop!  Experts will be on hand from the ACE and Oracle community to help you with your questions and make you a RAC Attack ninja!

Clone Attack

Delphix is bringing Clone Attack for those who want to find out how quickly you can provision environments!  Find out how much space and time savings can be reached and do it all on a VM on your laptop!

Oracle Engineered System and Hardware Demo

Want to get up close and personal with some great Oracle hardware?  You’ll get the chance at Project O.W.L.  Oracle is going to be bringing some of the newest, coolest appliances and engineered systems so you can find out just how cool it really is!

Stump the Chump

Have a real technical conundrum?  Want to see if you have the tech question that our experts can’t answer?  We’ll have opportunities to ask the experts your tough questions and if they can’t get you an answer, you’ll get a “I stumped the chump” button to wear proudly at the conference!

New Attendee Recommendation Initiative

I was first introduced to Training Days by the recommendation of a Senior DBA I worked with back in 2004.  There is nothing more valuable than word of mouth and we are going to reward that at TD2015.  If you recommend someone new to Training Days and they list your name on their registration form as the one that recommended them, we’ll reward you with a $25 amazon gift card after the conference!

Special Interest Meetup Lunch

Our SIGs are an important part of our membership.  Show your support the first day by taking your box lunch and sitting in on one of the SIG meetups!  There are so many special interest groups to be a part of, so find out what you’ve been missing out on!

  • Hyperion
  • Big Data and Cloud
  • Enterprise Manager
  • Higher Education
  • Database 12c
  • APEX
  • and others!

ACE Lunches

We’re bringing back our ACE lunches both days again!  If you aren’t in on a SIG lunch, sit with your favorite ACE, ACE Associate or ACE Director and find out what got them where they are in the Oracle community.  Talk tech with the best in the industry!

Deep Dive and Hands on Lab

For those with a full registration pass, (we have single day passes for those that can’t get away for the full conference…)  the first 1/2 day is our gift to you!  From 1-5:15pm on the 17th, you will get to immerse yourself in hands on labs and deep dives from the best of the best in Oracle database, development ADF and APEX and even one of my favorites, Enterprise Manager Database as a Service!  This is a first come first serve sessions that day, (until we hit the room capacity limit, trust me, you do not want to tick off the fire marshal at the convention center… :)) so get there early and get the most out of your full registration!

Two Full Days, 100 Sessions!

Yes, you heard me right-  two days, 100 sessions, 9 tracks!  We have Steven Feuerstein, Jeff Smith, Iggy Fernandez, Kyle Hailey, David Peake, Scott Spendolini, Alex Gorbachev, Graham Wood, Bryn Llewellyn, Carlos Sierra and John King.  New speakers this year for RMOUG, (we work very hard to introduce new speakers into our schedule…) include Bjoern Rost, Rene Antunez, Werner De Gruyter, (Yoda!) and Wayne Van Sluys.

Professional Development and More!

I will be heading up our ever popular Women in Technology series on the first full day again this year!  The panel is starting to form and I look forward to everyone who attends getting the most out of the session and to further their love of their tech career.  This session is not just for women, but for father’s of daughters, husbands who want more for their wives, managers of women employees and even those that are hoping to hire more diversity in their departments!

Jeff Smith and I will be doing another year of Social Media for the Database Professional!  Come learn HOW to do Social Media instead of just the WHY.  We’ll teach you how to automate, find the easy button for social media and how to find your social media style to make it work for you and your career.

The schedule is set up so that there is something impressive all day every day for the 2015 conference and I’m excited to share it with everyone!  Registration is open, so don’t miss out on what is going to be the best Training Days yet!







Copyright © DBA Kevlar [RMOUG Training Days 2015!], All Right Reserved. 2014.

Oracle database operating system memory allocation management for PGA – part 2: Oracle 11.2

This is the second part of a series of blogpost on Oracle database PGA usage. See the first part here. The first part described SGA and PGA usage, their distinction (SGA being static, PGA being variable), the problem (no limitation for PGA allocations outside of sort, hash and bitmap memory), a resolution for Oracle 12 (PGA_AGGREGATE_LIMIT), and some specifics about that (it doesn’t look like a very hard limit).

But this leaves out Oracle version 11.2. In reality, the vast majority of the database that I deal with at the time of writing is at version 11.2, and my guess is that this is not just the databases I deal with, but a general tendency. This could change in the coming time with the desupport of Oracle 11.2, however I suspect the installed base of Oracle version 12 to increase gradually and smoothly instead of in a big bang.

With version 11.2 there’s no PGA_AGGREGATE_LIMIT. This simply means there is no official way to limit the PGA. Full stop. However, there is an undocumented event to limit PGA usage: event 10261. This means that if you want to use this in a production database, you should ask Oracle support to bless the usage of it. On the other hand, Oracle corporation made this event public in an official white paper: Exadata consolidation best practices.

Let’s test event 10261! I’ve got the same table (T2) setup, a description how to set this up, and the anonymous PL/SQL code to allocate PGA using a collection is in the first part. I am using a database version with PSU 4 applied. The reason for choosing this version is that if you run a serious business on Oracle 11.2, THAT should be the version you should be running on!
(disclaimer: everything shown in this blogpost is purely for educational purposes. Do test everything thoroughly before applying this to a production system. Behaviour can or may be different in your specific situation)
The reason for this disclaimer: Bernhard (@bdcbuning_gridit) tweeted that he was warned that when setting it at the instance level, it could crash the instance. I am not sure if this means setting it at runtime, this event is always evaluated at the instance level.

Okay, let’s replicate more or less the test done to Oracle version in the first part. In this database PGA_AGGREGATE_SIZE is set to 500M, now let’s try to set the event to 600M, which means we set the PGA limit to 600M:
This is setting the event on runtime:

SYS@v11204 AS SYSDBA> alter system set events = '10261 trace name context forever, level 600000';

System altered.

This is setting the event in the spfile (which means you need a restart of the instance to activate this event, or the above syntax to set it on runtime):

SYS@v11204 AS SYSDBA> alter system set event = '10261 trace name context forever, level 600000' scope=spfile;

System altered.

The level is the amount of memory to which the PGA must be limited, in kilobytes.

Now start the anonymous PL/SQL block to fill up the PGA with a collection, again set to 900M:

TS@v11204 > @pga_filler
ERROR at line 1:
ORA-10260: limit size (600000) of the PGA heap set by event 10261 exceeded
ORA-06512: at line 20

That’s nice! There’s actually a meaningful, describing error message which explains why this PL/SQL block ended!

Let’s look at the actual PGA memory used, as reported by v$pgastat:

SYS@v11204 AS SYSDBA> select value/power(1024,2) from v$pgastat where name = 'maximum PGA allocated';


This is different than setting PGA_AGGREGATE_LIMIT, however there’s still more memory allocated than set as the limit (600000KB), but lesser (676M in versus 1041M in The outside visibility of the limiting happening is different too: there is NO notice of a process hitting the PGA limit set in the alert.log file nor the process’ trace file(!). Another difference is even SYS is limited, a test with the procedure running as SYS gotten me the ORA-10260 too, PGA_AGGREGATE_LIMIT does not limit SYS.

Event 10261 has got the same description to at least as low as version Here’s a test with with the event 10261 set at version to 600M:

TS@v11203 > @pga_filler
ERROR at line 1:
ORA-00600: internal error code, arguments: [723], [123552], [top uga heap], [], [], [], [], [], [], [], [], []
ORA-06512: at line 20

As has been detailed in the Oracle white paper, prior to version, an ORA-600 [723] is signalled when event 10261 is set, and more PGA memory is allocated as has been specified as limit. The amount of total allocated PGA is 677M, so roughly the same as with version

Because this is a genuine ORA-600 (internal error, ‘OERI’), this gives messages in the alert.log file:

Tue Dec 16 10:40:09 2014
Errors in file /u01/app/oracle/diag/rdbms/v11203/v11203/trace/v11203_ora_8963.trc  (incident=9279):
ORA-00600: internal error code, arguments: [723], [123552], [top uga heap], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/v11203/v11203/incident/incdir_9279/v11203_ora_8963_i9279.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

The process’ trace file in the trace directory only points to the incident file, no further details are available there.
The incident trace file contains a complete diagnostics dump.

The behaviour is identical with Oracle

The limiting of the total amount of PGA memory used must be done using an undocumented event prior to Oracle version 12. The event is 10261. The event is made known in an official white paper. Still I would open a service request with Oracle to ask blessing for setting this. This does not mean this functionality is not needed, I would deem it highly important in almost any environment, even when running a single database: this setting, when done appropriately, protects your system from over allocating memory, which could mean entering the swapping death-spiral. The protection means a process gets an ORA message, and the PGA allocation aborted and deallocated.

With version hitting the limit as set with event 10261 is not published, outside of the process getting the ORA-10260.

With versions prior to ( and verified) processes do get an ORA-600 [723], which is also visible in the alert.log, and incidents are created accordingly.

When a limit has been set using event 10261, it still means more memory is allocated than set as limit (approximately 677M when 600M is set), but this is way less than with the PGA_AGGREGATE_LIMIT (1041M when 600M is set) in my specific situation. Test this in your own environment when you start using this.

Tagged: consolidation, event, internals, oracle, performance, pga_aggregate_target, tuning

Adventures in RAC: gc buffer busy acquire and release

It seems that I’m getting more and more drawn into the world of performance analysis, and since I sometimes tend to forget things I need to write them down. I almost enjoy the “getting there” more than ultimately solving the problem. You pick up quite a few things on the way.

This environment is Exadata but as with so many things the fact that the database is on Exadata shouldn’t matter.

So here is one of these posts, this time I’m writing up what I saw related to GC Buffer Busy Acquire.

gc buffer busy acquire?

Whenever I see a wait event I haven’t dealt with extensively in the past I try to provoke behaviour to study it more closely. But first you need to know the event’s meaning. One option is to check v$event_name:

SQL> select name, parameter1, parameter2, parameter3, wait_class
  2  from v$event_name where name = 'gc buffer busy acquire';

------------------------------ ---------- ---------- ---------- -------------------
gc buffer busy acquire         file#      block#     class#     Cluster

So when you see this event in ASH/traces/v$session etc you know it’s a cluster wait and potentially limiting your processing throughput. It also tells you the file#, block# and class# of the buffer which you can link to v$bh. This view allows you to find the data object ID given these input parameters.

Using as a source I worked out that the event has to do with acquiring a buffer (=block) in RAC (gc = global cache) on the local instance. If the block you need is on the remote instance you wait for it to be released, and the wait event is gc buffer busy release.

Since Oracle will clone blocks in buffer caches for consistent reads and use a shared lock on these for reading I thought that waiting can only happen if someone requested a block in XCUR (exclusive current) mode. So with that working hypothesis I went to work.

How to test

I started off writing a small java class that creates a connection pool against my RAC database. I initially used the default service name in the connect descriptor but had to find out that dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE did not trace my sessions. In the end I created a true RAC service with CLB and RLB goals against both instances and I ended up with traces in the diagnostic_dest.

After setting up the UCP connection pool the code will create a number of threads that each will pull a connection from the pool, do some work (*) and hand it back to the pool as good citizens should do.

(*) The do some work bit is this::

                        try {

                                PreparedStatement pstmt = conn.prepareStatement(
                                  "select /* bufferbusy001 */ id, to_char(sysdate,'hh24:mi:ss') d " +
                                  "from t1 where id = ? for update");

                                int randomID = new Random().nextInt((1450770 - 1450765) + 1) + 1450765;
                                System.out.println("thread " + mThreadID + " now querying for ID " + randomID);
                                pstmt.setInt(1, randomID);

                                ResultSet rs = pstmt.executeQuery();

                                while ( {
                                        System.out.println("Thread " + mThreadID + " reporting an id of "
                                        + rs.getInt("id") + ". Now it is " + rs.getString("d"));

                                conn = null;

                        } catch (Exception e) {

I think that’s how a Java developer would do it (with more error handling of course) but then I’m not a Java developer. It did work though! What I considered most important was to generate contention on a single block. Using dbms_rowid I could find out which IDs belong to (a random) block:

SQL> select * from (
  2    select id,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid, 'BIGFILE') as block
  3      from t1
  4 ) where block = 11981654;

        ID      BLOCK
---------- ----------
   1450765   11981654
   1450766   11981654
   1450767   11981654
   1450768   11981654
   1450769   11981654
   1450770   11981654

6 rows selected.

So if I manage to randomly select from the table where ID in the range …765 to …770 then I should be ok and just hit that particular block.

It turned out that the SQL statement completed so quickly I had to considerably ramp up the number of sessions in the pool to see anything. I went up from 10 to 500 before I could notice a change. Most of the statements are too quick to even be caught in ASH-Tanel’s ashtop script showed pretty much nothing except ON-CPU occasionally as well as the odd log file sync event. Snapper also reported sessions in idle state.

SQL> r
  1  select count(*), inst_id, status, sql_id, event, state
  2  from gv$session where module = 'BufferBusy'
  3* group by inst_id, status, sql_id, event, state

  COUNT(*)    INST_ID STATUS   SQL_ID        EVENT                          STATE
---------- ---------- -------- ------------- ------------------------------ -------------------
       251          1 INACTIVE               SQL*Net message from client    WAITING
       248          2 INACTIVE               SQL*Net message from client    WAITING

2 rows selected.

That’s what you see for most of the time.

Let me trace this for you

So in order to get any meaningful idea about the occurrence (or absence) of the gc buffer busy acquire event I added a MODULE to my sessions so I can later on run trcsess to combine traces. Here is the resulting raw trace, or rather an excerpt from it:

PARSING IN CURSOR #140650659166120 len=96 dep=0 uid=65 oct=3 lid=65 tim=4170152514049 hv=1500360262 ad='5b58a4a10' sqlid='6a5jfvpcqvbk6'
select /* bufferbusy001 */ id, to_char(sysdate,'hh24:mi:ss') d from t1 where id = :1  for update
PARSE #140650659166120:c=0,e=5598,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2291732815,tim=4170152514046
WAIT #140650659166120: nam='gc buffer busy acquire' ela= 12250 file#=6 block#=11981654 class#=1 obj#=20520 tim=4170152526533
WAIT #140650659166120: nam='buffer busy waits' ela= 1890 file#=6 block#=11981654 class#=1 obj#=20520 tim=4170152528475
WAIT #140650659166120: nam='enq: TX - row lock contention' ela= 188606 name|mode=1415053318 usn<<16 | slot=1179674 sequence=1485 obj#=20520 tim=4170152717199
WAIT #140650659166120: nam='gc buffer busy acquire' ela= 1590 file#=6 block#=11981654 class#=1 obj#=20520 tim=4170152718839
WAIT #140650659166120: nam='enq: TX - row lock contention' ela= 36313 name|mode=1415053318 usn<<16 | slot=1245199 sequence=1894 obj#=20520 tim=4170152755340
WAIT #140650659166120: nam='gc buffer busy acquire' ela= 1268 file#=6 block#=11981654 class#=1 obj#=20520 tim=4170152756655
WAIT #140650659166120: nam='buffer busy waits' ela= 668 file#=6 block#=11981654 class#=1 obj#=20520 tim=4170152757363
WAIT #140650659166120: nam='KJC: Wait for msg sends to complete' ela= 11 msg=26941469232 dest|rcvr=65539 mtype=8 obj#=20520 tim=4170152757492
EXEC #140650659166120:c=1999,e=243530,p=0,cr=9,cu=4,mis=0,r=0,dep=0,og=1,plh=2291732815,tim=4170152757651
WAIT #140650659166120: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=20520 tim=4170152757709
FETCH #140650659166120:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2291732815,tim=4170152757749
STAT #140650659166120 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE  (cr=9 pr=0 pw=0 time=243443 us)'
STAT #140650659166120 id=2 cnt=2 pid=1 pos=1 obj=0 op='BUFFER SORT (cr=3 pr=0 pw=0 time=60 us)'
STAT #140650659166120 id=3 cnt=1 pid=2 pos=1 obj=48863 op='INDEX RANGE SCAN I_T1$SEC1 (cr=3 pr=0 pw=0 time=37 us cost=3 size=6 card=1)'
WAIT #140650659166120: nam='SQL*Net message from client' ela= 260 driver id=1413697536 #bytes=1 p3=0 obj#=20520 tim=4170152758109
CLOSE #140650659166120:c=0,e=5,dep=0,type=1,tim=4170152758141
XCTEND rlbk=1, rd_only=0, tim=4170152758170
WAIT #0: nam='gc buffer busy acquire' ela= 3764 file#=6 block#=11981654 class#=1 obj#=20520 tim=4170152761976
WAIT #0: nam='buffer busy waits' ela= 1084 file#=6 block#=11981654 class#=1 obj#=20520 tim=4170152763104
WAIT #0: nam='log file sync' ela= 246 buffer#=119491 sync scn=19690898 p3=0 obj#=20520 tim=4170152763502
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=20520 tim=4170152763536

Result! There are gc buffer busy acquire events recorded. I can’t rule out TX-row lock contention since with all those threads and only 6 IDs to choose from there was going to be some locking on the same ID caused by the “for update” clause.

Now I am reasonably confident that I worked out at least one scenario causing a gc buffer busy acquire. You might also find the location of the blocks in the buffer cache interesting:

SYS:dbm011> select count(*), inst_id, block#, status
  2  from gv$bh where block# = 11981654
  3  group by inst_id, block#, status order by inst_id, status;

----------- ----------- ----------- ----------
          9           1    11981654 cr
          1           1    11981654 xcur
          9           2    11981654 cr

There is the one block in XCUR mode and 9 in CR mode in the buffer cache for that block.

Making it worse

Now I didn’t want to stop there, I was interested in what would happen under CPU load. During my career I noticed cluster waits appear primarily when you are CPU-bound (all other things being equal). This could be the infamous middle-tier-connection-pool-mismanagement or an execution plan going wrong with hundreds of users performing nested loop joins when they should hash-join large data sets… This is usually the point where OEM users ask the DBAs to do something against that “sea of grey” in the performance pages.

As with every cluster technology an overloaded CPU does not help. Well-I guess that’s true for all computing. To increase the CPU load I created 10 dd sessions to read from /dev/zero and write to /dev/null. Sounds silly but one of these hogs 1 CPU core 100%. With 10 out of 12 cores 100% occupied that way on node 1 I relaunched my test. The hypothesis that CPU overload has an effect was proven right by suddenly finding ASH samples of my session.

SQL> @ash/ashtop sql_id,session_state,event "sql_id='6a5jfvpcqvbk6'" sysdate-5/1440 sysdate

  Seconds     AAS %This   SQL_ID        SESSION EVENT
--------- ------- ------- ------------- ------- ----------------------------------------
      373     1.2   79% | 6a5jfvpcqvbk6 WAITING enq: TX - row lock contention
       54      .2   11% | 6a5jfvpcqvbk6 WAITING gc buffer busy release
       20      .1    4% | 6a5jfvpcqvbk6 ON CPU
       11      .0    2% | 6a5jfvpcqvbk6 WAITING gc buffer busy acquire
       11      .0    2% | 6a5jfvpcqvbk6 WAITING gc current block busy
        1      .0    0% | 6a5jfvpcqvbk6 WAITING gc current block 2-way

6 rows selected.

Using ASQLMON I can even see where time is spent:

SQL> @scripts/ash/asqlmon 6a5jfvpcqvbk6 % sysdate-1 sysdate

    SECONDS Activity Visual       Line ID Parent ASQLMON_OPERATION                   SESSION EVENT                                         AVG_P3 OBJ_ALIAS_ ASQLMON_PR
----------- -------- ------------ ------- ------ ----------------------------------- ------- ---------------------------------------- ----------- ---------- ----------
         38    2.4 % |          |       0        SELECT STATEMENT                    ON CPU                                                883065
          1     .1 % |          |       1      0  FOR UPDATE                         WAITING gc current block 2-way                      33554433  [SEL$1]
         25    1.6 % |          |       1      0                                     ON CPU                                            5369727.36  [SEL$1]
         17    1.1 % |          |       1      0                                     WAITING buffer busy waits                                  1  [SEL$1]
        109    6.8 % |#         |       1      0                                     WAITING gc buffer busy release                             1  [SEL$1]
         31    1.9 % |          |       1      0                                     WAITING gc buffer busy acquire                             1  [SEL$1]
         27    1.7 % |          |       1      0                                     WAITING gc current block busy                       33554433  [SEL$1]
        768   48.0 % |#####     |       1      0                                     WAITING enq: TX - row lock contention            6685.143229  [SEL$1]
          3     .2 % |          |       2      1   BUFFER SORT                       ON CPU                                                     0
          2     .1 % |          |       3      2    INDEX RANGE SCAN [I_T1$SEC1]     ON CPU                                                     0 T1@SEL$1 [ [A:]
                                                                                                                                                  SEL$1]     "ID"=:1

          2     .1 % |          |       0        SELECT STATEMENT                    ON CPU                                            16777216.5
          2     .1 % |          |       1      0  FOR UPDATE                         WAITING gc current block busy                       33554433  [SEL$1]
         24    1.5 % |          |       1      0                                     WAITING write complete waits                               0  [SEL$1]
          9     .6 % |          |       1      0                                     WAITING gc buffer busy acquire                             1  [SEL$1]
         30    1.9 % |          |       1      0                                     WAITING gc buffer busy release                             1  [SEL$1]
          9     .6 % |          |       1      0                                     WAITING buffer busy waits                                  1  [SEL$1]
          7     .4 % |          |       1      0                                     ON CPU                                           158.8571429  [SEL$1]
        496   31.0 % |###       |       1      0                                     WAITING enq: TX - row lock contention            6396.395161  [SEL$1]
                   % |          |       2      1   BUFFER SORT
                   % |          |       3      2    INDEX RANGE SCAN [I_T1$SEC1]                                                                  T1@SEL$1 [ [A:]
                                                                                                                                                  SEL$1]     "ID"=:1

Further Reading

I’m sure there is a wealth of resources available out there, in my case Riyaj’s blog helped me a lot. He even tagged posts with gc buffer busy:

Have a look at the Oaktable World 2014 agenda and watch Tanel Poder’s session attentively. You’d be surprised how many scripts he made publicly available to troubleshoot perform. Like snapper? It’s only the tip of the iceberg. And if you can, you should really attend his advanced troubleshooting seminar.

SLOB Data Loading Case Studies – Part II. SLOB 2.2 For High-Bandwidth Data Loading.

This is Part II in a series. Part I can be found here (click here). Part I in the series covered a very simple case of SLOB data loading. This installment is aimed at how one can use SLOB as a platform test for a unique blend of concurrent, high-bandwidth data loading, index creation and CBO statistics gathering.

Put SLOB On The Box – Not In a Box

As a reminder, the latest SLOB kit is always available here: .

Often I hear folks speak of what SLOB is useful for and the list is really short. The list is so short that a single acronym seems to cover it—IOPS, just IOPS and nothing else. SLOB is useful for so much more than just testing a platform for IOPS capability. I aim to make a few blog installments to make this point.

SLOB for More Than Physical IOPS

I routinely speak about how to use SLOB to study host characteristics such as NUMA and processor threading (e.g., Simultaneous Multithreading on modern Intel Xeons). This sort of testing is possible when the sum of all SLOB schemas fit into the SGA buffer pool. When testing in this fashion, the key performance indicators (KPI) are LIOPS (Logical I/O per second) and SQL Executions per second.

This blog post is aimed at suggesting yet another manner of platform testing with SLOB–specifically concurrent bulk data loading.

The SLOB data loader (~SLOB/ offers the ability to test non-parallel, concurrent table loading, index creation and CBO statistics collection.

In this blog post I’d like to share a “SLOB data loading recipe kit” for those who wish to test high performance SLOB data loading. The contents of the recipe will be listed below. First, I’d like to share a platform measurement I took using the data loading recipe. The host was a 2s20c40t E5-2600v2 server with 4 active 8GFC paths to an XtremIO array.

The tar archive kit I’ll refer to below has the full slob.conf in it, but for now I’ll just use a screen shot. Using this slob.conf and loading 512 SLOB schema users generates 1TB of data in the IOPS tablespace. Please note the attention I’ve drawn to the slob.conf parameters SCALE and LOAD_PARALLEL_DEGREE. The size of the aggregate of SLOB data is a product of SCALE and the number of schemas being loaded. I drew attention to LOAD_PARALLEL_DEGREE because that is the key setting in increasing the concurrency level during data loading. Most SLOB users are quite likely not accustomed to pushing concurrency up to that level. I hope this blog post makes doing so seem more worthwhile in certain cases.


The following is a screenshot of the output from the SLOB 2.2 data loader. The screenshot shows that the concurrent data loading portion of the procedure took 1,474 seconds. On the surface that would appear to be a data loading rate of approximately 2.5 TB/h. One thing to remember, however, is that SLOB data is loaded in batches controlled by LOAD_PARALLEL_DEGREE. Each batch loads LOAD_PARALLEL_DEGREE number of tables and then creates a unique indexes and performs CBO statistics gathering.  So the overall “data loading” time is really data loading plus these ancillary tasks. To put that another way, it’s true this is a 2.5TB data loading use case but there is more going on than just simple data loading. If this were a pure and simple data loading processing stream then the results would be much higher than 2.5TB/h. I’ll likely blog about that soon.


As the screenshot shows the latest SLOB 2.2 data loader isolates the concurrent loading portion of In this case, the seed table (user1) was loaded in 20 seconds and then the concurrent loading portion completed in 1,474 seconds.

That Sounds Like A Good Amount Of Physical I/O But What’s That Look Like?

To help you visualize the physical I/O load this manner of testing places on a host, please consider the following screenshot. The screenshot shows peaks of vmstat 30-second interval reporting of approximately 2.8GB/s physical read I/O combined with about 435 MB write I/O for an average of about 3.2GB/s. This host has but 4 active 8GFC fibre channel paths to storage so that particular bottleneck is simple to solve by adding another 4 port HBA! Note also how very little host CPU is utilized to generate the 4x8GFC saturating workload. User mode cycles are but 15% and kernel mode utilization was 9%. It’s true that 24% sounds like a lot, however, this is a 2s20c40t host and therefore 24% accounts for only 9.6 processor threads–or 5 cores worth of bandwidth. There may be some readers who were not aware that 5 “paltry” Ivy Bridge Xeon cores are capable of driving this much data loading!

NOTE: The SLOB method is centered on the sparse blocks. Naturally, fewer CPU cycles are required for loading data into sparse blocks.

Please note, the following vmstat shows peaks and valleys. I need to remind you that SLOB data loading consists of concurrent processing of not only data loading (Insert as Select) but also a unique index creation and CBO statistics gathering. As one would expect I/O will wane as the loading process shifts from the bulk data load to the index creation phase and then back again.


Finally, the following screenshot shows the very minimalist init.ora settings I used during this testing.


The Recipe Kit

The recipe kit can be found in the following downloadable tar archive. The kit contains the necessary files one would need to reproduce this SLOB data loading time so long as the platform has sufficient performance attributes. The tar archive also has all output generated by as the following screenshot shows:


The SLOB 2.2 data loading recipe kit can be downloaded here (click here). Please note, the screenshot immediately above shows the md5 checksum for the tar archive.


This post shows how one can tune the SLOB 2.2 data loading tool ( to load 1 terabyte of SLOB data in well under 25 minutes. I hope this is helpful information and that, perhaps, it will encourage SLOB users to consider using SLOB for more than just physical IOPS testing.


Filed under: oracle

Oracle database operating system memory allocation management for PGA

This post is about memory management on the operating system level of an Oracle database. The first question that might pop in your head is: isn’t this a solved problem? The answer is: yes, if you use Oracle’s AMM (Automatic Memory Management) feature, which let’s you set a limit for the Oracle datababase’s two main memory area’s: SGA and PGA. But in my opinion any serious, real life, usage of an Oracle database on Linux will be (severely) constrained in performance because of the lack of huge pages with AMM, and I personally witnessed very strange behaviour and process deaths with the AMM feature and high demand for memory.

This means that I strongly advise customers to use Oracle’s ASMM (Automatic Shared Memory Management) feature. In the newer versions of 11.2 I found this to be working very well. Earlier versions like 10.2 could suffer from an ever growing shared pool (which also means an ever shrinking buffer cache), especially when bind variables weren’t used. This still could happen, but it seems the SGA memory management feature in 11.2 handles this well in most cases. The ASMM feature means a fixed memory area is allocated for the SGA. SGA allocation has always been fixed outside of the AMM feature, as far as I know.

When ASMM doesn’t work, meaning the memory areas are getting sized wrong and performance is influenced by that, the last option is to size the memory area’s yourself. However, since version Oracle will resize when the memory manager thinks it’s feasible. See Kurt van Meerbeek’s article about that.

That leaves the PGA (Process Global Area) as a memory area on itself. Most databases are using the automatic PGA memory management, which is enabled once the PGA_AGGREGATE_TARGET parameter is set to a non zero value. A common misunderstanding is this setting is actually limiting the overall PGA usage of an instance. The truth is automatic PGA memory management will make attempts to adhere to the PGA_AGGREGATE_TARGET value. These are the actual words in the official Oracle documentation: ‘attempts to adhere’!

This means sort memory, hash memory and bitmap memory will be actively limited in size per process by automatic PGA memory management, any attempt to allocate more than automatic PGA memory management allows will result in moving some contents of these memory areas to the assigned temporary tablespace of the database user, to make room for new data.

However, there are more memory area’s allocatable per process, which are never swapped to disk, thus always will stay in memory, and these could not be limited in an officially supported way prior to Oracle version 12. Two structures which are allocated in PGA and never swapped to disk are PL/SQL collections and PL/SQL tables. Creating and filling these requires the usage of PL/SQL (hence their names); the reason for mentioning this is that if your database is not used by PL/SQL but only SQL, you almost certainly will not run into the problem I describe below.

You might be thinking: wait a minute! Does this mean a developer can just create such a structure, and allocate whatever he/she likes, with all the consequences that it can have, like the operating system starting to swap, and can do that for every single process? Yes, this is what this means. This is why Oracle introduced a parameter called PGA_AGGREGATE_LIMIT with Oracle 12, to effectively limit the overall PGA heap size.

In case you wonder what this means, or even doubting my words, I have written a little program to demonstrate this behaviour.

This is the source code to create my test table T2:

exec dbms_random.seed('abracadabra');
create table t2
with generator as (
    select      rownum      id
    from        dual
    connect by
                rownum <= 1000
    rownum                                                id,
    trunc((rownum-1)/50)                            clustered,
    mod(rownum,20000)                               scattered,
    trunc(dbms_random.value(0,20000))               randomized,
    trunc(sysdate) + dbms_random.value(-180, 180)   random_date,
    dbms_random.string('l',6)                       random_string,
    lpad(rownum,10,0)                               vc_small,
    rpad('x',100,'x')                               vc_padding
    generator   g1,
    generator   g2
    rownum <= 1000000
exec dbms_stats.gather_table_stats(null,'T2');

This is a very smart way to generate a table. I actually borrowed this from Jonathan Lewis.

Next up, I created a small anonymous PL/SQL block to take the contents from the T2 table, and store them in a collection until I hit the limit in the variable ‘grow_until’.

	type sourcetab is table of t2%ROWTYPE;
	c_tmp		sourcetab;
	c_def		sourcetab	:= sourcetab();
	v_b_p		number		:= 0;
	v_c_p		number		:= 0;
	v_b_u		number		:= 0;
	v_c_u		number		:= 0;
	grow_until	number		:= 700000000;
	p_a_t		number;
	select value into v_b_p from v$mystat m, v$statname n where m.statistic#=n.statistic# and name = 'session pga memory max';
	select value into v_b_u from v$mystat m, v$statname n where m.statistic#=n.statistic# and name = 'session uga memory max';
	select value into p_a_t from v$parameter where name = 'pga_aggregate_target';
	select * bulk collect into c_tmp from t2;
	while v_c_p < grow_until loop
		for c in c_tmp.first .. c_tmp.last loop
			c_def(c_def.last) := c_tmp(c);
			select value into v_c_p from v$mystat m, v$statname n where m.statistic#=n.statistic# and name = 'session pga memory max';
			select value into v_c_u from v$mystat m, v$statname n where m.statistic#=n.statistic# and name = 'session uga memory max';
			if v_c_p >= grow_until then
			end if;
		end loop;
	end loop;
	dbms_output.put_line('vbp : '||v_b_p);
	dbms_output.put_line('vcp : '||v_c_p);
	dbms_output.put_line('vbu : '||v_b_u);
	dbms_output.put_line('vcu : '||v_c_u);
	dbms_output.put_line('pat : '||p_a_t);

Please mind the session needs to have create table, create session granted, enough quota in the default tablespace and select on v_$mystat, v_$parameter and v_$statname granted.

This is run on an Oracle database:

TS@v12102 > @pga_filler
vbp : 3535368
vcp : 700051976
vbu : 1103192
vcu : 4755704
pat : 524288000

PL/SQL procedure successfully completed.

The begin sizes of the UGA (vbu) and PGA (vbp) are 1’103’192 and 3’535’368. The PGA_AGGREGATE_TARGET size is set to 524’288’000 (500MB). I did set the grow_until variable to 700’000’000 (roughly 700MB), which is more than PGA_AGGREGATE_TARGET. After running this, it’s easy to spot the values of vcu (UGA allocation) and vcp (PGA allocation). vcu grew to 4’755’704 during the run, however vcp grew to 700’051’976, a little more than 700MB! This shows that the collection is stored in the PGA, and that the collection grew beyond the value set with PGA_AGGREGATE_TARGET.

This behaviour is consistent in versions,,, and

Let me emphasise once again that the above proof of concept code managed to allocate more memory than was set for the overall PGA usage of the entire instance. This can have an enormous, devastating impact on a consolidated database setup (meaning having multiple instances running on a single machine). Typically, once memory consumption of all the processes exceeds physically available memory, the operating system tries to use the swap device, to which it will swap memory pages in and out depending on memory usage of active (=on CPU) processes. Mild swapping shows as severely slowed-down processing (because a number of memory pages for processing need to be read from the swap device and placed in memory, from which the former contents need to be written to the swap device), heavy swapping shows as the machine coming down to a standstill.

Please mind that a diagnosis on the state of memory usage (alias swapping), just by looking at the amount of used swap (as can be seen in the ‘top’ output, or ‘swapon -s’) could be misleading. It’s also important to look at actual swapping in and out, as can be seen with ‘vmstat 1′ (si/so columns) or swap -W. I’ve found several systems which had been running for some time (approximately longer than a month) that had swap usage, sometimes up to 40%, while no ‘active swapping’, so memory pages being transfered to and from the swap device, was happening.

Luckily, starting with Oracle 12 you can actually limit overall PGA usage using the parameter PGA_AGGREGATE_LIMIT. The default value is the greater of (list from Oracle documentation):
a) 2GB
b) 200% of PGA_AGGREGATE_TARGET parameter (or lower if 200% > (90% of physical memory – total SGA size) but not below 100%)
c) 3MB * PROCESSES parameter
The parameter can not set below it’s default value, except when set in a pfile or spfile.

Let’s set the PGA_AGGREGATE_LIMIT to 600MB and see what happens when we start doing a large allocation again:

SQL> alter system set pga_aggregate_limit=600m scope=spfile;

System altered.

SQL> startup force;

Okay, let’s run the pga_filler.sql script again, and try to allocate 900MB. This means the “grow_until” variable must be set to 900000000.
PLEASE MIND this is done as a regular user, the SYS user and background processes other than job queue processes are not subject to the limiting.

TS@v12102 > @pga_filler
ERROR at line 1:
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-06512: at line 21

Great! Exactly like we expect, right?
Well…yes, but let’s look at the alert.log

Sat Dec 13 15:08:57 2014
Errors in file /u01/app/oracle/diag/rdbms/v12102/v12102/trace/v12102_ora_4147.trc  (incident=46599):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Incident details in: /u01/app/oracle/diag/rdbms/v12102/v12102/incident/incdir_46599/v12102_ora_4147_i46599.trc
Sat Dec 13 15:09:07 2014
Dumping diagnostic data in directory=[cdmp_20141213150907], requested by (instance=1, osid=4147), summary=[incident=46599].
Sat Dec 13 15:09:09 2014
Sweep [inc][46599]: completed
Sweep [inc2][46599]: completed

Okay, essentially, this tells us nothing interesting, except for the tracefile. Let’s look in/u01/app/oracle/diag/rdbms/v12102/v12102/trace/v12102_ora_4147.trc, being the tracefile as indicated in the above alert.log snippet:

*** 2014-12-13 15:08:57.351
Process may have gone over pga_aggregate_limit
Just allocated 65536 bytes
Dumping short stack in preparation for potential ORA-4036
----- Abridged Call Stack Trace -----
----- End of Abridged Call Stack Trace -----
781 MB total:
   781 MB commented, 646 KB permanent
   208 KB free (0 KB in empty extents),
     779 MB,   2 heaps:   "koh-kghu call  "            57 KB free held
Summary of subheaps at depth 1
779 MB total:
   778 MB commented, 110 KB permanent
    63 KB free (0 KB in empty extents),
     667 MB, 42786 chunks:  "pmuccst: adt/record       "
      83 MB, 5333 chunks:  "pl/sql vc2                "

Actually, this is the end of the tracefile. It seems that the pga limit dump (the text in between “Process may have gone over pga_aggregate_limit” to the private memory summary heap dumps) occurs several times before an actual ORA-4036 is triggered. In my private test instance, where I am obviously the only user process doing something, I get a pga limit dump approximately 20 times before the ORA-4036 is actually triggered:

sending 4036 interrupt
Incident 46599 created, dump file: /u01/app/oracle/diag/rdbms/v12102/v12102/incident/incdir_46599/v12102_ora_4147_i46599.trc
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Did you actually spot the oddity here?

Remember the PGA_AGGREGATE_LIMIT was set to 600M. Now look at the process’ PGA/Private heap summary dump above: it says 781M. Please mind the 781M is the PGA heap of a SINGLE process! When looking at the total PGA allocated for the entire instance, it’s even more:

SYS@v12102 AS SYSDBA> select value/power(1024,2) "MB" from v$pgastat where name = 'maximum PGA allocated';


So…despite PGA_AGGREGATE_LIMIT set to 600M, according to the v$pgastat view, there’s 1041MB allocated for PGA. Please mind I haven’t looked into how accurate v$pgastat is, but I tend to believe this.

I’ve seen PGA_AGGREGATE_TARGET being used as a calculation value for actual PGA usage of an instance. This is simply wrong. The actual amount of PGA memory allocated by the instance is highly depended on what is done, and can be less than PGA_AGGREGATE_TARGET, or more. Automatic PGA can control three per process memory area’s: the sort, hash and bitmap memory area’s. These are sized based on the setting of PGA_AGGREGATE_TARGET and the actual PGA memory usage instance wide. If more memory is needed for sort, hash or bitmap memory than is made available by the memory manager, excess memory needed is allocated in the temporary tablespace. Any other PGA memory allocation is always done, regardless of the setting of PGA_AGGREGATE_TARGET.

Starting with Oracle 12, it seems the actual PGA allocation now can actually be limited with the new parameter PGA_AGGREGATE_LIMIT. However, during some simple testing it shows that actually more memory can be allocated than set with PGA_AGGREGATE_LIMIT. I haven’t tested it in more situations, this post is meant to grow awareness that the actual limit as set by PGA_AGGREGATE_LIMIT might not be that hard as you would expect.

The next post will introduce a way to limit PGA usage in Oracle 11.2. Stay tuned!

Tagged: consolidation, database, exadata, linux, memory usage, oracle, pga, pga_aggregate_limit, pga_aggregate_target, swap, swapping, uga

Day 3+ of UKOUG Tech 14

Sorry it’s taken a couple of days to finish off this series of blog posts on UKOUG Tech 14. I had expected to get some time on the trip back to write this, but as you’ll see if you keep reading, that really didn’t happen. So let’s go back to Day 3 of the conference.

I started the day by attending Patrick Hurley’s “Adventures in Database Administration” session. This was quite different to the normal sorts of sessions you get at a conference. In fact, it was more like a roundtable session. For a start, Patrick did the whole presentation without a single slide, quite a refreshing difference! Basically he covered his career from the days when he became a DBA (with a brief nod to his development days prior to that). He also made the session much more interactive, getting feedback from attendees on when they had similar experiences. So while I didn’t learn anything new about the latest and greatest product, or deep technical details about something I probably will never be able to apply in my working life, I found this to be quite an enjoyable session overall.

Just before lunch, there was a session on “Rapid Database Cloning using SMU (Snap Management Utility) and ZFSSA”, by Jacco Landlust from Oracle. I think this session was a late add-on, as it wasn’t in the printed agenda, just the agenda boards and mobile app. Unfortunately for Jacco, I was the only attendee for that session, but I was quite interested to see how SMU worked and where it stands relative to Snap Clone (part of the EM product suite that also does rapid database cloning). SMU is specific for ZFS, whereas Snap Clone is storage agnostic (it provides both hardware and software options, including CloneDB – see my earlier posts here and here for more details on Snap Clone). SMU is also a much more standalone product, resulting in differences in clustering, security, auditing and logging. However, SMU does provide its much more limited functionality at a lower price, so for smaller sites it may be a viable alternative for cloning. I found Jacco’s session quite interesting as well, since I hadn’t been aware of SMU before. As I mentioned in my earlier post on Day 2 of the conference, there is also cloning functionality in ODA, so Oracle has quite a range of products to perform cloning, dependant on your hardware and other requirements, so it’s great to see the full range of capabilities in the different Oracle products.

The last session of the day is always a hard slot for a presenter to end up with, even more so when it’s the last day of the conference! This time, there were quite a few sessions I would have liked to get to, ranging from “Oracle Multitenant: Oracle’s Current and Future Architecure” by Dominic Giles, “Deep Dive into ASH and AWR in EM12c and Beyond”, by Kellyn Pot’Vin-Gorman, “Database as a Service on the Oracle Database Appliance Platform” by Marc Fielding and Maris Elsins, “Optimizing and Simplifying Complex SQL with Advanced Grouping” by Jared Still, and the “Women in IT” roundtable. I’d seen Kellyn’s presentation before, so that made it easy to cross that one off the list (sorry, Kellyn!). I would have liked to get to the Women in IT session as they are always interesting (though it did surprise me that it was on at the same time as Kellyn’s presentation as she is very passionate about Women in IT), but in the end went to Marc and Maris’s presentation. I had already seen the slides as Marc and Maris had sent them to me earlier in the week to validate some of their comments about EM12c, but you get so much more out of a presentation than just what’s on the slides if you attend the presentation. Of course, that’s if you have at least half decent presenters, and in this case both Marc and Maris are far more than half decent presenters! Again, it was a very interesting presentation to me personally, so that was a good way to round out the official part of the conference.

From there I went down to the Smuggler’s Cove bar and restaurant for a few drinks with some of the other attendees. The restaurant part was booked out for dinner, so we had dinner at another restaurant on Albert Dock, and that was the end of UKOUG Tech 14 for me! All in all, I found the conference to be very enjoyable. It has quite a number of presentations I wanted to get to (more than I could physically attend, unfortunately, as is often the case!) but of course, the best part of any conference for me is the networking you do with other attendees. It was great to meet up with quite a few people that I had interacted with before but hadn’t had the chance to physically meet, and of course it’s always enjoyable to catch up with old friends as well. Faye Wood and the other conference organizers did a great job, with most things working fairly smoothly. The only advice I would offer the organizers would be to not use that conference venue again, though. For that, there are three reasons:

  1. The presentation rooms off the exhibition hall itself were awful, from a sound perspective. It was impossible to NOT get sound bleed from the other presentation rooms, which made it physically quite difficult to hear at times. That was probably worse for me than most attendees, as I already have a hearing loss that makes it difficult for me to hear in noisy environments.
  2. The sessions in the main part of the venue were fine – unless it happened to rain hard or hail, which it did quite a few times over the course of the conference. Whatever the material was that the roof was made of, it was incredibly noisy when that happened and again I had a lot of difficulty hearing.
  3. A number of presenters I spoke to had difficulty getting their slides to present on the screens for attendees to see. My experience was (I think) unfortunately the worst, but others also had issues.

On Thursday, I went to Barclays to cover some of the material I had presented at the conference, as well as having a discussion about some of their specific issues with EM. This session was organized by Ian Carney, a good friend of mine from my days with Oracle in the USA. He had organized for quite a few of the Oracle speakers at the conference to visit Barclays at different times during the week, including Larry Carpenter, Uwe Hesse, Joel Goodman, Maria Colgan, and Graham Wood, so I felt quite honoured to also be asked to present with such an illustrious group.

From there I headed off to Manchester Airport, and the long trip home. This time I flew to Heathrow, then on to Dubai, Sydney and Canberra. Thankfully, I missed the issues they had at Heathrow because of problems in their computer systems. Unfortunately I wasn’t allowed to use the British Airways at Manchester lounge with my Qantas Club membership. As is often the case, if you are flying domestically and NOT flying business class, there is no reciprocity between the lounges, which has never impressed me much! :( As I had quite a distance to get around at Heathrow (and again at Sydney), the airline staff organized wheelchairs to get me from the gate to my next point of departure which did make life MUCH easier! Thankfully, I could use the BA lounge at Heathrow, but they were having issues with their wifi which meant I couldn’t get this blog posted there. The changes at both Dubai and Sydney were pretty tight, so I went direct to the gates both times, but at least this time my luggage made it with me! :)

I must say it’s great to be back home again, and to sleep in my own bed. To the conference organizers, a job well done. Apart from the sound issues, I really enjoyed my time at UKOUG Tech 14, and hopefully, I’ll be able to make it back again in the not too distant future!

ORA-4068 and CONSTANT keyword…good and bad

Anyone that has ever coded PLSQL will be familiar with the error ORA-4068, where you had some state persisted in a session due to a package variable, and then when you change the package, the state is cleared along with an ORA-4068.  Here’s a quick example:


Session 1 

SQL> create or replace
  2  package PKG is
  3    procedure P;
  4  end;
  5  /

Package created.

SQL> create or replace
  2  package body PKG is
  3      my_global int := 10;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

Session 2 

SQL> exec pkg.p

PL/SQL procedure successfully completed.

Session 1

Now we change the package… In this case, I’ve just changed the value of the global

SQL> create or replace
  2  package body PKG is
  3      my_global int := 11;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

Session 2 

And on next invocation, session 2 gets the error.  Not a great thing if you want to deploy changes to a live application (unless that application had the foresight to have appropriate handlers for ORA-4068).

SQL> exec pkg.p
BEGIN pkg.p; END;

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MCDONAC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "MCDONAC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "MCDONAC.PKG"
ORA-06512: at line 1


One of the nice things to come in 11.2, was that if the global variable was declared as CONSTANT, then the compiler/runtime engines was intelligent enough to know that there was no state, and hence the ORA-4068 could be avoided.  For example, the code below will not suffer from the ORA-4068 error.

SQL> create or replace
  2  package body PKG is
  3      my_global CONSTANT int := 12;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

This was cool because many a PLSQL programmer comes from a C, C++ or C# background, where constants are often used to enumerate static values, and so they use the same approach in PLSQL.  However, it appears that the compiler is not smart enough to deal with any form of expression.  For example, whilst the code below has a global defined as a constant…

SQL> create or replace
  2  package body PKG is
  3      my_global CONSTANT int := 13+15;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

…because it is assigned via an expression, the ORA-4068 still rears it head Sad smile 

SQL> exec pkg.p
BEGIN pkg.p; END;

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MCDONAC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "MCDONAC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "MCDONAC.PKG"
ORA-06512: at line 1

I discovered this when storing some constants for powers of 2 (for IP address manipulation).  So whilst

x := power(2,32)

conveys more meaning than

x := 4294967296

you need to be aware of the implications of doing so.

(plsql optimize level of 3 does not assist here).

Tested on and

EM12c Management Agent, OutOfMemoryError and Ulimits

While enjoying the lovely Liverpool, UK weather at Tech14 with UKOUG, (just kidding about that weather part and apologies to the poor guy who asked me the origin of “Kevlar” which in my pained, sleep-deprived state I answered with a strange, long-winded response…. :)) a customer contacted me in regards to a challenge he was experiencing starting an agent on a host that was home to 100’s of targets. - LOAD_TARGET_DYNAMIC running for 596 seconds
        oracle_database.rcvcat11 - LOAD_TARGET_DYNAMIC running for 596 seconds - LOAD_TARGET_DYNAMIC running for 596 seconds - LOAD_TARGET_DYNAMIC running for 596 seconds - LOAD_TARGET_DYNAMIC running for 596 seconds - LOAD_TARGET_DYNAMIC running for 596 seconds - LOAD_TARGET_DYNAMIC running for 596 seconds - LOAD_TARGET_DYNAMIC running for 596 seconds - LOAD_TARGET_DYNAMIC running for 596 seconds

Dynamic property executor tasks running

Agent is Running but Not Ready

The output from the “emctl start agent” wasn’t showing him anything he didn’t already know, but I asked him to send me the output and the following showed the actual issue that was causing the Agent not to finish out the run:

agentJavaDefines=-Xmx345M -XX:MaxPermSize=96M
Auto tuning was successful
----- Tue Dec  9 12:50:04 2014::5216::Finished auto tuning the agent at time Tue Dec  9 12:50:04 2014 -----
----- Tue Dec  9 12:50:04 2014::5216::Launching the JVM with following options: -Xmx345M -XX:MaxPermSize=96M -server -Dsun.lang.ClassLoader.allowArraySyntax=true -XX:+UseLinuxPosixThreadCPUClocks -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled -XX:+UseCompressedOops -----
#ff0000;">Agent is going down due to an OutOfMemoryError

This host target was a unique environment in that it contained so many targets, especially database targets.  One of the reasons that the management agent was created and OEM processing removed from an internal database back-end process was to lighten the footprint.  As EM12c introduced numerous features that has assisted its direction towards the center of the Oracle universe, the footprint became heavier, but I’ve been very impressed with development’s continued investment into lightening that footprint, even when considerable additions with plug-ins and metric extensions are added.

With all of this, the server administrator may have a different value set to limits on resource usage than what may be required for your unique environment.  To verify this, I asked the customer to run the following for me:

ulimit -Su
ulimit -Hu

Which returned the following expected values:

$ ulimit -Su
$ ulimit -Hu

The user limit values with these added arguments are to locate the following information:

-H display hard resource limits.
-S display soft resource limits.

I asked him to please have the server administrator set both these values to unlimited with the chuser command and restart the agent.

The customer came back to confirm that the agent had now started, (promptly!) and added the remaining 86 database targets without issue.

The customer and his administrator were also insightful and correctly assumed that I’d made the unlimited values not indefinitely, but as a trouble-shooting step.  The next step was to monitor the actual resource usage of the agent and then set the limits to values that would not only support the existing requirements, but allocate enough of a ceiling to support additional database consolidation, metric extensions, plug-in growth.







Copyright © DBA Kevlar [EM12c Management Agent, OutOfMemoryError and Ulimits], All Right Reserved. 2014.