cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic SQL for repserver. Is is stable? cache_size settings?

sladebe
Active Participant
0 Kudos

A few questions about "Dynamic SQL" (which is where you "prepare" an SQL statement once, then submit only the literals for each execution. See "Dynamic SQL for Enhanced Replication Server Performance" https://help.sap.com/viewer/62f3e3a6d49342cf9b7d72ee726a48b0/15.7.1.306/en-US/0049e00ebd1d1014aa9d81... )

Given that most a single delete/update operation on the primary db (er, active) can translate to thousands of per row commands at the DSI connection, this seems like it could be really helpful in those situations.

Has anyone one experienced any stability problems using this? Any other gotchas?

I notice the at repdefs have Dynamic SQL enabled by default, but then there's also the option of enabling "dynamic_sql" at the server or DSI connection level, but Dynamic SQL is off by default at a server and connection level.

I'm guessing that having "Dynamic SQL" on at the repdef level means it's only on if it's also turned on at the server or connection level (the doc's don't have info on this) Is that a good guess?

Any ideas about setting dynamic_sql_cache_size? How do you know if you've set it too big? (Ie., what do you look for in ASE?)

Also, it seems like setting dynamic_sql_cache_management to MRU would be better than the default of "fixed" (fixed="specifies that once the dynamic_sql_cache_size is reached, allocation for new dynamic SQL statements stops")

Thanks in advance
Ben

Accepted Solutions (0)

Answers (1)

Answers (1)

sladebe
Active Participant
0 Kudos

Thanks for the answer.

FYI, I have an environment that's in progress migrating from ASE 15.5 to 16. We have a few primary databases for database replication definition/subscriptions where the databases replicate a lot of large varbinary fields. We recently noticed that the large varbinary values were getting included in the "where" clauses of the replicated transactions. Ie., the repserver (15.7) wasn't automatically figuring out the proper primary key for the table in the absence of a table specific repdef.

It turns out you need an ASE 16 primary db for the repserver to automatically figure out primary keys for tables in a db repdef. And to make matters worse, on the replicate server, the statement cache auto literal param feature wasn't parameterizing the varbinary literals causing huge SQL statements to get cached (including huge varbinary literals in the where clauses)

We also had problems where a regular DSI subscription replicate connection, running a simple update statement against a table with a 2 field clustered index, occasionally decided not to use the index for the update (this is for a large table). These table scan updates essentially caused replication to hang (every 2-3 weeks). When we turned off the statement cache, the problem stopped. So statement caches are off for now.

So I'm going to wait until we're on ASE 16 for the primary databases, then I'll retry statement caching on the subscription replicates. If we still have problems with statement caching, maybe we'll try the repserver dynamic sql option.