Oracle SQL Best Practices

Here are Some basic Principles that Developer should remember while designing database.

SQL statement parsing

• SQL statement parsing is a CPU-intensive operation.Code should be written to reduce the amount of parsing required. 
• If a SQL statement needs to be executed multiple times, write a cursor to the parse statements from call to call. 
• This will ensure that a repeatedly executed statement is parsed only once. 
• Do not use literals in SQL statements, instead use bind variables to minimize parsing by reusing parsed statements cached in the shared pool. 
• For existing applications use the CURSOR_SHARING initialization parameter to avoid some of the parsing overhead. 
• Always consider using stored procedures because they are stored in a parsed form, which reduces run-time parsing. 
• For data warehouse using bind variables may give adverse effects on performance as bind variables will prevent the query optimizer from using histograms on columns with highly skewed data.
• So use a practice depending on

             1) a long individual query performance in Data warehouse
             2) (or) number of queries per second, in transaction database.

Web developers:

      i)  Reuse database connections
           As establishing a database connection is an expensive operation. 
           Applications should avoid continually creating and releasing database connections.
           Always use connection pooling also set a connection wait timeout to 
               prevent frequent attempts to reconnect to the database when no more 
               connections in the pool.

           Look for Transparent Application Failover for retrying connections.

Minimize or Reduce Data contention

       Try to reduce contention in your application distribute data in multiple 
               tablespaces/tables/partitions and avoid constant updates of the same row 
               (like update balance) and run periodic reports instead. 

   Refer Oracle Tech Note 

Use indexes on need basis

• Using an index to select, update or delete a few rows in a big table is orders of magnitude faster than using a table scan.
• Make sure that all big tables used have indexes, and that the indexes are actually used in query plans. 
• Prevent hefty transactions (OLTP) perform full scans of big tables. 
• But check, if your application needs to create report or perform batch data processing off-line, full table scans that use multi-block I/O may be the optimal solution. 
• Check the cost of a full table scan is sometimes the fastest way to execute a particular query, verify that the optimizer indeed plans to perform the scan and,in that case, specify the ALL_ROWS hint in the select statement.

Process multiple rows at a time whenever possible

• Fetching, processing, and writing rows in bulk is much faster than doing it 
  row by row.
• A single SQL statement that processes all rows and performs all operations offers the optimal performance.
• This is because it requires just one network round trip and uses set-oriented processing that is highly optimized and parallelized in the database server. 
• If you need to use a procedural approach, then design your application to process multiple rows at a time with the FORALL statement and BULK COLLECT clause  together with PL/SQL collection types to implement bulk processing.  Also use LIMIT for bulk changes to commit / rollback. 
• To check and process few the erroneous records at last after checking them individually you can use SAVE EXCEPTIONS clause with SQL%BULK_EXCEPTIONS

Web developers:
      The Oracle JDBC driver provides support for statement batching and mapping Oracle 
             collections to Java arrays.  

Take care while using object-orientation

• The use of object-oriented techniques and languages in application development might hide the data access layer from the business logic. 
• As a result, programmers might invoke methods without knowledge of the efficiency of the data access method being used.
• This tends to result in sub-optimal database access: performing row filtering in the application, and doing row-at-a-time instead of array processing.
• To achieve high performance when using O-O languages try to place data access calls next to the business logic code. 
• If you are storing your data for long-term or anticipate application development on the same schema, always use the relational storage method for the best performance and flexibility.
• Object-orientation at the programming level should not lead to de-normalization of the relational schema.

Tips to Developer

    Choosing index

• The Oracle Database supports a wide variety of indexing techniques. 
• The B*Tree index is sufficient forthe majority of applications. If they do not meet your requirements, then look at alternatives such as reverse-key, descending,bitmap, function-based, domain indexes and index-organized tables. 
• Every Index have advantages and disadvantages ,try The SQL Access Advisor help in determining which indexes are required for current situation.

    External tables

• Use external tables for loading and merging data they allow the database to load data from flat files.
• An external table can be joined to another table during a load. 
• External tables make it possible to filter the data by using any SQL predicate. 
• When loading from an external table Oracle will automatically determine the degree of parallelism, split up the input files and start parallel query slaves. 
• Using an external table and the MERGE command allows you to efficiently update an existing database table with data from a flat file.
• Use fixed width column, delimiter and convert function respectively to get optimized loading time. 

    Joins

        Choose an appropriate technique of joins including nested loop, hash, sort-merge,
                Cartesian, full outer and anti-join. 

  

    Datatypes

• Using incorrect data types might decrease the efficiency of the optimizer may also cause unnecessary data conversions leads to under perform. 
• Don’t use a string to store dates, times or numbers. 
• Ensure that conditional expressions compare the same data types. 
• Do not use type conversion functions (such as a TO_DATE or TO_CHAR) on indexed columns,  Instead use the functions against the values being compared to a column. 

    Column constraints

• Column constraints are often considered only from the data integrity point of view. 
• However, the query optimizer also uses constraint definitions to build high-performance execution plans. 
• Define NOT-NULL constraints when applicable; it does not impose noticeable overhead during query processing. 
• Implementing other constraints in the database might negatively affect performance.  

    PL/SQL 

        

• PL/SQL is the most efficient language for massively data bound applications that do not require complex computation. 
• Its data types are the same as in SQL, therefore, no type conversion is required in application code. 
• It provides implicit cursor caching that helps to minimize hard parsing. 
• Generally,the server side is the preferred place to store the PL/SQL.
• For very complex compute-bound applications with light data access consider using Java in the middle tier.  

        Choose the right algorithm and data structures.

    
    Use static SQL  

• There are many advantages of using static over dynamic SQL in PL/SQL. 
• Specifically, SQL statements that are fixed at compile time are usually executed faster and the principle “parse once, execute many” is automatically applied to them. 
• Also, the PL/SQL compiler automatically ‘bulkifies’ static SQL to improve performance.
• Dynamic SQL should be used only when static SQL is no longer practical. 

    Try native dynamic SQL 

• Native dynamic SQL was introduced as an improvement on the DBMS_SQL API because it is easier to write and executes faster. 
• It is the preferred way of implementing dynamic SQL code. 
• DBMS_SQL is still maintained for column name and numbering of bind variables.
• Also native dynamic SQL cannot be used for operations performed on a remote database.

    Use pipelined table functions  

• Pipelined table functions let you use PL/SQL to program a row source. 
• A table function can take a collection of rows as input. 
• Execution of a table function can be parallelized, and returned rows can be streamed directly to the next process without intermediate staging. 
• With pipelined functions rows are iteratively returned as they are produced, instead of in a batch after all processing is completed. 
• Streaming, pipelining, and parallel execution of table functions can dramatically improve performance of complex queries.

    Use analytic functions  

• Analytics provides spreadsheet-like functionality in SQL language. 
• It allows users to compute aggregates without grouping rows and display them with query results. 
• Analytics apply to systems that generate reports, perform batch jobs, cleanse/validate data, and so on. 
• Analytics can also be used to efficiently find a row in a partition and top-N in a group of queries. 
• It can help to partition a table into non-overlapping chunks for parallel processing by multiple copies of a PL/SQL routine. 
• Such application-managed parallelism makes sense when a parallel query cannot be used because of the inherently single-threaded nature of the PL/SQL routine.

General Tips to DBA

• Enable database auditing. 
• Monitor SQL statement execution using the V$SQL_MONITOR and  V$SQL_PLAN_MONITOR views.
• Maintain and analyze performance statistics using AWR and ADDM. 
• Use profiling tools to identify performance hot spots in your application.
• Eliminate identified hot spots so that optimal performance can be achieved. 
• Optimize most frequent operations Before implementing an application, analyze how users will use it and identify the most critical operations.

Other Best practices

• Do not re-implement database features Use the features provided by the database instead of trying to re-implement them.
• Materialized views can store aggregated and pre-joined data from multiple tables and provide fast access to the data when frequent queries of the actual tables are extremely expensive. 
• The ROWNUM pseudo column can also be used to materialize a query result set. It can also reduce the number of PL/SQL function calls, and improve performance of multiple joins and top-N query processing.
• Include performance testing during application development and maintenance Build a test environment. 
• Thoroughly test your application before going production. 
• Periodically benchmark application performance, especially after database upgrades, major version releases and applying any software patches. 
• Test against representative data and with multiple concurrent users ( use production Clones).
• Only move to multi-user testing after you are satisfied with single-user performance.
• Use Real Application Testing (RAT) to perform real-world testing. 
• Always design your data model, database schema and application code to answer the most common queries as efficiently as possible. 
• For non-critical queries, shortcuts in design can be adopted to enable a more rapid application development. 

• Materialized views, pseudo columns, client and server result cache, hierarchical queries, merge statements like techniques described may significantly improve performance for certain query types.

Note: It is a responsibility of application developers to correctly choose and utilize database features that can satisfy performance and scalability goals. 
       These are just guidelines and tuning is an art that depends on various aspects.