Missing Statistics Error in BI system

[Former Member]

Hello All,

In our BI System when we run check DB job from DBACOCKPIT we were getting the following log file with error as missing statistics and some warnings in parameters. However when we update statistics using tcode DB20 and run this job again we were not getting this MISSING_STATISTICS error. Also we have scheduled the Job Update statistics once in a week. Kindly refer the log below. How should we fix it? Our system details wer BI 7.0 with SPS13, OS - Windows 2003 Server, DB - Oracle. So far we have not applied the note 706927. Will this be the reason for the above problem? Help with your idea's.

-

---

Job log overview for job: DBA:CHECKDB_________@115118/2000 / 11511800

Job started

Step 001 started (program RSDBAJOB, variant &0000000000019, user ID <USERID>)

Execute logical command BRCONNECT On host <HOSTNAME>

Parameters: -u / -jid CHECK20080205115118 -c -f check

BR0801I BRCONNECT 7.00 (26)

BR0477I Oracle pfile D:\oracle\<SID>\102\database\init<SID>.ora created from spfile D:\oracle\<SID>\102\database\spfile<SID>.ora

BR0805I Start of BRCONNECT processing: cdxetrgh.chk 2008-02-05 11.51.19

BR0484I BRCONNECT log file: J:\oracle\<SID>\sapcheck\cdxetrgh.chk

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.24

BR0813I Schema owners found in database <SID>:

DBSNMP, DIP, OPS$<HOSTNAME>\<SID>ADM, OPS$<HOSTNAME>\SAPSERVICE<SID>, OPS$<HOSTNAME>\SAPSERVICESR3, OUTLN, SAPSR3*, SAPSR3DB%, SYS, SYSTEM,

TSMSYS

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.31

BR0814I Number of tables/partitions in schema of owner SAPSR3: 22222/466

BR0836I Number of info cube tables found for owner SAPSR3: 3036

BR0814I Number of tables in schema of owner SAPSR3DB: 582

BR0814I Number of tables/partitions in schema of owner SYS: 621/171

BR0814I Number of tables/partitions in schema of owner SYSTEM: 134/27

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.34

BR0815I Number of indexes/partitions in schema ofowner SAPSR3: 31290/1252

BR0815I Number of indexes in schema of owner SAPSR3DB: 891

BR0815I Number of indexes/partitions in schema ofowner SYS: 675/180

BR0815I Number of indexes/partitions in schema ofowner SYSTEM: 175/32

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.45

BR0816I Number of segments in schema of owner DBSNMP: 25

BR0816I Number of segments in schema of owner OPS$<HOSTNAME>\<SID>ADM: 1

BR0816I Number of segments in schema of owner OUTLN: 8

BR0816I Number of segments/LOBs in schema of owner SAPSR3: 56527/1338

BR0816I Number of segments/LOBs in schema of owner SAPSR3DB: 1971/249

BR0816I Number of segments/LOBs in schema of owner SYS: 1770/85

BR0816I Number of segments/LOBs in schema of owner SYSTEM: 353/22

BR0816I Number of segments in schema of owner TSMSYS: 4

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.45

BR0961I Number of conditions found in DBCHECKORA:118

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.45

BR0818I Number of tables found in DBDIFF for owner SAPSR3: 175

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.45

BR0818I Number of tables found in DBSTATC for owner SAPSR3: 100

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.45

BR0807I Name of database instance: <SID>

BR0808I BRCONNECT action ID: cdxetrgh

BR0809I BRCONNECT function ID: chk

BR0810I BRCONNECT function: check

BR0951I Number of active/inactive check conditions for database administration: 23/0

BR0952I Number of active/inactive check conditions for database operations: 8/2

BR0953I Number of active/inactive check conditions for database messages: 58/0

BR0954I Number of active/inactive check conditions for database profile: 27/0

BR0960I Last successful BRCONNECT check run: cdxeptku.chk 2008-02-04 16.44.04

BR0126I Unattended mode active - no operator confirmation required

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.45

BR0969I Checking database administration...

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/PDCINDIC

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SBWCOUNTER

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SDCINDIC

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SDOC_YEAR

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SINDSPECSTK

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SMOVETYPE

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRMA_RFLG

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRMA_XCC

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRSL_STAUTO

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRSL_STMAT

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRT_MATPOST

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRT_MOVADJ

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRT_MOVINV

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRT_MOVRET

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRT_MOVTRAN

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SRT_QTFYVRN

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SSTOCKRELEV

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/SVALSPECST

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/TDCINDIC

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/TINDSPECSTK

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/TRSL_STMAT

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BI0/TVALSPECST

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BIC/B0000404000

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BIC/B0000409000

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BIC/B0000410000

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BIC/B0000411000

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BIC/B0000412000

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (table) SAPSR3./BIC/B0000413000

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/PDCINDIC~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SBWCOUNTER~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SBWCOUNTER~00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SDCINDIC~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SDCINDIC~001

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SDOC_YEAR~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SDOC_YEAR~001

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SINDSPECSTK00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SINDSPECSTK~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SMOVETYPE~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SMOVETYPE~001

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRMA_RFLG~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRMA_RFLG~001

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRMA_XCC~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRMA_XCC~001

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRSL_STAUTO00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRSL_STAUTO~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRSL_STMAT~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRSL_STMAT~00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MATPOST00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MATPOST~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MOVADJ~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MOVADJ~00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MOVINV~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MOVINV~00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MOVRET~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MOVRET~00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MOVTRAN00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_MOVTRAN~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_QTFYVRN00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SRT_QTFYVRN~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SSTOCKRELEV00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SSTOCKRELEV~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SVALSPECST~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/SVALSPECST~00

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/TDCINDIC~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/TINDSPECSTK~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/TRSL_STMAT~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BI0/TVALSPECST~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BIC/B0000404000~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BIC/B0000409000~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BIC/B0000410000~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BIC/B0000411000~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BIC/B0000412000~0

BR0970W Database administration alert - level: ERROR, type: MISSING_STATISTICS, object: (index) SAPSR3./BIC/B0000413000~0

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.46

BR0972I Checking database operations...

BR0973W Database operation alert - level: WARNING, operation: Archive log backup, condition: Operation too old or has not been r

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.46

BR0974I Checking database messages in J:\ORACLE\<SID>\SAPTRACE\BACKGROUND\alert_<SID>.log ...

BR0976W Database message alert - level: WARNING, line: 16825, time: 2008-02-05 10.47.00, message:

Checkpoint not complete

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.46

BR0977I Checking database profile...

BR0978W Database profile alert - level: WARNING, parameter: LOG_BUFFER, value: 6139904 (>< 4096,512KB)

BR0978W Database profile alert - level: WARNING, parameter: OPTIMIZER_FEATURES_ENABLE, value: 10.2.0.1 (set in parameter file)

BR0978W Database profile alert - level: WARNING, parameter: PARALLEL_EXECUTION_MESSAGE_SIZE, value:2152 (>< 16384,4096)

BR0978W Database profile alert - level: ERROR, parameter: QUERY_REWRITE_ENABLED, value: TRUE (<> FALSE)

BR0978W Database profile alert - level: ERROR, parameter: REPLICATION_DEPENDENCY_TRACKING, value: TRUE (<> FALSE)

BR0978W Database profile alert - level: WARNING, parameter: SHARED_POOL_SIZE, value: 322961408 (< 400 MB)

BR0978W Database profile alert - level: WARNING, parameter: STAR_TRANSFORMATION_ENABLED, value: FALSE (<> TRUE)

BR0978W Database profile alert - level: WARNING, parameter: STATISTICS_LEVEL, value: TYPICAL (set in parameter file)

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.46

BR0980I Number of changed database profile parameters: 0

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.49

BR0955I Number of signaled error/warning/exception alerts for database administration: 73/0/0

BR0956I Number of signaled error/warning/exception alerts for database operations: 0/1/0

BR0957I Number of signaled error/warning/exception alerts for database messages: 0/1/0

BR0958I Number of signaled error/warning/exception alerts for database profile: 2/6/0

BR0806I End of BRCONNECT processing: cdxetrgh.chk2008-02-05 11.51.49

BR0280I BRCONNECT time stamp: 2008-02-05 11.51.49

BR0803I BRCONNECT completed successfully with warnings

External program terminated with exit code 1

BRCONNECT returned error status E

Job finished

-

---

[Former Member]

this was a great help. So it appears every time we do a data load from R-3 to BW we have to run update stats before we run dbcheck inorder to avoid the missing statistics errors when dbcheck runs. below is the schedule we have setup and it seems to have resolved our statistics errors.

Time Process

3am Data Load

315 am Check and update optimizer statistics

330 am CheckDB

Thanks for all your help.

Chad

[Former Member]

Chad,

This can be automated in the process chain(BI) to run the stats when the data load is finished.

You dont need schedule seperately.

What if the data load time extended?

[JPReyes]

Hi Madhu,

MISSING Statistics means exactly that!... schedule and run "Check and update optimizer statistics" at DB13 and this errors should be gone.

About

"BR0280I BRCONNECT time stamp: 2008-02-05 11.51.46

BR0977I Checking database profile...

BR0978W Database profile alert - level: WARNING, parameter: LOG_BUFFER, value: 6139904 (>< 4096,512KB)

BR0978W Database profile alert - level: WARNING, parameter: OPTIMIZER_FEATURES_ENABLE, value: 10.2.0.1 (set in parameter file)

BR0978W Database profile alert - level: WARNING, parameter: PARALLEL_EXECUTION_MESSAGE_SIZE, value:2152 (>< 16384,4096)

BR0978W Database profile alert - level: ERROR, parameter: QUERY_REWRITE_ENABLED, value: TRUE (<> FALSE)

BR0978W Database profile alert - level: ERROR, parameter: REPLICATION_DEPENDENCY_TRACKING, value: TRUE ( FALSE)

BR0978W Database profile alert - level: WARNING, parameter: SHARED_POOL_SIZE, value: 322961408 (< 400 MB)

BR0978W Database profile alert - level: WARNING, parameter: STAR_TRANSFORMATION_ENABLED, value: FALSE (<> TRUE)

BR0978W Database profile alert - level: WARNING, parameter: STATISTICS_LEVEL, value: TYPICAL (set in parameter file)"

This does not mean the parameters are incorrect (even tho will be worthy to check them), this means that they are out of the tresholds stablished at transaction DB26... so check your parameters and adjust them at DB26.

This should fix your problems

Regards

Juan

[Former Member]

Hello,

Michael and John - Thanks for your time and reply.

U mean to say we can avoid the MISSING_STATISTICS error. I have scheduled the Job update statistics once in a week. Is this going to be fine? or should we re-schedule the job or any other activities to be done? Our BW Consultants were not aware of this missing index. Can we ignore this error or change the frequency of this job update statistics. What will be the case if its a production system? Will it affect the performance of the system much. Kindly throw some light on this.

Regarding parameters i will go through the note and will get back if any clarifications needed.

regards,

Madhu

[Former Member]

Hi again

From a technical point of view the MISSING_STATISTICS is not a very severe error. But in your case missing stats on these objects might lead to poor access plans during BI queries, especially when the objects are big.

As a general rule, there should be new statistics collected every time the object is created or significantly changed.

Best regards, Michael

[Former Member]

Read point number 12 for the stats frequency.

Link: [https://service.sap.com/sap/support/notes/588668]

[Former Member]

Hi Madhu

MISSING_STATISTICS: these mean, that there are Objects (tables / table partitions) without cbo statistics. I can see that in your logfile all objects are BI objects (/BIC/, /BI0). These are created during data loads, compressions etc, these can be startet manually by your BI people or in background jobs. As far as i know it is possible to gather statistics directly when the jobs were started. Ask your BI folks about this. Another solution is to schedule the Update Statistics job before you run the Checkdb job.

Parameter Warnings: check if the parameters are correctly set, see sap note 830576 - Parameter recommendations for Oracle 10g. If they are correct and you still get the errors, then you need to customize the check settings. They can be altered in transaction DB17.

Best regards, Michael