On May 19, 5:42 pm, Ian <ianb...@mobileaudio.comwrote:
shenan...@gmail.com wrote:
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?
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?
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.
