Tuning Issue

[Dagon]

I'd be interested to see if anyone has any suggestions here, because it has got me utterly stumped. I have some SQL, which is as follows:

SELECT distinct 
g01.valn_pol_id, 
g01.index_no, 
g01.lives_covered_cd, 
g01.prc_sgmt_id, 
g01.sex_cd, 
g01.tobac_hab_cd,	  
g01.valn_typ_cd 
from (SELECT 
	 		 bam.prc_sgmt_id, 
	 		 bam.valn_typ_cd, 
     		 bam.valn_pol_id, 
     		 als.sex_cd, 
     		 ins.tobac_hab_cd, 
	 		 bam.lives_covered_cd, 
     		 dense_rank() OVER(PARTITION BY bam.bnft_tid ORDER BY als.als_tid) INDEX_NO 
   		FROM 
 		TBNFT_ADMN_METH_LI_STG bam, 
 		TALS_LI_STG als, 
 		TINSRD_LI_STG ins, 
 		TINSRD_BNFT_XWALK_LI_STG ibx 
WHERE als.ALS_TID = ins.ALS_TID 
AND   ibx.INSRD_TID = ins.INSRD_TID 
AND   ibx.BNFT_TID  = bam.BNFT_TID 
--AND   bam.PRC_SGMT_ID in (select DISTINCT PRC_SGMT_ID FROM TDSCRM_SET_RSLT_LI_STG) 
) g01 
where g01.index_no = 1 
ORDER BY g01.valn_pol_id,g01.index_no

On one system, it runs in a few seconds and on the other it never finishes. I've looked at the query plans on the two systems and they are totally different:

Operation Object Name Rows Bytes Cost

SELECT STATEMENT Optimizer Mode=CHOOSE 31 K 1279
SORT UNIQUE 31 K 1 M 1033
VIEW 31 K 1 M 787
WINDOW SORT PUSHED RANK 31 K 2 M 787
HASH JOIN 31 K 2 M 332
PARTITION LIST ALL
TABLE ACCESS FULL TBNFT_ADMN_METH_LI_STG 23 K 794 K 142
HASH JOIN 31 K 1 M 139
TABLE ACCESS FULL TINSRD_BNFT_XWALK_LI_STG 31 K 610 K 35
HASH JOIN 31 K 1 M 71
TABLE ACCESS FULL TALS_LI_STG 29 K 341 K 28
TABLE ACCESS FULL TINSRD_LI_STG 31 K 824 K 24


on the system where it dies. And:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 31 K 1279
SORT UNIQUE 31 K 1 M 1033
VIEW 31 K 1 M 787
WINDOW SORT PUSHED RANK 31 K 2 M 787
HASH JOIN 31 K 2 M 332
PARTITION LIST ALL 1 3
TABLE ACCESS FULL TBNFT_ADMN_METH_LI_STG 23 K 794 K 142 1 3
HASH JOIN 31 K 1 M 139
TABLE ACCESS FULL TINSRD_BNFT_XWALK_LI_STG 31 K 610 K 35
HASH JOIN 31 K 1 M 71
TABLE ACCESS FULL TALS_LI_STG 29 K 341 K 28
TABLE ACCESS FULL TINSRD_LI_STG 31 K 824 K 24

on the system where it runs fine.

a) all the init.ora parameters match exactly betwen the two systems (including hash_join parameters)
b) versions of Oracle are the same
c) the tables all have identical indexes and virtually the same number of rows
d) I have statted the tables to death on both systems, using both DBMS_STATS and analyze..compute statistics.

Can anyone suggest any other avenues of investigation, because I'm out of ideas.

 

[sem]

I don't see any difference between plans/ Can you mark it with bold?

Regards, Dima

http://seminihin.narod.ru

 

[dbtoo2001]

sqltrace the query on both systems and compare the tkprof outputs.

 

[akex]

maybe could help a look on v$parameter or ask DBAs about disks IOs for each system?

 

[Dagon]

In my efforts to format the trace output to make it look readable, I must have overwritten them both with the same output. The second one should have read:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 389
SORT UNIQUE 1 57 387
VIEW 1 57 386
WINDOW SORT PUSHED RANK 1 107 386
NESTED LOOPS 1 107 384
HASH JOIN 1 80 383
MERGE JOIN CARTESIAN 1 60 247
PARTITION LIST ALL 1 3
TABLE ACCESS FULL TBNFT_ADMN_METH_LI_STG 1 48 142 1 3
BUFFER SORT 118 K 1 M 105
TABLE ACCESS FULL TALS_LI_STG 118 K 1 M 105
TABLE ACCESS FULL TINSRD_BNFT_XWALK_LI_STG 122 K 2 M 135
TABLE ACCESS BY INDEX ROWID TINSRD_LI_STG 1 27 1
INDEX UNIQUE SCAN PK_INSRD_LI_STG 1
Note the merge join cartesian. I eventually got it to run in a few seconds by adding an /*+ORDERED */ hint in the subquery and ordering in the tables in the from clause to the natural join order. I've still not found what the difference between the environments was. It must have beeen teetering on the brink between the sensible plan and the mad plan: a few extra rows in one environment must have pushed it over the edge.

 

[vivekm]

Firstly, check the row count for TALS_LI_STG table, its table definition. If the row count is significantly different, then check when the primary key - PK_INSRD_LI_STG - was it last analyzed. Can you drop it and create it again before analyzing it.

Let us know what you find.

Regards,
Vivek