Table Statistics for EDI40 hung

[Former Member]

Hi ,

We are running table statistics for EDI40 table and its been long time more than 12 hours , no updates about it we are not sure whether something is happening or it is totally hung, Can you please anyone help , how to know the status of running Oracle Table statistics  like how much completed .. etc..

It is for our SAP POS system which is running with Oracle Database on Linux.

Thanks ,

Amaresh Nayak

[Former Member]

Hello Amaresh,

You can check if it's stuck by simply looking at the session in ST04 and checking if logical/physical reads are incrementing.

I'm not 100% sure if stats update will be visible in longops but please try the following SQL to see if your session appears:

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1;

Now the obvious question, how many entries do you have in EDI40 ?

KR,

Amerjit

[Former Member]

Hi Amerjit ,

Thanks ,

I get below out put from the querry

       SID    SERIAL# OPNAME                                                                SOFAR  TOTALWORK   COMPLETE

---------- ---------- ---------------------------------------------------------------- ---------- ---------- ----------

       909       2363 Table Scan                                                         65456298  130505222      50.16

Number of rows

TABLE_NAME                       NUM_ROWS

------------------------------ ----------

EDI40                            25220400

Is Table scan activity is same as Table Statistics ??

Because i see Table scan activity is going on for many other tables which we have not triggered.

May be its internal activity .

Few more points i like to mention

for this table statistics run was failing from 2 months , and later when it was triggered on June 30th It  got completed in just 70 Minutes with P1 sample size.

Thanks ,

Best regards,

Amaresh Nayak

[JamesZ]

Hi Amaresh,

Can you also share the arw, ash report?

Best regards,
James

[Former Member]

Hi Amerjit ,

Again the Table scan EDI40 got failed , can you please  suggest how can we run Statistics in Parallel for Large tables.

Best Regards,

AMaresh Nayak

[fidel_vales]

Hi,

The following command is "not good" for what you want:

brconnect -u / -c -f stats -o SAPSR3 -t EDI40 -e null -m E -s P.3 -f collect -p 12

-p is parallelization at BRCONNECT level, not at oracle level. as you are doing one table, then no parallelization is used.

You have to change the parallelization of the statistics to be run in paralllel for that table. If you want to increase the parallelism for the statistic calculation you have to use the -g option:

brconnect -u / -c -f stats -o SAPSR3 -t EDI40 -e null -m E -s P.3 -f collect -g 12

look at the following links for details

https://help.sap.com/saphelp_nwpi71/helpdata/en/46/a0bdc55d9452b6e10000000a155369/content.htm

https://help.sap.com/saphelp_nwpi71/helpdata/en/46/9f5515bb8e0488e10000000a1553f6/content.htm?frames...

BTW. I think that forcing the calculation of statistics on one table is (in general) a very bad idea and imply that a proper "performance issue" analysis is not done

[Former Member]

Hello Amaresh,

You just need to correlate the SID that is being shown from the script to the SID related to the stats update.

Your EDI40 table does seem to contain quite a few entries and you should look at archiving/deletion of processed IDOCs.

Now I see that you posted the command you are using and Fidel has already replied to this. "-p" is for the number of threads and "-g" is for degree of parallelism.

You should still be able to follow the progress of your stats update (using correct syntax given by Fidel) using the longops script I posted earlier.

Out of curiosity, could you tell us the size of your EDI40 table (DB02) as I did the math on what you pasted in and I get a very surprising number.

Kind Regards,

Amerjit

[JamesZ]

Hi Amaresh,

Which tools you are using? Can you check if the oracle process/session is working on the corresponding task?

Best regards,
James

[Former Member]

Hi James,

I have triggered Table statistics using BR connect , as below

brconnect -u / -c -f stats -o SAPSR3 -t EDI40 -e null -m E -s P.3 -f collect -p 12

and Yes I can see oracle session running , Lock file and log file created under sapcheck folder.

But its been more than 15 hours not able to find how much its progressed or it is hung.

Thank you.

Best Regards,

Amaresh Nayak