Home » SQL & PL/SQL » SQL & PL/SQL » query is taking 1 hour to execute
query is taking 1 hour to execute [message #276134] Wed, 24 October 2007 01:41 Go to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Dear All,
I am creating the view while creating it is created in few minutes but when I am giving

select count(1) from <view_name>

the query is not responding and machine gets hanged.I am sending the query as an attachment. Please reply me what would be the possible causes for the delay.

Ashu
Re: query is taking 1 hour to execute [message #276135 is a reply to message #276134] Wed, 24 October 2007 01:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Ashu,
Needless to say, post this in Performance Forum, post the query, don't attach the documents and post the explain plan.

By
Vamsi
Re: query is taking 1 hour to execute [message #276136 is a reply to message #276134] Wed, 24 October 2007 01:45 Go to previous messageGo to next message
nirajksharmacpr
Messages: 19
Registered: October 2007
Location: Mumbai
Junior Member

Hi
Did u Check The Index Sply on AND A.SOURCESYSTEM = 'CLASSIC'
Checl all Index In this Query

NKS
Re: query is taking 1 hour to execute [message #276137 is a reply to message #276134] Wed, 24 October 2007 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I am creating the view while creating it is created in few minutes

It should be less than a second, creating a view is (about) just inserting a row in the dictionary.

Quote:

select count(1) from <view_name>

count(*), *, *, *, * not 1

We don't download doc file, post a txt one if you want it to be read.

Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel

[Updated on: Wed, 24 October 2007 01:46]

Report message to a moderator

Re: query is taking 1 hour to execute [message #276143 is a reply to message #276137] Wed, 24 October 2007 01:58 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Michel,
Sorry to raise my hand in the middle.
why not count(1)? I habituated to use count(0). I have read somewhere count(0), 1,.... * are same.

Please let me know, why only *?

By
Vamsi

[Updated on: Wed, 24 October 2007 01:59]

Report message to a moderator

Re: query is taking 1 hour to execute [message #276144 is a reply to message #276136] Wed, 24 October 2007 02:02 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Michel,
I also have the doubt why we cannot use count(1)? Please elaborate.
AT
Re: query is taking 1 hour to execute [message #276153 is a reply to message #276144] Wed, 24 October 2007 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just because giving a constant is noise, any constant inside is meaningless, Oracle internally converts it to *, so just use *.
It is clear, it is what Oracle does, it is safe and we don't have to think why there is count(0) in some places in the code and count(1) in others and at the end count(42).

Regards
Michel
Re: query is taking 1 hour to execute [message #276160 is a reply to message #276153] Wed, 24 October 2007 03:15 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Thanks for the explanation, Michel.

By
Vamsi

[Updated on: Wed, 24 October 2007 03:16]

Report message to a moderator

Re: query is taking 1 hour to execute [message #276162 is a reply to message #276160] Wed, 24 October 2007 03:25 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
thanks for the help. our dba is looking into the matter now.
Ashu
Re: query is taking 1 hour to execute [message #276702 is a reply to message #276162] Fri, 26 October 2007 00:57 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi All,
just to update all that same query is giving output from another(Calcutta) location nut not from my place(Pune).

So can you suggest what might be the problem?

Regards,
AT
Re: query is taking 1 hour to execute [message #276715 is a reply to message #276702] Fri, 26 October 2007 01:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
what query?

If a query (any query) gives a result on database A and not on database B, then there is nothing we can say for sure.

This could be:
- database-version related
- database-server-OS related
- data related
- executing user related

etc.
Re: query is taking 1 hour to execute [message #276723 is a reply to message #276702] Fri, 26 October 2007 01:57 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You didn't post the explain plan till now.

By
Vamsi
Previous Topic: how to display the columnname with values from the table (merged)
Next Topic: How to find the number of connections in oracle?
Goto Forum:
  


Current Time: Fri Dec 09 17:36:51 CST 2016

Total time taken to generate the page: 0.26514 seconds