The first basic step towards PL/SQL block/procedure/package tuning:
In most of the EIM processing, the data in EIM tables are pushed through PL/SQL packages or standalone procedures etc. At times the pl/sql code becomes bottleneck during the cut-over window/deployment espicially when the amount of data is huge. Proper tuning of the pl/sql code in advance can reduce the execution time and increase the throughput.
Here I would like focus on just one little but very important and critical parameter for performance. Normally when the throughput is poor and the performance is at stake, we try to put the blame on database factors [and yes the DBA too :-)], available physical resources and many others. But the question is to realize what utilizes the database resources and how efficiently it utilizes.
The basic element of every pl/sql code is the embedded SQL statements. The first thing that should come to our mind when concerned with performance tuning is the SQL statements inside the pl/sql code. Slow SQL statements are the main reason for slow execution. Tuning these sql statement can greatly enhance the performance of the overall code.
You can find a lot amount of content on tuning SQL statement and that too in great details on the internet, here i will just summarize those tips for reference and relate it with Siebel EIM if applicable:
1. Make sure you have appropriate indexes on the staging and other external ables that are used to push data to EIM tables. This may depend on your requirements.
2. Make sure you have up-to-date statistics on all the tables, using the subprograms in the DBMS_STATS package. For EIM processing this is very very beneficial. (for both EIM and Base tables) E.g.
Here I would like focus on just one little but very important and critical parameter for performance. Normally when the throughput is poor and the performance is at stake, we try to put the blame on database factors [and yes the DBA too :-)], available physical resources and many others. But the question is to realize what utilizes the database resources and how efficiently it utilizes.
The basic element of every pl/sql code is the embedded SQL statements. The first thing that should come to our mind when concerned with performance tuning is the SQL statements inside the pl/sql code. Slow SQL statements are the main reason for slow execution. Tuning these sql statement can greatly enhance the performance of the overall code.
You can find a lot amount of content on tuning SQL statement and that too in great details on the internet, here i will just summarize those tips for reference and relate it with Siebel EIM if applicable:
1. Make sure you have appropriate indexes on the staging and other external ables that are used to push data to EIM tables. This may depend on your requirements.
2. Make sure you have up-to-date statistics on all the tables, using the subprograms in the DBMS_STATS package. For EIM processing this is very very beneficial. (for both EIM and Base tables) E.g.
-- Execute:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SIEBEL','EIM_CONTACT',DEGREE =>10,ESTIMATE_PERCENT =>100,METHOD_OPT =>'FOR ALL INDEXED COLUMNS SIZE AUTO',GRANULARITY =>'ALL',CASCADE=>TRUE);
3. Analyze the execution plans and performance of the SQL statements, using: EXPLAIN PLAN statement. E.g.
-- Execute:
explain plan for
select * from STG_PRODUCT where batch = '125642890235; -- your SQL statement
-- Output the explain plan
select PLAN_TABLE_OUTPUT
from table(dbms_xplan.display('plan_table',null,'basic'));
4. Rewrite the SQL statements if necessary. For example, query hints can avoid problems such as unnecessary full-table scans. Avoid unnecessary full table scans for tables, this is very costly in terms of execution time.
5. Combine multiple SQL statements into one if possible. Each time you run a SQL statement in PL/SQL (and vice versa) there is a context switch and SQL statements have to be sent to the SQL statement executor (SQL engine) in the database. Obviously, therefore, if you have fewer SQL statements embedded in your PL/SQL code you will reduce the load on the Oracle database processing those statements.
6. a) If you are running SQL statements inside a PL/SQL loop, look at the FORALL statement as a way to replace loops of INSERT, UPDATE, and DELETE statements.
b) If you are looping through the result set of a query, look at the BULK COLLECT clause of the SELECT INTO statement as a way to bring the entire result set into memory in a single operation.
explain plan for
select * from STG_PRODUCT where batch = '125642890235; -- your SQL statement
-- Output the explain plan
select PLAN_TABLE_OUTPUT
from table(dbms_xplan.display('plan_table',null,'basic'));
4. Rewrite the SQL statements if necessary. For example, query hints can avoid problems such as unnecessary full-table scans. Avoid unnecessary full table scans for tables, this is very costly in terms of execution time.
5. Combine multiple SQL statements into one if possible. Each time you run a SQL statement in PL/SQL (and vice versa) there is a context switch and SQL statements have to be sent to the SQL statement executor (SQL engine) in the database. Obviously, therefore, if you have fewer SQL statements embedded in your PL/SQL code you will reduce the load on the Oracle database processing those statements.
6. a) If you are running SQL statements inside a PL/SQL loop, look at the FORALL statement as a way to replace loops of INSERT, UPDATE, and DELETE statements.
b) If you are looping through the result set of a query, look at the BULK COLLECT clause of the SELECT INTO statement as a way to bring the entire result set into memory in a single operation.
Both these are PL/SQL features but helps in tuning the sql statements.
7. Inefficient usage of loops inside the code can also prove very costly. Loops can be replaced with FORALL statements.
8. Use the BULK COLLECT clause on the query to bring the query results into memory in one operation
9. Use subselect (nesting one query inside another) for filtering query results.
10. Use highly optimized string functions such as REPLACE, TRANSLATE, SUBSTR, INSTR, RPAD, and LTRIM, these are very efficient as these use low-level code.
11. use regular expressions like REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR for searching and manipulating strings.
12. Implement short-circuit evaluation (as it is called) i.e. put the least expensive conditions first in the WHERE clause that evaluates multiple conditions separated by AND or OR.
13. Choose data types carefully to minimize implicit conversions as too many implicit conversions becomes overhead during execution. Like use of PLS_INTEGER instead of INTEGER etc.
14. Finally, do not ignore the warnings thrown by the compiler, applying the advice of the compiler might increase the efficiency of the overall code.
Much more and much detail can be added to it, but I have kept it simple and precise. You can pick each one of these and explore and research more on oracle documentation.
7. Inefficient usage of loops inside the code can also prove very costly. Loops can be replaced with FORALL statements.
8. Use the BULK COLLECT clause on the query to bring the query results into memory in one operation
9. Use subselect (nesting one query inside another) for filtering query results.
10. Use highly optimized string functions such as REPLACE, TRANSLATE, SUBSTR, INSTR, RPAD, and LTRIM, these are very efficient as these use low-level code.
11. use regular expressions like REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR for searching and manipulating strings.
12. Implement short-circuit evaluation (as it is called) i.e. put the least expensive conditions first in the WHERE clause that evaluates multiple conditions separated by AND or OR.
13. Choose data types carefully to minimize implicit conversions as too many implicit conversions becomes overhead during execution. Like use of PLS_INTEGER instead of INTEGER etc.
14. Finally, do not ignore the warnings thrown by the compiler, applying the advice of the compiler might increase the efficiency of the overall code.
Much more and much detail can be added to it, but I have kept it simple and precise. You can pick each one of these and explore and research more on oracle documentation.
Have a great tuning :-)
No comments:
Post a Comment