Hi,
Even if you have statistics and don't have necessary indexes Full table
scan will heppen. And statistics don't make oracle to get row by rowid
as we see in Fast Explain Plan.
Thanks
Jim Kennedy wrote:
> "Roli" <Roli.Meier_at_gmx.ch> wrote in message
> news:fe53b0b7.0309030602.352fac0e_at_posting.google.com...
>
>>Hi there,
>>
>>I have a problem with a huge select statement which i run against a
>>Oracle 9i 9.2.0.3 DB.
>>
>>We have one instance in house with the same data and db version as on
>>client side. If I run the statement in house, the statement is rather
>>fast, if i run it at client side it is very slow.
>>
>>Enclosed i send the explain plans. Could possibly someone tell where I
>>could tune the oracle instance at client side....
>>
>>Thanks in advance.
>>
>>****************Slow Explain Plan
>>*****************************************************
>>
>>
>>Operation Object Name Rows Bytes Cost
>>
>>SELECT STATEMENT Hint=CHOOSE 593 1217
>> HASH JOIN SEMI 593 70 K 1217
>> MERGE JOIN 593 66 K 1206
>> MERGE JOIN 1 K 101 K 1121
>> SORT JOIN 20 K 589 K 305
>> TABLE ACCESS FULL OS_A 20 K 589 K 35
>> FILTER
>> SORT JOIN
>> TABLE ACCESS FULL OS_P 21 K 1 M 51
>> SORT JOIN 12 K 154 K 85
>> INDEX FAST FULL SCAN OA_PK 12 K 154 K 15
>> VIEW VW_NSO_1 497 2 K 2
>> CONNECT BY WITH FILTERING
>> NESTED LOOPS
>> INDEX UNIQUE SCAN O_PK 1 5 1
>> TABLE ACCESS BY USER ROWID OS_O
>> HASH JOIN
>> CONNECT BY PUMP
>> TABLE ACCESS FULL OS_O 497 3 K 2
>>
>>
>>
>>***********************Fast Explain
>>Plan*************************************
>>
>>Operation Object Name Rows Bytes Cost
>>SELECT STATEMENT Hint=CHOOSE 6 477
>> TABLE ACCESS BY INDEX ROWID OS_P 20 K 1 M 2
>> NESTED LOOPS 6 678 477
>> NESTED LOOPS 156 6 K 165
>> NESTED LOOPS 156 2 K 9
>> VIEW VW_NSO_1 5 30 2
>> SORT UNIQUE 5 40
>> CONNECT BY WITH FILTERING
>> NESTED LOOPS
>> INDEX UNIQUE SCAN OS_PK 1 5 1
>> TABLE ACCESS BY USER ROWID OS_O
>> NESTED LOOPS
>> BUFFER SORT 5 40
>> CONNECT BY PUMP
>> TABLE ACCESS BY INDEX ROWID 5 40 2
>> INDEX RANGE SCAN RO_FK 5 1
>> INDEX RANGE SCAN OS_A_PK 31 403 1
>> TABLE ACCESS BY INDEX ROWID OS_A 1 26 1
>> INDEX UNIQUE SCAN A_PK 1
>> INDEX RANGE SCAN R_A_FK 1 1
>
>
> Looks like one is analyzed and the other is not. Are your tables and
> indexes analyzed in one database and not the other? Are you using different
> optimizations?(first rows vs all rows)
> Jim
>
>
Received on Wed Sep 03 2003 - 10:04:30 CDT
