By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

DB2V8.2 DPF CPU Usage

P: 3
Hi all,

I'm working on a data warehouse split into 16 partitions. Whenever we run a query that involves a non-indexed predicate, the CPU hits 100% (mainly system CPU when checked with iostat). Anyone else experienced similar ? Is this correct, or is it a bug / config issue ?

Example query
Select * from table_a where non_indexed_column = 'AA'

It's DB2V8 FP13 on Solaris, I get the same results on Solaris 8 or Solaris 10, and with ZFS or VXFS filesystems.

Sample iostat output - you can see where I submit the query it goes to 0% idle, and when I cancel the query it goes back to 90%+ free.

iostat 2
tty md0 md1 md3 md4 cpu
tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id
3 2178 74 25 10 0 0 25 29 2 18 18 3 12 115 61 89 89
0 527 0 0 0 0 0 0 0 0 0 0 0 0 0 1 4 95
0 40 0 0 0 0 0 0 0 0 0 99 31 16 0 0 5 94
0 40 0 0 0 0 0 0 0 0 0 82 26 17 0 0 6 94
0 350 0 0 0 0 0 0 23 7 18 0 0 0 7 37 4 52
0 40 5 2 27 0 0 0 4 1 12 0 0 0 19 81 0 0
0 40 0 0 0 0 0 0 0 0 0 0 0 0 20 80 0 0
0 526 0 0 0 0 0 0 0 0 0 0 0 0 19 81 0 0
2 40 0 0 0 0 0 0 0 0 0 0 0 0 20 80 0 0
0 40 0 0 0 0 0 0 0 0 0 0 0 0 19 81 0 0
0 471 0 0 0 0 0 0 0 0 0 56 7 5 14 65 2 19
0 40 0 0 0 0 0 0 0 0 0 0 0 0 62 4 2 32
0 40 0 0 0 0 0 0 0 0 0 0 0 0 0 3 4 92

(Sorry, can't work out how to format it, so the columns line up).

The CPU acts normally for tablescans without predicates, or whenever the predicate is indexable (uses about 5% per query as an estimate).

The CPU acts normally when the table queried is not partitioned.

Is the 100% CPU just because when selecting in this way from a partitioned table, and having to read every row to satisfy the predicate it's more data than the system can handle, so 100% CPU is expected ? It's causing us issues, as nothing can run at the same time as one of these queries, and we we're thinking of using ZFS file compression, which also has a CPU overhead, and that's fine for most queries, it's just these non-index matching ones that don't leave any CPU free for data decompression (can't go to V9 compression yet).

Thanks,

Ben
Jun 27 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.