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
 

 

CPU Time proportional to Cost of Query?

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

Active User


Joined: 31 Mar 2010
Posts: 134
Location: India

PostPosted: Wed Jun 16, 2010 5:12 pm    Post subject: CPU Time proportional to Cost of Query?
Reply with quote

Hi,
I am running a job in two different regions; in one region (DEVA), the SQL query in the job is accessing a table which has index only primary keys. In other region (DEVB), another index has been added on that table. Now APPTUNE results show that the cost of the SQL query came way down in DEVB due to new index. However, same job running in DEVB is taking more CPU/Elapsed time than DEVA. Isn’t the CPU performance proportional to cost of query ? If so, why am I getting this discrepancy ?

Thanks
Gagan
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Wed Jun 16, 2010 5:53 pm    Post subject:
Reply with quote

It is an estimated cost of the query, based on the statistics and estimated filterfactors of the where clause.

For example :
TAB1 rows = 1.000.000; pages = 10.000
Index1(col1) = PK; clusterratio 100% ; fullkeycard 1.000.000
new duplicate index2 (col2,col3) ; clusterratio 10% ; fullkeycard 820.000

Select * from tab1 where col2 = :HV2 and col3 > :HV3

based on statistics DB2 estimates that it wil only have to read 20 rows (in the worst case : 20 pages) => low cost estimate,
but at runtime :HV2 = 0 and 60% of the rows have COL2 = 0 and :HV3 = spaces and 100% of COL3 is higher than spaces.
So DB2 will have to read 600.000 rows and will do this using a not-clustered index . ==> a lot of Getpages (worst case 600.000) => CPU, elapsed => very high real cost
without index2 db2 will always do a tablescan : 10.000 getpages with Sequential prefetching. => reasonable high estimate = real high cost
Back to top
View user's profile Send private message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 134
Location: India

PostPosted: Thu Jun 17, 2010 8:32 am    Post subject:
Reply with quote

Hi GuyC,

Thanks for your reply. It a very complex analysis, I don't have much idea( actually no idea icon_sad.gif ) about the terms like cluster index ratio etc. i wanted to know is there any relationship between CPU time (of executing a COBOL-DB2 program) and the cost of the DB2 query executed in the same program? I believed if cost of the query is high, CPU must be High, but our programs execution was showing the other result (CPU time is reduced while cost of the query is less).

To summarize, can we say increase/decrease in CPU time of execution of a program is independent of Cost of the query in it.
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: Thu Jun 17, 2010 8:47 am    Post subject:
Reply with quote

Hello,

Quote:
is there any relationship between CPU time (of executing a COBOL-DB2 program) and the cost of the DB2 query executed in the same program?

Quote:
can we say increase/decrease in CPU time of execution of a program is independent of Cost of the query in it.
The best you might say is that they may be related. . .

If a query returns 1000 rows and does some processing of them, this will require more program-cpu time than the same query with a different found set that returns only 10 rows and does the same processing.

On the other hand a different query might perform a 200million row full table traversal and return only 1 row for processing. The program-cpu time will be almost non-existant while the query resources required are huge. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Fri Jun 18, 2010 3:10 pm    Post subject:
Reply with quote

it is estimated cost based on statistics and assumptions.

Most of the time this is accurate.
but sometimes the statistics or the assumptions or both are wrong.

Like a statistician drowning in a river with average dept of 10 centimeter
Back to top
View user's profile Send private message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 134
Location: India

PostPosted: Fri Jun 18, 2010 6:10 pm    Post subject:
Reply with quote

So, can we say the cost of the query is not the deciding factor for CPU time of execution?
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 Jun 18, 2010 7:18 pm    Post subject:
Reply with quote

Hello,

The "cost of the query" is NOT the deciding factor for cpu time. . .
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
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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