OraFAQ Forum: SQL & PL/SQL » Problem in SQL report

Home » SQL & PL/SQL » SQL & PL/SQL » Problem in SQL report (Oracle 9i R2)

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

Problem in SQL report [message #603642]

Wed, 18 December 2013 03:00

devesh5683
Messages: 3
Registered: September 2008
Location: Pune

Junior Member

I have a perl script where I select data by joining 5 tables(2 different sql) and then do some manipulations to the data that was selected and in the last dump the data in csv files. Although the script is running fine and gets successfully executed in 2 hours but once or twice in a month the perl script run for longer duration(4 to 6 hours) and there is no data in output file.
Our DBA is saying this is not a database issue and is the script problem but I think otherwise. What should be my approach to solve the issue, any suggestions ? please help.
Can there be some issues related to locks in the database tables as the issue occurs once or twice in a month?

Report message to a moderator

Re: Problem in SQL report [message #603643 is a reply to message #603642]

Wed, 18 December 2013 03:03

cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester

Senior Member

if you're just selecting data then locks aren't the problem.
2 hrs to get data from 5 tables and dump to csv seems like a very long time.
How many csvs are you generating, how many rows go in each and how many rows does the query pull from the DB?

Report message to a moderator

Re: Problem in SQL report [message #603645 is a reply to message #603643]

Wed, 18 December 2013 03:24

devesh5683
Messages: 3
Registered: September 2008
Location: Pune

Junior Member

Thanks cookiemonster for your reply.
Yes there are only select statements in the script, In the script 13 different csv files are created by selecting and manipulating data. Considering the size of the tables and the select statements, 2 hours time for the script is fine. There is maximum 200 or less rows in each file.

[Updated on: Wed, 18 December 2013 03:26]

Report message to a moderator

Re: Problem in SQL report [message #603648 is a reply to message #603645]

Wed, 18 December 2013 03:29

cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester

Senior Member

2hrs is fine to generate 13 csvs with 200 rows or less?
I'd be worried if that took more than 10 minutes or half decent hardware.
I think your DBA is probably right - your script is doing something horribly inefficient.
Most likely you're doing calculations in perl that could be done far more efficiently in sql.
But without details of what the script is doing it's impossible to say for sure.

Report message to a moderator

Re: Problem in SQL report [message #603649 is a reply to message #603648]

Wed, 18 December 2013 03:32

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You can't tell, cookiemonster. A query could aggregate a lot of data only to produce a few rows of summarized data. On the other hand you're right: without details we are flying blind.

Report message to a moderator

Re: Problem in SQL report [message #603652 is a reply to message #603649]

Wed, 18 December 2013 03:58

devesh5683
Messages: 3
Registered: September 2008
Location: Pune

Junior Member

To summarize the perl script:
1. Open 13 csv files
2. Populate each file with header records.
3. Based on some input parameters fetch some rows and put the data in hash.
4. Based on some if conditions put the data in each file. close the files and exit.

To summarize the problem script runs fine 28 out of 30 times, but once or twice a month the problem I mentioned occurs. I would like to know if there is some possibility of any database related issue.

Report message to a moderator

Re: Problem in SQL report [message #603655 is a reply to message #603652]

Wed, 18 December 2013 04:05

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

If it's any database related issue, then the database should throw an error.

Are you checking for errors in the perl script and do you log them? Like in this example here, look at all the places where "errstr" is mentioned.

Report message to a moderator

Re: Problem in SQL report [message #603656 is a reply to message #603652]

Wed, 18 December 2013 04:06

cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester

Senior Member

How many rows are in the tables?
How many rows do you fetch from the tables?

Report message to a moderator

Previous Topic:	Select query given ORA-01722 error message
Next Topic:	LAG & LEAD Question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 09:13:46 CDT 2024