Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

COST*RATE Vs MIPS Vs CPU

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Apr 26, 2013 3:14 am    Post subject: COST*RATE Vs MIPS Vs CPU
Reply with quote

Team,

I need to understand on the below four items...


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?

Code:
I/O WAITS
  SYNC I/O.........................................:, 3889828,,  ,42:20.48802,,
  ASYNC READ I/O...................................:,  310103,,  ,06:38.77303,,
  ASYNC WRITE I/O..................................:,       0,,  ,        N/A,,
  LOG WRITE I/O....................................:,       0,,  ,        N/A,,
  COMMIT PHASE 1 WRITE I/O WAIT....................:,       0,,  ,        N/A,,
  ARCHIVE LOG......................................:,       0,,  ,        N/A,,
  TAPE ARCHIVE READ................................:,       0,,  ,        N/A,,


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?
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Fri Apr 26, 2013 3:31 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Apr 26, 2013 6:58 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Apr 26, 2013 8:46 am    Post subject:
Reply with quote

Dick,

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..icon_smile.gif) 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?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Apr 26, 2013 12:24 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Fri Apr 26, 2013 4:15 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Sat Apr 27, 2013 2:02 am    Post subject:
Reply with quote

Thanks a lot for your inputs...

something still I would like to clear a bit more,
Quote:

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?


Does this happens over DASD/BP/Both?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Sat Apr 27, 2013 4:01 am    Post subject:
Reply with quote

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).
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed May 01, 2013 7:54 pm    Post subject:
Reply with quote

Robert,

Quote:
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.


does this means the below stats,

Code:
I/O WAITS
  SYNC I/O.........................................:, 3889828,,  ,42:20.48802,,
  ASYNC READ I/O...................................:,  310103,,  ,06:38.77303,,


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... icon_smile.gif
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.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Wed May 01, 2013 8:43 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed May 01, 2013 8:51 pm    Post subject:
Reply with quote

Thanks Robert for all your valuable inputs...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. MIPS reduction for Batch job Virendra Shambharkar All Other Mainframe Topics 8 Mon Nov 07, 2016 4:02 pm
No new posts Need training ppt for DB2 v10 MSU / M... SRICOBSAS DB2 5 Tue Mar 15, 2016 10:30 pm
No new posts Can I calculate the total MIPS consum... ocjohnh All Other Mainframe Topics 2 Thu Jan 09, 2014 9:36 pm
No new posts Cost of an UPDATE query when executed... sasanka DB2 3 Sun Oct 13, 2013 12:06 am
No new posts Cost of mainframe job! fredrick andrews JCL & VSAM 2 Thu Sep 05, 2013 12:54 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us