This post is continuation of the earlier psots titled "Life Cycle of SQL Query in Oracle -I" and "Life Cycle of SQL Query in Oracle -II" . In Previous posts we discussed how the different phases of query execution by detailing each phase.
In this phase of query execution will discuss about the final steps in query retrieval like binding, executing & fetching result sets.
In the bind phase the query replaces the input values in place of bind variables placeholders. In oracle 11g optimizer has an enhanced feature of switching plans based on the bind variable value. Which sounds great and work wonders in some cases though.
If the initialization parameter "_OPTIM_PEEK_USER_BINDS" is enables i.e. set to true then optimizer will peek at the value of the bind variable and then use the histogram ( allows optimizer more detailed information about the distribution of values in a column) to pickup an appropriate plan based on the distribution of the bind value in a column of a table. This parameter is enabled by default.
If the initialization parameter "_OPTIM_PEEK_USER_BINDS" is disabled i.e. set to false then optimizer will use the same plan irrespective of how the reference column data is distributed/skewed in the table.
We always say that using the bind variables is good to avoid hard parse of SQL's. but when bind variables are used against the column of skewed data sometime leads to less than optimal execution plan. This is because the optimizer peeks at the bind value during the hard parse of the SQL. so the value of bind variable first presented will impact the subsequent execution of the statement. As the statement get hard parsed every time the statement runs and generates an optimal plan for the respective bind variable value. To overcome this problem Oracle introduce a mechanism called ACS (Adoptive Cursor Sharing) by allowing server to compare effectiveness of execution plans with different bind values. If the execution plan is suboptimal it will allow optimizer to choose an alternate plan.
Due to ACS some of the SQL statements may impact your performance, in that case you can disable the ACS and control what statements the optimizer implements ACS feature to.
Alternatively you can enable or disable the ACS on selected statements which you know will benefit from ACS using the follwoing hints
In the execution phase, the execution plan is executed. the optimizer look for related data blocks in the buffer cache if not reads the data from the disk. The affected rows based on the DML is locked as no other user can overwrite the records and also before and after images are written into redo log buffer and undo segments respectively.
During execution the optimizer reads the execution tree, the order in which the tree steps are executed is reversed.
i.e. the first step in this execution plan is ID '3' Table Access Full. so we read the plan bottom up. Each step of the execution tree either retrieves the row from database or accepts rows from one or more row source as input. During execution the database process reads the data from disk into the buffer cache if the data is not in the buffer cache. The database process also takes out locks and latches to ensure integrity and also logs any changes made during the SQL execution.
The final phase in the SQL execution is fetching the data. During the fetch phase data is read/fetched from the blocks in the buffer, filtered the data that does not match the predicate, and also sorted if any order by clause is used in the query.
The most important part in fetch phase is sorting, which is quite resource intensive. Oracle performs sorts the data automatically in these cases
Please feel free for any questions or suggestions. Thanks.
In this phase of query execution will discuss about the final steps in query retrieval like binding, executing & fetching result sets.
In the bind phase the query replaces the input values in place of bind variables placeholders. In oracle 11g optimizer has an enhanced feature of switching plans based on the bind variable value. Which sounds great and work wonders in some cases though.
If the initialization parameter "_OPTIM_PEEK_USER_BINDS" is enables i.e. set to true then optimizer will peek at the value of the bind variable and then use the histogram ( allows optimizer more detailed information about the distribution of values in a column) to pickup an appropriate plan based on the distribution of the bind value in a column of a table. This parameter is enabled by default.
If the initialization parameter "_OPTIM_PEEK_USER_BINDS" is disabled i.e. set to false then optimizer will use the same plan irrespective of how the reference column data is distributed/skewed in the table.
We always say that using the bind variables is good to avoid hard parse of SQL's. but when bind variables are used against the column of skewed data sometime leads to less than optimal execution plan. This is because the optimizer peeks at the bind value during the hard parse of the SQL. so the value of bind variable first presented will impact the subsequent execution of the statement. As the statement get hard parsed every time the statement runs and generates an optimal plan for the respective bind variable value. To overcome this problem Oracle introduce a mechanism called ACS (Adoptive Cursor Sharing) by allowing server to compare effectiveness of execution plans with different bind values. If the execution plan is suboptimal it will allow optimizer to choose an alternate plan.
Due to ACS some of the SQL statements may impact your performance, in that case you can disable the ACS and control what statements the optimizer implements ACS feature to.
alter system set “_optimizer_adaptive_cursor_sharing = false”;
alter system set “_optimizer_extended_cursor_sharing_rel” =
"none";
- - You can also disable ACS for user session only
alter session set “_optimizer_adaptive_cursor_sharing = false”;
alter sesssion set “_optimizer_extended_cursor_sharing_rel” =
"none";
Alternatively you can enable or disable the ACS on selected statements which you know will benefit from ACS using the follwoing hints
/*+BIND_AWARE
opt_param('_optim_peek_user_binds','true')
*/ -- to enable ACS/*+NO_BIND_AWARE
opt_param('_optim_peek_user_binds','true')
*/ -- to disable ACSIn the execution phase, the execution plan is executed. the optimizer look for related data blocks in the buffer cache if not reads the data from the disk. The affected rows based on the DML is locked as no other user can overwrite the records and also before and after images are written into redo log buffer and undo segments respectively.
During execution the optimizer reads the execution tree, the order in which the tree steps are executed is reversed.
i.e. the first step in this execution plan is ID '3' Table Access Full. so we read the plan bottom up. Each step of the execution tree either retrieves the row from database or accepts rows from one or more row source as input. During execution the database process reads the data from disk into the buffer cache if the data is not in the buffer cache. The database process also takes out locks and latches to ensure integrity and also logs any changes made during the SQL execution.
The final phase in the SQL execution is fetching the data. During the fetch phase data is read/fetched from the blocks in the buffer, filtered the data that does not match the predicate, and also sorted if any order by clause is used in the query.
The most important part in fetch phase is sorting, which is quite resource intensive. Oracle performs sorts the data automatically in these cases
- Creating an index.
- Group by clause used in SQL.
Please feel free for any questions or suggestions. Thanks.
