The process of analyzing and reducing processing time is called Performance Tuning…

SQL Tuning

The iterative process of improving the performance of SQL statements to measurable and achievable goals.

Scope of Tuning

 1) Analyze and create the index

2) Rewrite SQL

3) Using Hints

4) All improvements have been done from SQL and its related Objects

5) Tuning the Server, CPU, RAM, and Network

6) Tuning the instance memory parameters

7) Optimizer setting to improve performance

Where to start with Performance Tuning

 1) Manual inspection of code ( Number of lines of code is small)

2) Execute the code with multiple log statements and analyze the statements that take more time

3) Tune the SQL code and PLSQL code separately

How to optimize code

 1) Implement the logic in SQL ( instead of PLSQL ) as far as possible

2) Use Analytical Functions as much as possible

3) Use Global Temporary Tables

4) Use Insert append hints

5) Use pass-by-reference ( NO COPY Compiler hint)

6) Consolidate the redundant code

7) Use LOB variables only if needed (Use Extended Data types from 12C)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8