IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

CPU Time proportional to Cost of Query?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 1281
Location: Belgium

PostPosted: Wed Jun 16, 2010 5:53 pm
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
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

Moderator Emeritus


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

PostPosted: Thu Jun 17, 2010 8:47 am
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: 1281
Location: Belgium

PostPosted: Fri Jun 18, 2010 3:10 pm
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
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

Moderator Emeritus


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

PostPosted: Fri Jun 18, 2010 7:18 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the the current time DFSORT/ICETOOL 13
No new posts RC query -Time column CA Products 3
No new posts C Compile time time stamps Java & MQSeries 10
No new posts Parallelization in CICS to reduce res... CICS 4
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top