A General PL/SQL Code Review Checklist

HEADER ELEMENTS

File Name
Clear, meaningful and descriptive about main objective of the file.
Multiple words are joined using underscores (which adheres to the
file naming standards.
Purpose
Brief description of the functionality of the Code.
Usage
Method of calling the SQL in the prompt to the test the Code.
Date
when the development of the Code was started.
Edit History
History of changes made to the Code. Update Edit History in case
of Change Request to reflect the changes made in the code.
Author (Company Name)
Use standard Boiler Plate given by the client.

Coding Conventions Systematize and Automate
Use code templates to design and make available headers
and structures. Build your checklists into these templates!

Use automated formatters at both the individual and
team levels.
Do not manually format your code. Big waste of time.

General Guidelines while Coding
(i) Standard (space-based) block nesting indentations should be
two spaces
(ii) Avoid Using TABS for indendation, use spaces instead
(iii) All the Keywords of SQL should be in CAPS
(iv) The functional overview comment of a routine should be
indented two spaces. The highest-level statements that
follow the overview comment should be in the same column
as comment, with each nested block indented by two spaces
(v) Adequate blank lines should be left to aid readability of source code
(vi) Proper Indentations should be used when writing PL/SQL
code, like IF and ENDIF , LOOP and END LOOP should line up correctly.
(vii) Use SQL%ROWCOUNT to increment counters after
Insert/Update/Delete statements.
(viii) There should not be more than one expression per line
(ix) A blank line should separate the code and DECLARE/ BEGIN/
EXCEPTION/ END.
(x) The beginning of stored procedure should start with ‘BEGIN’ in
uppercase and end with ‘END’ in uppercase.
(xi) Write consistent Log messages using DBMS_OUTPUT package
detailing the no of rows updated/inserted/deleted in the tables.
(xii) Give inline comments wherever code is changed in case of
CR’s(Client Requests).
(xiii) Application code must raise, handle, log and communicate
errors in a consistent, robust manner.

Killing Invalid Sessions to increase Performance of Database

Many times we face performance problem on an application running on web. The reason can be many.
I have pointed out a common reason which is due to the INCATIVE sessions when user
either closes the window or leave it for long time or for any other reason.
The INVALID sessions needs to be killed to avoid performance issues.

To kill inactive session created at database end(with dba previledge), we have two options:

1)Get session serial#'s -
Please query all sessions from v$sessions where status='INACTIVE' first
SELECT sid,serial#,osuser,(last_call_et/60) as idle_time FROM v$SESSION;

Alter system kill session 'sid,serial#';
Example :- Alter system kill session '156,1';

2). Create a profile with a resource_name as IDLE_Time settings as 30 Minutes.
For this, resource_limit parameter has to be set to true.
This will enable the settings such that, all inactive sessions for more than 30
minutes will be killed. These will show up as SNIPED in v$session and after
some time will be released automatically.

If you are in dedicated server environment then for the first step do the following:
Alter system set resource_limit=true;
create profile only30minutes_of_inactivity limit idle_time 30;
alter user profile only30minutes_of_inactivity;

Welcome !!!

Dear Friends,
Welcome to my blog "OracleSource". This blog is mainly for SQL and PL/SQL Developers. I will be posting my experience through various articles, documents and examples which would help you in day to day work and learning. I will try to pour my knowledge on weekly or daily basis as well as will try to answer your questions on this.
Looking forward for your love and cooperation.

Thanks,
Shakun