[ptrikha21]

In Information Technology field, the best practices these days have a core thing at the center. It is that things are first perfected through Iterative and Multiple rounds(preferably) checks on Development/Test/Beta environments and then push on the Real Time, Live systems known as the Production systems.
In some exceptional situation, we might have to put something on the Production system, but that is at best avoidable.
Now, these environments ideally ought to be a replica of the Production systems.
However, it is not possible to buy so expensive servers or systems, or have as much data as we have in the production. Yet what do the technical folks do when faced with Production issues, that they have to replicate and work upon in the Development or the Test systems?
Well, here comes the concept of using Production systems Stats to such test systems.
And let us see how this is done in Oracle.

How to go about simulating Production like statistics and conditions on the Test systems

In Oracle Database, we have a mechanism wherein, we can replicate Production like conditions in Test environments.
This is the process of generating system, object or schema level statistics, storing them and then using them in another system. There are some limitations, as apart from the Database, the Production and Test environments also differ in terms of other attributes like Network speed, storage and other characteristics. Still, it can be an effective tool to start analyzing issues with more helpful conditions to visualize and come up with some good solutions and not merely fixes or workarounds.

A demo for the simulation :

1. After login through a Sql user :

sqlplus <username> /<password> @ tns and then when our session opens(Command prompt/Toad/Sql Developer or some other tool)

Create the stats_table
Execute the following API:
exec dbms_stats.create_stat_table(
ownname => '<schemaname>',
stattab => '<tablename for stats>');

2.Gather the statistics with gather_system_stats. Here, we compute histograms on all indexed columns as well

DBMS_STATS.gather_schema_stats(
ownname=> '<schemaname>' ,
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=> 'FOR ALL COLUMNS SIZE AUTO') ;

3. Export the stats to the prod_stats table using exp:
exp <username>/<password> file=<statsfile>.dmp log=stats_<date>.log tables=<Statstable> rows=yes

e.g-
exp scott/tiger file=scott_stats.dmp log=scott_stats.log tables=scott_stats_tbl rows=yes

4. Copy the above dump file(scott_stats.dmp) to the Production Server.

5. Now, we can go to the Target system and schema and use the import_schema_stats to use the above generated statistics to "copy" the Production statistics to the smaller test environment:

exec DBMS_STATS.import_schema_stats(ownname=> 'scott', stattab=> 'StatsTable', statown=> 'Admin_db' );

This in a way "imports" or "brings" the Production like scenario(or statistics) to our test environments and help us in solving some very critical issues.
And from experience, having Production like data does helps in problem solving.

Partly referred : My post on Wikinut in 2015 :

Source :
https://www.wikinut.com/how-to-utilize-production-stats-to-simulate-production-like-statistics-in-oracle-database-test-systems/1ni99as1/bq8vz_1h/