1) COST*RATE: This is I got after having explain on my cursor which comes close to 58K, how does this value getting calculated is it based on installation parameters?
After restucturing the query, the COST*RATE has got drooped to 1.8K only but there is no reduction seen on MIPS/CPU
Does explain really results really meant to save MIPS/CPU?
2)How is MIPS measured? like if I have a cursor which has below query
Code:
select col1,col2
from table1
where
col3=5
UNION
select col1,col2
from table1
where
col4=5
and the same query can be written like,
Code:
select col1,col2
from table1
where
col3=5 OR
col4=5
3) Now for the above two different queries there are less I/O's for the second query as because of no UNION, so does less I/O means less MIPS?
can someone explain this I/O what exactly they mean if we reduce the I/O's does it always reduce the MIPS?
4)all the data in DB2 is residing over LDS, so when we issue a SELECT query then as per me it pulls out the data from LDS(Physical location) and place it in the buffer pool (logical location) and then the subsequent fetch hapeens only from the buffer pools, Right?
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
1. Cost allocation for a z/OS machine is a complex area, and EXTREMELY dependent upon site specifications. So you need to ask your questions to your site support group.
2. MIPS is a term that has been obsolete for 10 years or more; IBM replaced "MIPS" with "MSU" (millions of service units) that far back.
3. MIPS cannot be measured for anything less than an LPAR. To state that so-and-so did such-and-such which reduced the MIPS for a job indicates that the person making the statement has NO CLUE as to what MIPS ever were, nor how to measure machine performance. MIPS cannot be used in reference to a DB2 task, nor application program, nor system task -- only the entire LPAR was ever measured with MIPS.
4. If COST*RATE is a DB2 term, you need to consult DB2 documentation; if not, it is a term in use at your site and you would need to talk to someone working at your site about what it means.
5. CPU time is divided into SRB (system request blocks) and TCB (task control blocks); SRB is system overhead (basically) and TCB is application CPU (basically). Reducing I/O (presumably reducing EXCP counts is what is meant) will reduce the SRB but have little or no impact on TCB.
6. Your question in your point 4 cannot be answered by anyone unless they work at your site. Modern disk drives tend to cache data, and z/OS can use hiperspace (among other conveniences) so while the broad concept you stated might be right, the specifics could vary -- A LOT -- for your site.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Just to add - usually COST = Usage * RATE. I have never seen a calculation that uses COST * RATE. If you post the specifics of where you see this it may help someone help you.
Quote:
4)all the data in DB2 is residing over LDS, so when we issue a SELECT query then as per me it pulls out the data from LDS(Physical location) and place it in the buffer pool (logical location) and then the subsequent fetch hapeens only from the buffer pools, Right?
It is a bit more complex than you understand. What is your goal here [rather than what you have posted for 4) ]? Why do you care where the fetch is serviced? Possibly (probably<g>) there is something i misunderstand.
when we run explain on any queries through BMC tool then results of it comes in terms of COST * RATE... I can give you the screen shots tomorrow.
As far as point 4 goes , being a developer I really do not care how it is done ( you are right..) but to enhance something from my knowledge just going couple of steps more down into the situation to know what exactly happens after any SQL query is issued from anywhere (SPUFI,Application program..e.t.c.).
Robert,
Quote:
3. MIPS cannot be measured for anything less than an LPAR. To state that so-and-so did such-and-such which reduced the MIPS for a job indicates that the person making the statement has NO CLUE as to what MIPS ever were, nor how to measure machine performance. MIPS cannot be used in reference to a DB2 task, nor application program, nor system task -- only the entire LPAR was ever measured with MIPS.
I am bit not clear here, in my shop I can see the stats of every Sp,batch program,job e.t.c. and their MIPS/CPU/avg elapsed time usage/day separately... may be customized for my shop, but I am sure this is widely used way to measure the performance of any component.
Quote:
5. CPU time is divided into SRB (system request blocks) and TCB (task control blocks); SRB is system overhead (basically) and TCB is application CPU (basically). Reducing I/O (presumably reducing EXCP counts is what is meant) will reduce the SRB but have little or no impact on TCB.
Okay, so can we here say whenever we try to reduce the I/O's we always get success in usage of CPU reduction? also when we say here I/O's what exactly it meant? where exactly this happens when we issue a query?
Quote:
6. Your question in your point 4 cannot be answered by anyone unless they work at your site. Modern disk drives tend to cache data, and z/OS can use hiperspace (among other conveniences) so while the broad concept you stated might be right, the specifics could vary -- A LOT -- for your site
Thanks and very true...but in general what are exactly the sequence of events that happens when we issue an SQL query? let's say any update/insert happens then does it really go every time to the physical location and commit or just a cache commit?
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
Robert is correct -- MIPS is a measure of 'throughput' of the machine and as such, it can't be reduced. This misconception of "reducing MIPS" is so popular that when you talk about it not being correct, people look at you as if you are reading the controversial chapters of da vinci code. Possibly, a better term is "MIPS Balancing". Perhaps, if you change the machine itself, oh well, yeah - you can say there is a change in MIPS; whether it's reduced or not will still be a different story.
Quote:
so can we here say whenever we try to reduce the I/O's we always get success in usage of CPU reduction?
No, because they are not part of CPU Consumption, to start with.
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
Quote:
MIPS/CPU/avg elapsed time usage/day
MIPS is a number for the processor (when it was used). Unless and until your site replaces one piece of hardware with another, the MIPS rating will not change. It is common -- BUT EXTREMELY WRONG -- to refer to MIPS as a shortcut when the overall system resource usage of a job / task / program is being investigated.
CPU usage is only vaguely related to I/O -- generally, the more I/O is being done the more CPU time is required by the program to process the data brought in by the I/O.
Elapsed time is COMPLETELY unrelated to I/O or CPU. Elapsed time depends upon a great number of factors, including how many other jobs / tasks are running on the system, the relative priorities of the jobs / tasks, the available amount of processing power, and so forth. It is very common to see a job's elapsed time vary by 2 orders of magnitude (that is, by factor or 100 or more) if the job is run when little else is going on versus running in the peak processing time.
also when we say here I/O's what exactly it meant? where exactly this happens when we issue a query?
Quote:
but in general what are exactly the sequence of events that happens when we issue an SQL query? let's say any update/insert happens then does it really go every time to the physical location and commit or just a cache commit?
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
Have you read and studied Redbook SG24-7942, DB2 10 for z/OS Performance Topics? If not, this is a 418-page book that most likely provides you the basics of what you are asking. Plus there are the usual (for DB2) culprits to consider: zIIP usage, indexed query versus nonindexed query, hiperspace, data in virtual, disk caches, system workload, how often and how much the data in the database is being changed, etc etc etc.
Broadly speaking, an SQL query may have been cached and hence not require any physical disk activity and on the other extreme the query may mean that every byte of the table has to be physically moved from disk to memory which would require lots of I/O activity. It is not really possible to predict what, precisely, will happen when you do a query since the specifics will depend upon many factors (and many of these factors can be dynamic -- for example, if someone else happens to be updating some of the same rows you want to update).
and what is the difference between SYNC/ASYNC...here because more I/Os the MIPS/CPU usage is more though COST*RATE out of explain is very less.
also I have gone through the concepts of containers/various Buffer pools/DMS vs SMS and now understood a bit more, how these queries works...
I also heard, in DB2 specifically the data is read by using the pointers and accesses directly from the address unlike other VSAM I/Os and hence it is faster.....I may be wrong.
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
Synchronous I/O occurs when the CPU must wait for the data before proceeding; asynchronous I/O occurs when the CPU can proceed without waiting for the I/O to be complete. I'm not sure what the difference would be in a DB2 context, especially since it's been 24 years since I did anything with DB2.
) but to enhance something from my knowledge just going couple of steps more down into the situation to know what exactly happens after any SQL query is issued from anywhere (SPUFI,Application program..e.t.c.).
