Luxoft Trainingは、Randolph Geist の記事「Advanced Oracle Troubleshooting Session-PGA / UGA memory fragmentation」の翻訳を読むことを勧めます。



Randolph Geistは、Oracleデータベースのパフォーマンスに関連するエラーの修正を専門としています。 彼は、SQLコード実行およびOracle最適化テクノロジーの分析の分野で、世界最高の専門家の一人です。 彼は、Oracle Database Administrator（OCP DBA）バージョン8i、9i、および10gの認定を受けています。

Oracle Advanced Troubleshooting Session-PGA / UGAメモリの断片化

クライアントのデータベースの診断およびトラブルシューティングセッションは、問題の次の説明から始まりました。特定の環境では、個別のバッチプロセスに予想よりもはるかに長い時間がかかる場合があります（数分、数時間ではなく）。 さらに、監視中に、非標準の動作をするSQLクエリを検出できます。



そのため、まず第一に、問題を再現する方法を学ぶ必要がありました。

すぐに、他の多くのストアドプロシージャ/パッケージにアクセスし、XMLデータを含むLOB（ラージオブジェクト）を処理するパッケージから、複雑なPL / SQLストアドプロシージャへの特定の呼び出しを検出することができました。



最初に報告されたように、このストアドプロシージャをループで複数回呼び出し、さらにさまざまなデータベース環境で問題を再現できることが判明しました。



このストアドプロシージャの固有のプロパティは、「排他的」モードで実行されたことです。 これは、他のアクティブなビジネスプロセスを同時に行わずに、十分に大きいIBMpSeriesサーバー（AIX 5.3、11.1.0.7、240 GB RAM、多数のP6マルチコアプロセッサー、高価なストレージ）で実行されたことを意味します。 基本的には順次実行でした。 監視の結果、この手順では2つの単純で明確なSQLクエリにほとんどの時間が費やされました。 これらは、一意のインデックスとROWIDによって同じ小さなテーブルにアクセスするPL / SQLから呼び出される静的SQLクエリでした。 したがって、2つのSQLクエリがありました。通常の条件下では、最悪の場合、インデックスとテーブルが完全にバッファキャッシュに配置されるため、2つの論理読み取りが必要になります。

前述のサイクルの開始時にこれらのSQLクエリの実行を監視すると、各開始時に（LOBXMLの内容に応じて）何千回も呼び出されることが明らかになりました。



これらは静的SQLであるため、PL / SQLカーソルキャッシュ（およびデータブロックキャッシュ）は非常にうまく機能しました-つまり、クエリは1回だけ解析され、その後、物理読み取りではなく論理読み取りのみが実行されるたびに発生しました。



さらに、ほとんどの場合、これらのSQLクエリは小さなテーブルで目的の値を見つけられませんでした。 これは、平均して、1つのクエリの実行に単一の論理読み取りが必要であることを意味します。つまり、目的の値が含まれていないため、一意のインデックスのスキャンです。

さまざまな環境（AIXの代わりにLinux）でのSQLクエリ実行の統計を確認すると、同じSQLクエリが同じ回数実行されていることがわかりましたが、これらの環境ではバッチジョブの実行にかかる時間が短くなりました。 この点で、膨大な数のSQL実行が奇妙に見えましたが、それらは問題の主な原因ではありませんでした。



だから：

•問題の原因としてXMLを表すLOBで動作するストアドプロシージャを定義しました。

•プロシージャ時間のほとんどは、ストアドプロシージャが開始されるたびに数千回実行される2つの静的SQLクエリに費やされました。

•ただし、これらのSQLは「最適な」実行プランを使用し、実際には一度に1つの論理I / Oのみを生成しました。

•バッチジョブの「排他的な」動作のため、他のプロセスとの競合は不可能でした。

•言い換えれば、「非効率的なクエリプラン」、「リソースの競合」、「シリアル化」などの通常の疑いについて話しているわけではありません。

•したがって、パフォーマンスを監視する通常の方法：待機イベント、拡張SQLトレース、アクティブセッションの履歴、セッション統計/システム統計、ASH / AWR / ADDMレポートは、問題の明らかな原因を明らかにしませんでした。

•別のプロセスで定義されたSQLクエリを実行すると、実行ごとに平均10マイクロ秒（！）がかかることが示されました。これは非常に高速です。 これに関連して、問題が発生します。これらの要求が問題として表示されるようになったのはどうしてですか。単純なサイクルで起動すると、1秒で1万回の起動が可能です。



「待機イベントは何も説明しないことがある（時々）」という古典的な状況に直面しています。 私たちが見るすべて-ほとんどの時間はこれらのSQLの実行に費やされました。 私たちが見つけたすべて-これらはすべて、これらの要求を実行する単一のプロセッサで発生しました。



ただし、セッションの統計デルタを監視している間（たとえば、Tanel Poederの「snapper」ユーティリティを使用して）、興味深い点が発見されました。プロセス中、プロセスはより多くのPGA / UGAメモリを必要としました。



したがって、高度な問題解決方法を適用する時が来ました。たとえば、Oak Tableの参加者であるTanel Poderは、この分野で多くの研究を行い、多数の有用なツールを提供しました。



さらなるチェックの過程で、さらにどの方向に進むべきかを決定するいくつかの異常が発見されました。



PGA / UGAプロセスの消費は、PL / SQLストアドプロシージャを開始したループの反復ごとに増加しました。 どこかにメモリの問題がありました。 この点で、PGA / UGAプロセスのメモリダンプを確認することをお勧めします。



注意：メモリダンプを取得すると、システムまたはプロセスが破損する可能性があるため、実稼働環境でこの操作を実行するときは十分に注意してください。 PGAメモリダンプの取得は通常1つのプロセスにのみ影響するため、あまり重要ではありません（もちろん、バックグラウンドプロセスでない限り）。



EVENTSを使用してメモリダンプを取得できます。

ALTER SESSION SET EVENTS 'immediate trace name heapdumplevel <lvl>';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SETOSPID、SETORAPID、またはSETMYPIDを使用してプロセスを定義した後、ORADEBUG ASSYSDBAを使用してメモリダンプを要求することもできます。

 ORADEBUG DUMP HEAPDUMP <LVL>';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

使用可能なメモリダンプのレベルの概要については、Julian DykeのWebサイトも参照してください。



この場合、5番目のレベル（5 = 4 + 1、つまりPGAとUGA）のダンプで十分なようです（現在のユーザーコールに興味がある場合は、レベル29 = 8 + 16 + 4 + 1が必要です）。



Oracleの書籍Oak Table Expert Adviceの第8章で、 Charlesと私は、この場合に使用される最も重要なメモリダンプオプションとその他のパフォーマンス監視方法について説明しています。



結果のトレースファイルは、たとえば、 Tanela heapdump_analyzerスクリプトを使用して最初に分析できます。この場合、トレースファイルのボリュームは非常に大きいため、 heapdump_analyzerからawkを少し修正したバージョンを使用して、外部Oracleテーブルとして接続されるファイルを作成しましたこのデータに対してより複雑なクエリを実行することができました。



トレースファイルを外部Oracleテーブルとして使用するのに適したファイルに変換するコマンドを次に示します。

 cat $TRACE_FILE | awk ' /^HEAP DUMP heap name=/ { split($0,ht,"\""); HTYPE=ht[2]; doPrintOut = 1; } /Chunk/{ if ( doPrintOut == 1 ) { split($0,sf,"\""); printf "%10d , %16s, %16s, %16s\n", $4, HTYPE, $5, sf[2]; } } /Total heap size/ { doPrintOut=0; } ' > $EXT_TAB_DIR/heapdump.txt
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

以下は、このような外部テーブルのDDLの例です。

 create table heapdump ( chunk_size integer , heap_type varchar2(16) , chunk_type varchar2(16) , alloc_reason varchar2(16) ) organization external ( type oracle_loader default directory ext_tab_dir access parameters ( records delimited by newline fields terminated by ',' lrtrim ) location ('heapdump.txt') );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このプロセスは一時LOBの「リーク」を引き起こします-LOBの数の一定の増加がV $ TEMPORARY_LOBSで観察されますが、それは遅く（ストアドプロシージャが開始されるたびに1つのオブジェクト）、一時テーブルスペースのメモリ消費は低いままです。



何度も繰り返される2つのSQLクエリの平均実行時間を計算すると、最初は個別の実行と同じくらい速く実際に実行されたが、実行が徐々に遅くなることが明らかになりました。

数分後、これらの要求は最初は数マイクロ秒かかりましたが、数ミリ秒（！）かかりました。 当然のことながら、これらの要求を完了するのに何度もかかったため、バッチジョブには数時間かかりました。 しかし、今では、各実行はプロセスの最初から1000倍の時間がかかりました。



よく調べてみると、このようなスローダウンは実行されるすべてのSQLクエリに影響することが明らかになりました。 しかし、これらのクエリの多くの実行にはいずれにせよ数ミリ秒かかり、数回実行されたため、数ミリ秒の実行時間の増加は実際にはそれほど重要ではありませんでした。 これらは非常に頻繁に実行されるリクエストであり、実行時間の最大の「増加」を受け取りました。



その結果、PL / SQLストアドプロシージャを開始したサイクルの完了後、このセッション内のすべてのプロセスが影響を受けることが明らかになりました。 たとえば、単純なSELECT * FROMDUAL操作またはPL / SQL nullブロック（BEGINNULL; END）を実行するには、約30分の1秒（0.3秒！）かかりました。 （別のセッションがまだ開いている間に）新しいセッションで同じ操作が即座に実行されました（0.01秒未満で、SQL * Plusタイムカウンターを使用してデータが取得されました）-これはこのセッションでのみ典型的でした、全体としてではありません。



したがって、明らかにより多くのCPU時間を必要とする何かが現れました。 これにより、「各」操作の実行時間が、それが何であるかに関係なく増加しました。



プロセスを完了するのに最も時間がかかるものを理解するために、より深いレベルに進み、オペレーティングシステムレベルでトレースを調べます。 残念ながら、AIX 5.3を扱っていたため、必要な測定を行うためのツールのセットは非常に限られていた。 AIXはDTraceをサポートしていません。新しいProbeVueツールは、AIX 6以降のバージョンでのみ使用できます。 したがって、Oracleの組み込みoradebugshort_stackとAIXのprocstackのみがあり、フォアグラウンドプロセスの呼び出しスタックからサンプルを取得するために使用していました。



しかし、再び、タネルは救助に来ます。 彼のOStackProfツールキットを使用して、コールスタックから項目を取得できましたが、プロセスがメモリの管理にほとんどの時間を費やしたことがわかりました。 コールスタックの関数名が実際に何を意味するのか誰も言えなかったため、これは単なる仮定でした。 MOS 175982.1のドキュメントに基づいて、それはすべてADTメモリ管理（Oracleオブジェクト）に関連していました。



スタックトレースは次のようになりました。

 Belowisthestackprefixcommontoallsamples: ------------------------------------------------------------------------ Frame->function() ------------------------------------------------------------------------ # 36 ->main() # 35 ->opimai_real()b0 # 34 ->sou2o() # 33 ->opidrv() # 32 ->opiodr()b98 # 31 ->opiino()f0 # 30 ->opitsk() # 29 ->ttcpip()c # 28 ->opiodr()b98 # 27 ->kpoal8()c # 26 ->opiexe() # 25 ->kkxexe()c # 24 ->peicnt() # 23 ->plsql_run() # 22 ->pfrrun() # 21 ->pfrrun_no_tool()c # 20 ->pfrinstr_EXECC()c # 19 ->pevm_EXECC()e4 # 18 ->psdnal()c # 17 ->psddr0() # 16 ->rpidrv() # ...(seecallprofilebelow) # # -#-------------------------------------------------------------------- # - Num.Samples ->incallstack() # ---------------------------------------------------------------------- 76 ->rpiswu2()c0->rpidru()->skgmstack()c4->rpidrus()c8->opiodr()b98->opipls()->opiexe()e4->auddft()b8->audbeg()->ktcxbr0()a0->kocbeg()->koctxbg()->kohalc()->kohalmc()->kghfru()c->44c0->-> 20 ->rpiswu2()c0->rpidru()->skgmstack()c4->rpidrus()c8->opiodr()b98->opipls()->opiexe()->44c0->-> 1 ->rpiswu2()c0->rpidru()->skgmstack()c4->rpidrus()c8->opiodr()b98->opipls()->opiexe()b8c->kksfbc()ec->ktcsna()c->ktucloGetGlobalMinScn()->44c0->-> 1 ->rpiswu2()c0->rpidru()->skgmstack()c4->rpidrus()c8->opiodr()b98->opipls()->opiexe()->ksuvrl()c->44c0->-> 1 ->rpiswu2()c0->rpidru()->skgmstack()c4->rpidrus()c8->opiodr()b98->opipls()->opiexe()->44c0->-> 1 ->rpiswu2()c0->rpidru()->skgmstack()c4->rpidrus()c8->opiodr()->ksuprc()c->44c0->->
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

関数名「auddft」/「audbeg」は、監査に関連するものを示している場合がありますが、パラメータ「audit_trail = DB」を除き、監査オプションはデータベースでアクティブ化されていません。 Alex Fatkulinは最近、含まれている監査オプションが11.2.0.1の速度を低下させる問題の説明を公開しました。もちろん、audit_trail = NONEパラメーターを11.1.07または11.2.0.1に設定すると、速度低下はそれほど明白ではありませんでした。 これは、実際には監査に関連するコードの一部があることを意味します。 ただし、これは問題を解決しませんでした-まだ大幅な減速がありました。



PGA / UGAメモリダンプを分析する際に重要な観察が行われました。



PGA / UGAダンプには、XMLの操作に関連する数十万もの非常に小さなデータが含まれていました（ "qmxdpls_subhea"）。 したがって、エラーはXMLのメモリ管理に関連する何かに関連しているという仮定が明らかになりました。



したがって、XML処理を担当するコードの部分に注目しました。 バグがすぐに見つかりました。コードにDBMS_XMLDOM.FREEDOCUMENTの呼び出しがありませんでした。



どうやら、このバグは最初からコードに存在していましたが、アプリケーションは10.2向けに開発されており、そのような速度の低下はありませんでしたが、PGA / UGAメモリ消費の増加や一時LOBのリークなど、問題の同様の症状がありましたが、同じスローダウンはありませんでしただった。 しばらく前にデータベースがバージョン11.1.0.7に転送された後、スローダウンの問題が発生しました。



したがって、この問題は、コードにfreedocumentアクセスコードを追加または削除するだけで解決または発見できます。



これにより、ほとんどの場合、データベース11.1.0.7以降を使用して、オンデマンドで問題を再現できる典型的なテストシナリオを開発することができました。



注：この問題は、特定のオペレーティングシステムのOracleバリアントに関連しているようです。 Linux x64用のOracle 11.1.0.7の動作は多少異なります。一部のSQLクエリのみが遅くなりました。これは、AIXバージョンを使用しているときに発生しました。

 drop table t_testloop; purge table t_testloop; create table t_testloop ( id integer not null , vc varchar2(255) , constraint t_testloop_pk primary key (id) ) ; insert into t_testloop ( id , vc ) select level as id , rpad('x', 100, 'x') as vc from dual connect by level <= 100; commit; exec dbms_stats.gather_table_stats(null, 'T_TESTLOOP') -- This is supposed to be a INDEX UNIQUE SCAN + TABLE ACCESS BY ROWID explain plan for select id , vc from t_testloop where id = to_number(:x); set linesize 160 set pagesize 999 select * from table(dbms_xplan.display); set timing on echo on serveroutput on -- This is the normal (reference) execution time for running -- the simple statement a thousand times declare procedure check_key as x integer; n_id integer; s_vc varchar2(255); begin x := 42 * 3; select id , vc into n_id , s_vc from t_testloop where id = x; exception when NO_DATA_FOUND then null; end; begin for i in 1..1000 loop check_key; end loop; end; / -- "Deterministic" randomness :-)) exec dbms_random.seed(0) declare start_time number; end_time number; -- Generate some CLOB containing XML -- Note that it looks like the CLOB needs -- to be different for every iteration -- otherwise the issue couldn't be reproduced function return_clob return clob as the_lobclob; s_name varchar2(20); n_sal integer; s_job varchar2(20); begin the_lob := '<root> '; for i in 1..20 loop s_name := dbms_random.string('U', trunc(dbms_random.value(1, 21))); n_sal := trunc(dbms_random.value(1, 1001)); s_job := dbms_random.string('U', trunc(dbms_random.value(1, 21))); the_lob := the_lob || '<emp attr1="val1" attr2="val2" attr3="val3"> <empno attr1="val1" attr2="val2" attr3="val3">' || to_char(i, 'TM') || '</empno> <name attr1="val1" attr2="val2" attr3="val3">' || s_name || '</name> <sal attr1="val1" attr2="val2" attr3="val3">' || to_char(n_sal, 'TM') || '</sal> <job attr1="val1" attr2="val2" attr3="val3">' || s_job || '</job> </emp> '; end loop; the_lob := the_lob || '</root>'; return the_lob; end return_clob; -- Some usage of the PL/SQL XML DOM API -- Some dummy processing of the attributes of the given node procedure process_attributes ( in_nodedbms_xmldom.DOMNode ) is len number; n dbms_xmldom.DOMNode; nnmdbms_xmldom.DOMNamedNodeMap; key varchar2(1000); val varchar2(32767); BEGIN nnm := dbms_xmldom.getAttributes(in_node); if (dbms_xmldom.isNull(nnm) = FALSE) then len := dbms_xmldom.getLength(nnm); -- loop through attributes for i in 0..len-1 loop n := dbms_xmldom.item(nnm, i); key := dbms_xmldom.getNodeName(n); val := dbms_xmldom.getNodeValue(n); end loop; end if; end process_attributes; -- Some usage of the PL/SQL XML DOM API -- Recursively walk the nodes of the DOM -- and call the attribute processing per node procedure walk_node ( in_nodedbms_xmldom.DOMNode ) is nldbms_xmldom.DOMNodeList; len number; n dbms_xmldom.DOMNode; node_name varchar2(100); begin -- loop through elements node_name:=dbms_xmldom.getNodeName(in_node); process_attributes(in_node); nl := dbms_xmldom.getChildNodes(in_node); len := dbms_xmldom.getLength(nl); for i in 0..len-1 loop n := dbms_xmldom.item(nl, i); node_name := dbms_xmldom.getNodeName(n); walk_node(n); end loop; end walk_node; -- The main procedure procedureprocess_xml_clob as the_lobclob; varXMLType; doc dbms_xmldom.DOMDocument; root dbms_xmldom.DOMNode; root_tag varchar2(100); begin -- Get the CLOB with the XML the_lob := return_clob; -- Instantiate an XMLTYPE var := xmltype(the_lob); -- Generate a new DOM document from the XMLType -- This seems to allocate a temporary LOB under the covers doc := dbms_xmldom.newDOMDocument(var); -- Some rudimentary XML DOM processing root := dbms_xmldom.makeNode(dbms_xmldom.getDocumentElement(doc)); root_tag := dbms_xmldom.getNodeName(root); -- If you want to burn more CPU to exaggerate the effect -- uncomment this -- walk_node(root); -- This omission here causes a significant PGA/UGA memory leak -- and causes version 11.1 and 11.2 to slow down everything -- in this session -- Version 10.2 suffers from the same symptoms but doesn't slow down --DBMS_XMLDOM.freeDocument(doc); end; begin -- Run this a thousand times and measure / output the runtime per execution for i in 1..1000 loop start_time := dbms_utility.get_time; process_xml_clob; end_time := dbms_utility.get_time; dbms_output.put_line('Run ' || to_char(i, 'TM') || ': Time (in seconds)= ' || ((end_time - start_time)/100)); end loop; end; / -- Do the simple statement again a thousand times -- Notice the difference in runtime when using 11.1.0.7 or 11.2.0.1 declare procedure check_key as x integer; n_id integer; s_vc varchar2(255); begin x := 42 * 3; select id , vc into n_id , s_vc from t_testloop where id = x; exception when NO_DATA_FOUND then null; end; begin for i in 1..1000 loop check_key; end loop; end; /
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

上記の重要な行は「DBMS_XMLDOM.FREEDOCUMENT」です。 データベースの低速バージョン（11.1.0.7または11.2.0.1）でFREEDOCUMENTを呼び出さずにスクリプトを実行すると、通常、結果は次のようになります。

 SQL> declare 2 procedure check_key 3 as 4 x integer; 5 n_id integer; 6 s_vc varchar2(255); 7 begin 8 x := 42 * 3; 9 select 10 id 11 , vc 12 into 13 n_id 14 , s_vc 15 from 16 t_testloop 17 where 18 id = x; 19 exception 20 when NO_DATA_FOUND then 21 null; 22 end; 23 begin 24 for i in 1..1000 loop 25 check_key; 26 end loop; 27 end; 28 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.94 . . . Run 1: Time (in seconds)= .49 Run 2: Time (in seconds)= .08 Run 3: Time (in seconds)= .08 Run 4: Time (in seconds)= .08 Run 5: Time (in seconds)= .05 Run 6: Time (in seconds)= .03 Run 7: Time (in seconds)= .03 Run 8: Time (in seconds)= .03 Run 9: Time (in seconds)= .03 Run 10: Time (in seconds)= .02 Run 11: Time (in seconds)= .03 Run 12: Time (in seconds)= .03 Run 13: Time (in seconds)= .03 Run 14: Time (in seconds)= .03 Run 15: Time (in seconds)= .03 Run 16: Time (in seconds)= .03 Run 17: Time (in seconds)= .02 Run 18: Time (in seconds)= .03 Run 19: Time (in seconds)= .03 Run 20: Time (in seconds)= .03 Run 21: Time (in seconds)= .03 Run 22: Time (in seconds)= .03 Run 23: Time (in seconds)= .03 Run 24: Time (in seconds)= .03 Run 25: Time (in seconds)= .03 . . . Run 287: Time (in seconds)= .03 Run 288: Time (in seconds)= .03 Run 289: Time (in seconds)= .03 Run 290: Time (in seconds)= .03 Run 291: Time (in seconds)= .02 Run 292: Time (in seconds)= .03 Run 293: Time (in seconds)= .03 Run 294: Time (in seconds)= .03 Run 295: Time (in seconds)= .03 Run 296: Time (in seconds)= .03 Run 297: Time (in seconds)= .03 Run 298: Time (in seconds)= .02 Run 299: Time (in seconds)= .03 Run 300: Time (in seconds)= .03 Run 301: Time (in seconds)= .03 Run 302: Time (in seconds)= .03 Run 303: Time (in seconds)= .17 Run 304: Time (in seconds)= .17 Run 305: Time (in seconds)= .17 Run 306: Time (in seconds)= .17 Run 307: Time (in seconds)= .17 Run 308: Time (in seconds)= .17 Run 309: Time (in seconds)= .17 Run 310: Time (in seconds)= .17 Run 311: Time (in seconds)= .18 Run 312: Time (in seconds)= .17 Run 313: Time (in seconds)= .18 Run 314: Time (in seconds)= .18 Run 315: Time (in seconds)= .18 Run 316: Time (in seconds)= .17 Run 317: Time (in seconds)= .19 Run 318: Time (in seconds)= .18 Run 319: Time (in seconds)= .18 Run 320: Time (in seconds)= .19 Run 321: Time (in seconds)= .18 Run 322: Time (in seconds)= .19 . . . Run 973: Time (in seconds)= .82 Run 974: Time (in seconds)= .83 Run 975: Time (in seconds)= .83 Run 976: Time (in seconds)= .82 Run 977: Time (in seconds)= .83 Run 978: Time (in seconds)= .83 Run 979: Time (in seconds)= .82 Run 980: Time (in seconds)= .82 Run 981: Time (in seconds)= .83 Run 982: Time (in seconds)= .82 Run 983: Time (in seconds)= .83 Run 984: Time (in seconds)= .83 Run 985: Time (in seconds)= .82 Run 986: Time (in seconds)= .84 Run 987: Time (in seconds)= .83 Run 988: Time (in seconds)= .86 Run 989: Time (in seconds)= .84 Run 990: Time (in seconds)= .83 Run 991: Time (in seconds)= .85 Run 992: Time (in seconds)= .84 Run 993: Time (in seconds)= .84 Run 994: Time (in seconds)= .85 Run 995: Time (in seconds)= .84 Run 996: Time (in seconds)= .85 Run 997: Time (in seconds)= .84 Run 998: Time (in seconds)= .87 Run 999: Time (in seconds)= .84 Run 1000: Time (in seconds)= .85 PL/SQL procedure successfully completed. Elapsed: 00:06:00.49 SQL> SQL> declare 2 procedure check_key 3 as 4 x integer; 5 n_id integer; 6 s_vc varchar2(255); 7 begin 8 x := 42 * 3; 9 select 10 id 11 , vc 12 into 13 n_id 14 , s_vc 15 from 16 t_testloop 17 where 18 id = x; 19 exception 20 when NO_DATA_FOUND then 21 null; 22 end; 23 begin 24 for i in 1..1000 loop 25 check_key; 26 end loop; 27 end; 28 / PL/SQL procedure successfully completed. Elapsed: 00:00:03.02 SQL>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

注意してください。 一定の反復回数の後、コマンドの実行はますます遅くなります。 さらに興味深いのは、1,000のSQLクエリの単純なサイクルが、このコードブロックの実行前よりもかなり長い実行時間を必要とすることです。



統計の収集中、またはこのコードブロックの実行中にV $ PROCESSおよびV $ TEMPORARY_LOBSを実行中に、PGA / UGAの消費を観察することができます。



バージョン10.2.0.4で同じ操作を実行すると、問題の同じ症状が表示されますが、ランタイムは少なくとも1,000回の繰り返しに対して安定しています。 システムが消費するメモリの量が増え続けることで問題が発生するときが来ることは明らかですが、これは私たちの問題とは関係ありません。



DBMS_XMLDOM.FREEDOCUMENTに正しくアクセスすると、11.1.0.7および11.2.0.1のコードの同じ部分がプロセスの安定した実行時間を提供します（PGA / UGAメモリまたは一時LOBの消費の増加はありません）。



Nigel Nobleは最近、バージョン11.1.0.7でのみ表示されるがバージョン10.2.0.4では見つからないPL / SQLメモリの問題に関するブログ投稿も投稿しました。



Alex FatkulinとNigel Nobleが指摘する問題は、ここで説明した問題と必ずしも関連しているわけではありませんが、Oracleバージョン11のリリースでメモリ管理にいくつかの変更が導入されたことを示しています（おそらく特定の条件下で以前のバージョンとは異なる動作をする自動メモリ管理機能AMMまたはmemory_targetパラメーターの実装。



3月24日に、モスクワは、Oracleデータベースのパフォーマンスの問題を解決する Randolph Geist によるワークショップを開催します。