SQL compiler and buffer pool

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shenanwei@gmail.com - 18 May 2007 21:02 GMT

I have a database DB2 v8.1.1.112 on AIX 5.2
With the following BP.
select BUFFERPOOLID, NPAGES,PAGESIZE from syscat.bufferpools

BPNAME NPAGES PAGESIZE
------------ ----------- -----------
IBMDEFAULTBP 512 4096
BP_4K 444 4096
BP04K01 1044 4096

The user tbs is on BufferPool 3
System tbs is on BufferPool 1

The result from db2batch
Prepare Time is: 0.000 seconds
Execute Time is: 0.043 seconds
Fetch Time is: 0.864 seconds
Elapsed Time is: 0.908 seconds

If the BufferPool 2 is altered by
ALTER BUFFERPOOL BP_4K IMMEDIATE SIZE 444

Prepare Time is: 90.207 seconds
Execute Time is: 0.048 seconds
Fetch Time is: 1.652 seconds
Elapsed Time is: 91.907 seconds

What is the sql compiler doing with the unused BufferPool?

Reply to this Message

Mark A - 19 May 2007 02:15 GMT

On May 18, 4:02 pm, shenan...@gmail.com wrote:

> I have a database DB2 v8.1.1.112 on AIX 5.2
> With the following BP.
[quoted text clipped - 24 lines]
>
> What is the sql compiler doing with the unused BufferPool?

Those allocations are pitifully small. Just for starters, set each
bufferpool to 10,000 pages and restart instance.

Reply to this Message

Ian - 19 May 2007 22:42 GMT

> I have a database DB2 v8.1.1.112 on AIX 5.2
> With the following BP.
[quoted text clipped - 24 lines]
>
> What is the sql compiler doing with the unused BufferPool?

The key is seeing that prepare time originally was 0.000 seconds.
Which almost certainly means you had a package cache hit.

When you alter the bufferpool, DB2 invalidates any cached access
plans in the package cache, so the next prepare will require that
the access plan be compiled again.

If you run db2batch again after the 90 second prepare time, do you
see the time decrease to near zero again?

Reply to this Message

shenanwei@gmail.com - 22 May 2007 20:28 GMT

> shenan...@gmail.com wrote:
> > I have a database DB2 v8.1.1.112 on AIX 5.2
[quoted text clipped - 35 lines]
> If you run db2batch again after the 90 second prepare time, do you
> see the time decrease to near zero again?

Thanks Ian and Mark.
The second db2batch run do see the package cache.
The default BP size if not a problem here, the testing database is
only 40MB.
The query here is 9 table join, it will take sometime for DB2 to
compile.

The first db2batch run when BP_4K = 444
Prepare Time is: 10.465 seconds
Execute Time is: 0.050 seconds
Fetch Time is: 0.611 seconds
Elapsed Time is: 11.126 seconds

The first db2batch run when BP_4K = 443
Prepare Time is: 0.060 seconds
Execute Time is: 0.040 seconds
Fetch Time is: 0.661 seconds
Elapsed Time is: 0.761 seconds

Why a smaller unused BP has such a big implact on Compile time?
When the package cache is full, DB2 has to recompile. It is still a
big performance issue take such a long time on compile.

Reply to this Message