Performance - Transfering Stats 11i - Oracle Trainings for Apps & Fusion DBA

Performance – Transfering Stats 11i

Source: Oracle Documentations

It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:
SQL> EXEC DBMS_STATS.create_stat_table(‘DBASCHEMA’,’STATS_TABLE’);
SQL> EXEC DBMS_STATS.export_schema_stats(‘APPSCHEMA’,’STATS_TABLE’,NULL,’DBASCHEMA’);

This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:

SQL> EXEC DBMS_STATS.import_schema_stats(‘APPSCHEMA’,’STATS_TABLE’,NULL,’DBASCHEMA’);
SQL> EXEC DBMS_STATS.drop_stat_table(‘DBASCHEMA’,’STATS_TABLE’);

Issues
Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
It is found gathering stats for the SYS schema can make the system run slower, not faster.
Gathering statistics can be very resource intensive for the server so avoid peak workload times or gather stale stats only. яндекс
Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads

About the Author Atul Kumar

Leave a Comment:

5 comments

Jagjeet Singh says January 23, 2008

>It is found gathering stats for the SYS schema can make the system run slower, not faster.

It is not the case always that gather sys stats
will make system slower. Please share your exp.
on this with specific detail. you can not see much
effect for medium size databases but databases
with large no. of objects would get benefited.

In 9i — Gather stats on sys only be done after
consulting with oracle.

in 10g — we can gather stats on sys as oracle
says it would use CBO for all recursive queries BUT I have seen in some queries still hard-coded hints.

11g — I hope this is purely CBO for sys/internal queries.

Regards,
Jagjeet Singh

Ramnik Gupta says January 23, 2008

Hi Jagjeet Singh,

Please read the post care fully.

“It is found gathering stats for the SYS schema “can” make the system run slower, not faster.”

I clearly mentioned that it can make the system run slower not faster.

We have 7 TB of database size and this post is based on my experience and Oracle Recommdation. It doesn’t apply to all environment (small or medium). It is really good you pointed out. Do you still have question please ask.

Regards
Ramnik Gupta

Jagjeet Singh says January 23, 2008

Yes Ramnik,

I have read the post and I am saying there would be some scenarios where system would
run much faster if you analyze the SYS schema.

Performance impact can be noticed on a system with
really large no. of records in internal tables.
Mean with really large no. of objects.

If CBO is useful for the Non-SYS schemas then
this would also be helpful for sys schema
as internal/recursive queries won’t perform
good with hard-coded hints [ RBO ].

This is the reason oracle has introduced this
feature.

1 – May I know what made you to believe on this, If you can tell us the reason ..

>It is possible to transfer statistics between >servers allowing consistent execution plans >between servers

Apart from statistics there are other environment settings also which may change the
plan of query.

Regards,
Jagjeet Singh

Ramnik Gupta says January 23, 2008

Please find my comments below:

In 8i with Apps Environment – Never gather stats for the SYS schema, because it is not supported or recommended

In 9i with Apps Environment – Gathering status for SYS schema is supported but not necessary.

In 10g with Apps Environment – It is mandatory to collect stats for SYS schema

Metalink note # 368252.1 is the reason to believe. I hope this will close the loop.

Anthony Cordell says January 24, 2008

Using the copy command in SQL*Plus translates all number values to decimals thus losing precision. At a minimum, this will cause the clustering factors to be either 1 or 0 instead of the in-between values that they probably are on the source. I’d recommend using exp/imp or a dblink for the actual transfer.

Also, you would want to include your CPU costing information to make sure plans are identical across environments. export_system_stats/import_system_stats can be used the same way.

Add Your Reply