web analytics
Avoid Unnecessary Context Switches Options
codeling
Posted: Friday, June 23, 2017 10:25:10 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 777
Points: 3264

Oracle use two engine for execution of PL/SQL programs:

  1. PL/SQL Engine
  2. SQL Engine

During execution of PL/SQL program to execute all procedural statements (like if, for, assignments...) Oracle use PL/SQL engine and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.

Which impose additional overhead like maintain the state of variables and stack on every switch..... means highly expensive. Specially the cursor loops are very expensive so instead of that one should use Bulk Bind and Bulk collect features which reduce context switching.

Sponsor
Posted: Friday, June 23, 2017 10:25:10 AM
 
codeling
Posted: Friday, June 23, 2017 10:30:12 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 777
Points: 3264

Instead of fetching into simple variables or records fetch into a collection of scalars or a collection of records using BULK COLLECT INTO.

FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine.

Usually, using of the BULK COLLECT and FORALL statements can drastically improve the performance.

Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2017 Digcode.com. All rights reserved.