Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0021805Openbravo ERPA. Platformpublic2012-10-01 10:572016-12-02 08:18
Reportermalsasua 
Assigned Tocaristu 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionno change required 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customerNo
Web browserGoogle Chrome
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0021805: In alert management, cursors are opened and never is closed [ORACLE]
DescriptionIn oracle database, when an alert is solved or created, cursor is opened and never is closed.

SQLs that the cursors never are closed:

UPDATE AD_ALERT SET STATUS = 'SOLVED', ...
INSERT INTO AD_ALERT (AD_ALERT_ID, AD_CLIENT_ID, AD_ORG_ID, ...


Steps To Reproduce. install module: org.openbravo.alerts.accounting
. delete accounting of a business partner category
. run SQL:
  SELECT SID, sql_text, COUNT(*)
  FROM v$open_cursor
  GROUP BY SID, sql_text
  ORDER BY COUNT(*) DESC

. run alert process immediatelly
. new alert is created
. run SQL:
  SELECT SID, sql_text, COUNT(*)
  FROM v$open_cursor
  GROUP BY SID, sql_text
  ORDER BY COUNT(*) DESC
. the cursors opened have increased
. add accounting to previous business partner category
. run SQL again:
  SELECT SID, sql_text, COUNT(*)
  FROM v$open_cursor
  GROUP BY SID, sql_text
  ORDER BY COUNT(*) DESC

. the cursors opened have increased
Proposed Solution
Additional Information
TagsPerformance
Relationships
depends on defect 00211253.0MP15 closed marvintm a cursor is opened and not closed when a tab is related to an auxiliar input 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2012-10-01 10:57malsasuaNew Issue
2012-10-01 10:57malsasuaAssigned To => AugustoMauch
2012-10-01 10:57malsasuaWeb browser => Google Chrome
2012-10-01 10:57malsasuaModules => Core
2012-10-01 10:57malsasuaOBNetwork customer => No
2012-10-01 10:58malsasuaRelationship addeddepends on 0021125
2012-10-18 10:45egoitzIssue Monitored: egoitz
2013-04-25 08:50alostaleTag Attached: Performance
2015-03-17 14:37alostaleAssigned ToAugustoMauch => platform
2015-11-11 11:31alostaleStatusnew => acknowledged
2016-06-23 10:11NaroaIriarteAssigned Toplatform => NaroaIriarte
2016-12-01 13:25alostalePriorityhigh => urgent
2016-12-01 14:12alostaleAssigned ToNaroaIriarte => caristu
2016-12-02 08:14caristuStatusacknowledged => scheduled
2016-12-02 08:18caristuReview Assigned To => caristu
2016-12-02 08:18caristuNote Added: 0091999
2016-12-02 08:18caristuStatusscheduled => closed
2016-12-02 08:18caristuResolutionopen => no change required
2016-12-02 08:19caristuNote Edited: 0091999bug_revision_view_page.php?bugnote_id=0091999#r13946
2016-12-02 08:21caristuNote Edited: 0091999bug_revision_view_page.php?bugnote_id=0091999#r13947

Notes
(0091999)
caristu   
2016-12-02 08:18   
(edited on: 2016-12-02 08:21)
Oracle's v$open_cursor shows cached cursors, not currently open cursors, by session. When looking for how many cursors a session has open, v$open_cursor does not provided such information. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.

To monitor open cursors, query v$sesstat where name='opened cursors current'. This will give the number of currently opened cursors, by session:

--total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';

By using this last query with the steps to reproduce, the number of records returned by the query is not being increased. Therefore after executing the alert process we can conclude that we are creating some cursors in cache but we are not leaving them open.

More info: http://www.orafaq.com/node/758 [^]