事例

まず、システム監視により、テナントの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)を作成します。