Statistics on IOT issue

[Former Member]

Hi,

I have this issue with my oracle statistics.

BR0301E SQL error -20000 at location stats_ind_collect-3, SQL statement:

'BEGIN DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => '"SAP<SID>"', INDNAME => '"SYS_IOT_TOP_114208"', ESTIMATE_PERCENT => 1, DEGREE => NULL, NO_INVALIDATE => FALSE); END;'

ORA-20000: Unable to analyze INDEX "SAP<SID>"."SYS_IOT_TOP_114208", insufficient privileges or does not exist

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

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

ORA-06512: at line 1

BR0886E Collecting statistics failed for index SAP<SID>.SYS_IOT_TOP_114208

Iu2019ve read sap note 641435 solution part 15 but thatu2019s not very helpful. I do not understand the <object_id> in the note in relation with the error above from DB16ORA

SELECT TO_CHAR(CREATED, 'dd:mm:yyyy, hh24:mi:ss') FROM DBA_OBJECTS

WHERE OBJECT_NAME = 'SYS_JOURNAL_<object_id>';

Anyway, one could suggest to exclude the statistic for these IOTu2019s, so I did. In ora<sid>.sap the following parameter is set.

stats_exclude = (SYS_IOT, SAP<SID>.SYS_IOT)

Unfortunately this is not helping too. The error remains. All help/suggestions/remarks/ideas are appreciated.

Kind regards

Patrick

[Former Member]

Hi,

The reason for your stats not getting updated was

" "SAP<SID>"."SYS_IOT_TOP_114208", insufficient privileges or does not exist"

Check if the index exist in your DB.

If it exist then try to find out who was owner of index and who was owner of retaled table, if there is ownership mismatch then also stat update fails.

Regards,

Sitarama R Uppalapati

[Former Member]

Apparently these indexes are created during a BI load. I've noticed that when I run the statistics at a later time, it runs OK. So I'm save to conclude that the indexes do not exist when I start a statistics run. So the sollution might be to not run statistics during a BI load.

[Former Member]

You should not update statistics during BI loads or during any data loads of SAP systems.

Please close the thread if your problem was resolved.

[Former Member]

Indeed , it's not a good idea to run statistics during a load. However, this is the source machine and one should not expect temporary tables/indexes to be created while the target BI machine sucks the data out. A learning moment nevertheless.

So the solution was to schedule the statistics run 2 hours later. Now I have those pretty green bars again in DB13.

Thanks.

Edited by: P. van Alphen on Dec 20, 2010 9:58 AM

[Former Member]

Hello Patrick,

this error should not happen - BRCONNECT should be clever enough to skip these indexes from statistics creation. Which version and patch level of BRCONNECT do you use?

Regards

Martin

[Former Member]

I'm running db stats from db13. Indeed you can only choose the PSAP<SID>* tables, but is that a wise thing to do.

I have learned that as of Oracle 10g, statistics must exist for ALL SAP tables.

The command used in DB13 is brconnect -u / -jid STATS20091023070000 -c -f stats -t ALL

P

Edited by: P. van Alphen on Dec 2, 2010 3:21 PM

[Former Member]

Can you please check whats there in table DBSTATC? for more information about this as below:

help.sap.com/saphelp_nw04/helpdata/en/df/455e93747111d6b25100508b6b8a93/frameset.htm and also follow the link Configuring Update Statistics in CCMS (Oracle) on same page.

[Former Member]

What/where do I look for exactly. DB21 does not show theses indexes.

[Former Member]

Are you running the db stats from SAP (db13) or OS level? If you execute via DB13 then you will have only option to select psap* tablespaces.

The index SYS_IOT_TOP_xx is owned by SYS and is under SYSAUX & SYSTEM tablespaces.

Can you please give the command which you executing?