Re: Oracle sql query not returning.

Re: Oracle sql query not returning.

 

  


I have put in a problem ticket to find out about the file size. Let's see what they say.
ThankYou,
Radhika.

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

You will need to dig deeper, as it may be that your file system only
supports 2 gb file size. I suggest you talk to your Unix SA.

Frank Dodgers
Evergreen Information Systems
Oracle/Linux/AIX Certified
303-810-6625
----- Original Message -----
From: "Mulpuri family "
<oracledba-ezmlmshield-x40491090.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, July 07, 2005 8:10 AM
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
>



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