CRJAZ1413E Database connection is null errors
We are running RTC 2.0.0.2 (I20091211-0451) on RedHat Enterprise 5.x and using an Oracle DB.
In recent months we occasionally see many, e.g., dozens, of the following errors in the jazz.log around 9-10 pm. I've talked with our Oracle DBA's a few times about this.
2010-11-04 18:50:10,423 ERROR repository.service.internal.rdb.JDBCConne ctionPool - CRJAZ1413E Database connection is null. Check database status and availability.
When this happened on 11/4, it was a couple hours earlier in the evening, I talked with our DBA's again and got the following response.
Thanks for any help you can provide!
Carl McMullen
----
Carl,
I pulled an AWR report for jazzprd from 6-8 last night and saw a lot of time spent on SQL parsing. One common culprit of this can be the use of literals instead of bind variables in frequently occuring SQL statements. Taking a look at the 7103 cached SQL statements in the db right now, I found 2 sets of statements that would benefit from the use of bind variables:
This statement was present in the SQL cache 3706 times with different literal values wherever you see '#' or @
WITH CTX AS (SELECT CONTEXT_UUID AS CTX_UUID FROM REPOSITORY_CONTEXT_MEMBERS WHERE MEMBER_UUID IN('#', '#')
UNION SELECT '#' FROM DUAL ) SELECT T1.ITEM_ID FROM (SELECT * FROM BUILD_BUILD_RESULT R1 WHERE R1.CONTEXT_ID IN(SELECT CTX_UUID FROM CTX)) T1 WHERE ((T@.BUILD_DEFINITION_ITEM_ID = '#') AND (T1.PERSONAL_BUILD = @) AND (T1.BUILD_STATE = '#')) ORDER BY T1.BUILD_START_TIME DESC
This statement was present in the SQL cache 136 times with different literal values wherever you see '#' or @
SELECT T1.ITEM_ID FROM BUILD_BUILD_RESULT T1 WHERE ((T1.BUILD_DEFINITION_ITEM_ID = '#') AND (T1.PERSONAL_BUILD = @) AND (T1.BUILD_STATE = '#')) ORDER BY T1.BUILD_START_TIME DESC
&nb sp;
If the literal values were replaced with bind variables in these 2 statements, the db would only need 2 parses instead of the 3842 parses they currently caused.
Is this custom SQL that you could modify?
If you can't modify it, there is a db parameter called CURSOR_SHARING that will allow the db to use the same execution plan for SQL statements that are the same except for the literal values they use. Sometimes this helps and sometimes it makes things worse, so we'd need to test the effect. It would be a good idea to reach out to the app vendor to see what their response to this issue has been and whether they have a recommendation on the use of the CURSOR_SHARING db parameter with RTC Jazz.
Thanks,
Al
In recent months we occasionally see many, e.g., dozens, of the following errors in the jazz.log around 9-10 pm. I've talked with our Oracle DBA's a few times about this.
2010-11-04 18:50:10,423 ERROR repository.service.internal.rdb.JDBCConne ctionPool - CRJAZ1413E Database connection is null. Check database status and availability.
When this happened on 11/4, it was a couple hours earlier in the evening, I talked with our DBA's again and got the following response.
Thanks for any help you can provide!
Carl McMullen
----
Carl,
I pulled an AWR report for jazzprd from 6-8 last night and saw a lot of time spent on SQL parsing. One common culprit of this can be the use of literals instead of bind variables in frequently occuring SQL statements. Taking a look at the 7103 cached SQL statements in the db right now, I found 2 sets of statements that would benefit from the use of bind variables:
This statement was present in the SQL cache 3706 times with different literal values wherever you see '#' or @
WITH CTX AS (SELECT CONTEXT_UUID AS CTX_UUID FROM REPOSITORY_CONTEXT_MEMBERS WHERE MEMBER_UUID IN('#', '#')
UNION SELECT '#' FROM DUAL ) SELECT T1.ITEM_ID FROM (SELECT * FROM BUILD_BUILD_RESULT R1 WHERE R1.CONTEXT_ID IN(SELECT CTX_UUID FROM CTX)) T1 WHERE ((T@.BUILD_DEFINITION_ITEM_ID = '#') AND (T1.PERSONAL_BUILD = @) AND (T1.BUILD_STATE = '#')) ORDER BY T1.BUILD_START_TIME DESC
This statement was present in the SQL cache 136 times with different literal values wherever you see '#' or @
SELECT T1.ITEM_ID FROM BUILD_BUILD_RESULT T1 WHERE ((T1.BUILD_DEFINITION_ITEM_ID = '#') AND (T1.PERSONAL_BUILD = @) AND (T1.BUILD_STATE = '#')) ORDER BY T1.BUILD_START_TIME DESC
&nb sp;
If the literal values were replaced with bind variables in these 2 statements, the db would only need 2 parses instead of the 3842 parses they currently caused.
Is this custom SQL that you could modify?
If you can't modify it, there is a db parameter called CURSOR_SHARING that will allow the db to use the same execution plan for SQL statements that are the same except for the literal values they use. Sometimes this helps and sometimes it makes things worse, so we'd need to test the effect. It would be a good idea to reach out to the app vendor to see what their response to this issue has been and whether they have a recommendation on the use of the CURSOR_SHARING db parameter with RTC Jazz.
Thanks,
Al
One answer
Hi Carl,
Just curious to know if you got rid of the problem. We have been facing this issue all of a sudden for the past couple of weeks everyday and have found no solution yet.
Many Thanks
Regards,
Sunita
Just curious to know if you got rid of the problem. We have been facing this issue all of a sudden for the past couple of weeks everyday and have found no solution yet.
Many Thanks
Regards,
Sunita
We are running RTC 2.0.0.2 (I20091211-0451) on RedHat Enterprise 5.x and using an Oracle DB.
In recent months we occasionally see many, e.g., dozens, of the following errors in the jazz.log around 9-10 pm. I've talked with our Oracle DBA's a few times about this.
2010-11-04 18:50:10,423 ERROR repository.service.internal.rdb.JDBCConne ctionPool - CRJAZ1413E Database connection is null. Check database status and availability.
When this happened on 11/4, it was a couple hours earlier in the evening, I talked with our DBA's again and got the following response.
Thanks for any help you can provide!
Carl McMullen
----
Carl,
I pulled an AWR report for jazzprd from 6-8 last night and saw a lot of time spent on SQL parsing. One common culprit of this can be the use of literals instead of bind variables in frequently occuring SQL statements. Taking a look at the 7103 cached SQL statements in the db right now, I found 2 sets of statements that would benefit from the use of bind variables:
This statement was present in the SQL cache 3706 times with different literal values wherever you see '#' or @
WITH CTX AS (SELECT CONTEXT_UUID AS CTX_UUID FROM REPOSITORY_CONTEXT_MEMBERS WHERE MEMBER_UUID IN('#', '#')
UNION SELECT '#' FROM DUAL ) SELECT T1.ITEM_ID FROM (SELECT * FROM BUILD_BUILD_RESULT R1 WHERE R1.CONTEXT_ID IN(SELECT CTX_UUID FROM CTX)) T1 WHERE ((T@.BUILD_DEFINITION_ITEM_ID = '#') AND (T1.PERSONAL_BUILD = @) AND (T1.BUILD_STATE = '#')) ORDER BY T1.BUILD_START_TIME DESC
This statement was present in the SQL cache 136 times with different literal values wherever you see '#' or @
SELECT T1.ITEM_ID FROM BUILD_BUILD_RESULT T1 WHERE ((T1.BUILD_DEFINITION_ITEM_ID = '#') AND (T1.PERSONAL_BUILD = @) AND (T1.BUILD_STATE = '#')) ORDER BY T1.BUILD_START_TIME DESC
&nb sp;
If the literal values were replaced with bind variables in these 2 statements, the db would only need 2 parses instead of the 3842 parses they currently caused.
Is this custom SQL that you could modify?
If you can't modify it, there is a db parameter called CURSOR_SHARING that will allow the db to use the same execution plan for SQL statements that are the same except for the literal values they use. Sometimes this helps and sometimes it makes things worse, so we'd need to test the effect. It would be a good idea to reach out to the app vendor to see what their response to this issue has been and whether they have a recommendation on the use of the CURSOR_SHARING db parameter with RTC Jazz.
Thanks,
Al