Hi All,
My site is attempting to participate in the HowOften (https://github.com/ohdsi-studies/HowOften/tree/master) study. We have ~1.4 million patients in our OMOP database and are using an Oracle (v19.21.0) backend. When I attempt to run the first portion of the study in StrategusCodeToRun.R (# Step 1 : Execute Azza Analysis), it runs for ~11 hours (after the package installation phase) and eventually fails with the following error: java.sql.SQLException: ORA-12801: error signaled in parallel query server P007; ORA-01555: snapshot too old: rollback segment number 12 with name “_SYSSMU12_1155298549$” too small. I’ve also attached the errorReportSql_1.txt that is generated.
From here, we thought that maybe our database server needs more resources (CPU cores – 8, Memory 64GB; Oracle SGA – 32GB, PGA – 20GB; UNDO tablespace size – max 200GB), so we randomly sampled 5% of our patients and created a reduced OMOP CDM. When pointing to this reduced database of ~70,000 patients, step 1 of StrategusCodeToRun.R (Execute Azza Analysis) runs for ~7 hours before failing with the following error: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression (see attached errorReportSql_2.txt). It appears the sql query is not valid for Oracle sql. From this test we have two immediate questions:
- Has anyone successfully run HowOften with an OMOP CDM instance that uses an Oracle backend? - It seems that perhaps the automatically generated sql query may not be compatible with Oracle.
- Is there something we should be doing to optimize our OMOP CDM database? - Indexing perhaps?
At the OHDSI Symposium earlier this year it was mentioned that step-1 should only take a few of hours to run (depending on several factors). With that in mind, our instance seems to be severely under-performing as compared to the rest of the community.
We welcome any advice or shared experiences that could help us resolve these issues.
errorReportSql_2.txt (553 Bytes)
errorReportSql_1.txt (37.9 KB)
Best,
Justin