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
| 
