Update Stats & Rebuild Index

[Former Member]

Hi All,

We are using ECC 6.0 installed on HP-Unix and Oracle 10g.

Update statistics job is running continously in our system but still in our Early Watch report we have got some tables to rebuild their indexes.

We expect that Update stats job shows us for tables whose indexes needs to be rebuild but still we have not got any table name in update stats job log to rebuild index.

So it seems that there is some creteria which Update stats job check to provide information to rebuid its index.

Please confirm that creteria and suggest how can we find out information for tables whose rebuild index is required to improve database performance.

Regards,

Shivam

[lbreddemann]

>

> Update statistics job is running continously in our system but still in our Early Watch report we have got some tables to rebuild their indexes.
> 
> We expect that Update stats job shows us for tables whose indexes needs to be rebuild but still we have not got any table name in update stats job log to rebuild index.
> 
> So it seems that there is some creteria which Update stats job check to provide information to rebuid its index.
> Shivam

Why do you expect this to be the case?

Where have you read that the update stats job will provide the information what indexes should be rebuild?

Maybe informing yourself about "Index Fragmentation" (e.g. in SAP notes) would help you...

regards,

Lars

[Former Member]

Brconnect does not update tables if there are no significant changes. By default is 50% new entries must be inserted to update the table stats.

You can force individual table by

brconnect -u / -p initSID.sap -l E -f stats -t "VBAK" -g 5 -p 5 -f collect

This is the command that I use to udpate our stats for most used app tables.

Thanks

Srikanth M

[Former Member]

Hi Srikanth,

Thanks for reply.

So it means if less than 50 % changes are there in Table , Its statistics will not be Updated by Update stats jobs.

Please suggest the way to find out such tables which we are not getting in Update stats jobs.

Shivam

[Former Member]

SELECT NULL OWNER, NULL TABLE_NAME, NULL CREATION_TIME FROM DUAL WHERE 1 = 0

UNION ALL (

SELECT NULL OWNER, NULL TABLE_NAME, NULL CREATION_TIME FROM DUAL WHERE 1 = 0

) UNION ALL ( SELECT * FROM (

WITH BASIS_INFO AS

( SELECT

'SAP%' OWNER

FROM

DUAL

)

SELECT

T.OWNER,

T.TABLE_NAME,

TO_CHAR(O.CREATED, 'dd.mm.yyyy hh24:mi:ss') CREATION_TIME

FROM

BASIS_INFO BI,

DBA_TABLES T,

DBA_OBJECTS O

WHERE

T.OWNER LIKE BI.OWNER AND

T.OWNER = O.OWNER AND

T.TABLE_NAME = O.OBJECT_NAME AND

T.LAST_ANALYZED IS NULL AND

O.OBJECT_TYPE = 'TABLE' AND

T.TEMPORARY != 'Y'

));

You can use the above script to find the tables with out stats otherwise, I use ST04 to determine the top sql and navigate to table stats which are older wrt current date and I would update their stats by RSANAORA repot.

Thanks

Srikanth M

[lbreddemann]

Hello Srikanth,

would you mind to stop writing plain nonsense in this forum?

What exactly is it that the script you copy and pasted into this forum does?

Does it provide information about indexes that need to be rebuild?

No way!

What other information does it present?

Have you actually read and understood the SQL?

If so, you understood that the statement just returns those tables (non-temporary tables!) for which no CBO statistics had been created.

So, what has this do to with index rebuilds?

Riente. Nothing. Nada. Nix.

Please - it's not a problem to not understand the question.

But throwing arbitrary answers into the forum is not improving anything.

regards,

Lars

[Former Member]

I never commented anything about Index Rebuild. I was quick to answer the thread and only posted about Table stats.

This earlier script would output the tables which doesn't have the table stats.

For index rebuild we can use RSORAISQN ABAP report to find out the frag indexes and rebuild them using brspace tool.

Personally, I would focus on addressing the issue rather than pointing at someones nonsense post.

Regards

Sri