Issue with Data Generation and SQL Loader Workflow

[breakxing]

Hi !!

I am reaching out regarding an issue encountered while using the data generation functionality to create a dataset of the desired size, which was then loaded into the Oracle database using SQL*Loader. However, when I executed the Virtual User’s "run" functionality, the output displayed as follows:

System achieved 0 NOPM from 6 Oracle TPM

In contrast, when I started from the GUI by manually creating the schema and following the steps to the Virtual User’s "run" functionality, the scores appeared normal.
Could you kindly provide guidance on identifying the root cause of this discrepancy?
Thank you for your assistance.

[sm-shaw]

Hi,

First step is to run the schema check, set the settings the same as the schema you created, i.e. if you loaded 1000 warehouses set that the same in the GUI as well and then run the check, it will tell you if the data in the schema is consistent and everything is created correctly.

[sm-shaw]

This message should be straightforward "TPROC-C Schema check failed system schema on table warehouse no indices." You have created the tables and loaded the data but not the indexes. You also need to create the stored procedures as well.
If this proves difficult another way is to do the load with the GUI once and then backup the user/schema and restore it, then this will backup and restore everything.

[breakxing]

Hi sm-shaw,

I successfully resolved the issue by following your suggestion. Thank you very much for your help!

However, I noticed that when running the schema check consecutively, check consistency 4 alternates between passing and failing. This confuses me, and I would like to understand why this is happening.

[sm-shaw]

You would need to provide the output for some guidance, also your performance results NOPM/TPM will help with this.

[breakxing]

When a schema check error occurs, the output is:
"Error in Virtual User 1: Error: TPROC-C Schema check failed system schema consistency check 4 failed."

However, when ignoring this error and running virtual user run with 8 virtual users while using all warehouses,
the NOPM is 14,713, and the TPM is 34,038.

[sm-shaw]

OK, that check is, for each District within a Warehouse, the sum of Order-Line counts [sum(o_ol_cnt)] for the Orders associated with the District equals the number of rows in the ORDER-LINE table associated with the same District. So as you are manually loading the data it is possible that something is not right how this has been done. I'd suggest loading with HammerDB directly and then running the test against this schema to make sure you don't see the same error.