Category: Technology

Oracle database performance tuning
database programming language software Technology Verbat

12 Oracle Design Consideration


Effective Schema Design

  • The Schema Plays an important role in  tuning joins
  • Choose data types for all primary and unique keys carefully, and remain consistent through out the schema
  • If you primary key column is varchar2(100), such a primary key is expensive because the large key must be carried in every referencing table, and also because Oracle bust compare long strings when performing a join
  • Create Index where necessary and if possible try to create indexes on multiple columns
  • Normalize your tables , so that there is not a lot of redundancy
  • Put necessary constraints on the table so that you have the right data.

… 12 Oracle Design Consideration

Advertisements
Oracle database performance tuning
database programming language software Technology Verbat

11 House Keeping in Oracle


Invalid Optimizer Statistics

  • Statistics are used by the optimizer to come up with a good execution plan
  • Sometimes optimizer statistics might be  stale or missing

The statistics may not always be accurate because the size of the table might have changed from a few 100k rows to a few million rows. There fore statistics should be periodically collected  and be made as part of the database maintenance routines.

… 11 House Keeping in Oracle

Oracle database performance tuning
database programming language software Technology Verbat

08 Considerations while using PLSQL in programming (Oracle)


Reduce the number of trips to the Database

Every time SQL is executed, Oracle needs to perform all the internal processing steps, so try to reduce the number of database accesses. This reduces a lot of burden on the database.

Example: If you want to fetch the data of 2 products in the PL/SQL programming language.

… 08 Considerations while using PLSQL in programming (Oracle)