In this post I will be sharing all possible ways in my knowledge to increase performance of the query, SQL or PlSql operations and other database operation. All these methods are various ways of Performance Tuning.
1. Create Needful Indexes
2. Performing Bulk Operation, Fetch bulk data or bulk INSERT, UPDATE or DELETE
Whenever huge amount of data need to be fetched then use BULK COLLECT on COLLECTION.
Also, we should use FORALL on collection to perform INSERT, UPDATE and DELETE operations. Context switching between Plsql engine and SQL engine is reduced a lot with this method.
Read more...
3. Different Tablespace for Index and table
4. Use of Bind Variable
Bind variable should be used instead of writing hard coding values in WHERE conditions. If we use bind variable then same SQL with different input value need not to be parsed again. Parsing is a CPU intensive and resource consuming process.
Read more...
5. Use Partitioning on Tables
6. Using Parallel hints for SELECT, INSERT, UPDATE, DELETE operations
The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the SELECT, INSERT, UPDATE, and DELETE portions of a statement, as well as to the table scan portion.
Read more...
7. Using Table/Index with more Degree
8. Take care when Creating and Using indexes
Indexes enhance performance only if used with care. We need to have good understanding of when to create indexes, which index suit the requirement and when not to create indexes. e.g. Disable Indexes before heavy bulk update or insert ( e.g. batch loading, batch update).
Read more...
9. Make SQL Statement as efficient as possible
-> Make sure you have appropriate indexes.
-> Make sure you have up-to-date statistics on all the table, using the subprograms in the DBMS_STATS package.
-> Analyse the Execution plan and performance of SQL using: EXMPLAIN PLAN, TKPROF and Oracle Trace.
-> Use AWR, ADDM reports to analyse the performance of database.
10. Too much calculation in pl/sql code use different datatype
Programs that do a lot of mathematical calculations. You will want to investigate the datatypes PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE. These datatype use machine algorithm and are fast compared to scalar data type NUMBER, INTEGER.
11. Making function DETERMINISTIC
Functions that are called from PL/SQL queries or SQL SELECT, DML queries, executing on millions of rows and used frequently, you will want to look at all performance features to
make the function as efficient as possible, and perhaps a function-based index to precompute the results for each row and save on query time. This is possible by specifying the function return as DETERMINISTIC and creating function based indexes.
Read more...
12. Create Function Based Index
If you use arithmetic calculation on columns or use some function on column in WHERE clause, then normal index will not help. You need to create a Function based index for that.
e.g. WHERE UPPER(first_name) = 'JOHN'
Read more...
13. Create Materialize View
If you need to perform operation of data fetch from OLAP application Databases server at remote location, then it's a good practice to create Materialized view of the data in OLAP DB server on which you need to work, on client site. Data fetch will be faster from Materialized view and there will be no concern of slow network network. Network load will be reduced a lot. However you will not have the updated data and you need to schedule refresh of materialized view or update it manually. Also MV need space on your client local system.
Read more...
1. Create Needful Indexes
To Increase performance of a SELECT query, we should have needful indexes on the columns used in WHERE clause conditions of query. Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table.
Read more..2. Performing Bulk Operation, Fetch bulk data or bulk INSERT, UPDATE or DELETE
Whenever huge amount of data need to be fetched then use BULK COLLECT on COLLECTION.
Also, we should use FORALL on collection to perform INSERT, UPDATE and DELETE operations. Context switching between Plsql engine and SQL engine is reduced a lot with this method.
Read more...
3. Different Tablespace for Index and table
If you have a huge table and you have also created index on that table. Then for performance benefit it is good to have indexes and table on different tablespace. However for good management point of view Tables and Indexes could be placed in same Tablespace.
4. Use of Bind Variable
Bind variable should be used instead of writing hard coding values in WHERE conditions. If we use bind variable then same SQL with different input value need not to be parsed again. Parsing is a CPU intensive and resource consuming process.
Read more...
5. Use Partitioning on Tables
This is application for OLAP application or where database tables have millions of data.
Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts 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.
6. Using Parallel hints for SELECT, INSERT, UPDATE, DELETE operations
The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the SELECT, INSERT, UPDATE, and DELETE portions of a statement, as well as to the table scan portion.
Read more...
7. Using Table/Index with more Degree
The number of parallel execution servers associated with a single operation is known as the degree of parallelism (DOP). Parallel execution is designed to effectively use multiple CPUs. The Oracle Database parallel execution framework enables you to either explicitly choose a specific degree of parallelism or to rely on Oracle Database to automatically control it.
Read more...8. Take care when Creating and Using indexes
Indexes enhance performance only if used with care. We need to have good understanding of when to create indexes, which index suit the requirement and when not to create indexes. e.g. Disable Indexes before heavy bulk update or insert ( e.g. batch loading, batch update).
Read more...
9. Make SQL Statement as efficient as possible
-> Make sure you have appropriate indexes.
-> Make sure you have up-to-date statistics on all the table, using the subprograms in the DBMS_STATS package.
-> Analyse the Execution plan and performance of SQL using: EXMPLAIN PLAN, TKPROF and Oracle Trace.
-> Use AWR, ADDM reports to analyse the performance of database.
10. Too much calculation in pl/sql code use different datatype
Programs that do a lot of mathematical calculations. You will want to investigate the datatypes PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE. These datatype use machine algorithm and are fast compared to scalar data type NUMBER, INTEGER.
11. Making function DETERMINISTIC
Functions that are called from PL/SQL queries or SQL SELECT, DML queries, executing on millions of rows and used frequently, you will want to look at all performance features to
make the function as efficient as possible, and perhaps a function-based index to precompute the results for each row and save on query time. This is possible by specifying the function return as DETERMINISTIC and creating function based indexes.
Read more...
12. Create Function Based Index
If you use arithmetic calculation on columns or use some function on column in WHERE clause, then normal index will not help. You need to create a Function based index for that.
e.g. WHERE UPPER(first_name) = 'JOHN'
Read more...
13. Create Materialize View
If you need to perform operation of data fetch from OLAP application Databases server at remote location, then it's a good practice to create Materialized view of the data in OLAP DB server on which you need to work, on client site. Data fetch will be faster from Materialized view and there will be no concern of slow network network. Network load will be reduced a lot. However you will not have the updated data and you need to schedule refresh of materialized view or update it manually. Also MV need space on your client local system.
Read more...
0 comments:
Post a Comment