View previous topic :: View next topic
|
Author |
Message |
GaganGarg
Active User
Joined: 31 Mar 2010 Posts: 134 Location: India
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010 Posts: 134 Location: India
|
|
|
|
Hi GuyC,
Thanks for your reply. It a very complex analysis, I don't have much idea( actually no idea ) 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010 Posts: 134 Location: India
|
|
|
|
So, can we say the cost of the query is not the deciding factor for CPU time of execution? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
The "cost of the query" is NOT the deciding factor for cpu time. . . |
|
Back to top |
|
|
|