FND_GLOBAL affected by New Global Performance Changes

Have you ever tried to use the search option of Online Help in Oracle Applications?
Our users did... and they got "The page cannot be found" message...
I checked it on firefox, hope to get more accurate message, and I got this:
"Not Found. The requested URL /pls/DEV/fnd_help.search was not found on this server".
  I checked fnd_help package and it's compiled and looks fine.
These errors appeared in error_log_pls:
[Mon Jul 28 10:34:54 2008] [warn] mod_plsql: Stale Connection due to Oracle error 20000
[Mon Jul 28 10:34:54 2008] [error] mod_plsql: /pls/DEV/fnd_help.search ORA-20000
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1209
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "APPS.FND_HELP", line 1043
ORA-06512: at "APPS.FND_HELP", line 873
ORA-06512: at line 20
I found note 306239.1 - "Cannot Search Online Help After Fresh Install of 11.5.10" which suggest that Applications interMedia Text indexes are corrupt. In my instance it even wasn't exist...
I followed this note which instructs to:

1. drop index FND_LOBS_CTX;
2. Rebuild the index using aflobbld.sql

But aflobbld.sql had been running for more than 10 hours and the size of DR$FND_LOBS_CTX$I table has reached to 35 GB !
I had been wondering how it can be that fnd_lobs table is less than 1GB and the index on it is 35 GB and counting.... ?!
Note 396803.1 - "FND_LOBS_CTX is having huge size, how to reduce the sizeof the index?" suggests it's a bug, indexing all documents in FND_LOB table, also the binary files, while using wrong filter.
So how can we make aflobbld.sql to index only FND_HELP documents?
For each row in FND_LOBS table the file_format column is populated with one of the following values: IGNORE, BINARY, TEXT.
aflobbld.sql will index only rows that have this column set to BINARY or TEXT.
If we set all rows to IGNORE except FND_HELP rows, we could index them only.
Note 397757.1 - "How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data" suggests the steps to do it.
These are the steps:

1. Backup the fnd_lobs table before updating it, we will use it later:
    
   create table fnd_lobs_bk as select * from fnd_lobs;  
   ** you can create a backup of this table and omit the file_date column to make this backup faster
    
2. Drop index FND_LOBS_CTX if exists:
    
   drop index applsys.FND_LOBS_CTX;  
3. Update all rows to IGNORE except FND_HELP rows:
    
   update fnd_lobs
   set file_format = 'IGNORE'
   where nvl(program_name,'@') <> 'FND_HELP' ;  
4. Execute aflobbld.sql from OS terminal:
    
   sqlplus apps/sppas @$FND_TOP/sql/aflobbld.sql applsys apps;  
5. Since I'm not sure about the impact of leaving the FND_LOBS rows as IGNORE, I updated them back to the previous state:
    
   create unique index fnd_lobs_bk_u1 on fnd_lobs_bk (file_id);
    
   update (select fl.file_format ffo,flb.file_format ffb
           from fnd_lobs fl
               ,fnd_lobs_bk flb
           where fl.file_id = flb.file_id)
   set ffo=ffb;
    
   drop table fnd_lobs_bk;  
6. Check the search option.... it should work now.

You are welcome to leave a comment .
Aviad

Thursday, May 29, 2008

FND_GLOBAL affected by New Global Performance Changes

After applying ATG Rollup 5 patch (and above) we discovered an issue with some of our custom developments.
For some processes we got the following errors:
ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 1410
ORA-06512: at "APPS.FND_GLOBAL", line 1655
ORA-06512: at "APPS.FND_GLOBAL", line 2170
ORA-06512: at "APPS.FND_GLOBAL", line 2312
ORA-06512: at "APPS.FND_GLOBAL", line 2250
and this:
ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been
detected in
fnd_global.set_nls.set_paramenters('NLS_LANGUAGE','AMERICAN').
After some debug work we found that this issue happens when executing FND_GLOBAL.apps_initialize more than once within a trigger/via a db link in the same transaction.
According to Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" this issue cause by a new global performance changes.
Oracle Development said: "Very sorry if the new global performance changes have exposed you to this error, but there is no way we can back out these changes. They are not only complex and wide spread but required to maintain functional performance levels. Using fnd_global to change user/resp context from a trigger is not only not supported it is ill advised."
OK, So we had to find a workaround to this issues and we found two...
I'll start with a sample of the new behavior of fnd_global to demonstrate the issue and the solutions/workarounds will come right after.
SQL> create table test1 (a number, b number); Table created
SQL> insert into test1 (a) values (1001); 1 row inserted
SQL> insert into test1 (a) values (1002); 1 row inserted
SQL> commit; Commit complete
SQL> create or replace trigger test1_trg_bi
  2  after update on test1
  3  for each row
  4  begin
  5       fnd_global.APPS_INITIALIZE(:new.a,1,1);
  6       -- fnd_request.submit_request...
  7       -- ....
  8       -- ....
  9  end;
10  / Trigger created
SQL> select fnd_global.user_id from dual;     USER_ID
----------
        -1
SQL> update test1 set b=1101 where a=1001; 1 row updated
SQL> select fnd_global.user_id from dual;     USER_ID
----------
      1001
SQL> update test1 set b=1102 where a=1002;
update test1 set b=1102 where a=1002
ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger
has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 1410
ORA-06512: at "APPS.FND_GLOBAL", line 1655
ORA-06512: at "APPS.FND_GLOBAL", line 2170
ORA-06512: at "APPS.FND_GLOBAL", line 2312
ORA-06512: at "APPS.FND_GLOBAL", line 2250
ORA-06512: at "APPS.TEST1_TRG_BI", line 2
ORA-04088: error during execution of trigger 'APPS.TEST1_TRG_BI'
As you can see, the second update failed because apps_initialize was executed for the second time in the same transaction.
Now I'll show two ways to workaround this issue:
1) As suggested in Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" a wrapper Concurrent Request which contain a call to the context set (apps_initialize) and afterwards submits the original request, is one possible solution.
instead:
create or replace trigger test1_trg_bi
after update on test1
for each row
declare
     . . .
begin
     fnd_global.APPS_INITIALIZE(:new.a,1,1);
     ret_code := fnd_request.submit_request ('OWNER', 'ORIGINAL_CONC', . . .);
     . . .
     . . .
end;
create the following trigger:
create or replace trigger test1_trg_bi
after update on test1
for each row
declare
     . . .
begin
     ret_code := fnd_request.submit_request ('OWNER', 'WRAPPER_CONC', . . . , :new.a, . . . );
     . . .
     . . .
end;
additionally - create a new plsql concurrent (WRAPPER_CONC) that contains the fnd_global.apps_initialize and submits the ORIGINAL_CONC concurrent request.
This way, the apps_initialize statement executed in a separate transaction with no error.
This is the preferred and recommended solution by Oracle.
2) The second solution is easier to implement, works fine but according to Note: 556391.1 is not supported since it contains calls to fnd_global within a database trigger.
Anyway...
The idea is to call the apps_initialize in an Autonomous Transaction procedure.
Follow this sample:
SQL> create or replace procedure test1_apps_init (p_user_id number) is
  2  pragma autonomous_transaction;
  3  begin
  4       fnd_global.APPS_INITIALIZE(p_user_id,1,1);
  5       commit;
  6  end;
  7  /
Procedure created
SQL> create or replace trigger test1_trg_bi
  2  after update on test1
  3  for each row
  4  begin
  5       test1_apps_init (:new.a);
  6       -- fnd_request.submit_request...
  7       -- ....
  8       -- .....
  9  end;
10  /
Trigger created
SQL> select fnd_global.user_id from dual;     USER_ID
----------
        -1
SQL> update test1 set b=1101 where a=1001; 1 row updated
SQL> select fnd_global.user_id from dual;     USER_ID
----------
      1001
SQL> update test1 set b=1102 where a=1002; 1 row updated
SQL> select fnd_global.user_id from dual;     USER_ID
----------
      1002
As you can see, the update statements were executed successfully this time and the session was updated with the appropriate user context in each update statement.
Those two solutions are working fine, but keep in mind that the second is not supported.
You are welcome to leave a comment.

Nag