GNM_RCS_System_Performance_TR

.

• 1. .Introduction

     1. Purpose

This document describes how system performance is monitored in both the development process and the production running of RCS.

• 1. Scope

This document describes some of the good practice principals followed in development, along with methods for identifying poor performance and how they can be addressed.

Separate (high-level) Functional Specifications and Technical Specifications provide more detail on the business functions supported by RCS.

• 1. .Performance considerations in development

     1. Oracle Forms

The Oracle Forms are designed to maximise the speed they open, query data, and speed of navigation.

• 1. Jar files

Jar files are used to make the loading of images for on-screen buttons faster, and to ensure they can be stored locally on the client machine after the first time they are loaded over the network.

• 1. Base table queries

Base table blocks have extensively tuned queries, often with hints and default where clauses defined differently an runtime depending on the user’s access level.

• 1. POST-QUERY trigger

POST-QUERY triggers are occasionally used to exclude data from a block’s query results by raising form_trigger_failure – if the equivalent cannot be done within the block query just as efficiently.

• 1. Other queries

As a general rule, all other queries are stored on the database in procedures and functions that can be called from the Oracle Forms. This provides better performance as well as encouraging better reuse of code.

• 1. Testing

During development and testing all new software is tested against production volumes of data to help identify potential performance issues prior to it being rolled out to the production system.

• 1. Oracle Reports

     1. Data model

The data model in Oracle Reports is defined to maximise overall query performance. Often queries may be joined into a single query, rather than linked, thereby improving the time it takes to execute the report.

• 1. Lexical bind variables

Data model queries often have lexical bind variables that evaluate to different where clause predicates depending on the values in the Report parameters and also depending on the access level of the user.

• 1. Other queries

As a general rule, all other queries are stored on the database in procedures and functions that can be called from the Oracke Reports. This provides better performance as well as encouraging better reuse of code.

• 1. Testing

During development and testing all new software is tested against production volumes of data to help identify potential performance issues prior to it being rolled out to the production system.

• 1. Database design

The database is designed for optimal performance in many ways. The following are a few examples:

• • Uniqueness is always indexed;

  • All foreign keys are indexed to improve queries and reduce locking overheads;

  • Function based indexes are used to manage queues in many areas of the system, or to target specific rows of data in other queries;

  • Lookup data may be denormalised to improve queries on data held in foreign key tables;

  • Sequences generate new IDs and have been embedded in the PLSQL since Oracle 11;

  • Synonyms are used sparingly with sessions being altered to behave as RCS;

  • Views are used to gains access to complex data;

  • Some smaller tables are cached;

  • Materialised views have largely been removed from the system due to the overhead of the redo logs being generated that then need to be applied to the standby database; and

  • Statistics are gathered regularly so that the optimiser has the best information on which to base its decisions.

• 1. Database software

     1. Queries

        1. Execution plans

The execution plan of a query shows the order in which Oracle accesses the tables, the indexes it uses on those tables, and the way those tables/indexes are joined to each other (merges and nested loops etc.), and presents the overall and step costs of the execution plan.

Explain Plan is used to check the execution plan of all significant queries, and they are then modified as required to ensure the best performance. Hints are very often added to the queries to instruct the optimiser to make particular decisions.

There are so many things that need to consider when tuning a query that it would be difficult to document them all here. However some of the most obvious performance issues to consider are:

• • table access order;

  • type of joins;

  • index types and suitability;

  • inline queries;

  • sub queries;

  • outer joins;

  • with clauses;

  • hierarchical queries;

  • inline functions;

  • set operators UNION, UNION ALL, INTERSECT, and MINUS;

  • unique / distinct queries;

  • aggregate and summary functions; and

  • order by clauses.

• 1. Cursor definitions

Cursors are defined with parameters to maximise re-usability. This avoids the use of variables bound at runtime which causes the database to consider each query as being separate and requiring caching.

• 1. Object style packages

Queries and stored in packages that are associated with tables, by naming convention, and so it is easy to ensure queries against each table are coded in one place. This significantly increases the chance of cursors being reused in development avoiding duplication.

• 1. Dynamic SQL

Dynamic SQL is used on rare occasions to improve performance.

• 1. Views

Views are defined to ensure query re-use, especially in areas of great complexity. Some views are bound to packaged functions who return the value that drives the query.

• 1. Triggers

There are many triggers in RCS and their performance is critical to system performance:

• • If the trigger code only applies to a restricted set of columns the triggers are defined as such;

  • WHEN clauses are used wherever possible to limit the need to execute the trigger body; and

  • Low cost processing paths are evaluated before higher cost processing paths to reduce the overall cost of processing.

• 1. Packages

All the object-style packages (associated with tables) have common procedures and functions that are always provided. This enables to developer to rely on a proven and well performing set of code rather than starting afresh.

Packages that support large batch processes are kept to a reasonable size to avoid the cost of loading code into database memory, whilst also reducing the impact of what else gets removed from memory to make space.

• 1. Result cache

Many functions in the system are now designed to result cache, provided they have a limited number of input parameter values. This reduces the need to query static data from the database tables themselves, or the need to cache results for each session using deterministic functions, or explicit caching using package state data.

• 1. Bulk collect & Forall

Bulk collect and Forall techniques are used to speed up bulk processing of large volumes of data.

Oracle 11g will actually do a lot of this in the background if cursor for loops are used but explicitly using this technique remains better and this was already implemented under earlier Oracle versions.

• 1. Loops

Where loops are nested consideration is given to choose the optimal structure to ensure processing is faster. Loops may often be combined (and cursors combined in the case of for loops) to improve the performance of the software.

• 1. If then else constructs

Least cost eventualities are evaluated before higher cost eventualities to provide faster overall processing times.

Case statements are used in place of if then else statements mainly for clarity, but they may also be embedded in queries where if then else statements cannot.

• 1. Recursion

Recursion is used in a few situations to reduce complexity of code and improve performance.

• 1. Data types and declarations

PLS_INTEGER is used to improve mathematical processing.

Constants are declared instead of variables as they are marginally lower cost.

• 1. Locking

Locking issues can cause huge preformnace issues as one process may have to wait for another to release a lock before it can continue. Or a process may timeout waiting for a lock to be released, and effectively crash meaning it must be restarted. Abnd finally deadly embrace sceanrios may cause two processes to prevent each other from continuing as they wait for each others locks to be released.

The main considerations during develpment are:

• • Locks are always taken out as late as possible and released as soon as possible;

  • Processes lock as few rows as possible to minimise their wider impact; and

  • Data locks are taken in a consistent order, generally alphabetic order, numeric order or date order, to try to avoid conflicts.

• 1. .Identifying poor performance in production

As the data volumes grow system performance inevitably suffers. This may be simply a result of the extra data to be processed, or because the database query optimiser decides to execute a query in a new way based upon new statistics gathered on the tables and indexes.

• 1. User reported issues

Users may report poor performance in the screen or report they are using, but every effort is made to find the problems before they become too bad for users to report. User reported performance issues are a treated like any other and resolved as quickly as possible.

• 1. Batch processes reporting performance statistics

Some of the batch processes have been designed in so that they can report timings of each processing step to a support email address.

This performance reporting can be turned on and off in a system admin screen called rcs_cpar_010_pc.fmb accessed from this menu option:

Housekeeping → System Configuration → Performance monitoring parameters

• 1. Workload Repository report

On an ad hoc basis Workload Repository Reports are requested from the DBAs. This tends to be after every few months, or after a significant software rollout. A window of between 2 and 5 days is looked at and the report lists the most resource intensive and time consuming PL/SQL processes and SQL queries during that period. These lists are regularly assessed and compared to previous reports to identify trends.

The queries listed are examined to see if more optimal execution plans can be identified: hints may be added, or indexes maybe added or changed, and very occasionally significant redesign may be undertaken to avert a growing problem.

The aim is to keep ahead of significant performance problems, tackling issues as soon as they appear.

• 1. DBMS_PROFILER

Batch processes that are known to be either slow or very resource intensive are evaluated using DBMS_PROFILER. This profiler report identifies exactly which steps of the process are performing badly. These steps are then targeted.

If appropriate the software may also be reviewed to see if a more performant structure could be used, or to see if high cost processes could avoided more often.

End of Document