Performance Tuning on SQL and PL/SQL
Performance Tuning can be performed by following few guidelines:
1. Database must be normalized which will help to decrease the storage requirement and faster search performance.
Normalization is the process of efficiently organizing data in a database. It deals with elimination of redundant i.e. duplicate data and ensures data dependences i.e. only storing related data in a table. Therefore, Normalization helps in reducing the amount of space a database consumes and ensures data is logically stored.
We use Normalization in order to show correct, accurate and reliable data.
Types of Normal forms:
1NF: In 1NF repeating groups i.e. duplicate columns are eliminated. Separate tables are created for each group of related data and are identified by primary key.
2NF: Table must be in 1NF. Partial dependency must be eliminated i.e. create primary-foreign key relationship.
3NF: Table must be in 2NF. Remove columns that are not dependent upon primary key.
4NF: Table must be in 3NF. A relation is in 4NF if it has no multi-valued dependencies.
5NF: Relation should have only candidate keys and its primary key should consist of only single column.
2. Use proper join conditions on complex queries.
Joins in Oracle
Cartesian Join: When a Join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. To avoid a Cartesian product, always include a valid join condition in a “where” clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions. For ex: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.
Equi Join: This type of Join involves primary and foreign key relation. Equi Join is also called Simple or Inner Joins.
Non-Equi Joins: A Non-Equi Join condition containing something other than an equality operator. The relationship is obtained using an operator other than equal operator (=).The conditions such as <= and >= can be used, but BETWEEN is the simplest to represent Non-Equi Joins.
Outer Joins: Outer Join is used to fetch rows that do not meet the join condition. The outer join operator is the plus sign (+), and it is placed on the side of the join that is deficient in information. The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. A condition involving an Outer Join cannot use IN and OR operator.
Self Join: Joining a table to itself.
3. Create indexes properly when and where required.
Index is a Schema object that is used to improve the performance of some queries. Is used and maintained automatically by the Oracle server. Indexes can be created explicitly or automatically. If you do not have an index on the column, than a full table scan occurs. Indexes are logically and physically independent of the table indexed. This means that they can be created or dropped at any time and have no affect on the base tables or other indexes. When you drop a table, corresponding indexes are also dropped.
There are two types of Indexes:
1) Unique Index: Is created automatically when you define a primary key or unique key constraint on a table definition.
2) Non-Unique Index: Which a user can create on columns to speed up access to the rows.
When to create an Index and when not:
1) If a column contains a wide range of values.
2) If a column contains a large number of NULL values.
3) When one or more columns are frequently used together in a WHERE clause or a Join condition.
Not to create Index:
1) When the table is small
2) The table is updated frequently.
Also, More Indexes on a table does not mean faster queries. Each DML operations that are committed on a table with indexes must be updated. The more indexes you have associated with a table, the more effort the oracle server must make to update all the indexes after a DML operation.
4. Use Order by clause for an index scan.
5. Use Exists clause instead of IN operator.
EXISTS clause is used for testing whether a given set is empty or not , returns the Boolean value either True or False. Whereas IN Clause is use to check every values that exists in the given set to the corresponding condition. EXISTS condition is faster than IN condition in case the condition needs to test for the existence of values in the set.
6. Use views when few columns need to be selected from multiple tables.
A View logically represents subsets of data from one or more table. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.
Views are used:
1) To restrict data access
2) To make complex queries easy
3) To provide data Independence
4) Views provide groups of user to access data according to their requirement.
7. Use materialized views when performing complex calculations on larger tables.
Views evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else. The upside of a view is that it will always return the latest data to you. The downside of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly. Materialized views are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query result set has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed. In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once (or as often as you refresh your materialized view), rather than each time you select from the materialized view. In addition, with query rewrite enabled, Oracle can optimize a query that selects from the source of your materialized view in such a way that it instead reads from your materialized view. In situations where you create materialized views as forms of aggregate tables, or as copies of frequently executed queries, this can greatly speed up the response time of your end user application. The downside though is that the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed. Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables. Materialized views can be incrementally updated by combining them with materialized view logs, which act as change data capture sources on the underlying tables. Materialized views are most often used in data warehousing / business intelligence applications where querying large fact tables with thousands of millions of rows would result in query response times that resulted in an unusable application.
8. Use SQL Hints for performance.
9. Use EXPLAIN PLAN and TKPROF for getting the execution plan of the statements.
The EXPLAIN PLAN checks the feasibility of a query. The EXPLAIN PLAN statement allows you to submit a SQL statement to Oracle and have the database prepare the execution plan for the statement without actually executing it. The execution plan is made available to you in the form of rows inserted into a special table called a plan table. You may query the rows in the plan table using ordinary SELECT statements in order to see the steps of the execution plan for the statement you explained. EXPLAIN PLAN is handy for evaluating individual SQL statements. The PLAN table includes columns like operations, object name, number of rows affected, size, cost etc.
TKPROF is a utility that you invoke at the operating system level in order to analyze SQL trace files and generate reports that present the trace information in a readable form. SQL trace files are text files that, strictly speaking, are human readable. In other words, you can say that TKPROF is a program that you invoke at the operating system command prompt in order to reformat the trace file into a format that is much easier to comprehend.
10. Use BULK COLLECT and FORALL statements to reduce context switch between SQL and PL/SQL engine.
A certain amount of performance overhead is associated with the context switches. However, the PL/SQL language has a number of features that can minimize the performance overhead known as Bulk SQL. Generally, if a SQL statement affects four or more rows, bulk SQL many improve performance significantly. Bulk SQL supports batch processing of SQL statements and their results. It consists of two features: – the FORALL statement and the BULK COLLECT clause. The FORALL statements sends INSERT, UPDATE or DELETE statements in batches from PL/SQL to SQL instead of one at a time. The BULK COLLECT is use to fetch or select all rows in a single go rather than one at a time.