Behind all this so called confusing behaviour of optimizer there is a LEGITIMATE reason...
Oracle Optimizer builds an execution plan for a query based on the values passed to its variables at run time and locks the EXECUTION PLAN in memory which is then referred to as a CURSOR. For subsequent execution of the same query the Optimzer uses the same CURSOR to execute the query thereby reducing PARSE TIMES by avoiding HARD PARSES.
It is quite possible that for a particular set of variables the same execution plan is not suitable hence the end user start to complain of SLOW performance.
Solution to such a scenario is very simple once your DBA , with the help of the architect has been able to figgure out the possible combination of variables and the data distribution in the base tables. Thats why I always say that a GOOD DBA is a GOOD ARCHITECT since he/she should be completely aware of the ARCHITECTURE of the system.
Now with the consent of Application Operation guys the DBA invalidates the relevant CURSOR,various methods to do so; common amongst them prior to 10.2.0.4 is to grant or revoke privileges on the MAIN TABLE involved in the query , so as to influence the Optimizer to build a FRESH execution plan upon the NEXT execution of this query.
The DBA can repeat this excerse with various combination of VARIABLES possible for the BIND VARIABLES present in the contending QUERY until an OPTIMAL PLAN is achieved.
DOWNSIDE to invalidating a CURSOR:
HARD PARSE occurs for all those queries who are using the TABLE which has been used by DBA to purge the CURSOR. Consequently all the CURSORS involving the table gets invalidated and your ENTRPRISE GRID shows a SPIKE which represents the HARD PARSING of multiple queries, this usually effects the performance of application and the end users must be ready for this effect which usually lasts for a few minutes. Best time to try this exercise on a production machine is in off peak hours.
Background is very simple. Consider the following example
Table COMPANY has two columns NAME , Value SULMAN appears 100000 times and value TOM appears 10 times.
imagine a user running following following command:
A)select * from COMPANY where NAME = 'TOM'
Second user runs the following command
B)select * from COMPANY where NAME = 'SULMAN'
Second user now starts to complain that performance of query has drastically degraded. REASON: Initially Optimizer chose to use INDEX RANGE SCAN for QUERY A and cached the plan in memory, when user 2 ran QUERY B, an optimal plan would have been a FULL TABLE SCAN since the user is querying more than 90% of total rows of the table , however due to the previous PLAN being cached in memory it is compelled to do a INDEX RANGE SCAN, hence a BAD PERFORMANCE experience.
However, it is NOT ALWAYS necessary that the NEW EXECUTION PLAN is a suitable one, or that the OPTIMIZER engine has infact generated TWO plans in memory for a particluar query.
In such situations the LINE OF ACTION for a DBA varies... Stay updated to look out for the solution to tackle such situations.... :)
#############################################################################################################################
This demonstration assumes that the DBA has already pinpointed the CONTENDING query which is causing the problem.
select executions, invalidations, sql_id, plan_hash_value from v$sql where sql_id = 'cuxp1ra2nkshf';
<><><><><><>
| EXECUTIONS | INVALIDATIONS | SQL_ID | PLAN_HASH_VALUE |
| 0 | 0 | cuxp1ra2nkshf | 3301931525 |
| 3295 | 0 | cuxp1ra2nkshf | 3839839006 |
By looking at both the execution plan after analyzing the dynamics of the underlying tables you determine that the plan against which EXECUTIONS is shown as null is a better plan and should be used instead.
select * from table(dbms_xplan.display_cursor('cuxp1ra2nkshf',null))
-----------------------------------------------------------
SQL_ID cuxp1ra2nkshf, child number 0
-------------------------------------
Plan hash value: 3301931525
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT ORDER BY | | 1 | 694 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 694 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 553 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 532 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| CX_MNP_NPR | 1 | 504 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | CX_MNP_NPR_I6 | 5 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | S_POSTN_M50 | 1 | 28 | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 21 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | S_USER_P1 | 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | CX_MNP_NPR_X | 1 | 141 | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | CX_MNP_NPR_X_U1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
SQL_ID cuxp1ra2nkshf, child number 1
-------------------------------------
Plan hash value: 3839839006
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT ORDER BY | | 1 | 694 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 694 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 553 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 532 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| CX_MNP_NPR | 1 | 504 | 1 (0)| 00:00:01 |
|* 6 | INDEX SKIP SCAN | CX_MNP_NPR_I3 | 1 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | S_POSTN_M50 | 1 | 28 | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 21 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | S_USER_P1 | 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | CX_MNP_NPR_X | 1 | 141 | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | CX_MNP_NPR_X_U1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Purge the relevant cursor through following command;
grant update on table to sulmandba; (name of the table should ideally be the one which is referenced in PREDICATE with bind variables)
Run the following query to ensure that the cursor has been invalidated. (the value INVALIDATION COLUMN would increment)
select executions, invalidations, sql_id, plan_hash_value from v$sql where sql_id = 'cuxp1ra2nkshf';
<><><><><><>
| EXECUTIONS | INVALIDATIONS | SQL_ID | PLAN_HASH_VALUE |
| 0 | 0 | cuxp1ra2nkshf | 3301931525 |
| 3295 | 1 | cuxp1ra2nkshf | 3839839006 |
Now re-execute the query from application process and see if the NEW CURSOR with NEW EXECUTION PLAN has been created.
| EXECUTIONS | INVALIDATIONS | SQL_ID | PLAN_HASH_VALUE |
| 2 | 0 | cuxp1ra2nkshf | 3301931525 |
No comments:
Post a Comment