Create artificial temporary stats for load testing in Oracle

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.