Tom,

We found some annoying differences when developing some application code. We have an update statement, that when you run an explain for in SQLPLUS or SQL Analyzer, the plan shows differently then from statspack.

We opened an SR on this to see why this would be the case. We didn't want to just flush the shared pool, and would like to understand better the reason why this could occur.

Query in question, although I guess it could be any query...
UPDATE ANTENNA A
SET A.ANTENNA_TECH = :B5
,A.CUST_ORG_SEQ_NUM = :B4
,A.STATUS = :B3
,A.COMMENTS = DECODE(INSTR(NVL(A.COMMENTS,'0'),'Touched by CAD Extraction routine'), 0
, A.COMMENTS || '; Touched by CAD Extraction routine',A.COMMENTS)
,A.MODIFIED_BY = PROCESS_CAD_EXTRACTION_PKG.GET_PERSON_NAME
,A.DATE_LAST_MODIFIED = SYSDATE
,A.ANTENNA_USE_ID = :B2
,A.ANTENNA_LEG = :B1
,A.CAD_RULE_APPLD_IND = :B15
WHERE A.CTRY_ID = :B14
AND A.BUS_UNIT = :B13
AND A.STRUCTURE_ID = :B12
AND A.MOUNT_LEVEL =:B11
AND A.MOUNT_LEVEL_UM = :B10
AND A.ANTENNA_POSITION = :B9
AND A.ORIENTATION = :B8
AND A.AZIMUTH_DEG = :B7
AND A.Z_CENTER = :B6
AND A.STATUS = :B3
AND (NVL(A.ANTENNA_TECH,'0') != NVL(:B5 ,'0')
OR NVL(A.CUST_ORG_SEQ_NUM,'0') != NVL(:B4 ,'0')
OR NVL(A.STATUS,'0') != NVL(:B3 ,'0')
OR NVL(A.ANTENNA_USE_ID,'0') != NVL(:B2 ,'0')
OR NVL(A.ANTENNA_LEG,'0') != NVL(:B1 ,'0'))

In statspack it's doing an FTS on the ANTENNA Table. (1.66 seconds per single row update)
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
--------------- ------------ -------------- ------ -------- ----------
13,528,146 936 14,453.1 1.6 893.86 1557.64

--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|UPDATE STATEMENT |----- 2113437159 ----| | | 1268 |
|UPDATE | | | | |
| TABLE ACCESS FULL |ANTENNA | 1 | 92 | 1268 |
--------------------------------------------------------------------------------


However from other third party tools, it's doing an Index range scan on columns...
BUS_UNIT
Z_CENTER
MOUNT_LEVEL
AZIMUTH_DEG
ANTENNA_POSITION
STRUCTURE_ID
STATUS
ORIENTATION
CTRY_ID
MOUNT_LEVEL_UM

Now, I realize the index does not match up with the update statement columns, and we are planning a better index, however I wanted to see if this was an issue with the third party tools, or potentially the CBO.

Thanks in advance,

ops$tkyte%ORA10GR2> create table t ( x varchar2(10), y varchar2(10) );

Table created.

ops$tkyte%ORA10GR2> insert into t select rownum from all_users;
insert into t select rownum from all_users
            *
ERROR at line 1:
ORA-00947: not enough values


ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable X number
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where x = :x;

Execution Plan
----------------------------------------------------------
Plan hash value: 1303508680

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    14 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    14 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=:X)

ops$tkyte%ORA10GR2> select * from t where x = TO_NUMBER(:x);

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    14 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    14 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")=TO_NUMBER(:X))

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2>