Kevin Kempf's Blog

August 16, 2010

Gather Schema Stats in 11i

Filed under: 11g, 11i — kkempf @ 9:56 am

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…

Advertisements

Blog at WordPress.com.