共有プールは実行済みSQLの実行計画を格納します。
再度、同様のSQLが実行される場合、共有プール内の実行計画を再利用することで、実行計画策定の負荷を減らすことが出来ます。
今回はv$SQLビューで共有プール内を覗いてみます。
SQL> alter system FLUSH SHARED_POOL;
システムが変更されました。
まずは、共有プールを綺麗にします。
SQL> select /* practiceSQL1 */ COL1 || ':' || COL2 "Recors" from TBL1 where ROWNUM <=3;
Recors
--------------------------------------------------------------------------------
199:199AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAA
200:200AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAA
201:201AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAA
適当にSQLを実行してみます。
これで共有プール内にSQL文の実行計画が格納されたはずです。
[oracle@db01 ~]$ sqlplus / as sysdba
SQL> set pagesize 100 linesize 120
SQL> col SQL_TEXT for a90
SQL> select SQL_ID, CHILD_NUMBER, SQL_TEXT
2 from V$SQL
3 where SQL_TEXT like 'select /* practiceSQL1 */ COL1 || '' : '' || %' ;
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------------------------------------
2vrywhbx1jxtb 0 select /* practiceSQL1 */ COL1 || ' : ' || COL2 "Record" from TBL2 where ROWNUM <=3
v$SQLに実行したSQLが格納されていました。
このSQLIDを頼りに色々と調べてみます。
set pagesize 100 linesize 120
col LAST_LOAD_TIME for a20
col PARSING_SCHEMA_NAME for a4
col MODULE for a8
select SQL_ID, CHILD_NUMBER, LAST_LOAD_TIME, EXECUTIONS, PARSING_SCHEMA_NAME, MODULE, CPU_TIME, ELAPSED_TIME
from V$SQL
where SQL_ID = '2vrywhbx1jxtb';
SQL_ID CHILD_NUMBER LAST_LOAD_TIME EXECUTIONS PARS MODULE CPU_TIME ELAPSED_TIME
------------- ------------ -------------------- ---------- ---- -------- ---------- ------------
2vrywhbx1jxtb 0 2016-05-31/00:40:33 1 USER SQL*Plus 5999 17737
1
さらにv$SQLを調べてみると、共有プールに書き込まれた(ロードされた)時間、合計のCPU時間、経過時間がなどがわかります。
さらに、そんな裏で先程のSQLを5回ほど実行してみます。
select SQL_ID, EXECUTIONS, PARSE_CALLS,
CPU_TIME, ELAPSED_TIME, ELAPSED_TIME - CPU_TIME,
USER_IO_WAIT_TIME, DISK_READS,PHYSICAL_READ_BYTES
from V$SQL
where SQL_ID = '2vrywhbx1jxtb';
SQL_ID EXECUTIONS PARSE_CALLS CPU_TIME ELAPSED_TIME ELAPSED_TIME-CPU_TIME USER_IO_WAIT_TIME DISK_READS
------------- ---------- ----------- ---------- ------------ --------------------- ----------------- ----------
PHYSICAL_READ_BYTES
-------------------
2vrywhbx1jxtb 6 3 7999 39166 31167 31218 168
1376256
EXECUTIONSが6になっていることがわかります。
これが、ソフト(共有プール内にSQLが存在)、ハード(共有プール内にSQLが存在しない)の合計回数です。
またCPU時間、経過時間が単純に6倍になっていないので、共有プール内に存在しているSQLを読み込むことでCPU使用率が削減されていることがわかります。
再度、同様のSQLが実行される場合、共有プール内の実行計画を再利用することで、実行計画策定の負荷を減らすことが出来ます。
今回はv$SQLビューで共有プール内を覗いてみます。
SQL> alter system FLUSH SHARED_POOL;
システムが変更されました。
まずは、共有プールを綺麗にします。
SQL> select /* practiceSQL1 */ COL1 || ':' || COL2 "Recors" from TBL1 where ROWNUM <=3;
Recors
--------------------------------------------------------------------------------
199:199AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAA
200:200AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAA
201:201AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAA
適当にSQLを実行してみます。
これで共有プール内にSQL文の実行計画が格納されたはずです。
[oracle@db01 ~]$ sqlplus / as sysdba
SQL> set pagesize 100 linesize 120
SQL> col SQL_TEXT for a90
SQL> select SQL_ID, CHILD_NUMBER, SQL_TEXT
2 from V$SQL
3 where SQL_TEXT like 'select /* practiceSQL1 */ COL1 || '' : '' || %' ;
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------------------------------------
2vrywhbx1jxtb 0 select /* practiceSQL1 */ COL1 || ' : ' || COL2 "Record" from TBL2 where ROWNUM <=3
v$SQLに実行したSQLが格納されていました。
このSQLIDを頼りに色々と調べてみます。
set pagesize 100 linesize 120
col LAST_LOAD_TIME for a20
col PARSING_SCHEMA_NAME for a4
col MODULE for a8
select SQL_ID, CHILD_NUMBER, LAST_LOAD_TIME, EXECUTIONS, PARSING_SCHEMA_NAME, MODULE, CPU_TIME, ELAPSED_TIME
from V$SQL
where SQL_ID = '2vrywhbx1jxtb';
SQL_ID CHILD_NUMBER LAST_LOAD_TIME EXECUTIONS PARS MODULE CPU_TIME ELAPSED_TIME
------------- ------------ -------------------- ---------- ---- -------- ---------- ------------
2vrywhbx1jxtb 0 2016-05-31/00:40:33 1 USER SQL*Plus 5999 17737
1
さらにv$SQLを調べてみると、共有プールに書き込まれた(ロードされた)時間、合計のCPU時間、経過時間がなどがわかります。
さらに、そんな裏で先程のSQLを5回ほど実行してみます。
select SQL_ID, EXECUTIONS, PARSE_CALLS,
CPU_TIME, ELAPSED_TIME, ELAPSED_TIME - CPU_TIME,
USER_IO_WAIT_TIME, DISK_READS,PHYSICAL_READ_BYTES
from V$SQL
where SQL_ID = '2vrywhbx1jxtb';
SQL_ID EXECUTIONS PARSE_CALLS CPU_TIME ELAPSED_TIME ELAPSED_TIME-CPU_TIME USER_IO_WAIT_TIME DISK_READS
------------- ---------- ----------- ---------- ------------ --------------------- ----------------- ----------
PHYSICAL_READ_BYTES
-------------------
2vrywhbx1jxtb 6 3 7999 39166 31167 31218 168
1376256
EXECUTIONSが6になっていることがわかります。
これが、ソフト(共有プール内にSQLが存在)、ハード(共有プール内にSQLが存在しない)の合計回数です。
またCPU時間、経過時間が単純に6倍になっていないので、共有プール内に存在しているSQLを読み込むことでCPU使用率が削減されていることがわかります。