RE: Oracle sql query not returning.

There's no reason not to have unlimited for all. You may be hitting the stack limit. If it's AIX you will need to set the stack_hard to -1. Also, as mentioned below be sure you filesystem can handle a file larger that 2GB.

David Riley
IKON The Way Business Gets Communicated
IKON Data Center
1738 Bass Rd.
Macon, GA 31210
Voice: 478-238-7560
Fax: (912)238-7676
Email; [Email address protected]

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

ulimit -a gave the following results:

time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 1024
vmemory(kbytes) unlimited

looks like file size is unlimited. Then I don't understand why the query is not returning.

-----Original Message-----
From: Frank Dodgers <oracledba-ezmlmshield-x3692036.[Email address protected]
Sent: Jul 7, 2005 10:07 AM
To: LazyDBA Discussion <[Email address protected]
Subject: Re: Oracle sql query not returning.

Depends, but ulimit -a is a place to start. Also, cat /etc/? depending upon
the flavor of Unix.

Frank Dodgers
Evergreen Information Systems
Oracle/Linux/AIX Certified
303-810-6625
----- Original Message -----
From: "Mulpuri family "
<oracledba-ezmlmshield-x79691753.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, July 07, 2005 8:03 AM
Subject: RE: Oracle sql query not returning.

> Oh! no. Yes, I am using unix. How do I find out if there is a file size
> limit.
>
>
> -----Original Message-----
> From: Harish Harbham <Harbh.[Email address protected]
> Sent: Jul 7, 2005 9:44 AM
> To: [Email address protected]
> Subject: RE: Oracle sql query not returning.
>
> **A LazyDBA.com subscriber has responded to your lazydba.com post**
> **LazyDBA.com mail shield has forwarded you this email,
> **and removed any attachments, and kept your email address secret
> **from this person, and any viruses/trojans.
> **If you reply to this email, the person will see your email address as
> normal
> **Anything below this line is the original email text
>
>
> You are probably spooling to a file which has 2G limit. Create your
> spool file on file-systems that support large files ( > 2G) ... ask
> your Unix person ( assuming you are using unix )
>
> Harish
>
> -----Original Message-----
> From: Mulpuri family
> [Email address protected]
> Sent: 07 July 2005 14:26
> 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
>
> **********************************************************
>
> If you are not the intended recipient, employee or agent responsible for
> delivering the message to the intended recipient, you are hereby notified
> that any dissemination or copying of this communication and its
> attachments is strictly prohibited.
>
> If you have received this communication and its attachments in error,
> please return the original message and attachments to the sender using the
> reply facility on e-mail.
>
> Internet communications are not secure and therefore the UCLES Group does
> not accept legal responsibility for the contents of this message. Any
> views or opinions presented are solely those of the author and do not
> necessarily represent those of the UCLES Group unless otherwise
> specifically stated.
>
> The UCLES Group is getting a new look - we are adopting a new name and a
> new identity which expresses what we do more clearly - from July this year
> (2005) we will be trading as Cambridge Assessment.
>
> **********************************************************
>
>
>
>
>
> --------
> 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
>

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

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