By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

ADO performance problems

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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********@data-tronics.com> wrote in message
news:ac**************************@posting.google.c om...
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

P: n/a
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

P: n/a
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*****@rrsytec.com> wrote in message
news:10*************@corp.supernews.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

P: n/a
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)@sympatico.ca> wrote in message news:<cT*******************@news20.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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.