Re: Oracle sql query not returning.

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



Oracle LazyDBA home page