Tag Archives: execution plan

TABLE ACCESS BY INDEX ROWID BATCHED

TABLE ACCESS BY INDEX ROWID BATCHED is new execution plan operation that helps improve performance.  It is generally used for range ( > or < ) queries.  For this new operation, Oracle selects few  ROWIDs from the index and then try to access the rows in blocks. This significantly reduces the number of times Oracle must access the blocks thereby improving performance.

Sample Execution plan

Execution Plan
———————————————————-
Plan hash value: 525114061

—————————————————————————————————————————-
| Id  | Operation                                                   | Name     | Rows   | Bytes   | Cost (%CPU)| Time
—————————————————————————————————————————–
|   0 | SELECT STATEMENT                                   |                 |    10  |  3020  |     3   (0)| 00:00:01
|*  1 |  COUNT STOPKEY                                      |                 |          |           |              |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | EMP          | 28012 |  8261K|     3   (0)| 00:00:01
|*  3 |    INDEX RANGE SCAN                                | EMP_SAL_I |          |           |     2   (0)| 00:00:01
—————————————————————————————————————————–

Advertisements

Want to know when there is change in query execution plan ?

Want to  get alerted whenever there is  change  in execution plan?  Try the below query.

COLUMN SQL_TEXT FORMAT A100
SET LINESIZE 150 PAGESIZE 2000
SET LONG 300000

SELECT DISTINCT A.SQL_ID ,A.PLAN_HASH_VALUE , B.SQL_TEXT FROM V$SQL_PLAN A, V$SQL B
WHERE A.SQL_ID IN (
SELECT SQL_ID  FROM V$SQL_PLAN
WHERE OBJECT_OWNER=’SCOTT
AND TIMESTAMP>SYSDATE-1
GROUP BY SQL_ID
HAVING COUNT (DISTINCT PLAN_HASH_VALUE) >1)
AND A.SQL_ID=B.SQL_ID

You can choose to run the query every hour or  once every day depending on the sensitivity of your applications to plan changes. It is good to change the TIMESTAMP”  accordingly  in the query. You can also ORDER BY SQL_ID if required.