Create artificial temporary stats for load testing in Oracle

Test the application with user created stats if you want to check the performance of SQL queries work on specific data amount after few years or months.
For this you can estimate and update the stats manually to predict or check the performance.

Manually set the Stats in Oracle for testing
Manually create statistics for a table, index, or the system. Following function used:
Table:DBMS_STATS.SET_TABLE_STATS
Index:DBMS_STATS.SET_INDEX_STATS
System:DBMS_STATS.SET_SYSTEM_STATS
Columns:DBMS_STATS.SET_COLUMN_STATS

Example to set Table stats

1. Check the Employee table stats.

SELECT NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME = 'EMPLOYEES' and owner='HR';

NUM_ROWS
----------
107


2. Set the employees table stats.

EXEC DBMS_STATS.SET_TABLE_STATS( ownname => 'HR',tabname => 'EMPLOYEES',numrows => 2000,numblks => 10 );


3. Check the employees table stats.

SELECT NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME = 'EMPLOYEES' and owner='HR';

NUM_ROWS
----------
2000


Example to set Index Stats

1. Check index stats.

SELECT NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME = 'EMP_EMP_ID_PK';

NUM_ROWS
----------
107


2. Set the Index stats as you want.

exec DBMS_STATS.SET_INDEX_STATS( ownname => 'HR', indname => 'EMP_EMP_ID_PK', numrows => 2000 );


3. Verify the Stats is updated.

SELECT NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME = 'EMP_EMP_ID_PK';
NUM_ROWS
----------
2000