473,574 Members | 3,033 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ADO performance problems

Hello,

I am trying to isolate some performance issues.

The database is DB2 v8.1 running on a Win2000 server with 4 processors and
1gb of RAM.

The application is a GIS application that uses ADO to access the database.
It was not written specifically for DB2 and I think almost all queries are
running as dynamic queries. The performance issue arise when users are
updating data. There may be up to 8 users on the system. After running for a
couple of hours, some users (not all) will experience a really long wait
time (~15 minutes!) before insert/updates/deletes are posted. I assumed this
was some sort of locking issue but looking at the snapshot data I can not
see the problem. However, I am new to DB2 and we do not have an experienced
DBA available so I am sure I am missing something. The database manager and
database parameters are set at the defaults except for:

Audit_Buf_Sz = 1000
Avg_Appls = 15
ApplHeapSz = 20480

CLI settings have autocommit turned off.
Cursors are server side.

These setting changes were uneducated guesses at fixing the problem.

The snapshot is below. Any advice would be greatly appreciated.

Database Snapshot

Database name = UMSII

Database path = C:\DB2\NODE0000 \SQL00002\

Input database alias =

Database status = Active

Catalog database partition number = 0

Catalog network node name =

Operating system running at database server= NT

Location of the database = Local

First database connect timestamp = 01-19-2004 07:08:52.132012

Last reset timestamp =

Last backup timestamp =

Snapshot timestamp = 01-19-2004 09:35:36.998705

High water mark for connections = 15

Application connects = 34

Secondary connects total = 0

Applications connected currently = 15

Appls. executing in db manager currently = 0

Agents associated with applications = 15

Maximum agents associated with applications= 15

Maximum coordinating agents = 15

Locks held currently = 39

Lock waits = 0

Time database waited on locks (ms) = 0

Lock list memory in use (Bytes) = 14000

Deadlocks detected = 0

Lock escalations = 0

Exclusive lock escalations = 0

Agents currently waiting on locks = 0

Lock Timeouts = 0

Number of indoubt transactions = 0

Total Private Sort heap allocated = 0

Total Shared Sort heap allocated = 0

Shared Sort heap high water mark = 0

Total sorts = 301

Total sort time (ms) = 0

Sort overflows = 0

Active sorts = 0

Buffer pool data logical reads = 12569

Buffer pool data physical reads = 7483

Buffer pool temporary data logical reads = 12479

Buffer pool temporary data physical reads = 0

Asynchronous pool data page reads = 7247

Buffer pool data writes = 52

Asynchronous pool data page writes = 45

Buffer pool index logical reads = 9386

Buffer pool index physical reads = 320

Buffer pool temporary index logical reads = 0

Buffer pool temporary index physical reads = 0

Asynchronous pool index page reads = 0

Buffer pool index writes = 37

Asynchronous pool index page writes = 36

Total buffer pool read time (ms) = 116

Total buffer pool write time (ms) = 116

Total elapsed asynchronous read time = 48

Total elapsed asynchronous write time = 116

Asynchronous data read requests = 907

Asynchronous index read requests = 0

No victim buffers available = 412

LSN Gap cleaner triggers = 0

Dirty page steal cleaner triggers = 16

Dirty page threshold cleaner triggers = 0

Time waited for prefetch (ms) = 1

Unread prefetch pages = 0

Direct reads = 1444

Direct writes = 0

Direct read requests = 641

Direct write requests = 0

Direct reads elapsed time (ms) = 38

Direct write elapsed time (ms) = 0

Database files closed = 0

Data pages copied to extended storage = 0

Index pages copied to extended storage = 0

Data pages copied from extended storage = 0

Index pages copied from extended storage = 0

Host execution elapsed time = 0.000956

Commit statements attempted = 588

Rollback statements attempted = 13

Dynamic statements attempted = 14591

Static statements attempted = 639

Failed statement operations = 2

Select SQL statements executed = 974

Update/Insert/Delete statements executed = 251

DDL statements executed = 0

Internal automatic rebinds = 0

Internal rows deleted = 0

Internal rows inserted = 0

Internal rows updated = 0

Internal commits = 34

Internal rollbacks = 0

Internal rollbacks due to deadlock = 0

Rows deleted = 63

Rows inserted = 140

Rows updated = 13

Rows selected = 31268

Rows read = 334580

Binds/precompiles attempted = 0

Log space available to the database (Bytes)= 203938034

Log space used by the database (Bytes) = 61966

Maximum secondary log space used (Bytes) = 0

Maximum total log space used (Bytes) = 62271

Secondary logs allocated currently = 0

Log pages read = 0

Log pages written = 97

Appl id holding the oldest transaction = 236

Package cache lookups = 1294

Package cache inserts = 462

Package cache overflows = 0

Package cache high water mark (Bytes) = 1593696

Application section lookups = 14629

Application section inserts = 617

Catalog cache lookups = 1332

Catalog cache inserts = 51

Catalog cache overflows = 0

Catalog cache high water mark = 0

Workspace Information

Shared high water mark = 0

Corresponding shared overflows = 0

Total shared section inserts = 0

Total shared section lookups = 0

Private high water mark = 261630

Corresponding private overflows = 0

Total private section inserts = 617

Total private section lookups = 1050

Number of hash joins = 1

Number of hash loops = 0

Number of hash join overflows = 0

Number of small hash join overflows = 0

Nov 12 '05 #1
6 3289
You might want to check your LOCKTIMEOUT and DLCHKTIME settings in
your db config. Mine are set to 30 and 10000 respectively.

If your apps are truely waiting on some sort of locking, this will
stop it...

Craig Wahlmeier
Nov 12 '05 #2
Thanks Craig,

I have set the LOCKTIMEOUT to 60 seconds since I posted but I still see the
problem. DLCHKTIME has not changed. Looking at the snapshots I did not see
anything that indicated a locking problem so I am not sure what is causing
the slow performance yet. If I figure it out I will update this post.

Thanks again,

George McLean

"Craig Wahlmeier" <cw********@dat a-tronics.com> wrote in message
news:ac******** *************** ***@posting.goo gle.com...
You might want to check your LOCKTIMEOUT and DLCHKTIME settings in
your db config. Mine are set to 30 and 10000 respectively.

If your apps are truely waiting on some sort of locking, this will
stop it...

Craig Wahlmeier

Nov 12 '05 #3
I'd get db2diag.log to see if there's anything unusual like a crahs/dump +
recovery or something.
(diaglevel=4)

Huge sort? / tempspace contention?
log/temp/db files all on same drive?

Try to see if there are any DDL or utilities running when the problem
occurs.
(snapshot or event monitor or list history command, ...)
DDL statements executed =0 though

Any application hung on a binding step? (list applications show detail)
I see none in your snapshot but List applications could show something
interesting.

As a side note, you could check your BP(s) size to see if it's big enough.
See also if you can increase the io_cleaners.
re: Dirty page steal cleaner triggers
PM
Nov 12 '05 #4
We're seeing similar effects (sqlcode -911 = timeout) that we still haven't
tracked down sufficiently in order to open a PMR.

Unfortunately, DB2 has limited support for tracking down locking problems,
the lock list trace has not been of much help.

If you can get on-site support I suggest you open a PMR.

"George McLean" <gm*****@rrsyte c.com> wrote in message
news:10******** *****@corp.supe rnews.com...
Hello,

I am trying to isolate some performance issues.

The database is DB2 v8.1 running on a Win2000 server with 4 processors and
1gb of RAM.

The application is a GIS application that uses ADO to access the database.
It was not written specifically for DB2 and I think almost all queries are
running as dynamic queries. The performance issue arise when users are
updating data. There may be up to 8 users on the system. After running for a couple of hours, some users (not all) will experience a really long wait
time (~15 minutes!) before insert/updates/deletes are posted. I assumed this was some sort of locking issue but looking at the snapshot data I can not
see the problem. However, I am new to DB2 and we do not have an experienced DBA available so I am sure I am missing something. The database manager and database parameters are set at the defaults except for:

Audit_Buf_Sz = 1000
Avg_Appls = 15
ApplHeapSz = 20480

CLI settings have autocommit turned off.
Cursors are server side.

These setting changes were uneducated guesses at fixing the problem.

The snapshot is below. Any advice would be greatly appreciated.

Database Snapshot

Database name = UMSII

Database path = C:\DB2\NODE0000 \SQL00002\

Input database alias =

Database status = Active

Catalog database partition number = 0

Catalog network node name =

Operating system running at database server= NT

Location of the database = Local

First database connect timestamp = 01-19-2004 07:08:52.132012

Last reset timestamp =

Last backup timestamp =

Snapshot timestamp = 01-19-2004 09:35:36.998705

High water mark for connections = 15

Application connects = 34

Secondary connects total = 0

Applications connected currently = 15

Appls. executing in db manager currently = 0

Agents associated with applications = 15

Maximum agents associated with applications= 15

Maximum coordinating agents = 15

Locks held currently = 39

Lock waits = 0

Time database waited on locks (ms) = 0

Lock list memory in use (Bytes) = 14000

Deadlocks detected = 0

Lock escalations = 0

Exclusive lock escalations = 0

Agents currently waiting on locks = 0

Lock Timeouts = 0

Number of indoubt transactions = 0

Total Private Sort heap allocated = 0

Total Shared Sort heap allocated = 0

Shared Sort heap high water mark = 0

Total sorts = 301

Total sort time (ms) = 0

Sort overflows = 0

Active sorts = 0

Buffer pool data logical reads = 12569

Buffer pool data physical reads = 7483

Buffer pool temporary data logical reads = 12479

Buffer pool temporary data physical reads = 0

Asynchronous pool data page reads = 7247

Buffer pool data writes = 52

Asynchronous pool data page writes = 45

Buffer pool index logical reads = 9386

Buffer pool index physical reads = 320

Buffer pool temporary index logical reads = 0

Buffer pool temporary index physical reads = 0

Asynchronous pool index page reads = 0

Buffer pool index writes = 37

Asynchronous pool index page writes = 36

Total buffer pool read time (ms) = 116

Total buffer pool write time (ms) = 116

Total elapsed asynchronous read time = 48

Total elapsed asynchronous write time = 116

Asynchronous data read requests = 907

Asynchronous index read requests = 0

No victim buffers available = 412

LSN Gap cleaner triggers = 0

Dirty page steal cleaner triggers = 16

Dirty page threshold cleaner triggers = 0

Time waited for prefetch (ms) = 1

Unread prefetch pages = 0

Direct reads = 1444

Direct writes = 0

Direct read requests = 641

Direct write requests = 0

Direct reads elapsed time (ms) = 38

Direct write elapsed time (ms) = 0

Database files closed = 0

Data pages copied to extended storage = 0

Index pages copied to extended storage = 0

Data pages copied from extended storage = 0

Index pages copied from extended storage = 0

Host execution elapsed time = 0.000956

Commit statements attempted = 588

Rollback statements attempted = 13

Dynamic statements attempted = 14591

Static statements attempted = 639

Failed statement operations = 2

Select SQL statements executed = 974

Update/Insert/Delete statements executed = 251

DDL statements executed = 0

Internal automatic rebinds = 0

Internal rows deleted = 0

Internal rows inserted = 0

Internal rows updated = 0

Internal commits = 34

Internal rollbacks = 0

Internal rollbacks due to deadlock = 0

Rows deleted = 63

Rows inserted = 140

Rows updated = 13

Rows selected = 31268

Rows read = 334580

Binds/precompiles attempted = 0

Log space available to the database (Bytes)= 203938034

Log space used by the database (Bytes) = 61966

Maximum secondary log space used (Bytes) = 0

Maximum total log space used (Bytes) = 62271

Secondary logs allocated currently = 0

Log pages read = 0

Log pages written = 97

Appl id holding the oldest transaction = 236

Package cache lookups = 1294

Package cache inserts = 462

Package cache overflows = 0

Package cache high water mark (Bytes) = 1593696

Application section lookups = 14629

Application section inserts = 617

Catalog cache lookups = 1332

Catalog cache inserts = 51

Catalog cache overflows = 0

Catalog cache high water mark = 0

Workspace Information

Shared high water mark = 0

Corresponding shared overflows = 0

Total shared section inserts = 0

Total shared section lookups = 0

Private high water mark = 261630

Corresponding private overflows = 0

Total private section inserts = 617

Total private section lookups = 1050

Number of hash joins = 1

Number of hash loops = 0

Number of hash join overflows = 0

Number of small hash join overflows = 0


Nov 12 '05 #5
Hi,
we experience quite similar performance problems. In our cases we
could track down to an IO-problem, which could be solved by using a
write-buffered Raid-Controller.
The reason, was that DB2 wait for each block written to the
transaction-log for an acknowledge of the IO-Controller. So DB2 always
waited for a physical write to the disk. (you can track this by
looking at the timestamps on the log-files)

Could this be the problem ??

Joerg
"PM \(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympati co.ca> wrote in message news:<cT******* ************@ne ws20.bellglobal .com>...
I'd get db2diag.log to see if there's anything unusual like a crahs/dump +
recovery or something.
(diaglevel=4)

Huge sort? / tempspace contention?
log/temp/db files all on same drive?

Try to see if there are any DDL or utilities running when the problem
occurs.
(snapshot or event monitor or list history command, ...)
DDL statements executed =0 though

Any application hung on a binding step? (list applications show detail)
I see none in your snapshot but List applications could show something
interesting.

As a side note, you could check your BP(s) size to see if it's big enough.
See also if you can increase the io_cleaners.
re: Dirty page steal cleaner triggers
PM

Nov 12 '05 #6
Now, i just wonder if it could be the database activation cost that you
see...

Do you Activate the database before anyone connects?

PM
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1386
by: Eric S. Johansson | last post by:
more questions about performance measurements in python. I solve the problems I had with shelf by not using it. I'm doing a direct pickle to and from file of an entire dictionary. That knocked off a good third of my CPU time. there was another big hit that that was quick and easy. I went from approximately 3.5 seconds down to .5 seconds CPU...
6
6758
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
12
8332
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I will work on preparing SQL statements that will create the tables, insert sample record and run the SP. I would hope people will look at my SP and...
5
471
by: John Bailo | last post by:
I wrote a webservice to output a report file. The fields of the report are formatted based on information in an in-memory XmlDocument. As each row of a SqlDataReader are looped through, a lookup is done, and format information retrieved. The performance was extremely poor -- producing about 1000 rows per minute. However, when I used...
5
3991
by: Scott | last post by:
I have a customer that had developed an Access97 application to track their business information. The application grew significantly and they used the Upsizing Wizard to move the tables to SQL 2000. Of course there were no modifications made to the queries and they noticed significant performance issues. They recently upgraded the...
24
2762
by: Bob Alston | last post by:
Most of my Access database implementations have been fairly small in terms of data volume and number of concurrent users. So far I haven't had performance issues to worry about. <knock on wood> But I am curious about what techniques those of you who have done higher volume access implementations use to ensure high performance of the...
4
3353
by: Martin | last post by:
I am using graphics as backgrounds for forms,buttons,labels etc. The question is: is it faster to load all graphics from files on app start or to use it embeded (places in editor during design). Reason for my question is that application has 5mb, while without graphics it has cca 400kb. Graphic files (bmps) take about 200kb (in program, they...
5
1893
by: Markus Ernst | last post by:
Hello A class that composes the output of shop-related data gets some info from the main shop class. Now I wonder whether it is faster to store the info in the output class or get it from the main class whenever it is needed: class shop_main { var $prices = null; function &get_prices() {
2
1262
by: Chris | last post by:
We have an asp application some of which will be being ported over to asp.net so this may be a little OT. It is having some bad performance problems and we need to isolate which areas are causing the problems. The main performance problems are database related i.e. timeouts and deadlocks Some questions: Microsoft's ACT. How is it better...
0
1131
by: Chris | last post by:
We have an asp application some of which will be being ported over to asp.net so this may be a little OT. It is having some bad performance problems and we need to isolate which areas are causing the problems. The main performance problems are database related i.e. timeouts and deadlocks Some questions: Microsoft's ACT. How is it better...
0
7841
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8100
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8270
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8137
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5651
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5335
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3773
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2270
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1369
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.