Thanks for the question, Richard .

Asked: July 29, 2003 - 12:21 am UTC

Last updated: December 13, 2005 - 1:21 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom:
I has two tables Serv and cq_change_cust, Serv is partition table the partition p200307 has 1691922 rows and cq_change_cust has 1783 rows.when I excute two statements one use in and other use exists, the explan as follows:

SQL> update serv partition(p200307) a set a.cust_id=(select cust_id from cq_change_cust b where a.se
rv_id=b.serv_id) where serv_id in(select serv_id from cq_change_cust);

updated 1783 rows

used time: 00: 00: 50.05

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=4055 Card=896 Bytes=
13440)

1 0 UPDATE OF 'SERV'
2 1 HASH JOIN (SEMI) (Cost=4055 Card=896 Bytes=13440)
3 2 TABLE ACCESS (FULL) OF 'SERV' (Cost=3139 Card=1691922
Bytes=16919220)

4 2 TABLE ACCESS (FULL) OF 'CQ_CHANGE_CUST' (Cost=2 Card=1
783 Bytes=8915)

5 1 TABLE ACCESS (FULL) OF 'CQ_CHANGE_CUST' (Cost=2 Card=1 B
ytes=10)

SQL> update serv partition(p200307) a set a.cust_id=(select cust_id from cq_change_cust b where a.se
rv_id=b.serv_id) where exists (select 'x' from cq_change_cust c where c.serv_id=a.serv_id );

updated 1783 rows

used time: 00: 00: 54.04

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=4055 Card=896 Bytes=
13440)

1 0 UPDATE OF 'SERV'
2 1 HASH JOIN (SEMI) (Cost=4055 Card=896 Bytes=13440)
3 2 TABLE ACCESS (FULL) OF 'SERV' (Cost=3139 Card=1691922
Bytes=16919220)

4 2 TABLE ACCESS (FULL) OF 'CQ_CHANGE_CUST' (Cost=2 Card=1
783 Bytes=8915)

5 1 TABLE ACCESS (FULL) OF 'CQ_CHANGE_CUST' (Cost=2 Card=1 B
ytes=10)

I think "IN" is different "EXISTS" why these plans is same?
Can you explain why I get this plan ?

and Tom said...

the CBO is smart enough to rewrite queries so that IN/exists NOT IN/not exists and many other constructs that are known to be interchangable in certain situations -- are interchanged by the optimizer itself.

The RBO was never that smart. It would do what it was told to do, period.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

mg, December 12, 2005 - 11:20 am UTC

Hi Tom,

I have created the new db instance from existing prod db. Then I execute same query on both instances.

Problem:
-------
In new instance it shows very high cost for execution plan. But I have checked all the objects, indexes memory components etc. All same on both instances. Could you please help me to find the reason for the problem?

Query:
-----
SELECT t2.f080_datum "c2", t2.f080_fondsnr "c3"
FROM v3copy_v3_fs080_t5_view t2
WHERE t2.f080_fondsnr = 333400
AND t2.f080_bestandstk <> 0;

execution plan on PROD DB
-------------------------
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 35 218
MERGE JOIN CARTESIAN 35 1 K 218
MERGE JOIN CARTESIAN 35 1 K 146
NESTED LOOPS 35 1 K 100
NESTED LOOPS 35 1 K 30
MERGE JOIN CARTESIAN 1 22 18
VIEW SYS.VW_NSO_2 1 9 11
SORT UNIQUE 1 127 11
NESTED LOOPS 1 127 9
NESTED LOOPS 1 123 9
NESTED LOOPS 2 224 7
NESTED LOOPS 1 93 5
NESTED LOOPS 1 78 4
NESTED LOOPS 1 54 3
NESTED LOOPS 1 39 2
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 24 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 24 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
INDEX RANGE SCAN BMO100.UNQ_FONDSGRUPPEN_ORIG 2 38 2
TABLE ACCESS BY INDEX ROWID BMO100.FONDSBEWERTUNGEN_ORIG 1 11 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSBEWERTUNGEN_ORIG 1
INDEX UNIQUE SCAN BMO100.PK_FONDSUMRECHNUNG_NEW 1 4
BUFFER SORT 1 13 18
VIEW SYS.VW_NSO_1 1 13
SORT UNIQUE 1 127 8
NESTED LOOPS 1 127 6
NESTED LOOPS 1 123 6
NESTED LOOPS 1 112 5
NESTED LOOPS 1 93 5
NESTED LOOPS 1 78 4
NESTED LOOPS 1 54 3
NESTED LOOPS 1 39 2
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 24 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 24 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSGRUPPEN_ORIG 1 19
TABLE ACCESS BY INDEX ROWID BMO100.FONDSBEWERTUNGEN_ORIG 1 11 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSBEWERTUNGEN_ORIG 160
INDEX UNIQUE SCAN BMO100.PK_FONDSUMRECHNUNG_NEW 1 4
TABLE ACCESS BY INDEX ROWID BMO100.V3COPY_V3_FS080_T5_ORIG 35 875 12
INDEX RANGE SCAN BMO100.UNQ_V3_FS080_T5 35 3
INDEX RANGE SCAN BMO100.UNQ_V3_FS110_T0 1 11 2
BUFFER SORT 1 144
VIEW 1
SORT AGGREGATE 1 131
NESTED LOOPS 1 131 11
NESTED LOOPS 1 116 10
NESTED LOOPS 1 101 9
NESTED LOOPS 2 154 7
NESTED LOOPS 2 124 5
NESTED LOOPS 2 116 5
NESTED LOOPS 2 94 3
HASH JOIN 2 86 3
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 24 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX FAST FULL SCAN BMO100.UNQ_FONDSGRUPPEN_ORIG 299 5 K 1
INDEX UNIQUE SCAN BMO100.UNQ_BEWERTUNGSFONDS_ORIG 1 4
TABLE ACCESS BY INDEX ROWID BMO100.FONDSBEWERTUNGEN_ORIG 1 11 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSBEWERTUNGEN_ORIG 1
INDEX UNIQUE SCAN BMO100.PK_FONDSUMRECHNUNG_NEW 1 4
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 24 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
BUFFER SORT 1 218
VIEW 1
SORT AGGREGATE 1 131
NESTED LOOPS 1 131 37
NESTED LOOPS 1 127 11
NESTED LOOPS 1 112 10
NESTED LOOPS 1 97 9
NESTED LOOPS 2 146 7
NESTED LOOPS 2 116 5
NESTED LOOPS 2 108 5
HASH JOIN 2 86 3
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 24 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX FAST FULL SCAN BMO100.UNQ_FONDSGRUPPEN_ORIG 299 5 K 1
TABLE ACCESS BY INDEX ROWID BMO100.FONDSBEWERTUNGEN_ORIG 1 11 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSBEWERTUNGEN_ORIG 1
INDEX UNIQUE SCAN BMO100.PK_FONDSUMRECHNUNG_NEW 1 4
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 24 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 15 1
INDEX FULL SCAN BMO100.IDX_BEWERTORG_FONDSNR 1 4 26

execution plan on new insatnce
------------------------------
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 17 K 34441
MERGE JOIN CARTESIAN 17 K 1 M 34441
MERGE JOIN CARTESIAN 17 K 1 M 17237
HASH JOIN 17 K 1 M 59
TABLE ACCESS BY INDEX ROWID BMO100.V3COPY_V3_FS080_T5_ORIG 19 912 3
NESTED LOOPS 38 2 K 32
MERGE JOIN CARTESIAN 2 44 26
VIEW SYS.VW_NSO_2 2 18 11
SORT UNIQUE 2 528 11
NESTED LOOPS 2 528 9
NESTED LOOPS 1 251 9
NESTED LOOPS 2 458 7
NESTED LOOPS 1 189 5
NESTED LOOPS 1 162 4
NESTED LOOPS 1 108 3
NESTED LOOPS 1 81 2
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 54 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 54 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
INDEX RANGE SCAN BMO100.UNQ_FONDSGRUPPEN_ORIG 2 80 2
TABLE ACCESS BY INDEX ROWID BMO100.FONDSBEWERTUNGEN_ORIG 1 22 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSBEWERTUNGEN_ORIG 1
INDEX UNIQUE SCAN BMO100.PK_FONDSUMRECHNUNG_NEW 2 26
BUFFER SORT 1 13 26
VIEW SYS.VW_NSO_1 1 13 8
SORT UNIQUE 1 264 8
NESTED LOOPS 1 264 6
NESTED LOOPS 1 251 6
NESTED LOOPS 1 229 5
NESTED LOOPS 1 189 5
NESTED LOOPS 1 162 4
NESTED LOOPS 1 108 3
NESTED LOOPS 1 81 2
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 54 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 54 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSGRUPPEN_ORIG 1 40
TABLE ACCESS BY INDEX ROWID BMO100.FONDSBEWERTUNGEN_ORIG 1 22 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSBEWERTUNGEN_ORIG 1
INDEX UNIQUE SCAN BMO100.PK_FONDSUMRECHNUNG_NEW 1 13
INDEX RANGE SCAN BMO100.UNQ_V3_FS080_T5 61 2
INDEX FULL SCAN BMO100.UNQ_V3_FS110_T0 45 K 573 K 26
BUFFER SORT 1 17211
VIEW 1
SORT AGGREGATE 1 277
NESTED LOOPS 1 277 13
NESTED LOOPS 1 250 12
NESTED LOOPS 1 223 11
NESTED LOOPS 3 507 8
NESTED LOOPS 3 426 5
NESTED LOOPS 2 258 5
NESTED LOOPS 2 214 3
HASH JOIN 2 188 3
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 54 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
TABLE ACCESS FULL BMO100.FONDSGRUPPEN_ORIG 246 9 K 1
INDEX UNIQUE SCAN BMO100.UNQ_BEWERTUNGSFONDS_ORIG 1 13
TABLE ACCESS BY INDEX ROWID BMO100.FONDSBEWERTUNGEN_ORIG 1 22 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSBEWERTUNGEN_ORIG 1
INDEX UNIQUE SCAN BMO100.PK_FONDSUMRECHNUNG_NEW 2 26
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 54 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
BUFFER SORT 1 34441
VIEW 1
SORT AGGREGATE 1 277
NESTED LOOPS 1 277 39
NESTED LOOPS 1 264 13
NESTED LOOPS 1 237 12
NESTED LOOPS 1 210 11
NESTED LOOPS 3 468 8
NESTED LOOPS 3 387 5
NESTED LOOPS 2 232 5
HASH JOIN 2 188 3
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 54 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
TABLE ACCESS FULL BMO100.FONDSGRUPPEN_ORIG 246 9 K 1
TABLE ACCESS BY INDEX ROWID BMO100.FONDSBEWERTUNGEN_ORIG 1 22 1
INDEX UNIQUE SCAN BMO100.UNQ_FONDSBEWERTUNGEN_ORIG 1
INDEX UNIQUE SCAN BMO100.PK_FONDSUMRECHNUNG_NEW 2 26
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
TABLE ACCESS BY INDEX ROWID BMO100.EINGABEAUFFORDERUNG 1 54 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
INDEX RANGE SCAN BMO100.IDX_EINSTELLUNG 1 27 1
INDEX FULL SCAN BMO100.IDX_BEWERTORG_FONDSNR 1 13 26
thank you very much

December 12, 2005 - 12:35 pm UTC

a) explain what this means exactly:

I have created the new db instance from existing prod db.

b) run a 10053 trace on both systems and see what you see between them (DO NOT POST THE TRACE, you can read it and see if anything pops out). if you don't know what a 10053 trace is, search for 10053 on this site.

mg, December 12, 2005 - 11:58 am UTC

Hi Tom

If it is possible, could you help me quickly. This is really urgent to solve as soon as possible.

thanks a million.

December 12, 2005 - 12:36 pm UTC

umm, i eat lunch and have a day job from time to time like everyone else. do not expect any sort of "I'll get back to you in 5 seconds" sort of responses here - I'll help as I have time.

to mg

Alexander the ok, December 12, 2005 - 1:00 pm UTC

Did you remember to gather fresh statistics when you created the new db? Whenever I have problems like this it's almost always stats related.

December 12, 2005 - 2:14 pm UTC

that is why I want the definition of:

"I have created the new db instance from existing prod db."

how did that happen, backup and restore, export import, dump and load, generate data, whatever....

mg, December 13, 2005 - 5:35 am UTC

Hi

Thank you very much for all your ideas.

(1).I create the empty db structure as existing prod db.
(2).Then import full dump from prod db.
(3).Yes I gathered statistics freshly.
(4).Now I have additional problem. My temp tablespace size is same as prod_db. But when my scripts are running it goes 100% and OEM says it is always 100%.

when I try to execute further queries, I get an error => cannot allocate space on TEMP tablespace.

I have no idea whats going on

Could you please help me?

thanks

December 13, 2005 - 9:37 am UTC

1,2,3 - so you have a completely and totally different database with very very different physical organization of data on disk.

4) that is perfect, that is exactly what you want. temp is a segment, it is full of extents, the segment is created and populated with extents - then the extents are used over and over and over again. temp is supposed to be "100% full", use the v$sort* views to see what is actually being used RIGHT NOW (nothing if you are idle)

but, if you get cannot allocate space, that just means you made it too small for the work you are doing.

mg, December 13, 2005 - 11:20 am UTC

Hi Tom,

Actually TEMP tablespaces are even higher that prod db. The problem is in new db, some indexes are not getting used.

Therefore vast number of records are manipulating.

I have again analyse the tables ncluding indexes. But they are not getting used.

Do you have any idea?

December 13, 2005 - 1:21 pm UTC

compare 10053 traces from prod and test and find out what init.ora parameters that affect the optimizer are radically different.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

to mg

More to Explore

Performance

Why the explan is same with "IN" and "EXISTS"

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

to mg

More to Explore

Performance