1. データベース管理者のための主要20c新機能の学習
  2. データベース管理者のための主要20c新機能の学習
  3. パフォーマンスおよび高可用性のオプション
  4. Oracle Database In-Memory
  5. インメモリー・ハイブリッド・スキャン
  6. 演習: 問合せでのインメモリー・ハイブリッド・スキャンの使用

演習: 問合せでのインメモリー・ハイブリッド・スキャンの使用

この演習では、INMEMORY列とNO INMEMORY列の両方を参照する問合せが列データにアクセスする方法を示します。IMハイブリッド・スキャンと呼ばれるこのオプティマイザ・アクセス・メソッドを使用すると、パフォーマンスを桁違いに向上させることができます。オプティマイザが表スキャンを選択した場合、記憶域エンジンは、バッファー・キャッシュからの通常の行ストア・スキャンよりもIMハイブリッド・スキャンの方がパフォーマンスが優れているかどうかを自動的に判断します。

  1. 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
  2. 次の条件が満たされるときに、オプティマイザはハイブリッド・スキャンを考慮に入れます。
    • 述語にはINMEMORY列のみが含まれていること。
    • SELECTリストには、INMEMORY列とNO INMEMORY列の任意の組合せが含まれていること。
  3. INMEMORY列とNO INMEMORY列が含まれるインメモリー表で問合せをテストする前に、/home/oracle/labs/M104783GC10/IM_Hybrid_setup.shを実行します。このシェル・スクリプトでは、IM列ストアを110Mに構成し、2つのINMEMORY列と1つのNO INMEMORY列を含むインメモリー表IMU.IMTABを作成し、最後に表に行を挿入します。このシェル・スクリプトでは、Oracle Database 19cとOracle Database 20cで同じ操作を実行します。
    $ cd /home/oracle/labs/M104783GC10
$ /home/oracle/labs/M104783GC10/IM_Hybrid_setup.sh
SQL*Plus: Release 20.0.0.0.0 - Production on Thu Jan 9 03:51:59 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  851442944 bytes
Fixed Size                  9571584 bytes
Variable Size             331350016 bytes
Database Buffers          385875968 bytes
Redo Buffers                7204864 bytes
In-Memory Area            117440512 bytes
Database mounted.
SQL> ALTER SYSTEM SET sga_target=812M SCOPE=spfile;

System altered.

SQL> ALTER SYSTEM SET inmemory_size=110M SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  851442944 bytes
Fixed Size                  9571584 bytes
Variable Size             331350016 bytes
Database Buffers          385875968 bytes
Redo Buffers                7204864 bytes
In-Memory Area            117440512 bytes
Database mounted.
Database opened.
SQL> ALTER PLUGGABLE DATABASE pdb20 OPEN;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL*Plus: Release 20.0.0.0.0 - Production on Thu Jan 9 03:53:36 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL> ALTER SYSTEM SET db_create_file_dest='';

System altered.

SQL> DROP USER imu CASCADE;

User dropped.

SQL> DROP TABLESPACE imtbs INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TABLESPACE imtbs DATAFILE '/home/oracle/labs/imtbs1.dbf' SIZE 500M;

Tablespace created.

SQL> CREATE USER imu IDENTIFIED BY password DEFAULT TABLESPACE imtbs;

User created.

SQL> GRANT create session, create table, unlimited tablespace TO imu;

Grant succeeded.

SQL>
SQL> CREATE TABLE imu.imtab (c1_noinmem NUMBER, c2_inmem NUMBER, c3_inmem VARCHAR2(4000))
  2         INMEMORY PRIORITY high MEMCOMPRESS for capacity low NO INMEMORY(c1_noinmem);

Table created.

SQL> INSERT INTO imu.imtab VALUES (3,4,'Test20c');

1 row created.

SQL> INSERT INTO imu.imtab SELECT c1_noinmem + (select max(c1_noinmem) from imu.imtab),
  2                               c2_inmem + (select max(c2_inmem) from imu.imtab),
  3                               c3_inmem|| (select max(c2_inmem) from imu.imtab) FROM imu.imtab;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> /

256 rows created.

SQL> /

512 rows created.

SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.

SQL> /

16384 rows created.

SQL> /

32768 rows created.

SQL> /

65536 rows created.

SQL> /

131072 rows created.

SQL> COMMIT;

Commit complete.

SQL> exit
Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0
$
  4. SYSTEMとしてPDB20に接続し、問合せ対象の列の書式を設定します。
    $ sqlplus system@PDB20
SQL*Plus: Release 20.0.0.0.0 - Development on Thu Jan 9 04:08:41 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: password
Last Successful login time: Wed Jan 08 2020 12:03:56 +00:00

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0
SQL> COL table_name FORMAT A10
SQL> COL inmemory_compression FORMAT A11
SQL> COL COL_NO_INMEM FORMAT 9999999999999999999999
SQL> COL COL_INMEM FORMAT 9999999999999999999999
SQL> COL segment_name FORMAT A12
SQL>
  5. IMU.IMTAB表および表のすべての列のインメモリー属性を表示します。
    SQL> SELECT table_name, inmemory_compression "COMPRESSION", inmemory_priority "PRIORITY"
FROM   dba_tables WHERE owner='IMU';

TABLE_NAME COMPRESSION       PRIORITY
---------- ----------------- --------
IMTAB      FOR CAPACITY LOW  HIGH
 
SQL> SELECT obj_num, segment_column_id, inmemory_compression FROM v$im_column_level im, dba_objects o
WHERE  im.obj_num = o.object_id
AND    o.object_name='IMTAB';

   OBJ_NUM SEGMENT_COLUMN_ID INMEMORY_CO
---------- ----------------- -----------
     74869                 1 NO INMEMORY
     74869                 2 DEFAULT
     74869                 3 DEFAULT

SQL>
  6. IM列ストアに表を移入するために、IMU.IMTAB表で全体スキャンを実行します。
    SQL> SELECT /*+ FULL(imu.imtab) NO_PARALLEL(imu.imtab) */ COUNT(*) FROM imu.imtab;

  COUNT(*)
----------
    262144

SQL>
  7. IMU.IMTAB表がIM列ストアに移入されていることを確認します。
    SQL> COL segment_name FORMAT A12
SQL> SELECT segment_name, bytes, inmemory_size, bytes_not_populated
FROM   v$im_segments;


SEGMENT_NAME      BYTES INMEMORY_SIZE BYTES_NOT_POPULATED
------------ ---------- ------------- -------------------
IMTAB          17481728       4456448                   0

SQL>
  8. IMU.IMTAB表に対して最初の問合せを実行します。SELECTリストにはNO INMEMORY列が含まれ、述語にはNO INMEMORY列のみが含まれています。次に、実行計画を確認します。
    SQL> SELECT sum(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab
WHERE  c1_noinmem BETWEEN 5 AND 1258291;

           COL_NO_INMEM
-----------------------
           103079608317

SQL> SELECT * FROM table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID  1dpya5ws8gbvx, child number 0
-------------------------------------
SELECT sum(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab WHERE  c1_noin
mem BETWEEN 5 AND 1258291

Plan hash value: 360700294
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |   547 (100)|       |
|   1 |  SORT AGGREGATE    |       |     1 |    13 |            |       |
|*  2 |   TABLE ACCESS FULL| IMTAB |   292K|  3712K|   547   (1)| 00 :00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("C1_NOINMEM">=5 AND "C1_NOINMEM"<=1258291))
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

24 rows selected.

SQL>

    両方のセッションのオプティマイザでは、述語にINMEMORY列のみが含まれているわけではないため、TABLE ACCESS FULLメソッドを選択します。

  9. IMU.IMTAB表に対して2番目の問合せを実行します。SELECTリストにはNO INMEMORY列が含まれ、述語にはNO INMEMORY列とINMEMORY列の両方が含まれます。次に、実行計画を確認します。
    SQL> SELECT sum(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab
WHERE  c1_noinmem BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test20c%';

           COL_NO_INMEM
-----------------------
           103079608317

SQL> SELECT * FROM table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID  afz9bm3rscr3y, child number 0
-------------------------------------
SELECT sum(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab WHERE  c1_noinmem
BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test20c%'

Plan hash value: 360700294
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |   582 (100)|       |
|   1 |  SORT AGGREGATE    |       |     1 |  2015 |            |       |
|*  2 |   TABLE ACCESS FULL| IMTAB |   230K|   443M|   582   (1)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("C1_NOINMEM">=5 AND "C1_NOINMEM"<=1258291 AND "C3_INMEM" LIKE 'Test20c%'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.

SQL>

    両方のセッションのオプティマイザでは、TABLE ACCESS FULLアクセス・メソッドを選択します。述語にINMEMORY列のみが含まれているわけではないためです。これには、INMEMORY列およびNO INMEMORY列が含まれています。

  10. IMU.IMTAB表に対して3番目の問合せを実行します。SELECTリストにはNO INMEMORY列が含まれ、述語にはINMEMORY列のみが含まれます。次に、実行計画を確認します。
    SQL> SELECT sum(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab 
WHERE  c2_inmem BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test20c%';

           COL_NO_INMEM
-----------------------
           103079608317

SQL> SELECT * FROM table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID  f07n4gc330rhz, child number 0
-------------------------------------
SELECT sum(c1_noinmem) AS COL_NO_INMEM FROM imu.imtab WHERE  c2_inmem
BETWEEN 5 AND 1258291 AND c3_inmem LIKE 'Test20c%'

Plan hash value: 360700294

-----------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       | |   582 (100)|          |
|   1 |  SORT AGGREGATE                      |       |     1 |  2028 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL (HYBRID)| IMTAB |   230K|   445M|   582   (1)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("C2_INMEM">=5 AND "C2_INMEM"<=1258291 AND "C3_INMEM"
LIKE 'Test20c%'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

24 rows selected.

SQL>

    両方のセッションのオプティマイザは、異なるアクセス・メソッドを選択します。20cでは、述語にINMEMORY列のみが含まれ、SELECTリストにNO INMEMORY列が含まれているため、TABLE ACCESS INMEMORY FULL (HYBRID)アクセス・メソッドが選択されます。

  11. IMUユーザーを削除します。
    SQL> DROP USER imu CASCADE;

User dropped.

SQL> EXIT
$

親トピック: インメモリー・ハイブリッド・スキャン