Common Sense

Developer: I am running this query: DELETE FROM tab; It's been running since 2PM yesterday.  Can you make it go faster? DBA: You shouldn't delete 80M rows in a single transaction.  You should have used TRUNCATE instead of DELETE.  I have killed your session and it will be a while before it completes. Developer (5 minutes later): I tried TRUNCATE but it says my table is locked. DBA: I know, the

Track and Trace E-Business Suite Concurrent Request – EBS Logs, O/S Process IDs, Locks

I often get asked to take a look at an Oracle eBusiness Suite concurrent request to see what it is doing, this can come from a few different angles:

  1. What requests are currently running?
  2. I have an operating system process that is taking too much CPU - what is it doing?
  3. Can you tell me where the request is at? I've clicked on the log but it doesn't show anything!
  4. My request is taking too long - can you check for blocking locks?

There are a number of strategies to track and trace where things are at for a running (more...)

Data Masking with Oracle Data Pump

If you expose production data to test, QA or UAT environment, most probably you’ll need to hide sensitive data. You can do this in different ways. One of them is to
use Oracle Data Pump to mask the data. You may choose to do that on the very export step (more...)

KEEP Pool

I have a persnickety problem with a particular table being aged out of the buffer cache.  I have one query that runs on a defined basis.  Sometimes when it is run, it does a lot of physical reads for this table.  Other times, it does no physical reads. So I decided to play around with a different buffer pool and let this table age out of cache on it's own terms rather than competing in the

Compression and SE

While researching a corrupt block on 11g SE, we came across a number of objects that were compressed according to the data dictionary. How could that be?  Compression is not a feature of SE, or so we thought. The objects in question were all indexes.  In fact, Oracle creates compressed indexes in ?/apex/core/tab.sql even though we are on SE. Further investigation lead me to Doc 1084132.1 which

Time Spent, Time Wasted

Sometimes tuning a database can be rewarding.  I get great satisfaction when helping a user take their query from 10 minutes down to 3 seconds.  But it doesn't always work that way.  Consider this recent exchange: Developer to DBA Manager: The database is slow. DBA Manager to DBA: Trace his process. The DBA traces the developer's process and comes up with about 12 queries that can benefit from

It’s always X, except when it’s not

I drill into my DBAs heads that the first step in tuning any process is to get a trace.  Don't show me the query plan until you show me what the database actually executed.  The trace is the fundamental clue in any performance investigation.  The wait events tell you exactly what's going on and there is no question where the bulk of the time is being spent. When we see a large number of waits on

11g Gotcha

Something I discovered recently is that the DEFAULT profile for Oracle 11g sets the PASSWORD_LIFE_TIME to 180 instead of UNLIMTED by default.  Applications will encounter an "ORA-28002: the password will expire within X days" error message if you keep the default value. To change the PASSWORD_LIFE_TIME, you: ALTER PROFILE default LIMIT password_life_time UNLIMITED; One thing to keep in mind is

What’s old is new again

We had a fair number of problems related to bind variable peeking in our 10.2.0.3 and 10.2.0.4 dbs.  In fact, the plans became so erratic that we decided to just turn bind variable peeking off by setting _optim_peek_user_binds=false in all the init.ora files. Flash forward to Oracle 11g (11.2.0.2) and the optimizer has been enhanced to use bind variable peeking much better.  After converting to

The Cost of Mediocre

Over the years I've written tons of scripts to monitor all sorts of things on the database.  Some of those scripts turned out to be helpful on an ongoing basis and some ended up dying on the vine when new features of Oracle came out. One of my scripts looks at the top processes in a database from the CPU's perspective.  In other words, how much time did the process use on the CPU over it's

Upgrading to Oracle 11gR2

I am about half way through upgrading all my dbs to Oracle 11gR2 from various versions of 10gR2.  Out of all the upgrades I've performed from 7.0 up to 10gR2, the 10g to 11g upgrade is by far the most hassle free of all. I have abandoned the GUI upgrade tool this time.  The GUI tool is fine, but internal issues prevented me from running the GUI remotely.  This meant that somebody had to be

From Oracle Newbie to Oracle ACE…

When I started this blog over 5 years ago, I never imagined that it would take me on a journey from an Oracle Newbie, to a Director of UKOUG and onto an Oracle ACE (even if I did go via some strange evenings in some strange pubs in Birmingham and (more...)

My first 11g show stopper: Purging the Recyclebin

I've run into my first issue with 11.2.0.2.  After one of my dbs was upgraded, every time I try to purge the recyclebin, I get an ORA-00600 error: SQL> purge dba_recyclebin; purge dba_recyclebin * ERROR at line 1: ORA-00600: internal error code, arguments: [ktcdso-1], [], [], [], [], [], [], [], [], [], [], [] It doesn't matter if the recyclebin is on or off. I have done about six upgrades and

Listener Logging in 11g

By default, the listener logging is not turned on in 11r2.  You have to turn it on by specifying: LOGGING_listener_name=ON in your listener.ora file.

Removing OLAP from 10g

There is a metalink note that describes how to remove the OLAP option if it was installed in your database incorrectly.  See ML Note 739032.1 for details.  Also know that the described procedure doesn't fully remove your OLAP object and that you have to drop some manually according to Note 1060023.1.

Environment Variables from database table – Oracle E-Business Suite

Are you running Oracle E-Business Suite (EBS) / Applications and want to get an operating system level environment variable value from a database table, for example for use in PL/SQL? Or perhaps to default a concurrent program parameter? Didn't think environment variables were stored in the database?

Try out out this query that shows you $FND_TOP:

select value
from   fnd_env_context
where  variable_name = 'FND_TOP'
and    concurrent_process_id = 
      ( select max(concurrent_process_id) from fnd_env_context );

VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/apps/apps_st/appl/fnd/12.0.0

Or did you want to find out the Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?

 (more...)

Job Market

The job market must be getting a little better. In the last weeks I've had three recruiter calls and they actually had jobs to fill.

Upgrading to R12.1.2, Tip #1

While applying patch 7303033 on top of a 12.1.0 R12 installation, I got the following error in one of my workers: FNDLOAD APPS/***** 0 Y UPLOAD @FND:patch/115/import/afscursp.lct @JTF:patch/115/import/US/jtfdiagresp.ldt - Connecting to APPS......Connected successfully. Calling FNDLOAD function. Returned from FNDLOAD function. Log file: /r12u/apps/apps_st/appl/admin/UPG/log/

Congratulations to my mate Paul!

Just a quick note to say congratulations to Paul Till, a mate of mine, at my current client, who has recently passed his OCM certification. I knew Paul was good, from having worked with him, on a DR implementation / upgrade for a large DW, but I hadn’t realised (more...)

$deleted$ tablespace names bug

This one turned out to be a an interesting bug the other day…

I did a simple select from DBA_TAB_PARTITIONS and noticed that some tablespace_names were of the form “_$deleted$n$m” where n and m are numbers. Slightly worrying, but at least the data was all present and correct, when I (more...)