SQL Anywhere Mobile - Re: Open_Cursor Problem in oracle Sync.

Author

Re: Open_Cursor Problem in oracle Sync.

Bob

2005-03-30, 9:43 am

HI,
This problem has nothing to do with UNIX or Windows 2003 ,
it occurs in all OS.

The issue is because the explicit cursors that are used in
our PL/SQL code or in our application(like recordset ,
command object etc.,) are not explicitly closed . for eg.,
In PL/SQL ,the CLOSE statement after the use of the cursor
i.e the OPEN ,FETCH and LOOP statements is mandatory.
Similarly a CLOSE statement for the recordset object and
connection object is necesary .

Oracle has an OPEN_CURSORS parameter that defines the
maximum open cursors allowed for a session . Default is 300
. The recommended value for this parameter is 0 to 10000 .
And we have set this value to 10,000 for our database.

But since all synchronisation actions happen through a
single DSN connection from the Mobilink server to Oracle,
there is only a single session created in oracle and the
open_cursors limit is exceeded in no time .

As we have no explicit cursors defined in our mobilink
scripts coded other than the simple DML statement(select
,insert , update)(implicit cursors) , the problem might me
in the way how the Mobilink server application is handling
the code internally to interact with oracle . Pls help us in
getting a fix at the earliest .

As this issue is raised by our Customer at site

Thanks a lot
Bob

> Bob wrote:
>
> I am not an Oracle guru by any stretch of the imagination,
> but I wonder if there is some setting in the Oracle
> server similar to ASA's MAX_CURSOR_COUNT which is set
> much lower on your AIX server than it is on your Win2003
> server.
>
> Do you know of any Oracle admin utility to extract these
> kinds of configuration values so you can compare the two
> servers' setup?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Greg Fenton

2005-03-30, 9:43 am

Bob wrote:
>
> But since all synchronisation actions happen through a
> single DSN connection from the Mobilink server to Oracle,

ML makes a number of *separate* database connections to the
consolidated. By default, it uses 5. Yes, they all use the same DSN to
connect, but they are separate connections.

Can you show use the command line you are using to start the ML server?

You can change the number of database connections using the "-cn"
argument to the dbmlsrv9 command. Though you might wish to read up on
the MobiLink Performance whitepaper to better understand how the current
default was determined:

http://www.ianywhere.com/whitepaper...nce
.html

> As we have no explicit cursors defined in our mobilink
> scripts coded other than the simple DML statement(select
> ,insert , update)(implicit cursors)

MobiLink uses the SELECT statements as cursors in the "*_cursor" events
(e.g. download_cursor and download_delete_curs
or). It also uses "batch"
or "wide" operations when doing INSERT/UPDATE/DELETE operations
(defaulting to 10 at a time).

You might also want to look at the "-ps" switch to dbmlsrv9 to change
the number of prepared statements that ML caches (though prepared
statements likely aren't affecting the number of open cursors at any one
time).

Barring a bug, I don't think that ML would be keeping any cursors open
after the particular event has passed. So after the download_cursor
event for a particular table has completed (and before the next
download_cursor has begun), I would expect that the cursor used for that
SELECT is closed.

> Pls help us in
> getting a fix at the earliest .
>
> As this issue is raised by our Customer at site

If this is truly urgent, then the best course of action is to open a
case with iAnywhere Technical Support:

http://www.ianywhere.com/support/co...ct_support.html

These newsgroups are run on a voluntary basis, so there is no guarantee
of a response or of getting a bug submitted to engineering by posting here.

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Bob

2005-03-30, 9:43 am

The command line we are using is :

dbmlsrv9 -c " dsn=EFBC;uid=%1;pwd=
%2" -ot EFBC.txt -v+ -zp
-zu+ -fr -x tcpip,

The cursor is not getting closed , all the SELECT , insert
and update queries remain opened as the OPEN_cursors . When
Multiple users sync like 10 to 12 , then it easily reaches
the limit.

expecting a solution at the earliest

Thanks
Bob

> Bob wrote:
> ,
>
> ML makes a number of *separate* database connections to
> the consolidated. By default, it uses 5. Yes, they all
> use the same DSN to connect, but they are separate
> connections.
>
> Can you show use the command line you are using to start
> the ML server?
>
> You can change the number of database connections using
> the "-cn" argument to the dbmlsrv9 command. Though you
> might wish to read up on the MobiLink Performance
> whitepaper to better understand how the current default
> was determined:
>
>
>
http://www.ianywhere.com/whitepaper...nce
.html
>
>
>
> MobiLink uses the SELECT statements as cursors in the
> "*_cursor" events (e.g. download_cursor and
> download_delete_curs
or). It also uses "batch" or "wide"
> operations when doing INSERT/UPDATE/DELETE operations
> (defaulting to 10 at a time).
>
> You might also want to look at the "-ps" switch to
> dbmlsrv9 to change the number of prepared statements that
> ML caches (though prepared statements likely aren't
> affecting the number of open cursors at any one time).
>
> Barring a bug, I don't think that ML would be keeping any
> cursors open after the particular event has passed. So
> after the download_cursor event for a particular table
> has completed (and before the next download_cursor has
> begun), I would expect that the cursor used for that
> SELECT is closed.
>
>
>
> If this is truly urgent, then the best course of action is
> to open a case with iAnywhere Technical Support:
>
>
>
http://www.ianywhere.com/support/co...ct_support.html
>
> These newsgroups are run on a voluntary basis, so there is
> no guarantee of a response or of getting a bug submitted
> to engineering by posting here.
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

BOB

2005-03-30, 9:43 am

Thanks for your reply ,

The command line we are using is :

dbmlsrv9 -c " dsn=EFBC;uid=%1;pwd=
%2" -ot EFBC.txt -v+ -zp
-zu+ -fr -x tcpip,

The cursor is not getting closed , all the SELECT , insert
and update queries remain opened as the OPEN_cursors . When
Multiple users sync like 10 to 12 , then it easily reaches
the limit.

expecting a solution at the earliest

Thanks
Bob

> Bob wrote:
> ,
>
> ML makes a number of *separate* database connections to
> the consolidated. By default, it uses 5. Yes, they all
> use the same DSN to connect, but they are separate
> connections.
>
> Can you show use the command line you are using to start
> the ML server?
>
> You can change the number of database connections using
> the "-cn" argument to the dbmlsrv9 command. Though you
> might wish to read up on the MobiLink Performance
> whitepaper to better understand how the current default
> was determined:
>
>
>
http://www.ianywhere.com/whitepaper...nce
.html
>
>
>
> MobiLink uses the SELECT statements as cursors in the
> "*_cursor" events (e.g. download_cursor and
> download_delete_curs
or). It also uses "batch" or "wide"
> operations when doing INSERT/UPDATE/DELETE operations
> (defaulting to 10 at a time).
>
> You might also want to look at the "-ps" switch to
> dbmlsrv9 to change the number of prepared statements that
> ML caches (though prepared statements likely aren't
> affecting the number of open cursors at any one time).
>
> Barring a bug, I don't think that ML would be keeping any
> cursors open after the particular event has passed. So
> after the download_cursor event for a particular table
> has completed (and before the next download_cursor has
> begun), I would expect that the cursor used for that
> SELECT is closed.
>
>
>
> If this is truly urgent, then the best course of action is
> to open a case with iAnywhere Technical Support:
>
>
>
http://www.ianywhere.com/support/co...ct_support.html
>
> These newsgroups are run on a voluntary basis, so there is
> no guarantee of a response or of getting a bug submitted
> to engineering by posting here.
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

David Fishburn

2005-03-30, 9:43 am

Bob wrote in news:4232ed8b.4ef0.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:

B> This problem has nothing to do with UNIX or Windows 2003 ,
B> it occurs in all OS.
B>
B> The issue is because the explicit cursors that are used in
B> our PL/SQL code or in our application(like recordset ,
B> command object etc.,) are not explicitly closed . for eg.,
B> In PL/SQL ,the CLOSE statement after the use of the cursor
B> i.e the OPEN ,FETCH and LOOP statements is mandatory.
B> Similarly a CLOSE statement for the recordset object and
B> connection object is necesary .

So is there something you can do in your application to close these
cursors that are being left open? It sounds like a problem with your
application, not the MobiLink server.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Bob

2005-03-30, 9:43 am

Greg had asked for the command line we are using and here
it is .

The command line we are using is :

dbmlsrv9 -c " dsn=EFBC;uid=%1;pwd=
%2" -ot EFBC.txt -v+ -zp
-zu+ -fr -x tcpip,

The cursor is not getting closed , all the SELECT , insert
and update queries remain opened as the OPEN_cursors . When
Multiple users sync like 10 to 12 , then it easily reaches
the limit.

Check if something could be done in the END synchronisation
event , as we are only using download_cursor and
download_delete_curs
or scripts , what else could be done
from our application to handle this situation.

expecting a solution at the earliest

Thanks
Bob

> Bob wrote in news:4232ed8b.4ef0.1681692777@sybase.com
> of sybase.public.sqlanywhere.mobilink:
>
> B> This problem has nothing to do with UNIX or Windows
> 2003 , B> it occurs in all OS.
> B>
> B> The issue is because the explicit cursors that are used
> in B> our PL/SQL code or in our application(like recordset
> , B> command object etc.,) are not explicitly closed .
> for eg., B> In PL/SQL ,the CLOSE statement after the use
> of the cursor B> i.e the OPEN ,FETCH and LOOP statements
> is mandatory. B> Similarly a CLOSE statement for the
> recordset object and B> connection object is necesary
> .
>
> So is there something you can do in your application to
> close these cursors that are being left open? It sounds
> like a problem with your application, not the MobiLink
> server.
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD
> number with EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
>
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>

David Fishburn

2005-03-30, 9:43 am

Bob wrote in news:4236f03c.1511.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:

B> The cursor is not getting closed , all the SELECT , insert
B> and update queries remain opened as the OPEN_cursors . When
B> Multiple users sync like 10 to 12 , then it easily reaches
B> the limit.

How are you determining this?
Are you running some command in SQLPlus?
Please be exact.

B> expecting a solution at the earliest

Then you should open a case with technical support. The newsgroups are
merely volunteer time. When you get into involved cases, we (the NG) do
not have the bandwidth to cover it.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Bob

2005-03-30, 9:43 am

Thanks a lot to all ,for the Support

The "ps" option seems to solve the issue . Now the
open_cursors does not grow , it is well within limit .

Regards
Bob

> Bob wrote in news:4236f03c.1511.1681692777@sybase.com
> of sybase.public.sqlanywhere.mobilink:
>
> B> The cursor is not getting closed , all the SELECT ,
> insert B> and update queries remain opened as the
> OPEN_cursors . When B> Multiple users sync like 10 to 12
> , then it easily reaches B> the limit.
>
> How are you determining this?
> Are you running some command in SQLPlus?
> Please be exact.
>
> B> expecting a solution at the earliest
>
> Then you should open a case with technical support. The
> newsgroups are merely volunteer time. When you get into
> involved cases, we (the NG) do not have the bandwidth to
> cover it.
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD
> number with EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
>
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>

Sponsored Links