You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
My site is attempting to participate in the HowOften 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.
Note: I posted this on the OHDSI forums as well (https://forums.ohdsi.org/t/issues-running-howoften-study/20664)
My site is attempting to participate in the HowOften 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:
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
errorReportSql_1.txt
The text was updated successfully, but these errors were encountered: