DB13 UpdateStats fails with Process died due to signal 11

[former_member277156]

SAP 7.40/ Oracle 12c on AIX

UpdateStats from DB13 in our BI system runs for a few minutes, then fails.  It does not generate a ST22 dump.  In the Action Log of DB13 screen, it shows status = "Finished with error" and return code = "0006 Severe crash".

Any suggestions on what's causing this issue?

This detailed log looks like this.  As shown, the stats jobs starts processing and gets thru several tables, then it just dies. 

Job started

Step 001 started (program RSDBAJOB, variant &0000000000134, user ID BDEES)

No application server found on database host - rsh/gateway will be used

Execute logical command BRCONNECT On host bipdb01-lp

Parameters: -jid STATS20160610082235 -u / -c -f stats -t ALL

BR0801I BRCONNECT 7.40 (12)

BR0805I Start of BRCONNECT processing: cetjcwdv.sta 2016-06-10 08:22:35

BR0484I BRCONNECT log file: /oracle/BIP/sapcheck/cetjcwdv.sta

...

...

...

BR0883I Table selected to collect statistics after check: SAPBW3./BIC/AZSD_O0900 +(49945397/699649:7663925:0)

BR0280I BRCONNECT thread 6 time stamp: 2016-06-1008:23:20

BR0881I Collecting statistics for table SAPBW3./B28/FWFIXT3Z with method/sample EH/P10 ...

BR0280I BRCONNECT thread 2 time stamp: 2016-06-1008:23:20

BR0881I Collecting statistics for table SAPBW3./BIC/AZSD_O0900 with method/sample EH/P1 ...

Process died due to signal 11

SXPG_COMMAND_EXECUTE failed for BRCONNECT - Reason: unknown

Job cancelled after system exception ERROR_MESSAGE

[Reagan · Accepted solution]

What version of AIX is this system on?

Check the errpt errors.

Check if the statistics collection runs fine when you execute it from the OS level.

brconnect -u / -c -f stats -t all -f collect

[former_member277156 · Accepted solution]

AIX version:

     bipdb01-lp:bipadm 13> oslevel

     6.1.0.0

     bipdb01-lp:bipadm 15> oslevel -s

     6100-09-03-1415

Running stats from command line (brconnect -u / -c -f stats -t all -f collect) failed with core dump.  Below are the last lines of the execution:

BR0280I BRCONNECT time stamp: 2016-06-13 08:41:10
BR0815I Number of indexes/partitions in schema of owner SAPBW3: 64915/38245

BR0280I BRCONNECT time stamp: 2016-06-13 08:41:13
BR0818I Number of tables found in DBSTATC for owner SAPBW3: 126

BR0280I BRCONNECT time stamp: 2016-06-13 08:41:13
BR0807I Name of database instance: BIP
BR0808I BRCONNECT action ID: cetjrren
BR0809I BRCONNECT function ID: sta
BR0810I BRCONNECT function: stats
BR0812I Database objects for processing: ALL
BR0851I Number of tables/partitions with missing statistics: 90/400
Owner SAPBW3: 90/*
Owner SAPBW3: */400
BR0852I Number of tables to delete statistics: 0
BR0854I Number of tables to collect statistics without checking: 44369
Owner SAPBW3: 44369
BR0855I Number of indexes with missing statistics: 0
BR0856I Number of indexes to delete statistics: 0
BR0857I Number of indexes to collect statistics: 0
BR0853I Number of tables to check (and collect if needed) statistics: 0
BR0862I Force option with value 'collect' set
BR0846I Number of threads that will be started in parallel to the main thread: 6
BR0126I Unattended mode active - no operator confirmation required

BR0280I BRCONNECT time stamp: 2016-06-13 08:41:13
BR0877I Checking and collecting table and index statistics...
BR0847I Thread 1 started successfully
BR0847I Thread 2 started successfully
BR0847I Thread 3 started successfully
BR0847I Thread 4 started successfully
BR0847I Thread 5 started successfully
BR0847I Thread 6 started successfully
Segmentation fault (core dumped)

[Reagan · Accepted solution]

Update the BR*Tools to the latest supported patch level and see if that helps. I can see there is patch 21 and you are on patch 12.

Check SAP note 2087004 - BR*Tools support for Oracle 12c

BR*Tools 7.40 - full support for Oracle 12c with regard to functions to date plus support for the new features of Oracle 12c. The prerequisite for this is Patch 14 or above. BR*Tools 7.40 can be used with all SAP kernel versions for which Oracle 12c is released.

[Former Member · Accepted solution]

As already been mentioned, please check errpt output and attach detailed information (errpt -a) for relevant errors.

[former_member277156 · Accepted solution]

Finally got a chance to update BR Tools.  Now running 7.40 (patch 22).  With the new version of BR Tools, the Update Stats job is still returning an error, but it is finishing now instead of getting signal 11.

The error being returned is: shown below.  The table being processed at this time has 244 columns.  From what I can tell, it looks like this job is trying to feed a list of all columns into the DBMS_STATS.GATHER_TABLE_STATS procedure, which is causing the job to fail for that table.

Have you seen an issue like this before?  Is there an option or way to tell brconnect to not feed all the columns to the gather stats procedure?

Here is an excerpt of the error.  I remove some of the middle since it was just a list of columns.

BR0301E SQL error -6502 in thread 1 at location stats_tab_collect-65, SQL statement:

'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPBW3"', TABNAME => '"/BIC/AZSDO070100"', ESTIMATE_PERCENT => 3, METHOD_OPT => 'FOR ALL COLUMNS SIZ

E 1 FOR COLUMNS SIZE 75 "/BIC/ZASTAG","/BIC/ZCUSSALES", "/BIC/ZDELVITM","/BIC/ZSERIAL","/BIC/ZSORDITM"

......

....

...."/BIC/ZCLDSTRDT","/BIC/ZPUR_NO","VENDOR")',

DEGREE => 6, CASCADE => TRUE, NO_INVALIDATE => FALSE); END;'

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "SYS.DBMS_STATS", line 34663

ORA-06512: at line 1

[Reagan · Accepted solution]

Check the SAP note 2250334 - 12c : ORA-06502 PL/SQL: numeric or value error: character string buffer overflow creating ...

[former_member277156 · Accepted solution]

Reagan, thanks for that.  Ironically, when we first started getting the signal 11 errors with update Stats, we put in a work around similar to what the note suggested.  We scheduled a cron job to run stats at the DB level on the specific tables that were causing the signal 11.  We scheduled the cron job to run just before the Update Stats job, so that Update Stats job would see those tables stats were up to date and skip them.  By doing the DB stats before the DB13 job, we don't have to make any updates to the DBSTATC table.

Thanks again for you help and guidance.

[Former Member]

Hello Bradley,

As Reagan already asked, what version and TL of AIX are you on ?

Signal 11 is a segmentation fault and can be due to a number of things such as incorrect ulimits, missing APARs or quite simply a kernel bug.

As the Oracle user and also as the <sid>adm user could you execute "ulimit -a" and post the output here.

KR,

Amerjit

[former_member277156]

Hi Amerjit, I replied to Reagan with results of running stats as os.  Below is the other info you asked for.

AIX version:

     bipdb01-lp:bipadm 13> oslevel

     6.1.0.0

     bipdb01-lp:bipadm 15> oslevel -s

     6100-09-03-1415

<SID>adm user

> ulimit -a

time(seconds)        unlimited

file(blocks)         unlimited

data(kbytes)         unlimited

stack(kbytes)        unlimited

memory(kbytes)       unlimited

coredump(blocks)     2097151

nofiles(descriptors) 32000

threads(per process) unlimited

processes(per user)  unlimited

ora<SID>

> ulimit -a

time(seconds)        unlimited

file(blocks)         unlimited

data(kbytes)         unlimited

stack(kbytes)        unlimited

memory(kbytes)       unlimited

coredump(blocks)     2097151

nofiles(descriptors) 65536

threads(per process) unlimited

processes(per user)  unlimited