|
|
4. How to know PL/SQL compile parameters ?
- SHOW PARAMETERS PLSQL
- ALL_PLSQL_OBJECT_SETTINGS
5. Tell some tips to avoid performance problems in PL/SQL.
- Use FORALL instead of FOR, and use BULK COLLECT to avoid looping many times
- Tune SQL statements to avoid CPU overhead
- Use NOCOPY for OUT and IN OUT if the original value need not be retained. Overhead of keeping a copy of OUT is avoided.
- Reorder conditional tests to put least expensive ones first
- Minimize datatype conversions => Assign data to exact same type variables
- Use PLS_INTEGER for computation intensive code. NUMBER, INTEGER maintain precision and scale but not optimized for performance as additional checks are made to maintain precision and scale.
- Do not use subtypes like POSITIVE, NATURAL, INTEGER as they have additional checks
- Use BINARY_FLOAT, BINARY_DOUBLE
- EXECUTE IMMEDIATE is faster than DBMS_SQL
6. How do you handle exceptions for bulk operations ?
- Use the SAVE EXCEPTIONS clause ( FORALL index IN bound_clause SAVE EXCEPTIONS LOOP ... END LOOP )
- Use 'Exceptions When Others' to handle the exceptions
- SQL%BULK_EXCEPTIONS(i).ERROR_CODE,
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
SQL%BULK_EXCEPTIONS.COUNT
|
| Pervious1-3
Next7-10 |
|
|
|
|
|
|
|
