• High CPU usage on DB host?

    Its Unix 101 statement, but I have heard it wrong so many time, so decided to put a blog for it. If in OLTP environment, per ‘top’ %wa (IOWAIT) is major contributor for CPU busy, adding CPU would not help. There is no need for more CPU. Period. ************** %wa IS PERCENTAGE WHAT COUNT TO CPU IDLE ****************** Simple test. Push some IO: user1@myhost:~$ dd if=/dev/zero of=/tmp/file1 conv=notrunc bs=1000 count=3000000 & [1] 31240 user1@myhost:~$ dd if=/dev/zero of=/tmp/file2 conv=notrunc bs=1000 count=3000000 & [2] 31241 user1@myhost:~$ dd if=/dev/zero of=/tmp/file3 conv=notrunc bs=1000 count=3000000 & [3] 31242 user1@myhost:~$ dd if=/dev/zero of=/tmp/file4 conv=notrunc bs=1000 count=3000000 & [4] 31243 top looks:
  • Index Clustering factor. One way how to avoid negative performance impact on joins which involves bad clustered indexes.

    High Index Clustering factor is popular problem in SQL world. The most known solution for it – rebuild table ordering by columns what are in the index. But in real world, with table size growing exponentially its not always practical or possible to do it. Here is one way to avoid index clustering problem by SQL optimization.. Consider classical example. We have tables: customer CREATE TABLE customer (customer_id NUMBER, name VARCHAR2(50), dob DATE, status VARCHAR2(20), .