TruTek TruTalk Newsletter for Oracle Professionals
 TruTek at Collaborate 09 in Orlando

June, 2009
Welcome to the June issue of TruTalk. We're back from Collaborate 09 in Orlando, Florida. We have some terrific classes coming up, taught by some of our favorite experts.  

Don't miss out on this terrific lineup of classes with the best trainers in their fields!


Mike Swing

ORA-04031 Errors by Tanel Poder
What We Do
Spiff Winners!
the little r12 upgrade guide
We Do More Than Just Training!
How to Automatically Resolve Blocking Locks Using DCD
Transaportable Database
Oracle Workflow SIG Meeting
Article Writing Contest
TruTek Training Schedule
ORA-04031 Errors and Monitoring Shared Pool Subpool Memory Utilization with sgastatx.sql by Tanel Poder
Don't miss Tanel Poder's upcoming seminar Advanced Oracle Troubleshooting Seminar, to be held in Denver June 15-17.  If you're on the fence about whether to take this intensive class, take a look at some class feedback.

Since Oracle 9.2, the shared pool can be "partitioned" into multiple parts. This was probably done to relieve shared pool latch contention for poorly designed applications (which use shared pool latches too much due to bad cursor or connection management).

The "partitions" are called shared pool subpools and there can be up to 7 subpools. Each subpool is protected by a separate shared pool latch and each subpool has its own freelists and LRU list. If you are interested in more details, a good starting point is this whitepaper by Oracle.
There are a few different ways to detect how many subpools you have in use. The more convenient ones are:

You could query X$KGHLU, which has a line for each shared pool subpool and (from 10g) also java pool if it's defined:

 SQL> select count(distinct kghluidx) num_subpools
       2 from x$kghlu
       3 where kghlushrpool = 1;
The "kghlushrpool" column, which is 1 for shared pool subheaps and 0 for java pool, isn't there in 9i (and in 9i the java pool apparently is not reported in x$kghlu anyway).

The reason I don't just count all matching lines from x$kghlu, but use count distinct instead, is that in Oracle there are 4x more lines reported in this x$table. There's an additional concept called sub-sub-pool starting from 10.2 where each shared pool sub-pool is split futher into 4 areas (allocations with different expected lifetime/durations go into different sub-sub-pools, but the same sub-pool latch protects all activity in sub-sub pools too). But in the x$kghlu reports all sub-sub-pools too for some reason. The whitepaper from Oracle mentioned above explains this in more detail.
So from the above output I see that in my instance all 7 shared pool subpools are in use. Oracle determines the number of needed subpools (during instance startup) based on your shared pool size and cpu_count. IIRC in 9.2 if you had 4 CPUs or more AND the shared_pool_size was bigger than 256 MB then 2 subpools were used, in 10g shared_pool_size had to be bigger for that, 512 MB I think and in 11g its 1GB. I don't recall the exact threshold values and that's not really important as you can see yourself how many subpools are in use with the above query.

Read More
What We Do
TruTekTruTek is a national leader in technical and functional Oracle training and consulting. We offer Oracle database and E-Business Suite consulting, training and remote services. We have a state of the art training facility in Salt Lake City, Utah. If you have 5 or more people interested in a class, we can also bring classes to your company if you would like onsite training.

And if you've wanted to take a training class, but were intimidated by the crowds, we also offer "Personal Training" for certain classes.
Collaborate 09 - It's All About the Spiffs
Fun TruTekOur Collaborate spiffs were a big hit at the conference. We managed to save a few for our loyal readers, so here are the winners of our two post-conference contests:

1. The Why My Boss Deserves a Spiff Far More Than I Contest - Daniel Malaxa, Brian Stecklein, Karen Woodbury, Gayle Biguere, Ann Danielson, Lynn Olpin, Susan Smith, Nola Humphries

Steve Wozniak2. The Who's a Bigger Geek Than Larry Elison? Contest - Lynn Olpin, Sharon Kovac, Larry Klein and Eric Suber

Surprisingly, Steve Wokniak and Bill Gates tied for the honor of being a bigger geek than Larry Elison.

For those of you who won, we will be sending you your prizes shortly.
Upgrades Shouldn't Be Scary


We've just changed TruTek's Oracle E-Business Suite R11i/R12 Technical Upgrade class from 4 days to 5 days long. We concluded that getting through an upgrade of the Release Vision instance to Release 12.0.6, complete with additional patches that were introduced prior to the class, needed a full 5 days to do it justice.

The class size is limited to 4 to 6 students to allow plenty of instructor attention for dealing with problems that arise, and questions that students have. Each student uses a quad core Linux server with 4-8 GB of memory and 1 TB of disk space to perform the upgrade.
Minimizing downtime is a common theme throughout the R12 Upgrade class. Techniques include merging patches, using fast IO, snapshots and other downtime reducing techniques.
Mike Swing's the little r12 upgrade guide describes the process we follow during the class. Can you skip the class and do it yourself with just this book? Sure you can. We think there are advantages to taking the class, most importantly having others to bounce questions and problems off of. Taking the class also offers the advantage of helping to build confidence levels.

the little r12 upgrade guideWill this book work perfectly for every environment that users have in place? Probably not. Since Oracle continues to provide patches for issues that are reported by customers, there is always a chance that after we publish this book a new patch will become available. This book is no substitute for using the power of your own analytical skills. And of course, our hardware will likely not be exactly the same as your hardware configuration, so there may be differences due to operating system and other variations.
We've got our first feedback from a reviewer:

I read the through the WHOLE BLESSED THING, and I have to say that this is incredible!  An incredibly thorough, detailed, and (near as I can tell) complete 165-page, step-by-step cookbook. Great work, Mike!
Tim Gorman
Don't miss Mike's upcoming training class, Oracle E-Business Suite R11i/R12 Technical Upgrade in Denver, June 15-19,  in Atlanta, July 13-18, and in Albany, NY, August 3-7. 
Did You Know? We Do More Than Just Training!
At TruTek, we offer training classes, remote database and applications administration support, and on-site consulting. And in the next month, a few of our top consultants will be rolling off of projects, so check them out:

Mike Swing - Besides teaching many of our E-Business Suite technical classes, including several of our DBA classes, Mike has extensive experience implementing, upgrading and maintaining various Oracle RDBMS versions and the E-Business Suite. Mike teaches our classes on installing and upgrading to Release 12, so if you need someone with plenty of hands on experience, he is one of our best resources. Mike also has experience supporting RAC implementations and has a deep understanding of parallel concurrent processing, load balancing and failover. 

Dennelle - It's always good to have a skilled financials functional expert in your corner. Dennelle specializes in General Ledger, Fixed Assets, Purchasing, Payables, Receivables, Cash Management, Order Management, ADI, FSGs and UPK.  Dennelle is also a great trainer, so her hand-offs to clients go especially well.

Rick - With 15 years of manufacturing planning, forecasting and inventory management experience, and 10 years of functional Oracle ERP implementation experience, Rick is one of our top manufacturing experts for the E-Business Suite. Rick specializes in supporting the Oracle BOM, CST, EAM, ENG, INV, PO, MRP, MSCA and WIP modules.

Samuel - Here's a Database Administrator with excellent credentials. Samuel is an Oracle Certified Professional, an Oracle 10g Certified Technician, and he has an Oracle DBA Masters. Couple that with years of experience as a senior database analyst, and you've got someone that can manage your database and work well with any development team.

Susan - Offering strong technical skills combined with excellent project management and team lead capabilities, Susan is an experienced ERP application designer and developer. Susan is a Certified Oracle Application Developer, and can resolve issues quickly due to her wide variety of experience and technical knowledge.

Fred - Fred has strong experience supporting large, worldwide Oracle E-Business Suite implementations and upgrades as a project manager, lead, and functional consultant. Fred's experience includes both Project Manufacturing and Oracle Financials. 

Joe - Joe is an Oracle developer with over 12 years of experience in designing, building, maintaining and enhancing both ERP and custom applications across a variety of business sectors. 

Bob - Bob is an accomplished Oracle DBA with extensive experience as an Oracle Apps DBA. Bob's E-Business Suite experience includes installing, updating and cloning environments.

Nathan - Nathan has both functional and technical expertise, which makes him a valuable asset in supporting the E-Business Suite. Nathan has worked as both an Oracle Database Analyst and Oracle Financial Applications specialist, responsible for implementing Oracle Accounts Payable, Oracle Purchasing, Order Management, Fixed Assets, Cash Management, Accounts Receivable and Oracle General Ledger. Nathan has performed data conversions as well as Oracle database installation, reorganization, tuning, and instance recovery on various Linux, Unix, NT, and VAX/VMS platforms. Nathan's technical skills include RMAN backup and recovery, RAC on AIX, Oracle Warehouse Builder, Workflow and XML Publisher.

Jim - Jim is one of our best manufacturing consultants. His experience includes full life cycle implementations in manufacturing management, purchasing management, production planning, and materials control. Jim has worked as a Project Manager, Team Leader, and as a Functional Application Implementer.

Craig - Craig's most recent assignment had him multi-tasking as a Unix System Administrator, Oracle Database Administrator, Applications System Administrator and E-Business Suite Developer. Craig's solid technical background makes him an excellent candidate for any E-Business Suite team.

Also, if you're looking for a developer to design custom software or programs for your company, we have a Java team ready to roll!  We have other consultants with E-Business Suite, DBA and developer skills available as well.

Contact us if you'd like to see more resumes.
How to Automatically Resolve Blocking Locks Using Dead Connection Detection by Mike Swing

In a recent load test of the Oracle Applications Release Oracle Time and Labor module, we simulated time card entry and approvals for 400 simultaneous users. The generated wait events and subsequent wait times indicated the top three potential performance improvements were:

1.        Running Statistics

2.        Purging Workflow

3.        Resolving Blocking Locks

The first two performance techniques are fairly well understood. These issues can be resolved by periodically running two concurrent programs: Analyze something something and Purge Obsolete Workflow Runtime Data (though Workflow may require additional scripts to deal with data that isn't in the correct format for the purge program).

The blocking locks are primarily caused by abnormally terminated processes that refuse to release database locks when the process terminates. The typical process to remove blocking locks is performed manually by the DBA at the command line by querying the session and serial number of the process and then killing the specific database session. This manual approach takes time for the user to recognize there is a problem and more time for the DBA to find the session information and kill the session.

The use of Dead Connection Detection will automatically remove any dead database connections for any application that can recognize a dead process. The problem is the database considers the SQL*Net connection to be active, and by default, the Transparent Network Substrate can't determine the status of the database connection .By setting parameters in the sqlnet.ora and tnsnames.ora files, dead connection detection can easily be enabled.

A common problem in Oracle Applications illustrates this problem. The Human Resources module uses a table named PER_ASSIGNMENT_ALL. Users access forms that lock rows in the table PER_ASSIGNMENT_ALL. When two users, or many times the same user with two forms open querying the same row, access that same row with the intention of updating the row, a blocking lock is created.  This can happen when the first form is closed abnormally and the lock is not released. One lock is blocking and the other lock is waiting, but the blocking lock process has terminated and the database doesn't realize the blocking lock is no longer valid, because DCD has not been enabled.

Oracle Network Basics

TCP/IP is a connection-oriented protocol, and provides packet timeout and retransmission in order to guarantee the safe and sequenced order of data packets. If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out. After TCP/IP gives up, then SQL*Net receives notification that the probe failed.

1.        Dead Connection Detection

Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later, including Oracle Net8. DCD detects when a partner in a SQL*Net V2 client/server or server/server connection has terminated unexpectedly, and releases the resources associated with it.

DCD is initiated on the server when a connection is established. At this time, SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm.  The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.

When the timer expires, SQL*Net on the server sends a "probe" packet to the client. The probe is an empty SQL*Net packet and does not represent any form of SQL*Net level data, but it creates data traffic on the underlying protocol.

If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset.  If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources.

The client may be running any supported SQL*Net V2 release. DCD is more resource-intensive than similar mechanisms at the protocol level.

With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.

To Configure Dead Connection Detection (DCD)

Implement by:

      changing SQLNET.EXPIRE_TIME = 1 (Minutes) to the sqlnet.ora file

With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.

If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out. After TCP/IP gives up, then SQL*Net receives notification that the probe failed.

If the client side connection is still connected and responsive, the client sends a response packet back to the database server, resetting the timer, and another packet will be sent when the next interval expires , assuming no other activity on the connection

If the client fails to respond to the DCD probe packet:

  • The Server side process is marked as a dead connection and
  • The database Process Monitor (PMON) performs the clean-up of the database processes / resources

Dead Connection Detection:

  • DCD initiates clean up of OS and database processes that have disconnected / terminated abnormally
  • DCD will not initiate clean-up for sessions that are still connected

2.        tnsnames.ora

Client side SQL*Net connections do not enable keepalive for TCP connections by default. However, it is possible to enable this by adding the ENABLE=BROKEN parameter to the SQL*Net connect string in the tnsnames.ora file on the server..

Sample TNS alias to enable keepalive (notice the ENABLE=BROKEN clause):




                 (ADDRESS_LIST =

                                 (LOAD_BALANCE = ON)

                                 (FAILOVER = ON)

                                 (ADDRESS = (PROTOCOL = TCP)(HOST = rh8)(PORT = 1521))

                                 (ADDRESS = (PROTOCOL = TCP)(HOST = rh6)(PORT = 1521)))

For more details, check out Mike's paper Parallel Concurrent Processing Failover and Load Balancing of E-Business Suite Release 11i and Release 12

Transportable Database by Robert Freeman
Don't miss Robert's upcoming classes Oracle Database 11g New Features, August 3-7 in Salt Lake City, and Backup and Recovery Using 10g RMAN in Denver, August 10-14.
I came across this little RMAN feature that I have not written about. So I thought I'd share it with you.

It's called Transportable Database. It's really an off-shoot of Transportable tablespaces except now you can move the whole database kit-and-kabootle over to a brand-new database on any supported Oracle platform. Transportable database is supported by RMAN. This is a new feature starting in Oracle Database 10g Release 2.

The steps to move the database between platforms is pretty straight forward:

1. Verify the prerequisites
2. Identify any external files and directories with DBMS_TDB.CHECK_EXTERNAL.
3. Shutdown (consistent) and restart the source database in READ ONLY mode.
4. Use DBMS_TDB.CHECK_DB to make sure the database is ready to be transported.
5. Run the RMAN convert database command.
6. Copy the converted files to the target database. Note that this implies that you will need 2x the storage on the source database for the converted files.
7. Copy the parameter file to the target database.
8. Adjust configuration files as required (parameter, listener.ora, tnsnames, etc).
9. Fire up the new database!

This can make for a much quicker and easier migration between platforms than the old IMPDP/EXPDP method!

Robert's latest technical book, called OCP Oracle Database 11g Certified Professional Study Guide, is available. It's a prep guide for the Oracle Database 11g OCP Exam (Exam 1Z0-053). Check out this and other great books on our books link.

Robert Freeman's OCP: Oracle Database 11g Certification Kit      Robert Freeman's Oracle Database 10g RMAN Backup and Recovery           Oracle Database 11g New Features    
Oracle Workflow SIG Meeting at Collaborate by Barbara Matthews
The ABCs of WorkflowI attended the OAUG Workflow SIG at Collaborate, which is always an interesting meeting. Hosted by John Peterson and Karen Brownfield, they covered quite a bit of ground.

The usual question at this point in Workflow's life cycle is: "When do you use BPEL instead of Workflow?" Good rules of thumb are:
  • If you are creating something totally customized, use BPEL. If you use Workflow, you'll end up having to convert it, sooner or later, to BPEL, so you might as well use BPEL.
  • If you are modifying an existing Workflow, use Workflow.
Oracle will not provide an automated migration tool to take your Workflow code and turn it into BPEL, so sooner or later, someone in your organization will need to learn BPEL. The switch to BPEL will occur when you migrate to the Fusion Applications. You have time, but you probably should start learning BPEL sooner, rather than later. The good news is, you can use BPEL now with both Release 11i and Release 12.

Confio Igniter Suite

Confio builds performance management software that improves the effectiveness of IT systems and the people who run them. The Confio Igniter Suite currently includes products that help Oracle DBAs, managers, and developers continuously monitor the performance of databases and the applications that depend on them, isolate specific problems, and identify solutions in production systems.

Confio Ignite

Your Claim to Fame...
AmazonYou might argue that our newsletter staff weighs in more on the technical side than the functional side. Yes, we admit it, we're geeks. But that doesn't mean we don't want to include articles about the functional side of the E-Business Suite. Really, we do. So here's your chance to help us keep a balanced perspective in our newsletter. If you've got a good functional topic, please, let us know. Enter our writing contest!  If you'd like to enter, just write an article about Oracle or the Oracle E-Business Suite. Tell us about a technique that you've used that made your life easier at work. Or describe something you've learned from all those books on your bookshelf, and how it applied to your environment. If you're not sure if you've got a good topic, send us a note, and we'll help you figure it out. Go ahead, you know you want to!

Submissions should be sent to by June 25th. We'll include the winning article in our next newsletter. And the winning author will win a very cool prize, a $50 Gift Certificate from Amazon!
Upcoming TruTek Training Schedule
We're always open to adding new classes, so let us know your interests! We add new classes regularly, so be sure to check the latest version of the schedule on our website.





Technical Classes


Jun 15-17

Advanced Oracle Troubleshooting Seminar with Tanel Poder

Denver, CO

Jun 15-19

Oracle R12 Applications DBA Concepts and Administration

Denver, CO

Jun 15-19

Oracle E-Business Suite R11i/R12 Technical Upgrade

   Denver, CO

Jun 23-26

Introduction to Oracle Business Intelligence EE (OBIEE)


Jun 23-26

Oracle Release 11i Applications System Administration


Jul 6-10

Oracle R12 Applications  DBA Concepts and Administration

   Atlanta, GA

Jul 13-18

Oracle E-Business Suite R11i/R12 Technical Upgrade

   Atlanta, GA

Aug 3-7

Oracle Database 11g New Features with Robert Freeman


Aug 3-7

Oracle E-Business Suite R11i/R12 Technical Upgrade

Albany, NY

Aug 10-14

Backup and Recovery Using 10g RMAN with Robert Freeman

Denver, CO

Aug 10-12

Oracle BI Publisher


Aug 13-14

Oracle Discoverer for Admin


Aug 19-20

Oracle Discoverer for End User Training


Sep 14-18

Oracle R12 Applications  DBA Concepts and Administration

Fullerton, CA

Sep 14-18

Oracle E-Business Suite R11i/R12 Technical Upgrade

Fullerton, CA

Sep 15-16

The Best of Oracle PL/SQL Seminar with Steve Feuerstein

Fullerton, CA

Sep 15-16

Oracle SQL Performance Tuning Tips and Techniques

Fullerton, CA

Sep 17

Toad Tips and Techniques

Fullerton, CA

Sep 21-25

Oracle 10g DBA Boot Camp I with Robert Freeman


Nov 10-11

The Best of Oracle PL/SQL Seminar with Steve Feuerstein

New Jersey

Nov 9-13

Oracle 10g DBA Boot Camp II with Robert Freeman

New Jersey

Nov 9-13

Oracle R12 Applications  DBA Concepts and Administration

New Jersey

Dec 10-14

Oracle 10g DBA Boot Camp II with Robert Freeman



Functional Classes


Jun 22-24

Oracle Release 12 AME (Approvals Management Engine)


Jun 29-Jul 2

R11i/R12 Financial Business Process Overview

San Diego, CA

Jul 6-7

Oracle Release 11i Fixed Assets

San Diego, CA

July 6-9

Oracle Release 12 New Features (Functional)

San Diego, CA

Jul 13-16

Oracle Release 11i Procure to Pay

San Diego, CA

Jul 13-14

Oracle Release 11i Accounts Payable

San Diego, CA

Jul 15-16

Oracle Release 11i  Purchasing

San Diego, CA

Aug 5-6

Oracle Applications Desktop Integrator (ADI) Training


Enough Already!
Our newsletter editor's dad, who is 82, likes to spend a little bit of time every day shredding his junk mail and then putting it into the pre-addressed stamped reply envelope and mailing it back in. That's one way to deal with too much information! We know how it is. You're on everybody's mailing list, and maybe you're just not interested in being there. Hey, we don't want to cram your mailbox unless you'd like to hear from us. So here's what you can do:

If you like our newsletter and think someone you know would like it as well, click on this box to forward it:
Forward to a Friend

And, if you're just not the right person for this newsletter, click on Safe Unsubscribe at the bottom of this newsletter and take yourself off the list.

And if you accidentally remove yourself from the list and want to be put back on, click here, enter your email and be sure to click Submit, then click Update Profile in the email that you receive.

And if we're just not hitting the topics that you want to hear about, either submit an article yourself, or click here and fill out our survey. We know people who know stuff - that's our claim to fame - and we'll get them to write about that stuff!
Contact us for Group Discounts and Additional Offers 801-486-6655