
- AWS
- Oracle Cloud
- Oracle Database
- Exadata
運用管理の秘訣!バックアップ&監視実践ガイド~Oracle Database@AWS解析白書③
Oracle Databaseの利用において安定稼働を実現するためには『バックアップや監視をどう実施していくのか?』という点の検討は欠かせません。今回は、これらのドキュメントを読み解きながらOracle Database@AWSにおけるバックアップ/監視にフォーカスして情報をお届けいたします。
|
「○○しているセッションを調べる方法を教えて欲しい」というお問い合わせをいただくことがあります。○○の部分は「CPUを使用」、「メモリを使用」、「REDOを多く生成」など様々ですが、これらの確認方法を知っておくことは、データベースの管理を行う上で重要です。
今回はメモリを使用しているセッションの確認方法を2つ紹介します。
データベースサーバのメモリ使用率が高騰した際には、まず、OS側(Windows:パフォーマンスモニタ、Unix系:psやtopなど)からOracle Databaseのプロセスがメモリを使用しているのかの確認を行います。
もしメモリを使用しているのがOracle Databaseのプロセスだった場合は、そのプロセスはどのセッションに紐付いていて、どのような処理でメモリを使用しているのか確認します。
メモリを使用しているセッションの特定には動的パフォーマンスビュー(V$ビュー)のV$PROCESSとV$SESSIONを使用します。
V$PROCESSにはPGA_ALLOC_MEMという列があります。この列はプロセスによって現在割り当てられており、OSには解放されていないPGAをバイト単位で表示します。
V$PROCESSだけではセッションの情報(DBユーザ名やセッションの状態)を確認することができないため、V$SESSIONの情報も確認します。
V$PROCESSのADDR列とV$SESSIONのPADDR列の値は同じ値を持ちますので、この値を使用して結合します。既にpsやtopなどのOSコマンドによりメモリを使用しているプロセスのプロセスIDが判明しているのであれば、V$PROCESS.SPID列の値をWHERE句の条件に指定します。以下はOS側でプロセスID4880のプロセスがメモリを使用していた場合の実行例です。
SQL> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; SQL> SELECT 2 to_char(sysdate) 3 ,s.sid 4 ,s.serial# 5 ,s.username 6 ,s.machine 7 ,s.event 8 ,s.state 9 ,s.status 10 ,s.sql_address 11 ,s.sql_hash_value 12 ,p.program 13 ,p.pga_alloc_mem 14 FROM v$session s,v$process p 15 WHERE s.paddr = p.addr 16 AND p.spid = 4880; TO_CHAR(SYSDATE) SID SERIAL# USERNAME MACHINE EVENT STATE STATUS SQL_ADDRESS SQL_HASH_VALUE PROGRAM PGA_ALLOC_MEM ------------------- ---------- ---------- -------- ------------------------- ------------------------- ----------------- -------- ---------------- -------------- ----------------- ------------- 2017-03-09 14:08:39 124 30506 TESTUSR WORKGROUP\WIN-L3UL0ER9R6K SQL*Net message to client WAITED SHORT TIME ACTIVE 000007FF1C7273C0 405230308 ORACLE.EXE (SHAD) 122261759
この例ではSID:124、ユーザ名:TESTUSRがSQL_ADDRESS:000007FF1C7273C0/SQL_HASH_VALUE:405230308を実行中(STATUS=ACTIVE/STATE=WAITED SHORT TIME)でPGAを約116MB獲得していることがわかります。また、このセッションの接続元クライアントマシン名はWORKGROUP\WIN-L3UL0ER9R6Kです。
WHERE句の条件を"p.pga_alloc_mem >= 104857600"のように変えれば、指定したサイズ以上にPGAを獲得しているセッションを調べることもできます。
なお、今回はわかりやすいようにV$SESSION、V$PROCESSのまま記載していますが、
マニュアルに記載のとおり
V$ビューの並べ替えや結合はサポートされていません。CREATE TABLE AS SELECTなどで別の表に書き出した上でご実行ください。
処理内容はV$SQLTEXTからSQL_ADDRESSとSQL_HASH_VALUE、もしくはSQL_IDを使用して検索することで確認できます。
SQL> SELECT 2 sql_text 3 FROM 4 v$sqltext 5 WHERE 6 address = '000007FF1C7273C0' 7 AND hash_value = 405230308 8 ORDER BY piece; SQL_TEXT ---------------------------------------------------------------- select count(*) from (select * from dba_source a, dba_source b, dba_source c, dba_source d, dba_source e, dba_source f, dba_sour ce g, dba_source h, dba_source i, dba_source j, dba_source k ord er by 1)
もしこのセッションの処理が意図しないものであれば、クライアントから処理を停止させるか、ALTER SYSTEM KILL SESSIONでKILLするなどで対応することになるでしょう。
SQL> ALTER SYSTEM KILL SESSION '&SID,&SERIAL#' IMMEDIATE;
Enterprise Edition + Diagnostics Packの追加オプションライセンスをお持ちの環境であれば、Active Session History(ASH)を使用することができます。
V$ビューではSELECT実行時に存在しているセッションやプロセスの情報しか確認ができませんが、ASHが利用可能であればDBA_HIST_ACTIVE_SESS_HISTORYから過去のセッション情報を確認できます。そのため、問題の対処優先でデータベースに接続しているセッションのKILLなどを行ってしまった後でも遡って調査を行うことができます。
DBA_HIST_ACTIVE_SESS_HISTORYではアクティブなセッションの情報の履歴が10秒間隔で保存されています。たとえば以下のようなSQLを使用することで過去のある時間帯に絞ってPGAを獲得していたセッションを確認するといったことも可能です。
SQL> SELECT 2 sample_time 3 ,session_id 4 ,session_serial# 5 ,seq# 6 ,user_id 7 ,sql_id 8 ,top_level_sql_id 9 ,event 10 ,session_state 11 ,program 12 ,machine 13 ,pga_allocated 14 FROM 15 dba_hist_active_sess_history 16 WHERE 17 sample_time BETWEEN to_timestamp ('17-03-09 14:05:00', 'YY-MM-DD HH24:MI:SS') 18 AND to_timestamp ('17-03-09 14:10:00', 'YY-MM-DD HH24:MI:SS') 19 ORDER BY 1 20 ; SAMPLE_TIME SESSION_ID SESSION_SERIAL# SEQ# USER_ID SQL_ID TOP_LEVEL_SQL EVENT SESSION PROGRAM MACHINE PGA_ALLOCATED --------------------- ---------- --------------- ---------- ---------- ------------- ------------- ---------- ------- ------------------ ------------------------- ------------- 17-03-09 14:06:09.211 228 50729 232 111 46qnc2fc3y614 46qnc2fc3y614 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 43843584 17-03-09 14:06:19.351 228 50729 232 111 46qnc2fc3y614 46qnc2fc3y614 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 43843584 17-03-09 14:07:30.269 124 30506 35 111 budghy0c2fnr4 budghy0c2fnr4 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 117309440 17-03-09 14:07:40.409 124 30506 35 111 budghy0c2fnr4 budghy0c2fnr4 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 118947840 17-03-09 14:07:50.549 124 30506 35 111 budghy0c2fnr4 budghy0c2fnr4 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 118947840 17-03-09 14:08:00.686 124 30506 35 111 budghy0c2fnr4 budghy0c2fnr4 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 99418112 17-03-09 14:08:10.826 124 30506 35 111 budghy0c2fnr4 budghy0c2fnr4 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 99418112 17-03-09 14:08:20.966 124 30506 35 111 budghy0c2fnr4 budghy0c2fnr4 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 99418112 17-03-09 14:08:20.966 356 9006 7390 0 ON CPU ORACLE.EXE (PSP0) WIN-L3UL0ER9R6K 721920 17-03-09 14:08:31.106 124 30506 35 111 budghy0c2fnr4 budghy0c2fnr4 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 99418112 17-03-09 14:08:41.246 124 30506 35 111 budghy0c2fnr4 budghy0c2fnr4 ON CPU sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K 99418112
この例では先程のSID:124のセッションが14:07:30からPGAを獲得していたことがわかります。DBA_HIST_ACTIVE_SESS_HISTORYにはUSERNAME列はありませんが、USER_ID列はALL_USERSのUSER_ID列と同じ値ですので、こちらからユーザ名を確認できます。
実行されていたSQL文はV$SQLTEXTからSQL_IDを用いて確認できますが、共有プール上からフラッシュされてしまった場合には確認することができません。定期的にAWRによってDBA_HIST_SQLTEXTにSQL文が挿入されているため、V$SQLTEXTから確認できない場合でもAWRから確認できる可能性があります。
以下は、AWRからSQL_ID:budghy0c2fnr4のSQL文と実行計画を表示する例です。
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('budghy0c2fnr4',null,null,'ALL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID budghy0c2fnr4 -------------------- select count(*) from (select * from dba_source a, dba_source b, dba_source c, dba_source d, dba_source e, dba_source f, dba_source g, dba_source h, dba_source i, dba_source j, dba_source k order by 1) : 略
今回はV$SESSION、V$PROCESS、DBA_HIST_ACTIVE_SESS_HISTORYを使用したメモリを使用しているセッションの調査方法をご紹介しました。これらのビューの列の意味はリファレンスマニュアルに記載されており、今回例示した列以外からも様々な情報を得ることができます。
V$ビューやASHを確認しても特定のセッションが多くのメモリを使用している状況でない場合は、セッション数が普段より増加していないかといった点も確認のポイントになります。日頃からどのアプリケーションからどの程度の接続があるのかを把握しておくことで、問題発生箇所の早期切り分けに繋がります。
なお、Oracle Database 12.1 以降では初期化パラメータPGA_AGGREGATE_LIMITによりインスタンスで消費できる総PGAのハードリミットを設定できます。設定値を超えるPGAの獲得が行われた場合にはORA-04036が発生し、その処理は失敗します。
![]() |
---|
サービス事業部 サポートセンター
2007年にアシスト入社後、Oracle Databaseのサポート業務に従事。現在はサポート業務の傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されているノウハウを社内外に伝える活動を行っている。
■本記事の内容について
本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。
■商標に関して
・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。
Oracle Databaseの利用において安定稼働を実現するためには『バックアップや監視をどう実施していくのか?』という点の検討は欠かせません。今回は、これらのドキュメントを読み解きながらOracle Database@AWSにおけるバックアップ/監視にフォーカスして情報をお届けいたします。
前回の記事では、HCXの概要をお伝えしました。今回はOCVSでHCXを利用するための検討ポイントや前提事項を説明します!
Oracle Database 23aiではSQL関連の機能も数多く追加されています。本ブログでは23aiのSQL機能について「新機能編」と「機能拡張編」の2回にわたってご紹介します。本記事ではサポートセンターが注目している便利機能をご紹介します。