Best Practices for Oracle Developers and DBAs

There has been paradigm shift in the technological and scientific innovations that are happening around us. From First generation Vaccum Tubes to Latest generation of Nanotechnology, From Machine Language to High Level Programming Language, Technological innovation has made the computing devices – cheaper, faster, smaller.

The communication between the devices has seen a phenomenal growth. The volume of data produced by all this is rising exponentially. Today technology touches human lives more than ever before.

With all the technological and scientific progress happening around us we have kept our rich cultural heritage and values intact. We as humans are evolving so is the Oracle Database Product.

Over the years there have been several Oracle Database releases and version upgrades. What has remained mostly same are the best practices.

Here is the list of best practices which you can expect in my next few blog posts.

SQL and PL/SQL:

1. Comments – In Line and Multi Line
2. Replace Hard coded literals with Constants and move all related constants into a common package.
3. Modular Approach – Split big program into generic and standalone sub-programs.
4. Instrumentation whereever possible – ON/OFF as required.
5. Achieve as much as poosible with SQL, to avoid context switching between SQL and PLSQLengine.
6. Exception Handling – Try to handle all possible exceptions
7. Avoid “Select * from”, Use fully qualified names when selecting from multiple tables
8. Mention AUTHID clause, BEQUEATH clause,
9. Avoid default Oracle implicit type conversion.
10. Use %ROWTYPE for record types and %TYPE for variables. Avoid hardcoding VARCHAR2 length.
11. Exit the program gracefully. Make sure to free up resources and memory before exiting.
12. Familiarize with in-built oracle packages – Don’t write routine which is already provided by oracle.
13. Show user, con_id – set timing on and many more, use spool in SQL scripts
14. Ensure that the rollback scripts are ready.
15. Compile objects in the order of precedene.
16. Ensure you use paranthesis in expressions to override the default precedence order.
17. Start with the data model and then proceed with the coding for complex SQL queries.
18. Maintain test scripts/harness to test the logic and test performance.
19. Use GET and SET to modify package variables.
20. Use NOCOPY to copy variables by reference for performance benefits.
21. Use control tables to make the code customized and generic.

DBA:

1. Group related privileges and grant it to a role for better manageability.
2. Lock all unused user accounts and change default passwords for all default accounts
3. Use seperate disks for system, sysaux; application data and Index tablespaces for better performance.
4. Use comments whenever a parameter is changed – Record – date, time and changed by.
5. Use seperate disks for multiplexed redo logs files in same redo log groups.
6. Gather statistics using DBMS_STATS after BULK DML on tables and partitions.
7. Schedule the housekeeping jobs to runs during off peak time.
8. Keep oracle documents and useful links ready and handy for reference just like a bible.

In my next few blog posts, I shall showcase some of the best practices both for Developers and DBAs with the help of demo scripts.

Conclusion:

This list is not complete and is ever evolving. It is outcome of my years of association with Oracle technology and the learnings I had taken from Oracle pundits. Its kind of survival kit for both Developers and DBAs.

Cheers!
Vijay

Advertisement