Hi,
Query has been running for more than 12 hours. It ran for 3 to 4 hours spooled about 2gig data and then the file size does not increase anymore. But, the sqlplus session is inactive but the control never returns back to the program. Running this sql through a shell script. Is there anyother better way I could tune this sql. Any pointers is greatly appreciated.

Thanks,
Radhika.


I have the following query:
SELECT /*+ FIRST_ROWS */
RPAD(B.terr_cd,10) b,
RPAD(A.MO_ID,6) c,
LPAD(A.GLX_SPEC_GRP_ID,10) d,
RPAD(' ',10) b,
LPAD(A.BRND_FM_STRN_ID,10) d,
LPAD(A.RTLRX_TRX_CNT,10) d ,
LPAD(A.RTLRX_TRX_QTY,10) d,
LPAD(A.RTLRX_TRX_DLR_AMT,10) d,
LPAD(A.RTLRX_EQU_TRX_CNT,10) d,
LPAD(A.RTLRX_NRX_CNT,10) d,
LPAD(A.RTLRX_NRX_QTY,10) d,
LPAD(A.RTLRX_NRX_DLR_AMT,10) d,
LPAD(A.RTLRX_EQU_NRX_CNT,10) d
FROM
icmsadm2.ics_retl_rx_algn_mv01 a,
icmsadm2.ics_terr_lkp b,
icmsadm2.ics_base_sell_team_lkp c,
capadm.cap_ftp_loc d,
capadm.cap_ftp_selling_team e

WHERE
A.terr_id=B.terr_id
AND A.BASE_SELL_TEAM_ID = C.BASE_SELL_TEAM_ID
AND C.BASE_SELL_TEAM_CD = E.BASE_SELL_TEAM_CD
AND E.FTP_ID = D.FTP_ID
AND D.DIVISION = 'PHL'



And it's using the following explain plan:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=HINT: FIRST_ROWS 46 M 4840775.45710259
NESTED LOOPS 46 M 2G 4840775.45710259
NESTED LOOPS 46 M 2G 1400420.21119554
NESTED LOOPS 4 64 44.9469050344828
NESTED LOOPS 4 40 19.45834
TABLE ACCESS BY INDEX ROWID CAP_FTP_LOC 1 5 7.8914
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN CAP_FTP_LOC_I1
TABLE ACCESS BY INDEX ROWID CAP_FTP_SELLING_TEAM 34 170 19.45834
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE CAP_FTP_SELLING_TEAM_I1
TABLE ACCESS BY INDEX ROWID ICS_BASE_SELL_TEAM_LKP 87 522 44.9469050344828
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE ICS_BASE_SELL_TEAM_LKP_I1
PARTITION RANGE ITERATOR
TABLE ACCESS BY LOCAL INDEX ROWID ICS_RETL_RX_ALGN_MV01 405 M 14G 1400420.21119554
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN ICS_RETL_RX_ALGN_MV01_I1
TABLE ACCESS BY INDEX ROWID ICS_TERR_LKP 10 K 106 K 4840775.45710259
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE ICS_TERR_LKP_I1


Oracle LazyDBA home page