A quick Guide to Performance Tuning in Oracle
Often, we come across situations where our query is taking too long to retrieve results than expected. Initially, when we setup the database, we experience the faster retrieval of data set as the data was less but as time flies, data grows up. Once we have crossed some million records in our table our query starts taking up time. Unlike Programming in PL/SQL we don't have any definitive guidelines for the performance optimization, it can't be taught it must be experienced.
Below I have clubbed the various topics of Oracle(which are very vast) to give an overview of Performance Tuning and is suitable for audience who already have a conceptual understanding of same.
When we speak about performance it is not only how fast we can retrieve the data. Many queries will return you results quickly but they are making more disk reads or consuming more CPU Time. The best way to analyse the performance bottleneck is by reading the trace files. A query execution takes place in 3 steps :- Parsing, Execution and Fetching and has 3 performance factors CPU Time, Elapsed Time and Disk Read.
In order to achieve Optimum performance of queries we have to first understand the Oracle Optimizer. When we fire a query, the first task the database perform is analyzing and determining the best and most efficient way to retrieve results. The part of database that does this analysis is called the query Optimizer or Optimizer. An Optimizer finds out the different ways or approaches to retrieve data from disk considering many factors related to the referenced objects and the conditions specified in the query Then it compares the cost of all possible approaches and choose the most efficient approach to fetch/retrieve the results. The path/approach a SQL Statement executes physically to get the result is called The Execution plan.
We can easily view the plan in and IDE like Toad or SQL Developer, for e.g. in the figure(Right), we can see our query is accessing the data using an Index N5. So at first the database will retrieve ROWIDs from Index and then later it will access the table for those ROWIDs.
Generally, in OLTP Systems like Oracle Applications, where data is not very huge and we rely on Indexes for the performance improvement while on the other hand in Data Warehouse systems our data is historical and is huge (Millions of records) thus we rely more on Partitions. Here I would be mentioning some of my commonly used techniques to achieve Optimum Performance of the application.
- Indexes - Indexes are best way to boot the query performance if we have some millions of records, especially when are spending more time in data retrieval as Indexes can slow down the Insertion on the table. We should make indexes on the very frequently used columns either Single or Composite Indexes can be made. e.g. Create an Index on ORDER_NUMBER column of a Table where we do a lot of search based on the Order Number.
- Bulk Collect - Bulk collect is a very effective in scenarios when we have to retrieve Multiple rows in a single fetch, improving speed. Consider a case where we have to validate a column value against a Lookup/Dimension for all the rows using a function. The query to retrieve values from the Lookup/Dimension is not cost intensive but running the query a million times can increase the Running time of the session drastically. The results retrieved from the Lookup/Dimension table are very few. We can store the results of the Lookup/Dimension to a Collection and for each record. Then instead of triggering a query we can check for the value in the collection. This will save the CPU and Elapsed time, taking the performance to the program to an Optimal level.
- FORALL - FORALL is used to perform DML(DELETE, UPDATE and INSERT) Operations to the collection very quickly and saving the Context Switching time. So Whenever we have to perform a DML in a Loop we should prefer FORALL. e.g. Using BULK COLLECT and FORALL instead of performing a DML statement in a Cursor Loop.
- Partitions - Partitioning is very effective and efficient way of Organizing and Retrieval of data in Oracle. It enhances the performance and reduce the cost of ownership for storing large amount of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. e.g. We can create a table Partitioned on ORG_ID and then create a Loacal Index on ORDER_NUMBER column for a much efficient manageability rather than creating a Single Composite Index of the two columns.
- Function Result Cache - Let's consider a scenario where we have a Table which gets retrieved by numerous applications but changes infrequently, perhaps once a day. As a result the data gets repeatedly retrieved from the block buffer cache which is mostly static. Using RESULT_CACHE clause we can instruct Oracle to remember/store the result of a function for each record retrieved for a specific Input. When a session execute the function with parameters which was previously executed, the PLSQL engine will simply retrieve the record from the Cache. We can also instruct oracle to invalidate the cache results if any Commit has been applied to the table.
FUNCTION one_employee (employee_id_in
IN employees.employee_id%TYPE)
RETURN employees%ROWTYPE
RESULT_CACHE RELIES_ON (employees)
IS
l_employee employees%ROWTYPE;
BEGIN
.
.
.
- Parallelism or Concurrency - When we have to execute a procedure multiple times for a given set of records and the result of one procedure execution is not dependent on the other. We can introduce the concurrency in their execution by AUTONOMOUS_TRANSACTION program or by creating the child programs which can run independently on a certain set of records in parallel.
- Parallel Hints - We can use a parallel hint to specify Oracle Optimizer to retrieve records from a table in parallel through slave processes. The parallel hint accepts a table name and an optional "degree" argument to tell Oracle how many slave processes to use in parallel. The recommended approach for using Parallel query is to add a parallel hint to the SQL statement that perform a full-table scan.
select /*+ FULL(emp) PARALLEL(emp, 35) */
from employees
Glossary
