Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Unanswered: Query optimization ..

    may someone help me with this ?! ...
    i want to know how can i calculate the cost for processing a query ..
    i mean how can i calculate the number of disc accesses to process the query ..
    in order to optimize the query processing ... thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The [estimated] I/O and CPU load figures can be found in the query explain plan. Use Visual Explain, db2expln or db2exfmt to obtain the plan in readable form.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can do an Explain, which will estimate the cost and number of rows based on the statistics as of the last runstats. You can run the explain via command line, or use the Visual Explain from the Control Center.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Feb 2010
    Posts
    4
    i meant it simple ..
    an example ...my selection will select 400 records out of 1000 record and then select another 200 records out of 1000 records and cartisian product them (Selection 1 × Selection 2).. and every block carries 4 records (blocking factor = 4 records) .. how many hard accesses then ? ..

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    each time will be different due to what is in bufferpool at the time, so there is never anyway to determine how many times you will actually go to disk. The way to optimize your query is to use visual explain and ensure proper index access with matching columns to all of your tables. Also, stay away from cartesian products.
    Dave

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Take a look at the dynamic SQL snapshot:
    Code:
     Rows read                          = 23640
     Internal rows updated              = 0
     Rows written                       = 0
     Statement sorts                    = 3
     Statement sort overflows           = 0
     Total sort time                    = 0
     Buffer pool data logical reads     = 192
     Buffer pool data physical reads    = 1
     Buffer pool temporary data logical reads   = 0
     Buffer pool temporary data physical reads  = 0
     Buffer pool index logical reads    = 1
     Buffer pool index physical reads   = 1
     Buffer pool temporary index logical reads  = 0
     Buffer pool temporary index physical reads = 0
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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