Re: Newbie help with explain plans

Roli wrote:

>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
>
>

Post the SQL statement and verify that the constraints and indexes are the same between the two instances.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)