Does your sql file have an exit command? Sometimes they just sit there if you dont have the exit line in the file.

-----Original Message-----
From: Mulpuri family
[mailto:oracledba-ezmlmshield-x87961445.[Email address protected]
Sent: Thursday, July 07, 2005 8:26 AM
To: LazyDBA Discussion
Subject: Oracle sql query not returning.




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



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page