oracle dbは自身に利用実績が少ない為、メモ。

事前準備
チューニングの為のTIPS (調査・分析)
- V$SQLSTATS による統計情報
- 実行計画確認 - 「explain plan for」「autotrace」
チューニングの為のTIPS (対策実施)

事前準備

テスト用 table 作成

create table TEST_TBL_1 (
num_1         NUMBER(10),
num_2         NUMBER(10),
str_1         VARCHAR2(10),
str_2         VARCHAR2(10),
PRIMARY KEY (num_1)
);
CREATE INDEX TEST_TBL_1_STR_1 ON TEST_TBL_1(str_1);

create table TEST_TBL_2 (
num_1         NUMBER(10),
num_2         NUMBER(10),
str_1         VARCHAR2(10),
str_2         VARCHAR2(10),
PRIMARY KEY (num_1)
);
CREATE INDEX TEST_TBL_2_STR_1 ON TEST_TBL_2(str_1);

作成したtabl定義や、pkey、index確認。

まず、カラム定義

SQL> DESCRIBE TEST_TBL_1;
 Name                       Null?    Type
 -------------------------- -------- -------------
 NUM_1                      NOT NULL NUMBER(10)
 NUM_2                               NUMBER(10)
 STR_1                               VARCHAR2(10)
 STR_2                               VARCHAR2(10)

SQL> DESCRIBE TEST_TBL_2;  
 Name                       Null?    Type
 -------------------------- -------- ------------
 NUM_1                      NOT NULL NUMBER(10)
 NUM_2                               NUMBER(10)
 STR_1                               VARCHAR2(10)
 STR_2                               VARCHAR2(10)

次に、primary key

SQL> column constraint_name format a20
column table_name      format a20
column column_name     format a20
SELECT c.table_name, cc.column_name,constraint_type
FROM user_constraints c
JOIN user_cons_columns cc
  ON (c.table_name     =cc.table_name AND
      c.constraint_name=cc.constraint_name)
WHERE c.table_name in ('TEST_TBL_1','TEST_TBL_2')
ORDER BY cc.table_name, cc.column_name;

TABLE_NAME           COLUMN_NAME          C
-------------------- -------------------- -
TEST_TBL_1           NUM_1                P
TEST_TBL_2           NUM_1                P

最後に、index

SQL> column INDEX_NAME format a16
column INDEX_TYPE format a10
column TABLE_NAME format a10
SELECT
  INDEX_NAME, INDEX_TYPE, TABLE_NAME,
  TABLE_TYPE, UNIQUENESS, LAST_ANALYZED
FROM DBA_INDEXES
WHERE table_name in ('TEST_TBL_1','TEST_TBL_2');

INDEX_NAME       INDEX_TYPE TABLE_NAME TABLE_TYPE  UNIQUENES LAST_ANAL
---------------- ---------- ---------- ----------- --------- ---------
SYS_C007451      NORMAL     TEST_TBL_1 TABLE       UNIQUE    26-DEC-20
TEST_TBL_1_STR_1 NORMAL     TEST_TBL_1 TABLE       NONUNIQUE 26-DEC-20
SYS_C007452      NORMAL     TEST_TBL_2 TABLE       UNIQUE    26-DEC-20
TEST_TBL_2_STR_1 NORMAL     TEST_TBL_2 TABLE       NONUNIQUE 26-DEC-20

テストテーブルへのデータinsert

先程のエントリにある python script の通りです。

Oracle Instant Client + cx_Oracle による python3 on centos7 から、oracle 12cへ接続 - end0tknr's kipple - web写経開発

念の為?、indexを再構築

ALTER INDEX TEST_TBL_1_STR_1 REBUILD;
ALTER INDEX TEST_TBL_2_STR_1 REBUILD;

チューニングの為のTIPS (調査・分析)

V$SQLSTATS による統計情報

日々、実行されるSQLのうち遅いSQLを抽出するには、以下のように V$SQLSTATS を参照すると良いかと思います。

以下では、一回あたりの実行時間(マイクロ秒)が長いSQL 上位5種を出力しています。

EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;

SELECT * FROM(
  SELECT  SQL_TEXT,
          ELAPSED_TIME/EXECUTIONS, 
          LAST_ACTIVE_TIME
  FROM V$SQLSTATS
  WHERE EXECUTIONS > 0
  ORDER BY ELAPSED_TIME/EXECUTIONS desc)
WHERE ROWNUM <= 5;

SQL_TEXT
--------------------------------------------------------------------------------
ELAPSED_TIME/EXECUTIONS LAST_ACTI
----------------------- ---------
call dbms_stats.gather_database_stats_job_proc (  )
             4154223.47 26-DEC-20

select count(*) from wri$_optstat_opr o, wri$_optstat_opr_tasks t where o.id = t
.op_id(+) and o.operation = 'gather_database_stats (auto)' and (not regexp_like(
   extract(xmltype('<notes>' || o.notes || '</notes>'), '//error'),   '^<error>O
RA-200[0-9][0-9]') or  not regexp_like(   extract(xmltype('<notes>' || t.notes |
| '</notes>'), '//error'),   '^<error>ORA-200[0-9][0-9]'))
                3094077 26-DEC-20

SQL_TEXT
--------------------------------------------------------------------------------
ELAPSED_TIME/EXECUTIONS LAST_ACTI
----------------------- ---------

begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
                1023710 26-DEC-20

select /*+  no_parallel_index(t, "I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST")  dbms_stats c
ursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlinde
x_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  no_e
xpand index(t,"I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST") */ count(*) as nrw,count(distinc
t sys_op_lbid(14364,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend(

SQL_TEXT
--------------------------------------------------------------------------------
ELAPSED_TIME/EXECUTIONS LAST_ACTI
----------------------- ---------
"OBJ#")||sys_op_descend("INTCOL#")||sys_op_descend("SYS_NC00018$")||sys_op_desce
nd("COLNAME"))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "SYS
"."WRI$_OPTSTAT_HISTGRM_HISTORY" t where "OBJ#" is not null or "INTCOL#" is not
null or "SYS_NC00018$" is not null or "COLNAME" is not null
                 880529 26-DEC-20

MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_cdn(ST)                OPT
_PARAM('_parallel_syspls_obey_force' 'false')             */ INTO STATS_TARGET$
ST USING (SELECT CASE WHEN DBMS_STATS_INTERNAL.BITCLR(AFLAGS, :B20 + :B23 ) = :B
  :

5 rows selected.

「EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;」は不要かも

実行計画確認 - 「explain plan for」「autotrace」

mysql の explain analyze と異なり、まず「explain plan for ～」で実行計画を登録し、その後、「SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());」で参照します。

参考として、出力結果は以下に記載していますが、実際に動かしながら、確認する方が理解しやすいと思います。

SQL> explain plan for
select count(*)
from test_tbl_1 t_1
join test_tbl_2 t_2 on (t_1.num_1=t_2.num_1)
where t_1.str_1 like 'A%';  2    3    4    5  
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3343162413

------------------------------------------------------------------------------
| Id |Operation              |Name       |Rows |Bytes| Cost (%CPU)| Time     |
------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
|  0 |SELECT STATEMENT       |           |    1|   14|   528   (2)| 00:00:01 |
|  1 | SORT AGGREGATE        |           |    1|   14|            |          |
|* 2 |  HASH JOIN            |           | 2559|35826|   528   (2)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL   |TEST_TBL_1 | 2559|23031|    12   (0)| 00:00:01 |
|  4 |   INDEX FAST FULL SCAN|SYS_C007452|1000K|4882K|   514   (1)| 00:00:01 |
------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
   2 - access("T_1"."NUM_1"="T_2"."NUM_1")
   3 - filter("T_1"."STR_1" LIKE 'A%')

「explain plan for ～」の他、実際にSQLを実行した結果から表示する「set autotrace on;」もあります。

set autotrace on;
select * from test_tbl_2 where str_1 like 'A%';
set autotrace off;

チューニングの為のTIPS (対策実施)

ヒント句による index 指定

mysqlでは「using index～」のように記載していましたが、 oracleではヒント句において「/+ INDEX(～～) /」のように指定します。

以下の例では、先程のSELECT文では「TABLE ACCESS FULL」がありましたが「INDEX RANGE SCAN」で効率化されていることが分かります。

SQL>  explain plan for
SELECT /*+ INDEX(tbl TEST_TBL_2_STR_1) */ *
from TEST_TBL_2 tbl
where str_1 = 'ABCD';
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) ;
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 3456234334

------------------------------------------------------------------------------------------
|Id |Operation                           |Name            |Rows|Bytes|Cost(%CPU)|Time    |
------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
|  0|SELECT STATEMENT                    |                |3959|75221|2330   (0)|00:00:01|
|  1| TABLE ACCESS BY INDEX ROWID BATCHED|TEST_TBL_2      |3959|75221|2330   (0)|00:00:01|
|* 2|  INDEX RANGE SCAN                  |TEST_TBL_2_STR_1|3959|     |  11   (0)|00:00:01|
------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STR_1"='ABCD')

オプティマイザ統計情報の移行

RDBMSは統計情報を参照し、実行計画を作成しますが、開発環境<->本番環境等、オプティマイザ統計情報の移行を行った方が良いケースもあります。

試していませんが、以下のurlが分かりやすいです。

qiita.com

パーティショニング

別のエントリに記載しました

oracle database 12c の range パーティショニングを試す - end0tknr's kipple - web写経開発

end0tknr's kipple - web写経開発

太宰府天満宮の狛犬って、妙にカワイイ

oracle 12c における sql チューニング - 「explain plan for」「V$SQLSTATS」等