Business Transactions Essay, Research Paper

Business Transactions

In many business environments there are a subset of transaction that are time

critical. The amount of time that it takes to perform the transaction

radically impacts other business processes downstream. An example of this may

be a marketing report that allows the marketing division of an organization to

judge the effectiveness of an advertising campaign in various cities across

the country. The ability to judge the effectiveness of the campaign in a

timely manner can be critical in making decisions concerning the purchase of

ad space in newspapers or magazines. The inability to make that decision

because a report is performing poorly can potentially lead to inaccurate and

costly decisions.

If the DBA is concerned about making certain that business critical queries

are insulated from factors that may change their performance characteristics

then stored outlines can provide some measure of performance stability.

Query Processing

Each query that is delivered to the Oracle server goes through a number of

processes before it is executed. Some of these basic steps are:

Parse – this step does an analysis of the query to determine a number of

basic attributes associated with the query such as if the table the query is

referencing exists in the database. Another function of this step is to hash

the query. The result of the hash is a value that can be used to search the

library buffer cache to determine if the query has been executed before. If

the query has been executed before then an execution plan has already been

created for the query. If the query has never been executed since the instance

has started then an execution plan needs to be created for the query.

Execute – this step actually executes the query once it has been parsed.

Fetch – this step returns rows to the process executing the query assuming that

the command was a select.

Execution Plan

During the parse phase of query processing an execution plan is created for a

query. The execution plan defines how the data is to be accessed from the

database. For instance it defines if a table will be accessed with a full

table scan or an index retrieval, it will also define how an index will be

accessed assuming an index is available for data retrieval.

Design Objective

The design objective for stored outlines was to provide a mechanism that would

allow a business critical transaction to remain stable regardless of some types

of database changes. As an example if the statistics used by the cost based

optimizer are accidentally deleted then the cost based optimizer may create a

different execution plan for a query. This could radically alter the

performance characteristics of the query.

Functionality

The implementation of stored outlines relies on the ability to provide hints

to the cost based optimizer. Consequently, the scope of stored outlines is

limited by the capabilities of hints to alter the execution plan of a query.

Data Structures

The following section will define the views representing the data structures

used to implement stored outlines.

DBA_OUTLINES

Bibliography

oracles