OraFAQ Forum: Performance Tuning » SQL getting timed out.

Home » RDBMS Server » Performance Tuning » SQL getting timed out.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL getting timed out. [message #301863]

Fri, 22 February 2008 01:19

senthildurga
Messages: 9
Registered: December 2006

Junior Member

Hi,

When I tried to run the following SQL,it getting timed out.

SELECT A.BUSINESS_UNIT,
A.JOURNAL_ID,
TO_CHAR(A.JOURNAL_DATE, 'YYYY-MM-DD'),
A.UNPOST_SEQ,
A.FISCAL_YEAR,
A.ACCOUNTING_PERIOD,
A.JRNL_HDR_STATUS,
C.INVOICE,
C.ACCT_ENTRY_TYPE,
C.LINE_DST_SEQ_NUM,
C.TAX_AUTHORITY_CD,
C.ACCOUNT,
SUM(C.MONETARY_AMOUNT),
D.BILL_SOURCE_ID,
D.IDENTIFIER,
SUM(D.VAT_AMT_BSE),
SUM(D.VAT_TRANS_AMT_BSE),
D.VAT_TXN_TYPE_CD,
D.TAX_CD_VAT,
D.TAX_CD_VAT_PCT,
D.VAT_APPLICABILITY,
E.BILL_TO_CUST_ID,
E.BILL_STATUS,
E.BILL_CYCLE_ID,
TO_CHAR(E.INVOICE_DT, 'YYYY-MM-DD'),
TO_CHAR(E.ACCOUNTING_DT, 'YYYY-MM-DD'),
TO_CHAR(E.DT_INVOICED, 'YYYY-MM-DD'),
E.ENTRY_TYPE,
E.ENTRY_REASON,
E.AR_LVL,
E.AR_DST_OPT,
E.AR_ENTRY_CREATED,
E.GEN_AR_ITEM_FLG,
E.GL_LVL,
E.GL_ENTRY_CREATED,
(Case
when C.ACCOUNT in
('30120000', '30180050', '30190000', '30290000', '30490000',
'30690000', '30900040', '30990000', '35100000', '35120000',
'35150000', '35160000', '39100050', '90100000') and
D.TAX_CD_VAT_PCT <> 0 then
'Ej_Momskonto_med_moms'
When C.ACCOUNT not in
('30120000', '30180050', '30190000', '30290000', '30490000',
'30690000', '30900040', '30990000', '35100000', '35120000',
'35150000', '35160000', '39100050', '90100000') and
D.TAX_CD_VAT_PCT <> 25 then
'Momskonto_utan_moms'
end)
FROM SYSADM.PS_JRNL_HEADER A,
SYSADM.PS_JRNL_LN B,
SYSADM.PS_BI_ACCT_ENTRY C,
SYSADM.PS_BI_LINE D,
SYSADM.PS_BI_HDR E
WHERE A.BUSINESS_UNIT = 'SE001'
AND A.JOURNAL_DATE BETWEEN TO_DATE('2007-01-01','YYYY-MM-DD')
AND TO_DATE('2007-01-31','YYYY-MM-DD')
AND A.SOURCE = 'BI'
AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.JOURNAL_ID = B.JOURNAL_ID
AND A.JOURNAL_DATE = B.JOURNAL_DATE
AND A.UNPOST_SEQ = B.UNPOST_SEQ
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.JOURNAL_ID = C.JOURNAL_ID
AND B.JOURNAL_DATE = C.JOURNAL_DATE
AND B.JOURNAL_LINE = C.JOURNAL_LINE
AND C.ACCT_ENTRY_TYPE = 'RR'
AND C.BUSINESS_UNIT = A.BUSINESS_UNIT
AND C.INVOICE = D.INVOICE
AND C.LINE_SEQ_NUM = D.LINE_SEQ_NUM
AND C.ACCOUNTING_DT > TO_DATE('2006-12-31','YYYY-MM-DD')
AND D.BUSINESS_UNIT = E.BUSINESS_UNIT
AND D.INVOICE = E.INVOICE
AND E.BUSINESS_UNIT = A.BUSINESS_UNIT
AND
((C.ACCOUNT IN
('30120000', '30180050', '30190000', '30290000', '30490000',
'30690000', '30900040', '30990000', '35100000', '35120000',
'35150000', '35160000', '39100050', '90100000') AND
D.TAX_CD_VAT_PCT <> 0) OR
(C.ACCOUNT NOT IN
('30120000', '30180050', '30190000', '30290000', '30490000',
'30690000', '30900040', '35120000', '35150000', '35160000',
'39100050', 'Z', '35100000', '30990000') AND
D.TAX_CD_VAT_PCT <> 25))
GROUP BY A.BUSINESS_UNIT,
A.JOURNAL_ID,
TO_CHAR(A.JOURNAL_DATE, 'YYYY-MM-DD'),
A.UNPOST_SEQ,
A.FISCAL_YEAR,
A.ACCOUNTING_PERIOD,
A.JRNL_HDR_STATUS,
C.INVOICE,
C.ACCT_ENTRY_TYPE,
C.LINE_DST_SEQ_NUM,
C.TAX_AUTHORITY_CD,
C.ACCOUNT,
D.BILL_SOURCE_ID,
D.IDENTIFIER,
D.VAT_TXN_TYPE_CD,
D.TAX_CD_VAT,
D.TAX_CD_VAT_PCT,
D.VAT_APPLICABILITY,
E.BILL_TO_CUST_ID,
E.BILL_STATUS,
E.BILL_CYCLE_ID,
TO_CHAR(E.INVOICE_DT, 'YYYY-MM-DD'),
TO_CHAR(E.ACCOUNTING_DT, 'YYYY-MM-DD'),
TO_CHAR(E.DT_INVOICED, 'YYYY-MM-DD'),
E.ENTRY_TYPE,
E.ENTRY_REASON,
E.AR_LVL,
E.AR_DST_OPT,
E.AR_ENTRY_CREATED,
E.GEN_AR_ITEM_FLG,
E.GL_LVL,
E.GL_ENTRY_CREATED,
(Case
when C.ACCOUNT in
('30120000', '30180050', '30190000', '30290000', '30490000',
'30690000', '30900040', '30990000', '35100000', '35120000',
'35150000', '35160000', '39100050', '90100000') and
D.TAX_CD_VAT_PCT <> 0 then
'Ej_Momskonto_med_moms'
When C.ACCOUNT not in
('30120000', '30180050', '30190000', '30290000', '30490000',
'30690000', '30900040', '30990000', '35100000', '35120000',
'35150000', '35160000', '39100050', '90100000') and
D.TAX_CD_VAT_PCT <> 25 then
'Momskonto_utan_moms'
end)

Please help me on this issue.

Report message to a moderator

Re: SQL getting timed out. [message #301865 is a reply to message #301863]

Fri, 22 February 2008 01:22

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

A query does not time-out unless you have a remote access.
Or maybe your application times out the query?

In this case, you have to post all relevant information: index, statistics, execution plan...

Regards
Michel

Report message to a moderator

Previous Topic:	Pivoting or Cross Tab Report
Next Topic:	sql tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 19:40:43 CDT 2024