Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Optimizing temporary tablespace

    Hi,

    I have a global temp table that I'm using in a stored procedure.. the execution time of the procedure seems to be REALLY slow and I want to experiment a little to figure out if I can make it faster.. here's what I have for the temp tablespace --

    CREATE USER TEMPORARY TABLESPACE USERTMP_TBLSP IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
    USING ('/path1/path2')
    EXTENTSIZE 32
    PREFETCHSIZE 32
    BUFFERPOOL IBMDEFAULTBP
    OVERHEAD 12.670000
    TRANSFERRATE 0.180000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY OFF;

    Here are the parameters for another regular tablespace --

    CREATE REGULAR TABLESPACE MAIN_TBLSP IN DATABASE PARTITION GROUP MAIN_GROUP PAGESIZE 8192 MANAGED BY SYSTEM
    USING ('/path1/path2')
    EXTENTSIZE 32
    PREFETCHSIZE 96
    BUFFERPOOL MAIN_BP
    OVERHEAD 24.100000
    TRANSFERRATE 0.900000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY ON;


    Is there anything I should change in the USERTMP_TBLSP parameters that would help me improve performance ? Thanks!!!

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    what's the size of IBMDEFAULTBP?

    You should turn off FILE SYSTEM CACHING, if the Bufferpool has a sufficient size or is set to automatic.

    Different values for OVERHEAD and TRANSFERRATE for the tablespaces does not make sense, if you use the same disks. The default values are changed from Version to Version since the Performace of disks changes too.

    Tuning of Extentsize and Prefetchsize requires some knowledge about the disklayout.

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    thanks for the reply! If I give the command db2 "select * from syscat.bufferpools", I get this ---

    IBMDEFAULTBP
    NPAGES = 1000
    PAGESIZE = 4096

    Also, am really not sure how or why the values of OVERHEAD and TRANSFERRATE were set to the current numbers.


    Won't file system caching set to ON make data retrieval faster if the procedure has already been called before?


    On a side note, if you're getting the monthly data sum value for an entity... basically getting the sum of values from 8929 records,how long do you think that should take.. my query takes this long --

    real 0m8.696s
    user 0m0.000s
    sys 0m0.012s
    Last edited by db2user24; 10-12-10 at 12:58.

  4. #4
    Join Date
    Jan 2010
    Posts
    335
    Which Version of DB2 are you using and what OS?

    The IBMDEFAULTBP is set to the default size. Currently the BP has a size of 4 MB.
    According to your System, the amount of free and the Size of your UserTemp you should increase the size of the Bufferpool.

    If you have activated the Filesystem Caching then you are caching in the Database (bufferpools) and in the Filesystem. This is not very useful.
    Only Tablespaces for Large Objects (BLOBs, CLOBs etc) should use Filesystem Caching, because DB2 is not caching the IOs for LOBs.

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    Thanks, am using DB2 version 8.2 UDB on a Linux Server. Here are the partition groups, bufferpools and tablespaces that have been setup --


    CREATE DATABASE PARTITION GROUP "NGROUP" ON DBPARTITIONNUMS
    (0);


    CREATE DATABASE PARTITION GROUP "R_GROUP" ON DBPARTITIONNUMS
    (0);


    CREATE DATABASE PARTITION GROUP "P_GROUP" ON DBPARTITIONNUMS
    (0);


    CREATE DATABASE PARTITION GROUP "REGROUP" ON DBPARTITIONNUMS
    (0);


    ------------------------------------
    -- DDL Statements for BUFFERPOOLS --
    ------------------------------------

    CREATE BUFFERPOOL "BP" DATABASE PARTITION GROUP "NGROUP" SIZE 10000 PAGESIZE 8192 NOT EXTENDED STORAGE;

    CREATE BUFFERPOOL "R_BP" DATABASE PARTITION GROUP "R_GROUP" SIZE 10000 PAGESIZE 8192 NOT EXTENDED STORAGE;

    CREATE BUFFERPOOL "TEMPSP2_BP" DATABASE PARTITION GROUP "IBMTEMPGROUP" SIZE 2000 PAGESIZE 8192 NOT EXTENDED STORAGE;

    CREATE BUFFERPOOL "P_BP" DATABASE PARTITION GROUP "P_GROUP" SIZE 2000 PAGESIZE 8192 NOT EXTENDED STORAGE;

    CREATE BUFFERPOOL "C_BP" DATABASE PARTITION GROUP "P_GROUP" SIZE 2000 PAGESIZE 8192 NOT EXTENDED STORAGE;

    CREATE BUFFERPOOL "RE_BP" DATABASE PARTITION GROUP "REGROUP" SIZE 3000 PAGESIZE 8192 NOT EXTENDED STORAGE;

    ------------------------------------
    -- DDL Statements for TABLESPACES --
    ------------------------------------

    CREATE REGULAR TABLESPACE MAIN_TBLSP IN DATABASE PARTITION GROUP NGROUP PAGESIZE 8192 MANAGED BY SYSTEM
    USING ('/path1/path2')
    EXTENTSIZE 32
    PREFETCHSIZE 96
    BUFFERPOOL BP
    OVERHEAD 24.100000
    TRANSFERRATE 0.900000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY ON;

    CREATE REGULAR TABLESPACE R_TBLSP IN DATABASE PARTITION GROUP R_GROUP PAGESIZE 8192 MANAGED BY SYSTEM
    USING ('/path/...')
    EXTENTSIZE 32
    PREFETCHSIZE 192
    BUFFERPOOL R_BP
    OVERHEAD 24.100000
    TRANSFERRATE 0.900000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY ON;

    CREATE TEMPORARY TABLESPACE TEMPSPACE2 IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 8192 MANAGED BY SYSTEM
    USING ('/path...')
    EXTENTSIZE 32
    PREFETCHSIZE 192
    BUFFERPOOL TEMPSP2_BP
    OVERHEAD 24.100000
    TRANSFERRATE 0.900000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY OFF;

    CREATE REGULAR TABLESPACE P_TBLSP IN DATABASE PARTITION GROUP P_GROUP PAGESIZE 8192 MANAGED BY SYSTEM
    USING ('/path...')
    EXTENTSIZE 32
    PREFETCHSIZE 192
    BUFFERPOOL P_BP
    OVERHEAD 24.100000
    TRANSFERRATE 0.900000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY ON;


    CREATE REGULAR TABLESPACE C_TBLSP IN DATABASE PARTITION GROUP P_GROUP PAGESIZE 8192 MANAGED BY SYSTEM
    USING ('/path...')
    EXTENTSIZE 32
    PREFETCHSIZE 192
    BUFFERPOOL C_BP
    OVERHEAD 24.100000
    TRANSFERRATE 0.900000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY ON;

    CREATE REGULAR TABLESPACE R_TBLSP IN DATABASE PARTITION GROUP REGROUP PAGESIZE 8192 MANAGED BY SYSTEM
    USING ('/path....')
    EXTENTSIZE 32
    PREFETCHSIZE 96
    BUFFERPOOL RE_BP
    OVERHEAD 12.670000
    TRANSFERRATE 0.180000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY ON;

    CREATE REGULAR TABLESPACE SYSTOOLSPACE IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 4096 MANAGED BY SYSTEM
    USING ('/home/dn2inst1/db2inst1/NODE0000/SQL00001/SYSTOOLSPACE')
    EXTENTSIZE 32
    PREFETCHSIZE 32
    BUFFERPOOL IBMDEFAULTBP
    OVERHEAD 12.670000
    TRANSFERRATE 0.180000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY ON;

    CREATE USER TEMPORARY TABLESPACE USERTMP_TBLSP IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
    USING ('/path...')
    EXTENTSIZE 32
    PREFETCHSIZE 32
    BUFFERPOOL IBMDEFAULTBP
    OVERHEAD 12.670000
    TRANSFERRATE 0.180000
    FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY OFF;

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    So should I increase the size of IBMDEFAULTBP? or just associate USERTMP_TBLSP with a brand new partition group / bufferpool that's bigger in size?

    Like this?


    CREATE DATABASE PARTITION GROUP "USERGROUP" ON DBPARTITIONNUMS
    (0);


    CREATE BUFFERPOOL "USERTMP_BP" DATABASE PARTITION GROUP "USERGROUP" SIZE 2000 PAGESIZE 8192 NOT EXTENDED STORAGE;


    CREATE USER TEMPORARY TABLESPACE USERTMP_TBLSP IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY SYSTEM
    USING ('/path...')
    EXTENTSIZE 32
    PREFETCHSIZE 32
    BUFFERPOOL USERTMP_BP
    OVERHEAD 12.670000
    TRANSFERRATE 0.180000
    NO FILE SYSTEM CACHING
    DROPPED TABLE RECOVERY OFF;



    Thanks for the help!
    Last edited by db2user24; 10-12-10 at 13:48.

  7. #7
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by nvk@vhv View Post
    Which Version of DB2 are you using and what OS?

    If you have activated the Filesystem Caching then you are caching in the Database (bufferpools) and in the Filesystem. This is not very useful.
    Only Tablespaces for Large Objects (BLOBs, CLOBs etc) should use Filesystem Caching, because DB2 is not caching the IOs for LOBs.


    Just thought I'd add that I changed it to 'NO FILE SYSTEM CACHING' and it didn't seem to help the processing time

  8. #8
    Join Date
    Jan 2010
    Posts
    335
    Huh,

    that's a lot of Questions ...

    I don't know your System. Why do you have so many Partition Groups? Is there a reason for it?
    All your Bufferpools are pretty small. The larger ones have a size of 80 MB. Smaller ones have 16 MB. IBMDEFAULTBP has 4 MB.

    Could you give us some Information?

    > db2pd -osinfo
    > db2 get snapshot for bufferpools on <DATABASE>

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    thanks.. am really not sure why there were so many partition groups setup to begin with..

    here is what I get from the above commands :

    db2pd -osinfo

    Operating System Information:

    OSName: Linux
    NodeName: <systemname>
    Version: 2
    Release: 6
    Machine: x86_64

    CPU Information:
    TotalCPU OnlineCPU ConfigCPU Speed(MHz) HMTDegree Cores/Socket
    4 4 4 2393 1 1

    Physical Memory and Swap (Megabytes):
    TotalMem FreeMem AvailMem TotalSwap FreeSwap
    7937 25 n/a 7633 7577

    Virtual Memory (Megabytes):
    Total Reserved Available Free
    15570 n/a n/a 7602

    Message Queue Information:
    MsgSeg MsgMax MsgMap MsgMni MsgTql MsgMnb MsgSsz
    n/a 8192 16384 1024 16384 16384 16

    Shared Memory Information:
    ShmMax ShmMin ShmIds ShmSeg
    1073741824 1 4096 4096

    Semaphore Information:
    SemMap SemMni SemMns SemMnu SemMsl SemOpm SemUme SemUsz SemVmx SemAem
    32000 1024 32000 32000 250 32 n/a 20 32767 32767

    CPU Load Information:
    Short Medium Long
    0.070000 0.050000 0.010000




    db2 get snapshot for bufferpools on dbname

    Bufferpool Snapshot

    Bufferpool name = IBMDEFAULTBP
    Database name = db2name
    Database path = /path/
    Input database alias = db2name
    Snapshot timestamp = 10/13/2010 13:58:35.153332

    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool data writes = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Total buffer pool read time (milliseconds) = Not Collected
    Total buffer pool write time (milliseconds)= Not Collected
    Asynchronous pool data page reads = Not Collected
    Asynchronous pool data page writes = Not Collected
    Buffer pool index writes = Not Collected
    Asynchronous pool index page reads = Not Collected
    Asynchronous pool index page writes = Not Collected
    Total elapsed asynchronous read time = Not Collected
    Total elapsed asynchronous write time = Not Collected
    Asynchronous data read requests = Not Collected
    Asynchronous index read requests = Not Collected
    No victim buffers available = Not Collected
    Direct reads = Not Collected
    Direct writes = Not Collected
    Direct read requests = Not Collected
    Direct write requests = Not Collected
    Direct reads elapsed time (ms) = Not Collected
    Direct write elapsed time (ms) = Not Collected
    Database files closed = Not Collected
    Data pages copied to extended storage = Not Collected
    Index pages copied to extended storage = Not Collected
    Data pages copied from extended storage = Not Collected
    Index pages copied from extended storage = Not Collected
    Unread prefetch pages = Not Collected
    Vectored IOs = Not Collected
    Pages from vectored IOs = Not Collected
    Block IOs = Not Collected
    Pages from block IOs = Not Collected
    Physical page maps = Not Collected

    Node number = 0
    Tablespaces using bufferpool = 5
    Alter bufferpool information:
    Pages left to remove = 0
    Current size = 1000
    Post-alter size = 1000

  10. #10
    Join Date
    Nov 2007
    Posts
    265
    Bufferpool Snapshot

    Bufferpool name = BP
    Database name = dbname
    Database path = /path
    Input database alias = dbname
    Snapshot timestamp = 10/13/2010 13:58:35.153332

    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool data writes = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Total buffer pool read time (milliseconds) = Not Collected
    Total buffer pool write time (milliseconds)= Not Collected
    Asynchronous pool data page reads = Not Collected
    Asynchronous pool data page writes = Not Collected
    Buffer pool index writes = Not Collected
    Asynchronous pool index page reads = Not Collected
    Asynchronous pool index page writes = Not Collected
    Total elapsed asynchronous read time = Not Collected
    Total elapsed asynchronous write time = Not Collected
    Asynchronous data read requests = Not Collected
    Asynchronous index read requests = Not Collected
    No victim buffers available = Not Collected
    Direct reads = Not Collected
    Direct writes = Not Collected
    Direct read requests = Not Collected
    Direct write requests = Not Collected
    Direct reads elapsed time (ms) = Not Collected
    Direct write elapsed time (ms) = Not Collected
    Database files closed = Not Collected
    Data pages copied to extended storage = Not Collected
    Index pages copied to extended storage = Not Collected
    Data pages copied from extended storage = Not Collected
    Index pages copied from extended storage = Not Collected
    Unread prefetch pages = Not Collected
    Vectored IOs = Not Collected
    Pages from vectored IOs = Not Collected
    Block IOs = Not Collected
    Pages from block IOs = Not Collected
    Physical page maps = Not Collected

    Node number = 0
    Tablespaces using bufferpool = 1
    Alter bufferpool information:
    Pages left to remove = 0
    Current size = 10000
    Post-alter size = 10000


    Bufferpool Snapshot

    Bufferpool name = R_BP
    Database name = dbname
    Database path = /path
    Input database alias = dbname
    Snapshot timestamp = 10/13/2010 13:58:35.153332

    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool data writes = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Total buffer pool read time (milliseconds) = Not Collected
    Total buffer pool write time (milliseconds)= Not Collected
    Asynchronous pool data page reads = Not Collected
    Asynchronous pool data page writes = Not Collected
    Buffer pool index writes = Not Collected
    Asynchronous pool index page reads = Not Collected
    Asynchronous pool index page writes = Not Collected
    Total elapsed asynchronous read time = Not Collected
    Total elapsed asynchronous write time = Not Collected
    Asynchronous data read requests = Not Collected
    Asynchronous index read requests = Not Collected
    No victim buffers available = Not Collected
    Direct reads = Not Collected
    Direct writes = Not Collected
    Direct read requests = Not Collected
    Direct write requests = Not Collected
    Direct reads elapsed time (ms) = Not Collected
    Direct write elapsed time (ms) = Not Collected
    Database files closed = Not Collected
    Data pages copied to extended storage = Not Collected
    Index pages copied to extended storage = Not Collected
    Data pages copied from extended storage = Not Collected
    Index pages copied from extended storage = Not Collected
    Unread prefetch pages = Not Collected
    Vectored IOs = Not Collected
    Pages from vectored IOs = Not Collected
    Block IOs = Not Collected
    Pages from block IOs = Not Collected
    Physical page maps = Not Collected

    Node number = 0
    Tablespaces using bufferpool = 1
    Alter bufferpool information:
    Pages left to remove = 0
    Current size = 10000
    Post-alter size = 10000


    Bufferpool Snapshot

    Bufferpool name = TEMPSP2_BP
    Database name = dbname
    Database path = /path
    Input database alias = dbname
    Snapshot timestamp = 10/13/2010 13:58:35.153332

    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool data writes = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Total buffer pool read time (milliseconds) = Not Collected
    Total buffer pool write time (milliseconds)= Not Collected
    Asynchronous pool data page reads = Not Collected
    Asynchronous pool data page writes = Not Collected
    Buffer pool index writes = Not Collected
    Asynchronous pool index page reads = Not Collected
    Asynchronous pool index page writes = Not Collected
    Total elapsed asynchronous read time = Not Collected
    Total elapsed asynchronous write time = Not Collected
    Asynchronous data read requests = Not Collected
    Asynchronous index read requests = Not Collected
    No victim buffers available = Not Collected
    Direct reads = Not Collected
    Direct writes = Not Collected
    Direct read requests = Not Collected
    Direct write requests = Not Collected
    Direct reads elapsed time (ms) = Not Collected
    Direct write elapsed time (ms) = Not Collected
    Database files closed = Not Collected
    Data pages copied to extended storage = Not Collected
    Index pages copied to extended storage = Not Collected
    Data pages copied from extended storage = Not Collected
    Index pages copied from extended storage = Not Collected
    Unread prefetch pages = Not Collected
    Vectored IOs = Not Collected
    Pages from vectored IOs = Not Collected
    Block IOs = Not Collected
    Pages from block IOs = Not Collected
    Physical page maps = Not Collected

    Node number = 0
    Tablespaces using bufferpool = 1
    Alter bufferpool information:
    Pages left to remove = 0
    Current size = 2000
    Post-alter size = 2000

  11. #11
    Join Date
    Nov 2007
    Posts
    265
    Bufferpool Snapshot

    Bufferpool name = P_BP
    Database name = dbname
    Database path = /path
    Input database alias = dbname
    Snapshot timestamp = 10/13/2010 13:58:35.153332

    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool data writes = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Total buffer pool read time (milliseconds) = Not Collected
    Total buffer pool write time (milliseconds)= Not Collected
    Asynchronous pool data page reads = Not Collected
    Asynchronous pool data page writes = Not Collected
    Buffer pool index writes = Not Collected
    Asynchronous pool index page reads = Not Collected
    Asynchronous pool index page writes = Not Collected
    Total elapsed asynchronous read time = Not Collected
    Total elapsed asynchronous write time = Not Collected
    Asynchronous data read requests = Not Collected
    Asynchronous index read requests = Not Collected
    No victim buffers available = Not Collected
    Direct reads = Not Collected
    Direct writes = Not Collected
    Direct read requests = Not Collected
    Direct write requests = Not Collected
    Direct reads elapsed time (ms) = Not Collected
    Direct write elapsed time (ms) = Not Collected
    Database files closed = Not Collected
    Data pages copied to extended storage = Not Collected
    Index pages copied to extended storage = Not Collected
    Data pages copied from extended storage = Not Collected
    Index pages copied from extended storage = Not Collected
    Unread prefetch pages = Not Collected
    Vectored IOs = Not Collected
    Pages from vectored IOs = Not Collected
    Block IOs = Not Collected
    Pages from block IOs = Not Collected
    Physical page maps = Not Collected

    Node number = 0
    Tablespaces using bufferpool = 1
    Alter bufferpool information:
    Pages left to remove = 0
    Current size = 2000
    Post-alter size = 2000


    Bufferpool Snapshot

    Bufferpool name = C_BP
    Database name = dbname
    Database path = /path
    Input database alias = dbname
    Snapshot timestamp = 10/13/2010 13:58:35.153332

    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool data writes = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Total buffer pool read time (milliseconds) = Not Collected
    Total buffer pool write time (milliseconds)= Not Collected
    Asynchronous pool data page reads = Not Collected
    Asynchronous pool data page writes = Not Collected
    Buffer pool index writes = Not Collected
    Asynchronous pool index page reads = Not Collected
    Asynchronous pool index page writes = Not Collected
    Total elapsed asynchronous read time = Not Collected
    Total elapsed asynchronous write time = Not Collected
    Asynchronous data read requests = Not Collected
    Asynchronous index read requests = Not Collected
    No victim buffers available = Not Collected
    Direct reads = Not Collected
    Direct writes = Not Collected
    Direct read requests = Not Collected
    Direct write requests = Not Collected
    Direct reads elapsed time (ms) = Not Collected
    Direct write elapsed time (ms) = Not Collected
    Database files closed = Not Collected
    Data pages copied to extended storage = Not Collected
    Index pages copied to extended storage = Not Collected
    Data pages copied from extended storage = Not Collected
    Index pages copied from extended storage = Not Collected
    Unread prefetch pages = Not Collected
    Vectored IOs = Not Collected
    Pages from vectored IOs = Not Collected
    Block IOs = Not Collected
    Pages from block IOs = Not Collected
    Physical page maps = Not Collected

    Node number = 0
    Tablespaces using bufferpool = 1
    Alter bufferpool information:
    Pages left to remove = 0
    Current size = 2000
    Post-alter size = 2000


    Bufferpool Snapshot

    Bufferpool name = RE_BP
    Database name = dbname
    Database path = /path
    Input database alias = dbname
    Snapshot timestamp = 10/13/2010 13:58:35.153332

    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool data writes = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Total buffer pool read time (milliseconds) = Not Collected
    Total buffer pool write time (milliseconds)= Not Collected
    Asynchronous pool data page reads = Not Collected
    Asynchronous pool data page writes = Not Collected
    Buffer pool index writes = Not Collected
    Asynchronous pool index page reads = Not Collected
    Asynchronous pool index page writes = Not Collected
    Total elapsed asynchronous read time = Not Collected
    Total elapsed asynchronous write time = Not Collected
    Asynchronous data read requests = Not Collected
    Asynchronous index read requests = Not Collected
    No victim buffers available = Not Collected
    Direct reads = Not Collected
    Direct writes = Not Collected
    Direct read requests = Not Collected
    Direct write requests = Not Collected
    Direct reads elapsed time (ms) = Not Collected
    Direct write elapsed time (ms) = Not Collected
    Database files closed = Not Collected
    Data pages copied to extended storage = Not Collected
    Index pages copied to extended storage = Not Collected
    Data pages copied from extended storage = Not Collected
    Index pages copied from extended storage = Not Collected
    Unread prefetch pages = Not Collected
    Vectored IOs = Not Collected
    Pages from vectored IOs = Not Collected
    Block IOs = Not Collected
    Pages from block IOs = Not Collected
    Physical page maps = Not Collected

    Node number = 0
    Tablespaces using bufferpool = 1
    Alter bufferpool information:
    Pages left to remove = 0
    Current size = 3000
    Post-alter size = 3000

  12. #12
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    lots and lots of "Not Collected". You need to turn on the Monitor Switch for Bufferpools.
    For your Session:
    update monitor switches using bufferpool on global

    Permanently:
    update dbm cfg using DFT_MON_BUFPOOL on

    Turn on the switches and let the System run for a period of time, e.g. when you run the procedure.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •