Sybase ASE 15 supports the use of many optimization techniques and tools. The use of these tools requires an expert level of understanding since improper application of query optimization tools can degrade performance for ASE as a whole. In this excerpt, the following optimization techniques are discussed from a standpoint of how to recognize problems resulting from the improper application of optimization strategies in ASE 15.

Optimization Goal Performance Analysis
Use optimization goals with caution. When optimization goals are altered by the database administrator, it may be necessary to periodically benchmark queries where optimization goals are employed. This benchmarking is necessary since the optimization goals instruct a query to perform optimization based upon known database usage patterns of OLTP, DSS, or mixed-use systems as an example. Over time, the usage balance of queries can change from one type to another, or the ratio of OLTP to DSS queries can change on a system. It is especially important to pay attention to the usage patterns on systems where optimization goals are set at a more broad level, such as at the server level.

To illustrate how optimization goals can affect query plans in an adverse manner, the following example shows the optimization goal of allrows_oltp employed on an OLTP system at the server level. As a first line of problem detection related to the incorrect employment of optimization goals, the set statistics io and set statistics time diagnostic commands are enabled.

select e.eventID, el.eventTypeCode, e.actualEndTime
from Event e,
EventList el
where e.eventID = el.eventID

and e.eventID = 4
and el.eventTypeCode = 1
and e.actualEndTime = (select min(e2.actualEndTime)
from Event e2
where e.eventID = e2.eventID)
go

Statistics I/O, time with optimization goal of allrows_oltp:

Table: EventList scan count 1, logical reads: (regular=6 apf=0 total=6), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0
Table: Event scan count 1, logical reads: (regular=95 apf=0 total=95), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0
Table: Event scan count 756, logical reads: (regular=24189 apf=0 Total=24189), physical reads: (regular=0 apf=0
total=0), apf IOs used=0
Total writes for this command: 0
Execution Time 10.
SQL Server cpu time: 1000 ms. SQL Server elapsed time: 993 ms.

Statistics I/O with no optimization goal set:

Table: EventList scan count 1, logical reads: (regular=6 apf=0 total=6), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0
Table: Event scan count 1, logical reads: (regular=95 apf=0 total=95), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0
Table: Event scan count 1, logical reads: (regular=31 apf=0 total=31), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0
Total writes for this command: 0
Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 53 ms.

In this example, the logical I/O required to satisfy the query is adversely impacted due to the use of a poorly selected optimization goal. In terms of query performance degradation, the logical I/Os performed on the second scan of the event table where a poorly set optimization goal is employed increased to 24,189 in comparison to the 31 I/Os performed on the event table where the optimization goal is selected by the optimizer.

The conclusion that can be drawn from this example is to use common sense when employing optimization goals! The query in this example is typical of a query performed on a DSS system. The optimization goal is set to allrows_oltp, an optimization strategy that is not appropriate for DSS queries.

This example illustrates a more important point: Restrict the use of optimization goals to the most granular level possible, especially on systems where mixed query types are standard. For the server as a whole, leave the optimization goals at the default ASE setting, unless a great majority of queries issued in ASE would benefit from non-default optimization goals.

Optimization Criteria Performance Analysis
ASE 15 provides database administrators with a mechanism to specify query optimization criteria, such as the ability to suggest join strategies for queries through abstract query plans. While database administrators and users may properly apply and understand the application of ASE optimization criteria, in some instances the application of optimization criteria may be incorrect. Additionally, the application of optimization criteria may be correct given the characteristics and volume of data within the database. This, however, can prove to be incorrect as data volume and characteristics change over time. To offer a strategy for the detection of misapplied query optimization criteria, an example is presented to demonstrate the detection of optimization criteria issues:

select e.eventID, el.eventTypeCode, e.actualEndTime
from Event e,
EventList el
where e.eventID = el.eventID

and e.eventID = 4
and el.eventTypeCode = 1
and e.actualEndTime = (select min(e2.actualEndTime)
from Event e2
where e.eventID = e2.eventID)
go