REBUILDING AN INDEX
Rebuilding an index has a key advantage over re-creating an index from scratch, as well as a key disadvantage. The advantage of rebuilding an index is that the existing index is in place until the rebuild operation is complete, so it can therefore be used by queries that are run concurrently with the rebuild process. The main disadvantage of the index rebuild process is that you will need space for both indexes, which is required during the rebuild process. Some of the key reasons to rebuild an index include the following:
* Rebuilding an index that has become fragmented over time
* Rebuilding an index after a large, direct-path load of data
* You want to move an index to a different tablespace
* The index is in an unusable state due to a partition-level operation on the associated table.
Rebuilding an index has a key advantage over re-creating an index from scratch, as well as a key disadvantage. The advantage of rebuilding an index is that the existing index is in place until the rebuild operation is complete, so it can therefore be used by queries that are run concurrently with the rebuild process. The main disadvantage of the index rebuild process is that you will need space for both indexes, which is required during the rebuild process. Some of the key reasons to rebuild an index include the following:
* Rebuilding an index that has become fragmented over time
* Rebuilding an index after a large, direct-path load of data
* You want to move an index to a different tablespace
* The index is in an unusable state due to a partition-level operation on the associated table.
CONTEXT SWITCHING
When we consider "CONTEXT SWITCHING" in relation to Oracle Database, we specifically refer to the exchange of processing control between the SQL and PL/SQL engines (without necessarily understanding what occurs in such a process). These two engines are separate and distinct but we use them interchangeably. This means that when we call SQL from PL/SQL or vice versa, the calling context needs to store its process state and hand over control and data to its counterpart engine (which may or may not be picking up from an earlier switch). This switching cycle is computationally intensive and can typically be repeated so many times that its effects on response times can become quite noticeable.
WHEN A SQL STATEMENT IS WRAPPED IN A PL/SQL FUNCTION AND CALLED FROM SQL QUERIES, THE COST OF CONTEXT-SWITCHING IS AUTOMATICALLY DOUBLED—IT NOW OCCURS BOTH INSIDE AND OUTSIDE THE FUNCTION.
SQL statements are called from PL/SQL in a loop, so the execution will switch back and forth between the PL/SQL engine and the SQL engine. This switch between two environments is known as a context switch. Context switches increase elapsed time of your programs and introduce unnecessary CPU overhead. You should reduce the number of context switches by eliminating or reducing the switching between these two environments.
If you need to access the column value after inserting a row, then use returning clause to fetch the column value. If you need to access SYSDATE in PL/SQL itself, use PL/SQL construct to fetch the current date in to a variable.
Avoid the use of the DUAL Table which calls for a context Switching between SQL and PLSQL environments.
It is recommended you never issue a FETCH BULK COLLECT on a resultset without a LIMIT clause when you do not know ahead of time the size (or approximate size) of the resultset.You may be thinking that the database initialization parameter PGA_AGGREGATE_TARGET will insulate your system from such problems. This is not correct. This parameter only applies to memory allocations that the database can adjust internally as required, such as memory for sorting or hashing. If you ask for 50 GB of PGA for PL/SQL collection memory, THE DATABASE WILL TRY TO HONOR THAT REQUEST, NO MATTER HOW MUCH TROUBLE THAT MAY CAUSE.
Once collections become sparse, bulk bind will not automatically work using low and high boundary index values. However, from version 10.2 onwards, the FORALL syntax has been extended to include the INDICES OF and VALUES OF specification.
CALLING PLSQL FROM SQL
When PL/SQL functions are heavily used, particularly in SQL statements, there can be a range of associated costs, most notably for performance. Depending on the nature of the function, simply calling PL/SQL from SQL and/or excessive I/O can degrade performance of even the most trivial of queries.
When we consider "CONTEXT SWITCHING" in relation to Oracle Database, we specifically refer to the exchange of processing control between the SQL and PL/SQL engines (without necessarily understanding what occurs in such a process). These two engines are separate and distinct but we use them interchangeably. This means that when we call SQL from PL/SQL or vice versa, the calling context needs to store its process state and hand over control and data to its counterpart engine (which may or may not be picking up from an earlier switch). This switching cycle is computationally intensive and can typically be repeated so many times that its effects on response times can become quite noticeable.
WHEN A SQL STATEMENT IS WRAPPED IN A PL/SQL FUNCTION AND CALLED FROM SQL QUERIES, THE COST OF CONTEXT-SWITCHING IS AUTOMATICALLY DOUBLED—IT NOW OCCURS BOTH INSIDE AND OUTSIDE THE FUNCTION.
When PL/SQL functions are heavily used, particularly in SQL statements, there can be a range of associated costs, most notably for performance. Depending on the nature of the function, simply calling PL/SQL from SQL and/or excessive I/O can degrade performance of even the most trivial of queries.
When we consider "CONTEXT SWITCHING" in relation to Oracle Database, we specifically refer to the exchange of processing control between the SQL and PL/SQL engines (without necessarily understanding what occurs in such a process). These two engines are separate and distinct but we use them interchangeably. This means that when we call SQL from PL/SQL or vice versa, the calling context needs to store its process state and hand over control and data to its counterpart engine (which may or may not be picking up from an earlier switch). This switching cycle is computationally intensive and can typically be repeated so many times that its effects on response times can become quite noticeable.
WHEN A SQL STATEMENT IS WRAPPED IN A PL/SQL FUNCTION AND CALLED FROM SQL QUERIES, THE COST OF CONTEXT-SWITCHING IS AUTOMATICALLY DOUBLED—IT NOW OCCURS BOTH INSIDE AND OUTSIDE THE FUNCTION.
Thanks for clarifying about context switching.
ReplyDelete