Ideally, this type of high-level code review scan of our custom code could occur Quarterly and only look back at code created within the last Quarter for our Development environment. This gives a good pulse of how we are applying our best practices as well as identifying early on where we need to apply some reinforcement of same.

An additional benefit is visualization of new learning opportunities that can be shared.

My vision, is that this high-level code review could and should be performed by Application Architects. As it is our responsibility to guide development and solutions. It also serves as a good excercise for maintaining our awareness as architects.

Overtime, we would expect to see a shift and many items below begin to decrease in occurrence. We may see new things crop up, and reoccurrence of old issues. By taking the pulse periodically, we maintain a healthy development and production environment which is sustainable long term.

Our initial scan may go back to the last two years of custom code created. Just to get things started.

Here is a sample description of what your first scan might turn up. Work with your Architects and Team Leads to develop a strategy to address these items. Create a backlog of items and prioritize accordingly. Don’t expect to solve it overnight. It will take time, but it will time well spent.

Examples of performance impact discoveries

- select count(*) when true total count is not needed

- Need to know if it exists, fetch and exit when cnt is 1
- Need to know if it exists and need to know the count
but anything beyond count of 11 gets handled in some way
then fetch and exit when cnt = 11.

* This keeps us from thrashing a million rows of data unnecessarily
when we already reached our theshhold at the beginning of the result
set 9 times out 10.

- “WHERE rownum < 2″ is not a best practice and should be avoided

- We have to be very cautious how this is used as it only works correctly
under certain key situations. For this very reason, it is safer to avoid
this practice and instead use a FETCH cursor process to populate a variable
and then exist when that variable is populated and close the cursor

- Use PLS_INTEGER or SIMPLE_INTEGER (11g)

- Both behave differently than INTEGER
- INTEGER is nothing more than an alternate name for the
core numeric types of NUMBER
- PLS_INTEGER value range is (+- 2,147,483,648) and
supports NULL values and checks for overflow conditions.
Arithmetic operations are performed using the hardware’s
native machine instructions. Very fast.
- SIMPLE_INTEGER is the same as PLS_INTEGER but
does not support NULL values and does not check for
overflow conditions. So if you know that it will never be
NULL and will never overflow, then use SIMPLE_INTEGER.

- Native SIMPLE_INTEGER is over 300% faster than native
PLS_INTEGER
- Native SIMPLE_INTEGER is over 1000% faster than NUMBER

- If you declaring a variable to hold the count of something where precision is
not required, use PLS_INTEGER or SIMPLE_INTEGER (11g). Note: The latter req
that you have 11i installed on your development workstation. Understand the
differences between the two. The value they serve is that they require less
overhead in the system thus improved performance of code and improved system
performance.

- Bulk processing is not being leveraged

- This can definitely cause performance issues with processes
as well as the system overall.

Whenever possible, avoid DDL commands in a loop by the ones
Instead perform bulk operations

* Sometimes you may need to load up into a collection and then
apply that collection using bulk update methods

* Use BULK EXCEPTION logic

* Write exceptions out at the end instead of within the loop
itself. This allows the benefit of bulk processing still.

* Sometimes, it may be necessary to consider some threading method to
provide parallel execution of processing. Examples are available for
PLSQL driven, Unix Shell Script driven, as well as Concurrent Manager
driven.

If a multi-threaded solution is approached it generally benefits
from as few as 5 parallel threads where it each thread is handling the
same relative number of transactions to process. It is not recommended to
exceed 10 parallel concurrent threads however. Too many threads can cause
performance issues as well, so appropriate balance must be identified and
maintained. That said, multi-threading a long running process can provide
dramatic improvement. Experiences are very process dependent thus results
will of course vary.

- Use of COMMIT
- Consider using SAVEPOINTs where applicable

Examples of readability and maintainability discoveries

- Exception Handling varies across the board, no consistency between systems. This
can make it difficult to augumented resources to adapt quickly and easily

- Use of SQLCODE and SQLERRM should be discouraged unless augmented by DBMS_UTILITY
error handling routines.
- In fact, we should encourage use of DBMS_UTILITY over use of SQLCODE and SQLERRM all
together
- Use of l_step and l_stage debug entries is not the same as using the DBMS_UTILITY
routines. Debug entries only declare where I was before the error occurred. Whereas
the DBMS_UTILITY error handling routines have the ability to specifically identify
the actual line # of where the error occurred within a given package. This can
greatly reduce the resolution turn-around time.

- Use of RAISE_APPLICATION_ERROR

- Some best practice items worth mentioning
i.e. RAISE_APPLICATION_ERROR(-20011, ‘Message’)
RAISE_APPLICATION_ERROR(-20011, ‘Different Message’)

Ideally, define the errors up front with distinct
numbers assigned. Be consistent within the current
program/pkg.

- Either place defined errors in separate package header for the project as a common
error pkg for that specific process. Or in the package body of the same package.

This way all the known errors or easy to see as well make sure that we don’t reuse
numbers assigned.

- Error string should be short and to the point.
e.g. 100 characters or less

- multiple return statements in functions

- Functions should never have an OUT parameter. Functions should only return
one value. If OUT parameters are needed, then this needs to be a Procedure
instead.

- Need to be cautious of include error handling in functions. Instead consider
exception handling to be performed outside of the function from the calling
procedure. Use of DBMS_UTILITY error handling routines would identify that our
error occurred in the FUNCTION for x reason. Depending on our needs, we would
then either log our error and proceed, override with a default value and proceed,
or abort processing.
- In the event the function is to be used in external SQL say in a reporting view
then it makes sense to provide some error handling in the function to ensure it
does not cause the SQL reporting to fail but instead replaces with some value
in the event of error or NULL value depending upon requirements.

- (SYSDATE between x.startdate and x.enddate) is not the same as
(TRUNC(SYSDATE) between x.startdate and x.enddate)

- We need to be aware of when we use SYSDATE on conditions when the values
in those fields are truncate dates i.e. 01-OCT-2011 vs 01-OCT-2011 14:54:33

Use TRUNC(SYSDATE) when working with truncated date fields.

- VARCHAR2(1) vs CHAR(1)
- Long story short, VARCHAR is variable size and CHAR is fixed size. In *large*
quantities this can make a difference in storage space as well as affect
computation time.

Performance wise, use CHAR is data is going to be fixed length. If you are
flexible with varying length of your data then use VARCHAR

- Be mindful of conditional clauses i.e. x = ‘Y’
- If you know that x will always be uppercase less of a concern, but best practice
is UPPER(x) = ‘Y’ to ensure proper evaluation is occuring