|
|
|
|
|
|
| Author |
Message |
Civilian_Target
*nix forums beginner
Joined: 26 May 2005
Posts: 3
|
 Posted: Mon Feb 14, 2005 8:22 am Post subject:
Re: How to analyse poor performance of the database
|
|
|
sac wrote:
| Quote: | I am using DB2 v8.1 on UNIX.
At times the database shows extremely poor performance.
I do not have dba/admin rights nor do I have the web based client for
db2 v8.1.
I have only command line access.
Also DBA support starts after our jobs have completed.
(1) Is there any command that I could use from command line to find
out the process taking up maximum of the database resources ?
|
Well, yes. See 2.
| Quote: | (2) Can the database snapshot be used for this purpose ?
|
Yep. You want to take a snapshot and read it. Any blaring performance
problems will generally be evident in the summary, the top 4 screenfuls
of info.
Classic performance problems would be evident from "High water mark for
connections", "Time database waited on locks" and "Deadlocks detected".
If you're getting high numbers for either of the first two, or any of
the third, your web app is probably causing DB2 performance issues.
Sometimes of course, a misconfigured DB2 server is the issue, such as
lack of indexing. For this, you should find the statement in the
"Dynamic SQL Snapshot Result" with the highest Total Execution Times.
Copy the statement text and load it into the DB2 control centre on any
server with a complete copy of the DB you're testing, and run a "Query
Plan" on it. If you can see any TSCANs, or any blaring loss of execution
time on a certain part of the statement, you should be able to either
index it, fix your code so that it never occurs, or if worst comes to
worst, set the table to volatile cardinality.
| Quote: | (3) Is there any log created by DB2 which could be used for finding
the process consuming database resources ?
|
I don't follow you here, (2) may cover it.
Civilian_Target |
|
| Back to top |
|
 |
Thiru
*nix forums addict
Joined: 20 May 2005
Posts: 80
|
| Posted: Mon Feb 14, 2005 5:26 am Post subject:
Re: How to analyse poor performance of the database
|
|
|
Hi,
If you have enough privilege(Sysadmin Authority), monitoring
switches has to be turned on. This can be done by using
update dbm cfg using <Monitoring Switch Name> on IMMEDIATE;
Then snapshot can be taken by using
get snapshot for dynamic sql on <database alias>;
Obtained output can be used for analysis purpose.
Cheers,
Thiru
WantedToBeDBA. |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Sun Feb 13, 2005 2:10 pm Post subject:
Re: How to analyse poor performance of the database
|
|
|
"sac" <schoubal@gmail.com> wrote in message
news:15260680.0502130653.42b9a279@posting.google.com...
| Quote: | I am using DB2 v8.1 on UNIX.
At times the database shows extremely poor performance.
I do not have dba/admin rights nor do I have the web based client for
db2 v8.1.
I have only command line access.
Also DBA support starts after our jobs have completed.
(1) Is there any command that I could use from command line to find
out the process taking up maximum of the database resources ?
(2) Can the database snapshot be used for this purpose ?
(3) Is there any log created by DB2 which could be used for finding
the process consuming database resources ?
I would appreciate any help in this direction.
Thanks in advance !
|
You could turn on the statement monitor and do a snapshot for dynamic
statements. It will tell you the number of times each SQL statement was
executed during the snapshot window, the total execution time, total CPU
time, etc. Then you could take the ones with high average times and do
explains on them.
But you need sysadmin authority to do the snapshots. |
|
| Back to top |
|
|
sac
*nix forums beginner
Joined: 13 Feb 2005
Posts: 6
|
| Posted: Sun Feb 13, 2005 1:53 pm Post subject:
How to analyse poor performance of the database
|
|
|
I am using DB2 v8.1 on UNIX.
At times the database shows extremely poor performance.
I do not have dba/admin rights nor do I have the web based client for
db2 v8.1.
I have only command line access.
Also DBA support starts after our jobs have completed.
(1) Is there any command that I could use from command line to find
out the process taking up maximum of the database resources ?
(2) Can the database snapshot be used for this purpose ?
(3) Is there any log created by DB2 which could be used for finding
the process consuming database resources ?
I would appreciate any help in this direction.
Thanks in advance ! |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Jan 08, 2009 3:04 am | All times are GMT
|
|
Credit Cards UK | NFL Talk Football Forums | Buy WoW Gold | Internet Advertising | Looking for Credit Cards?
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
