View previous topic :: View next topic
|
Author |
Message |
murali3955
New User
Joined: 25 Nov 2009 Posts: 11 Location: Chennai
|
|
|
|
Hi,
Can someone tell me the possible reasons for high CPU consumption for an OPEN cursor statement.
Thanks |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
How you came to conclusion that OPEN cursor statement is taking lot of CPU? |
|
Back to top |
|
|
murali3955
New User
Joined: 25 Nov 2009 Posts: 11 Location: Chennai
|
|
|
|
Using EXPLAIN statement i found that the open cursor statement is taking lot of time.
Please let me know the possible reasons for increased CPU.
Thanks. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
Please let me know the possible reasons for increased CPU. |
Poorly designed table(s)/index definitions or query syntax. Very high volume processed. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Quote: |
Using EXPLAIN statement i found that the open cursor statement is taking lot of time.
|
?
The underlying problem is in your cursor definition. You cant fine tune an open statement. |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
If you could post the how cursor is declared. People here might able to help you fine tuning it. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
- An explain doesn't show execution times or cpu consumption. At best it gives a high estimated cost on the DECLARE statement. |
|
Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009 Posts: 365 Location: Denmark
|
|
|
|
GuyC wrote: |
- An explain doesn't show execution times or cpu consumption. At best it gives a high estimated cost on the DECLARE statement. |
A lot of clues can tell you if your cursor takes time to open:No signs of program activity that indicates row processing have started Use of a DB2 activity monitoring tool A query EXPLAIN giving a high estimated cost, would be likely to also take relative long time to execute. But do use EXPLAIN to identify if your query do not use indexes as intended, if an index is not created/not available, or if proper runstats for your tables are not available. |
|
Back to top |
|
|
|