Oracle statistics without Brtools

[Former Member]

Hi Oracle experts ,

I have a simple question is that any option to run  sql statement  to update oracle stats equivalent to Brconnect commands ?

I mean is that possible not to use Brtools to update oracle statistics for SAP Systems /

Thanks ,

Euge

[janos_mucsi-besze · Accepted solution]

Hi Michael.

Yes, it is possible. BR*Tools is using DBMS_STATS package to update the statistics. But please note, SAP is using its own way of statistics management (DBSTATC table). Oracle and so DBMS_STATS package if you run this all alone, will never check if there are tables with special handling needs. This can lead to big problems later on if you create incorrect statistics for a table that requires special handling.

Regards,

János

[fidel_vales · Accepted solution]

Hi,

I will add a little comment to Janos reply:

Janos Mucsi-Besze wrote:

... This can lead to big problems later on if you create incorrect statistics for a table that requires special handling.

Regards,
János

this WILL lead to problems in the future.

Note that BRCONNECt is using DBMS_STATS, therefore not using to calculate statistics them implies that you are losing specific SAP features.

[JamesZ]

Hi Euge,

Of course, it is possible to run oracle command to update statisitcs instead of brtools. In fact, brtools call oracle

package dbms_stats. To find out the exact oracle command brtools uses backend, you can increase brtools trace level by appending ' -TRC 15' to the brcommand you use. Then check brtools log.

Best regards,

James

[Former Member]

hi Michael,

You can use dbms_stat exec packages to run the stats as like below:-

for schema stats:-

exec dbms_stats.gather_schema_stats( -

  ownname => '<schema>', -

  options => 'GATHER AUTO', -

  estimate_percent => dbms_stats.auto_sample_size, -  --> you can mention your own percentage here

  method_opt => 'for all columns size repeat', -

  degree => 34 -

  )

For table stats:-

EXEC DBMS_STATS.gather_table_stats('<schema>', '<table name>');

EXEC DBMS_STATS.gather_index_stats('<schema>', '<indexname>');

Let us know if this helps.

Regards,

Ram