13
• User-defined caches and cache partitioning, ASE allows you to partition the default data cache by creating user-
defined caches and/or partitioning, using sp_cacheconfig or sp_configure "local cache partition number". This is
very useful in an SMP environment where contention can be experienced by multiple processes trying to write to
default data cache at the same time (see Performance and Tuning guide, Chapter 10: Memory Use and Performance).
• sp_configure "max network packet size", the maximum packet size can be decided, when a lot of data is send the
network overhead can be reduced by setting a larger size.
• sp_configure "number of worker processes", this will allow certain queries to be processes more optimally.
• sp_configure "max online engines", this will allow the workload of the server to be spread over more engines. The
max number of engines should not exceed the number of CPU’s available on the machine.
• sp_configure “tcp no delay”, set to enable will make sure the packets are send immediately.
• sp_configure "statement cache size", size_of_cache
The statement cache allows Adaptive Server to store the text of ad hoc SQL statements. Adaptive Server
compares a newly received ad hoc SQL statement to cached SQL statements and, if a match is found, uses the
plan cached from the initial execution. In this way, Adaptive Server does not have to recompile SQL statements for
which it already has a plan.
The statement cache is a server-wide resource, which allocates and consumes memory from the procedure cache
memory pool. Set the size of the statement cache dynamically using the statement cache size configuration parameter.
The size_of_cache is the size, in 2K pages:
For example, to set your statement cache to 5000 2K pages, enter:
sp_configure "statement cache size", 5000
• sp_configure “optimization timeout limit”, this will specify the amount of time Adaptive Server can spend
optimizing a query as a percentage of the total time spent processing the query.
• The convenient way of matching query demands with the best optimization techniques is optimization goals. It
ensures optimal use of the optimizer's time and resources. The query optimizer allows you to configure two types
of optimization goals, which you can specify at three tiers: server level, session level, and query level.
Set the optimization goal at the desired level. The server-level optimization goal is overridden at the session level,
which is overridden at the query level.
These optimization goals allow you to choose an optimization strategy that best fits your query environment:
allrows_mix – the default goal, and the most useful goal in a mixed-query environment. It balances the needs of
OLTP and DSS query environments.
allrows_dss – the most useful goal for operational DSS queries of medium to high complexity. At the server level,
use sp_configure. For example:
sp_configure "optimization goal", 0, "allrows_mix"
At the session level, use set plan optgoal. For example:
set plan optgoal allrows_dss
At the query level, use the select or other DML command. For example:
select * from A order by A.a plan
"(use optgoal allrows_dss)"
4 MIGRATING THE DATABASE ARCHITECTURE
Migration of Oracle database to Adaptive Server Enterprise can be divided into three steps:
1. Create the Sybase ASE server and database schema. This include all the database objects, the users, roles and security:
• create, configure and determine devices for the Sybase ASE server and database
• create the database, set the database options, create the server logins and set the database ownership
• Create the database schema from the Oracle database and transform into Sybase ASE database schema, run
the Sybase ASE schema