Gather Schema Stats in 11i

Background

You are gathering stats, right?

If not, you’re in for a nasty surprise one day, when the statistics go stale and all your explain plans go South.  This happens occasionally to me in non-production ERP environments that haven’t been cloned in a long time (I cancel gather schema stats jobs after a clone).  It feels like after 6 months Oracle marks them as stale, but I can’t recall off the top of my head; it could be a year.

Concurrent Request to gather statistics

I use the sysadmin concurrent request Gather Schema Stats to keep statistics on the apps schemas up to date; I run it weekly on Saturday mornings, when load is low and nobody will really notice:

Status: Error, Completion Text: Normal. Huh?

Error#1: Object statistics are locked

So I happened to notice that the request had errored out, and I began to keep closer tabs on it.  The first week, it failed with the following 
 
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS: object_name=PO.PO_OTM_OUT***ORA-20005: object statistics are locked (stattype = ALL)***

In short, this meant that the statistics were locked on this table, and the job failed.  I’d seen this before, but not in ages.  In fact I’d written a pl/sql routine to clear this at one point in the past, it was so prevalent.  So I simply had EM schedule a job to run prior to the statistics run which force-unlocked the statistics on all the schemas:

procedure unlock_schema_stats
is
  cursor c1
  is
    select
      distinct (owner) ownername
    from
      dba_tab_statistics
    where
      stattype_locked is not null
    and
      owner not in ('SYSTEM', 'APPLSYS', 'SYS')
    ;
begin
  for c1_rec in c1
  loop
    dbms_stats.unlock_schema_stats (c1_rec.ownername)
    ;
  end loop
  ;
end unlock_schema_stats
;

This worked great; however it revealed the next problem…

Error#2: Duplicate rows in fnd_histogram_cols

I wasn’t expecting to see a problem this week, but I came in this morning and saw this:

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
                        object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
                        object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
                        object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

It turns out, this is a known issue with 11g RDBMS per Doc 781813.1; essentially there are duplicate rows in FND_HISTOGRAM_COLS for the table in question.  Somehow this worked in 9i and 10g, but not with 11g.   The solution is to simply locate the duplicate rows and delete them.

declare
  cursor c1 is
    select
      table_name
     ,column_name
    from
      fnd_histogram_cols
    group by
      table_name
     ,column_name
    having
      count(*) > 1
    ;
begin
  for c1_rec in c1 loop
    delete from
      fnd_histogram_cols
    where
      table_name = c1_rec.table_name
    and
      column_name = c1_rec.column_name
    and
      rownum=1
    ;
  end loop
  ;
  commit
  ;
end
;
/

Error#3: Duplicate rows in fnd_histogram_cols

After errors #1 and #2 were resolved, I got a new error:

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

The column PARENT_FLEX_VALUE is listed in FND_HISTOGRAM_COLS, but it does not exist in the table anymore.. that is the reason the fnd_stats fails (fnd_stats calls dbms_stats, which in 11g makes more consistency checks than the previous versions, but the error messages are not clear)

Now I had to run:

delete from FND_HISTOGRAM_COLS
where table_name = ‘FII_FIN_ITEM_HIERARCHIES’
and column_name = ‘PARENT_FLEX_VALUE’;

We’ll see if that fixes it…

Patching Oracle on Windows 2003 (and how msconfig can make it less painful)

When is a file more than a file?

When it’s a .dll, of course!  Then it’s like a villain in a bad Bruce Willis movie.    It refuses to die.  Or in this case, be deleted.

The walk-through

I went to upgrade to the 10.2.0.5 client on a few Windows 2003 applications servers and remembered why I hate Windows so much.  It’s just not enterprise-ready.   After I get a few steps into the installer, it’s complaining about 2 services running (COM+ System Application and Distributed Transaction Coordinator):

You Shall not Pass!

Linux would behave more properly; if it had permissions (which I can manipulate without a reboot!) it would overlay a file and pick up the new version when the service or process restarted.  However, I’m stuck in Bill Gates’ idea of a server, so like a good Windows person, I go over and stop those services:

I'm sure this is all it will take, right?

But… SURPRISE!  It didn’t work.  Now I get the mysterious “Close any running applications” error:

Isn't the installer a running application?

Plan B

Start->Run->msconfig

Bringing out the big guns

First, check “Hide all Microsoft Services”.  Then Disable All:

A word of caution, if you run a non-Windows service to log in to the server (in my case, VNC), you need to enable that or you can’t get back in.

OK, now uncheck the “Hide All Microsoft Services” box, and uncheck the two it specifically complained about before (COM+ System Application and Distributed Transaction Coordinator, your mileage may vary) for good measure.

You thought Windows was handicapped before!

Hit Apply & OK.  You will get this message:

Now hit restart and see how it comes back.  Windows should obediently remind you that you’ve crippled it:

Note to self

Now, the Oracle installer runs without complaint!

I will reluctantly bend to your will

Finally, when you’re done with the installer or whatever you may be doing (I suspect this trick will work well for CPU patches as well), restore the system startup services via msconfig and reboot: