I don't want to say this is a bug in oracle but it really confused me :
Oracle7.3.3 database has 2 users which have same objects . Both of the users are analyzed and cost-based optimizer will be choose in that case .
User1 has 9GB tablespace and 3 GB is free and User2 has 4 GB tablespace and 0.5GB is free . I run the one query on User1 , it took 2 seconds .
But same query running on User2 ,
it's hanging for more than 2 hours and still not finished .
The query looks like :
ltrim(rtrim(a.V_SELLER_CODE)) V_SELLER_CODE ,
ltrim(rtrim(a.V_BUYER_CODE)) V_BUYER_CODE ,
ltrim(rtrim(a.V_PART_CODE)) V_PART_CODE ,
ltrim(rtrim(A.V_LOT_CODE )) V_LOT_CODE ,
a.N_AICO_FLAG , a.V_LOT_CODE, a.D_VANNING_DATE,
a.N_SERIAL_NUMBER , a.N_ORDER_LOT_SIZE, a.V_EXTERNAL_COLOR_CD,
a.V_INTERNAL_COLOR_CD, a.N_INVOICE_QTY, a.N_SHIPMENT_QTY,
a.V_CAR_FAMILY_CODE , a.V_SHIP_SCHED,
a.USERCRE, a.HODTCRE , a.USERCHG ,
a.HODTCHG, a.V_DISP_MODEL_CODE, a.N_SEQ_NUMBER
,a.V_RE_EXPORT_CODE ,a.V_SS_NO -- ,a.N_PAMS_LOT_SIZE -- ,a.V_DAY_BYDAY_QTY -- ,a.NOTENO -- ,a.V_ROWID -- ,b.D_VANNING_MONTH -- FROM TT_ORDER_DET_BY_PART a, TT_ORDER_HEADER b
WHERE a.V_VALID_FLAG = 'Y'
AND NVL(a.V_POSTED_FLAG,'N') = 'N'
AND a.n_seq_number = b.n_seq_number
AND b.v_valid_flag = 'Y'
FOR UPDATE OF a.V_POSTED_FLAG NOWAIT;
Table b has around 50 records and table a has 800 records (table a is child table of table b)
If I remove the for update of cause and rerun it in user2 , it's taking only 2 second ( I'm sure it's not because of locking , I checked no any other user connection)
If I delete the analyze statics of table a and b in user2 , it's running also very fast only 2 seconds .
I can not understand why , the number of records in these 2 tables are so less . And full table scan should be very fast .
I think it's a bug in oracle7.3.3 optimizer . Do U think So ?
Click Here to Expand Forum to Full Width