• Oracle Database
• 2013.12.20
• 渡部 亮太

lotshparses.sql from TPT Scripts : Generate Lots of hard parses and shared pool activity for testing purposes

このエントリは (全部俺) Oracle ACE Director Tanel Poder Advent Calendar 2013 20日目の記事です。

Tanel Poderさんのエントリを翻訳しようと思ったのですが、翻訳作業に使用しているGoogle翻訳者ツールキットの調子がイマイチだった（今では改善されているかもしれません）ので、翻訳はあきらめて、 スクリプト lotshparses.sql を紹介することにします。 lotshparses.sql は、大量のハードパースを人為的に発生させるスクリプトです。

declare
    x number;
begin
    for i in 1..&1 loop
        execute immediate 'select count(*) from dual where rownum = '||to_char(dbms_random.random) into x;
    end loop;
end;
/

SQL> @i

USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- ---------------
SADDR            PADDR
---------------- ----------------
RYWATABE             c101         l64rw3.domain             26    7023     12.1.0.1.0 20131016 17193           52    17190
000000008C4CB908 000000008C9C1B98

SQL> @lotshparses 1000000

以下の通り、lotshparses.sqlを実行しているプロセス(SPID=17193)が、CPUを90%以上使用していることがわかります。一般にハードパースの多発は、CPU使用率の上昇を招きます。

Tasks: 248 total,   2 running, 246 sleeping,   0 stopped,   0 zombie
Cpu(s): 13.0%us, 40.1%sy,  0.0%ni, 45.6%id,  1.2%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:   1922520k total,  1808688k used,   113832k free,   100104k buffers
Swap:  4194296k total,   737496k used,  3456800k free,  1253032k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
17193 oracle    20   0 1031m 219m 215m R 92.3 11.7   2:45.02 oracle_17193_c1 <==
 4388 oracle    -2   0 1027m 3568 3252 S  7.0  0.2   2553:50 ora_vktm_c101
 4818 oracle    20   0 1031m 114m 111m S  5.3  6.1   1:04.82 ora_w002_c101
 4454 oracle    20   0 1029m  37m  35m S  0.7  2.0  29:47.53 ora_mmnl_c101
 4462 oracle    20   0 1029m 3144 2804 S  0.3  0.2   2:18.29 ora_s000_c101
18907 oracle    20   0 15156 1420  988 R  0.3  0.1   0:00.01 top
  : 

また、V$SGASTATから sql area("SQLA")のサイズが増えていることがわかります。多数のSQLを実行することにより、共有カーソルが使用する共有プールの領域サイズが増えたことがわかります。

lotshparses.sql実行前

SQL> SELECT * FROM ( SELECT * FROM V$SGASTAT WHERE POOL='shared pool' order by bytes desc) WHERE rownum <= 30;

POOL         NAME                            BYTES     CON_ID
------------ -------------------------- ---------- ----------
shared pool  free memory                 113135448          0
shared pool  XDBSC                        10350568          0
shared pool  row cache                     8638768          0
shared pool  private strands               6945792          0
shared pool  event statistics per sess     6766592          0
shared pool  ktli log buf s                6297096          0
shared pool  KGLH0                         5516680          0
shared pool  KGLSG                         5272896          0
shared pool  kglsim object batch           4957512          0
shared pool  dbktb: trace buffer           4915200          0
shared pool  KTSL subheap                  4587760          0
shared pool  ASH buffers                   4194304          0
shared pool  kglsim hash table bkts        4194304          0
shared pool  object queue hash buckets     4194304          0
shared pool  KCB Table Scan Buffer         3981120          0
shared pool  FileOpenBlock                 3979208          0
shared pool  KSFD SGA I/O b                3977152          0
shared pool  Multiblock Index SO           3874176          0
shared pool  ksunfy : SSO free list        3855296          0
shared pool  SQLA                          3795824          0<== 
shared pool  KTI-UNDO                      3612432          0
shared pool  kglsim heap                   2888704          0
shared pool  obj stats allocation chun     2703360          0
shared pool  SGA - SWRF Metric CHBs        2591048          0
shared pool  message pool freequeue        2541592          0
shared pool  KKSSP                         2361192          0
shared pool  dbwriter coalesce buffer      2101248          0
shared pool  KGLS                          1763856          0
shared pool  write state object            1634736          0
shared pool  VIRTUAL CIRCUITS              1598336          0

30行が選択されました。

lotshparses.sql実行後

SQL> SELECT * FROM ( SELECT * FROM V$SGASTAT WHERE POOL='shared pool' order by bytes desc) WHERE rownum <= 30;

POOL         NAME                            BYTES     CON_ID
------------ -------------------------- ---------- ----------
shared pool  free memory                  73052120          0
shared pool  KGLH0                        32603816          0
shared pool  SQLA                         12734784          0<==
shared pool  XDBSC                        10350568          0
shared pool  row cache                     8638768          0
shared pool  private strands               6945792          0
shared pool  event statistics per sess     6766592          0
shared pool  ktli log buf s                6297096          0
shared pool  KGLHD                         6236816          0
shared pool  kglsim object batch           6151488          0
shared pool  KGLSG                         5272896          0
shared pool  dbktb: trace buffer           4915200          0
shared pool  KTSL subheap                  4587760          0
shared pool  ASH buffers                   4194304          0
shared pool  kglsim hash table bkts        4194304          0
shared pool  object queue hash buckets     4194304          0
shared pool  KCB Table Scan Buffer         3981120          0
shared pool  FileOpenBlock                 3979208          0
shared pool  KSFD SGA I/O b                3977152          0
shared pool  Multiblock Index SO           3874176          0
shared pool  ksunfy : SSO free list        3855296          0
shared pool  KTI-UNDO                      3612432          0
shared pool  kglsim heap                   3606912          0
shared pool  obj stats allocation chun     2703360          0
shared pool  SGA - SWRF Metric CHBs        2591048          0
shared pool  message pool freequeue        2541592          0
shared pool  KKSSP                         2214944          0
shared pool  dbwriter coalesce buffer      2101248          0
shared pool  KGLDA                         1685064          0
shared pool  write state object            1634736          0

30行が選択されました。