Cases

The system metrics show that the QPS_RT and TPS_RT of the tenant have soared.

The drill-down analysis indicates that the IOPS and data volume of disk reads have soared, which means SQL exceptions are possible.

Locate the suspicious SQL statements based on the information provided by the SQL Audit feature.

Conclusion:

• The values of the avg_logical_read field of the two SQL statements are 97 and 5, respectively.

• The values of the cnt field indicate the high concurrency of the two SQL statements.

High concurrency leads to a large disk I/O throughput, which results in the I/O wait of tenant worker threads and the values of theQUEUE_TIME field of all SQL statements exceed 30s.

Analyze the suspicious SQL statements

• Obtain the text of one suspicious SQL statement.

  obclient> select query_sql from v$ob_sql_audit where sql_id in ('E49D99FE0221C22E9E65352924104224') limit 1\G
  *************************** 1. row ***************************
  query_sql:
  select *
  from t1
  where user_id = 'xxx' and product_type = 'xxx' and standard_category not in('xxx','xxx')           
  order by gmt_last_trans DESC  limit 35
  1 row in set (0.01 sec)
  

  The GV$OB_PLAN_CACHE_PLAN_EXPLAIN view indicates that this statement uses the user_id column as the index, which leads to a heavy workload of table access by index primary key if a large number of users are involved. Therefore, the index must be optimized.

  Taking account of other queries, the new index is (user_id, product_type, standard_category, gmt_last_trans).

• Obtain the text of the other suspicious SQL statement.

  obclient> select query_sql from v$ob_sql_audit where sql_id in ('49E666DA7094F3C6089853875D94F8E6') limit 1\G
  *************************** 1. row ***************************
  query_sql:          
  select sum(trans_amount)  
  from t2   
  where a_id = 'xxx' and user_id = 'xxx' and trans_code = 'xxx' and out_date <= '20180831'
  1 row in set (0.00 sec)
  

  The GV$OB_PLAN_CACHE_PLAN_EXPLAIN view indicates that this statement uses the user_id column as the index, which leads to a heavy workload of table access by index primary key if a large number of users are involved. Therefore, the index must be optimized.

  Taking account of other queries, the new index is (a_id, trans_code, user_id, out_date, trans_amount).