SQL Anywhere Mobile - statement cache

Author

statement cache

Carl Kayser

2005-11-16, 1:24 pm

There is a -ps <N> option for dbmlsrv9, which controls the maximum number of
ODBC prepared statements per connection kept in the prepared statement
cache. And one can configure the amount of memory (versus number of
statements) for the statement cache on, say, ASE or Oracle.

But, it seems to me, that the "-ps" setting on dbmlsrv9 cannot control
anything in the consolidated statement cache. Can anyone clarify this? Has
anyone had experience with (and guidelines for setting) this option?

Reg Domaratzki $iAnywhere Solutions$

2005-11-17, 1:24 pm

The -ps switch on the ML Server is caching the memory locations in the
MobiLink server where the input parameters and result sets generated from
MobiLink events are stored. It has absolutely nothing to do with the memory
that the consolidated database uses to cache table and/or index pages.

Note that I recommend using "-ps 0" on Oracle consolidated databases if you
plan on having multiple synchronizations active at once.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Carl Kayser" <kayser_c@bls.gov> wrote in message
news:437b722b@forums
-1-dub...
> There is a -ps <N> option for dbmlsrv9, which controls the maximum number
of
> ODBC prepared statements per connection kept in the prepared statement
> cache. And one can configure the amount of memory (versus number of
> statements) for the statement cache on, say, ASE or Oracle.
>
> But, it seems to me, that the "-ps" setting on dbmlsrv9 cannot control
> anything in the consolidated statement cache. Can anyone clarify this?
Has
> anyone had experience with (and guidelines for setting) this option?
>
>

Carl Kayser

2005-11-17, 1:24 pm

"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
wrote in message news:437cce00$1@foru
ms-1-dub...
> The -ps switch on the ML Server is caching the memory locations in the
> MobiLink server where the input parameters and result sets generated from
> MobiLink events are stored. It has absolutely nothing to do with the
> memory
> that the consolidated database uses to cache table and/or index pages.

OK. The 9.0.2 doc section on "-ps option" isn't very clear to me: "Some
consolidated database types have configurable limits on the number of
prepared statements, so this option may be set accordingly." How does one
determine "accordingly"?

>
> Note that I recommend using "-ps 0" on Oracle consolidated databases if
> you
> plan on having multiple synchronizations active at once.

You're suggesting that one specify a MobiLink argument (-ps) on an Oracle
consolidated database server?

>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
> http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set filter to "Display ALL platforms IN ALL MONTHS"
>
>
> "Carl Kayser" <kayser_c@bls.gov> wrote in message
> news:437b722b@forums
-1-dub...
> of
> Has
>
>

Reg Domaratzki $iAnywhere Solutions$

2005-11-17, 8:25 pm

Comments in-line.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Carl Kayser" <kayser_c@bls.gov> wrote in message
news:437cd327$1@foru
ms-1-dub...
>
> "Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
> wrote in message news:437cce00$1@foru
ms-1-dub...
from[color=darkred]
>
> OK. The 9.0.2 doc section on "-ps option" isn't very clear to me: "Some
> consolidated database types have configurable limits on the number of
> prepared statements, so this option may be set accordingly." How does one
> determine "accordingly"?

That will vary from RDBMS to RDBMS. I can answer this question for ASA.
There is a database option called MAX_STATEMENT_COUNT that determines that
maximum number of prepared statements per connection. The default value is
50. If you were to stay at the default level, I would suggest using "-ps
50" on the MobiLink command line.

>
> You're suggesting that one specify a MobiLink argument (-ps) on an Oracle
> consolidated database server?

Yes, and I'm suggesting you use a value of zero to prevent MobiLink from
caching any statements.

number[color=darkred
]
>
>

Carl Kayser

2005-11-21, 9:24 am

Comments in-line in two sections.

"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
wrote in message news:437cdb88$1@foru
ms-1-dub...
> Comments in-line.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
> http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set filter to "Display ALL platforms IN ALL MONTHS"
>
>
> "Carl Kayser" <kayser_c@bls.gov> wrote in message
> news:437cd327$1@foru
ms-1-dub...
> from
>
> That will vary from RDBMS to RDBMS. I can answer this question for ASA.
> There is a database option called MAX_STATEMENT_COUNT that determines that
> maximum number of prepared statements per connection. The default value
> is
> 50. If you were to stay at the default level, I would suggest using "-ps
> 50" on the MobiLink command line.

Agreed that that "-ps" "has absolutely nothing to do with the memory that
the consolidated database uses to cache table and/or index pages." I'm
interested in the consolidated memory that it uses for its' statement cache.

On my consolidated ASE server I see the following in the dedicated statement
cache:

UPDATE ml_subscription
SET progress = @0001, publication_name = @0002
WHERE user_id = @0003 AND subscription_id = @0004

UPDATE ml_subscription
SET last_upload_time = @0001
WHERE user_id = @0002
AND subscription_id = @0003

UPDATE ml_subscription
SET last_download_time = @0001,
publication_name = @0002
WHERE user_id = @0003 AND subscription_id = @0004

From your ASA example, it appears that the consolidated statement cache
drives the MobiLink "-ps" setting. Does your recommendation assume that the
consolidated server supports only synchronization? (We have many
non-synchronization databases and applications on the same ASE server where
synchronization also exists.) If "yes", then we would probably want the
consolidated statement cache to be larger in order to support the other
applications.

>
>
> Yes, and I'm suggesting you use a value of zero to prevent MobiLink from
> caching any statements.

You must be having a rough week, Reg. How do I specify "-ps NNNN" in
an Oracle pfile/spfile? Assuming that you mean "-ps 0" on the MobiLink
server, wouldn't this also be recommended for any other consolidated server
that has multiple synchronizations active at once? Hmmm, perhaps I should
ask for clarification of "multiple synchronizations active at once". We
mean one consolidated database server and one MobiLink server, but possibly
multiple concurrent connections with the remotes. Correct? If so, then
"-ps 0" seems to conflict with the previous ASA "-ps 50" recommendation.

The reason for preventing MobiLink from caching any statements (in certain
situations) is ...?

>
> number
>
>

Reg Domaratzki $iAnywhere Solutions$

2005-11-22, 11:24 am

I'm not sure I've explained myself properly here. The MobiLink "statement
cache" and the consolidated "statement cache" are two completely and
un-related animals. Neither "drives" the other, but the setting of one
might make for a good value to use on the other.

MobiLink uses ODBC to execute statements against the consolidated database.
MobiLink will prepare statements, bind variables to the statement and
allocate memory structures to store possible result sets. Assume a simple
SQL statement of "select table_name from SYS.SYSTABLE where table_id = ?".
An ODBC app that calls this statement will look similar to :

SQLAllocHandle(SQL_H
ANDLE_ENV, SQL_NULL_HANDLE, &env );
SQLAllocHandle(SQL_H
ANDLE_DBC, env, &dbc );
SQLConnect( dbc, dsn, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);
SQLAllocHandle(SQL_H
ANDLE_STMT, dbc, &stmt);
SQLPrepare( stmt, "select table_name from SYS.SYSTABLE where table_id = ?",
SQL_NTS );
SQLBindParameter( stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0,
&p1, 4, NULL );
SQLExecute( stmt );
SQLBindCol( stmt, 1, SQL_C_CHAR, r1, 128, &i1 );
SQLFetch( stmt );
SQLFreeHandle(SQL_HA
NDLE_STMT,stmt);
SQLDisconnect( dbc );
SQLFreeHandle(SQL_HA
NDLE_DBC,dbc);
SQLFreeHandle(SQL_HA
NDLE_ENV,env);

What the -ps switch on MobiLink does it that is does not do a SQLFreeHandle
on the stmt, but instead keeps it in memory. I believe we also keep the
memory locations "p1" and "r1" that store the memory locations for the input
parameters and the result set instead of freeing them once we're done. So,
we're caching information that is needed if we want to re-execute a SQL
statement through the open ODBC connection to the database again, which is
quite likely, since each synchronization for the same the script version
will tend to execute the same SQL.

This is completely separate from the "statement cache" that a RDBMS system
may have. It's also caching statements independently of MobiLink, and it's
caching statements from all types of connections, including ODBC, JDBC,
native connections and any other type of connection type it may support. My
suggestion to use "-ps 50" on the MobiLink command line when connected to an
ASA consolidated database with the default setting for MAX_STATEMENT_COUNT
was because if a connection to ASA prepares more than 50 statements without
freeing them (so in ODBC talk, I've done a SQLPrepare but no
SQLFreeHandle ), you will get an error. So, the connection that MobiLink
has to consolidated database cannot prepare more than 50 statements without
freeing them. If you had 200 synchronization scripts, but did not change
the MAX_STATEMENT_COUNT options in your ASA database, you would get errors.
You can either increase the MAX_STATEMENT_COUNT option in the ASA database
or increase the value specified on the -ps switch of the MobiLink start
line.

Your description of "concurrent synchronizations is correct". One RDMBS,
one ML Server, but multiple remote database connected to and synchronization
at the same time. The reason I suggest using "-ps 0" on the MOBILINK START
LINE when connection to an Oracle consolidated database ( I'm not suggesting
you disable Oracle's statement cache) is because we've seen issues where the
ODBC driver we ship will crash, but only when we've cached the statements.
The same code in the MobiLink server works perfectly against all the other
RDBMS that we support, but for some reason, there appears to be a
multi-threaded issue with the Oracle driver only when we re-use the same
ODBC statement handle. I am NOT suggesting that you use "-ps 0" when
connected to other RDBMS (such as ASA), which is why I suggested "-ps 50"
might be needed against ASA, but "-ps 0" is my suggestions against Oracle.

I hope this clears things up.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Carl Kayser" <kayser_c@bls.gov> wrote in message
news:4381dd0c$1@foru
ms-1-dub...
> Comments in-line in two sections.
>
> "Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
> wrote in message news:437cdb88$1@foru
ms-1-dub...
<FirstName.LastName@ianywhere.com>[color=darkred]
the[color=darkred]
pages.[color=darkred]
"Some[color=darkred]
that[color=darkred]
"-ps[color=darkred]
>
> Agreed that that "-ps" "has absolutely nothing to do with the memory that
> the consolidated database uses to cache table and/or index pages." I'm
> interested in the consolidated memory that it uses for its' statement
cache.
>
> On my consolidated ASE server I see the following in the dedicated
statement
> cache:
>
> UPDATE ml_subscription
> SET progress = @0001, publication_name = @0002
> WHERE user_id = @0003 AND subscription_id = @0004
>
> UPDATE ml_subscription
> SET last_upload_time = @0001
> WHERE user_id = @0002
> AND subscription_id = @0003
>
> UPDATE ml_subscription
> SET last_download_time = @0001,
> publication_name = @0002
> WHERE user_id = @0003 AND subscription_id = @0004
>
> From your ASA example, it appears that the consolidated statement cache
> drives the MobiLink "-ps" setting. Does your recommendation assume that
the
> consolidated server supports only synchronization? (We have many
> non-synchronization databases and applications on the same ASE server
where

> synchronization also exists.) If "yes", then we would probably want the
> consolidated statement cache to be larger in order to support the other
> applications.
>
if[color=darkred]
Oracle[color=darkred
]
>
> You must be having a rough week, Reg. How do I specify "-ps NNNN" in
> an Oracle pfile/spfile? Assuming that you mean "-ps 0" on the MobiLink
> server, wouldn't this also be recommended for any other consolidated
server
> that has multiple synchronizations active at once? Hmmm, perhaps I should
> ask for clarification of "multiple synchronizations active at once". We
> mean one consolidated database server and one MobiLink server, but
possibly
> multiple concurrent connections with the remotes. Correct? If so, then
> "-ps 0" seems to conflict with the previous ASA "-ps 50" recommendation.
>
> The reason for preventing MobiLink from caching any statements (in certain
> situations) is ...?
>
statement[color=dark
red]
control[color=darkre
d]
>
>
>
>
>
>
>

Sponsored Links