事例

まず、システム監視により、テナントのQPS_RTとTPS_RTが急上昇していることが判明しました。

その後、詳細な分析を行った結果、ディスク読み取りのIOPSとデータ量が急増しており、SQLに起因する問題ではないかと疑われました。

SQL Auditを用いて、疑わしいSQL文を特定しました。

結論：

• avg_logical_readフィールドから、これら2つのSQLの論理読み取り数はそれぞれ97と5であることが分かりました。

• cntフィールドから、これら2つのSQLの並行実行数が多いことが分かりました。

並行実行数が多いために大量のディスクI/Oが発生し、結果としてテナントのワーカースレッドのI/O待ちが生じ、すべてのSQLのQUEUE_TIMEが30秒以上に達しました。

疑わしいSQLの分析

• 疑わしいSQLのTextテキストを取得します。

  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)
  

  GV$OB_PLAN_CACHE_PLAN_EXPLAINビューを分析した結果、使用されているインデックスは(user_id)であり、このインデックスは大口顧客に対して大量のリバウンドを引き起こす可能性があるため、インデックスの最適化が必要です。

  他のクエリを総合的に検討した結果、新たに(user_id, product_type, standard_category, gmt_last_trans)インデックスを作成します。

• 疑わしいSQLのTextテキストを取得します。

  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)
  

  GV$OB_PLAN_CACHE_PLAN_EXPLAINビューを分析した結果、使用されているインデックスは(user_id)であり、このインデックスは大口顧客に対して大量のリバウンドを引き起こす可能性があるため、インデックスの最適化が必要です。

  他のクエリを総合的に検討した結果、新たに(a_id, trans_code, user_id,out_date,trans_amount)インデックスを作成します。