Oracle TPC-H refresh test fails

Madhura
2013-11-05
2013-11-12
  • Madhura
    Madhura
    2013-11-05

    I am trying to run the TPC-H tests on an Oracle database. I was able to build the schema and run the query part of the tests successfully. When I change the driver options to enable refresh, and I create a virtual user and start it, I am getting the following errors:

    Vuser 1:Error in cursor 2:oratcl0.2 ORA-02291: integrity constraint (TPCH.LINEITEM_ORDER_FK) violated - parent key not found
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-02291: integrity constraint (TPCH.LINEITEM_ORDER_FK) violated - parent key not found
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-02291: integrity constraint (TPCH.LINEITEM_ORDER_FK) violated - parent key not found
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-02291: integrity constraint (TPCH.LINEITEM_ORDER_FK) violated - parent key not found
    Vuser 1:Error in cursor 1:oratcl0.1 ORA-02291: integrity constraint (TPCH.ORDER_CUSTOMER_FK) violated - parent key not found

    and so on.

    Has anyone seen this and were you able to resolve it? If so, can you share your experience and what you did to resolve this issue?

    This is my first time working on TPC-H and Oracle databases, so please forgive my ignorance.

    Thanks,
    Madhura

     
  • Steve Shaw
    Steve Shaw
    2013-11-06

    Hi,

    It appears from the error that you have run the refresh function previously and are attempting to re-run it. There are some details in the documentation on this http://hammerora.sourceforge.net/hammerora_oracle_dss_v2.7.pdf - the refresh function can only be run on a new schema once (this is based on the TPC-H specification) - once it has been run the schema needs to be refreshed (eg datapump export and import) to run it again otherwise you see constraint violations for both existing and missing keys as the refresh function both inserts and deletes rows. Although its not covered in the documentation (it does need updating) if running with one virtual user it is a special case and it does a power test by running an insert refresh - query stream and delete refresh. Otherwise to run the refresh stream you need multiple virtual users with the first one doing the refresh and the rest doing the queries.
    So it looks like you need to refresh your schema - back it up and run your test again - this should mean that no longer see the constraint violation errors.

    Cheers,

    Steve

     
  • Madhura
    Madhura
    2013-11-06

    Thanks. I did an impdp and then retried the refresh test with multiple virtual users. But now I am getting a different error :

    Vuser 1:Error in cursor 2:oratcl0.2 ORA-01950: no privileges on tablespace 'SYSTEM'
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-01950: no privileges on tablespace 'SYSTEM'
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-01950: no privileges on tablespace 'SYSTEM'
    Vuser 1:Error in cursor 1:oratcl0.1 ORA-01950: no privileges on tablespace 'SYSTEM'
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-01950: no privileges on tablespace 'SYSTEM'
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-01950: no privileges on tablespace 'SYSTEM'
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-01950: no privileges on tablespace 'SYSTEM'

    I have no idea why it is complaining about the 'SYSTEM' tablespace. The tablespace that it should use is 'TPCHTAB'.

    Thoughts on why I am seeing this error?

    Madhura

     
  • Steve Shaw
    Steve Shaw
    2013-11-07

    Madhura,

    it looks like there was an error somewhere in your export/import process and the data has indeed gone into your SYSTEM tablespace, you are correct that no data should go into your SYSTEM tablespace although simple queries can help locate where your data is. Once it has been correctly imported into a tablespace with the correct privileges then it should work for you although remember once you run the refresh function just the once you have changed the data and then to run it again from the start you need to refresh the schema again or with Oracle using the flashback functionality can be a very quick way to reset the data for retesting.

    Cheers,

    Steve

     
  • Madhura
    Madhura
    2013-11-11

    I am following the instructions in the Oracle TPC-H related document from the HammerDB website(http://hammerora.sourceforge.net/hammerora_oracle_dss_v2.7.pdf). But whether I do the export as SYSTEM user or as TPCH user, it seems to fail. The output that I get from the export says that there are no rows and no bytes:

    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/COMMENT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
    . . exported "TPCH"."CUSTOMER" 0 KB 0 rows
    . . exported "TPCH"."LINEITEM" 0 KB 0 rows
    . . exported "TPCH"."NATION" 0 KB 0 rows
    . . exported "TPCH"."ORDERS" 0 KB 0 rows
    . . exported "TPCH"."PART" 0 KB 0 rows
    . . exported "TPCH"."PARTSUPP" 0 KB 0 rows
    . . exported "TPCH"."REGION" 0 KB 0 rows
    . . exported "TPCH"."SUPPLIER" 0 KB 0 rows
    Master table "TPCH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded


    Dump file set for TPCH.SYS_EXPORT_SCHEMA_01 is:
    /mnt/oradb/dump1/expdat.dmp
    Job "TPCH"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Nov 10 23:18:49 2013 elapsed 0 00:00:15

    The above is the output when I try the export with the tpch user, but with the system user I get the same problem.

    And later, when I try to run the refresh function for the very first time on the new built schema (after doing one sanity query cycle) I get the parent key related errors:

    user 1:Error in cursor 1:oratcl0.1 ORA-02291: integrity constraint (TPCH.ORDER_CUSTOMER_FK) violated - parent key not found
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-02291: integrity constraint (TPCH.LINEITEM_ORDER_FK) violated - parent key not found
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-02291: integrity constraint (TPCH.LINEITEM_ORDER_FK) violated - parent key not found
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-02291: integrity constraint (TPCH.LINEITEM_ORDER_FK) violated - parent key not found
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-02291: integrity constraint (TPCH.LINEITEM_ORDER_FK) violated - parent key not found
    Vuser 1:Error in cursor 2:oratcl0.2 ORA-02291: integrity constraint (TPCH.LINEITEM_ORDER_FK) violated - parent key not found

    Here are the steps that I have followed:
    1. Create a tpch user on the database.
    2. Using HammerDB, create a schema with a 100 scale factor and 20 virtual users, using the tpch user and a tpchtab tablespace.
    3. Run a query only test on this newly created schema which works successfully.
    4. Try doing the export, before attempting a refresh, which seems to fail as above, although I am using the exact command line as shown in the document (except that it is on Linux, not Windows)
    5. Try a refresh test, during which I get the integrity constraint erros related to parent key not being found.

    Am I missing some steps in between? Is the expdp supposed to be used differently on Linux (all of my searches on Oracle documentation as well as generic google searches seemed to suggest it is very similar)?

    Your help is appreciated. I am very new to this and so it could certainly be some simple steps that I am missing.

    Thanks,
    Madhura

     
  • Steve Shaw
    Steve Shaw
    2013-11-11

    Madhura,

    One thing that is different that you are doing the step:

    1. Create a tpch user on the database.

    If you give HammerDB the system login details then it will create the user correctly for you and assign the correct permissions. It could be worth looking in the alert log to see if there is any permissions related issues being reported.

    You also report that the queries work - you should ensure that the setting to capture and report errors with the queries is enabled to ensure that this is the case. You can also investigate the schema with standard SQL commands to ensure that everything has been created and populated correctly. The export and import certainly should work if done as system and it does look as if the schema is created but empty.

    Cheers,

    Steve

     
  • Madhura
    Madhura
    2013-11-11

    I ended up creating the tpch user myself since HammerDB complained about the absence of the tpch user when I tried to build the schema. Any idea why HammerDB should complain instead of just creating the user?

    For the query settings, if you mean checking the exit on Oracle errors and if you mean logging the output, I am doing that. I am not sure if there are other places where I should look to see if the queries are indeed successful.

    I don't know what HammerDB creates in the schema so not sure what it is I need to check. Could you provide some things that I could look for within the schema to ensure it is indeed populated correctly? At this point, I am beginning to doubt if the schema build is happening as it is supposed to. So, maybe there is a problem even before I do the export/import

    Thanks for your patience with my questions.

    Madhura

     
  • Steve Shaw
    Steve Shaw
    2013-11-12

    Madhura,

    It definitely should not complain when creating the user, I'd recommend looking in the Oracle alert log to see why it failed and ensuring that it completes successfully. To check the schema creation look in user_tables selecting table_name and num_rows to start with and select count(*) from lineitem for example.

    Cheers,

    Steve