Reducing CPU problem caused due to high buffer gets.
Problem: CPU 90% of Telecom Database.
Troubleshooting: While interpreting the statspack report I found that one of the queries is consuming more CPU and it has more buffer gets(count running into crores)
Top Sql Session of Statspack
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ ------ -- --------- ----------
20,19,496,503 216,616 90.0 63.2 1411.25 2037.01 3131397695
I was looking at that particular query and surprised to find that it has only 20 records in that table and performing a full table scan.
Based on this I ran the following query to know the no of blocks the table has occupied.
Select blocks, num_rows from dba_tables where owner='XYZ';
It has occupied 1500 blocks for 20 rows. Each time the query runs, it is scanning 1200 blocks for 20 records which is the cause for the high CPU consumption.
Solution:
The CPU came back to 20% after recreating the table where it occupied 2 blocks for 20 records. The reason for this is that, the table used to have millions of records earlier and they have performed a delete operation, which is the cause for blocks not being released after the delete.
