この記事は約23分7秒で読むことができます。

索引の使用状況の監視

バージョン


[oracle@centos7 ~]$ docker exec -it orcl_18cr3 bash
[oracle@d102981b4117 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 13 01:41:52 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 13 2019 01:19:00 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

テストデータ


PURGE RECYCLEBIN;

DROP TABLE warehouse_mst PURGE;
CREATE TABLE warehouse_mst AS
SELECT DISTINCT
    rpad('w_',6,dbms_random.STRING('X',4)) AS warehouse_code
	,TRUNC(ABS(dbms_random.VALUE(10,20)),0) AS shipping_permission_over_days
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE warehouse_mst ADD CONSTRAINT warehouse_mst_pk PRIMARY KEY(warehouse_code);

DROP TABLE item_mst PURGE;
CREATE TABLE item_mst AS
SELECT DISTINCT
    s1.warehouse_code
    ,rpad('p_',6,dbms_random.STRING('X',4)) AS item
    ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
    ,dbms_random.STRING('U',2) || lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),5,'0') AS main_loc
FROM
    warehouse_mst s1
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE item_mst ADD CONSTRAINT item_mst_pk PRIMARY KEY(warehouse_code,item);

DROP TABLE loc_mst PURGE;
CREATE TABLE loc_mst AS 
SELECT DISTINCT
	warehouse_code
    ,main_loc AS loc
	,lpad(ROWNUM,2,'0') AS ZONE
	,substr(main_loc,1,2) AS BLOCK
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),3,'0') AS area
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),2,'0') AS LINE
FROM
    item_mst
;

ALTER TABLE loc_mst ADD CONSTRAINT loc_mst_pk PRIMARY KEY(warehouse_code,loc);

DROP TABLE zone_mst PURGE;
CREATE TABLE zone_mst AS 
SELECT DISTINCT
    warehouse_code
	,ZONE
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS zone_kbn
FROM
    loc_mst
;

ALTER TABLE zone_mst ADD CONSTRAINT zone_mst_pk PRIMARY KEY(warehouse_code,ZONE);

DROP TABLE cust_mst PURGE;
CREATE TABLE cust_mst AS 
SELECT
    rpad('c_',6,dbms_random.STRING('X',4)) AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cust_kbn
	,'bas' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS basket_no
	,'car' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS cart_no
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE cust_mst ADD  CONSTRAINT cust_mst_pk PRIMARY KEY(cust_id) ;

DROP TABLE basket_mst PURGE;
CREATE TABLE basket_mst AS 
SELECT DISTINCT
    basket_no
	,TRUNC(ABS(dbms_random.VALUE(2000,5000)),0) AS upper_limit_weight
	,TRUNC(ABS(dbms_random.VALUE(2000,4000)),0) AS upper_limit_volume
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS basket_kbn
FROM
    cust_mst
;

ALTER TABLE basket_mst ADD  CONSTRAINT basket_mst_pk PRIMARY KEY(basket_no) ;

DROP TABLE cart_mst PURGE;
CREATE TABLE cart_mst AS 
SELECT DISTINCT
    cart_no
	,TRUNC(ABS(dbms_random.VALUE(1,20)),0) AS upper_stack_cnt
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cart_kbn
FROM
    cust_mst
;

ALTER TABLE cart_mst ADD  CONSTRAINT cart_mst_pk PRIMARY KEY(cart_no) ;

DROP TABLE route_mst PURGE;
CREATE TABLE route_mst AS 
SELECT DISTINCT
	warehouse_code
	,rpad('r_',6,dbms_random.STRING('X',4)) AS route_no
	,lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS barth_no
FROM
    warehouse_mst
;

ALTER TABLE route_mst ADD CONSTRAINT route_mst_pk PRIMARY KEY(warehouse_code,route_no);

DROP TABLE store_mst PURGE;
CREATE TABLE store_mst AS
SELECT DISTINCT
	rpad('s_',6,dbms_random.STRING('X',4)) AS store_no
	,s1.route_no AS route_no
    ,lpad(TRUNC(ABS(dbms_random.VALUE(1,99)),0),2,'0') AS route_order
    ,s2.cust_id AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS store_kbn
FROM
    route_mst s1
	,cust_mst s2
;

ALTER TABLE store_mst ADD CONSTRAINT store_mst_pk PRIMARY KEY(store_no);

DROP TABLE stock PURGE;
CREATE TABLE stock AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    s0.item
    ,s1.warehousing_date AS warehousing_date
    , s2.expiration_date AS expiration_date
    , TRUNC(ABS(dbms_random.VALUE(100, 300)), 0) AS stock_qty
    , TRUNC(ABS(dbms_random.VALUE(10, 90)), 0) AS reserved_stock_qty
FROM
    item_mst s0
    ,(SELECT s1.std_date,to_char(TO_DATE(s1.std_date) - INTERVAL '10' DAY,'YYYYMMDD') AS warehousing_date FROM make_date s1) s1
    , LATERAL(SELECT s2.std_date,to_char(TO_DATE(s2.std_date) + INTERVAL '30' DAY,'YYYYMMDD') AS expiration_date FROM make_date s2 WHERE s1.std_date <= s2.std_date) s2
;

ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY(item,warehousing_date,expiration_date);

DROP TABLE sales_trn PURGE;
CREATE TABLE sales_trn AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    ROWNUM AS seq
    ,s0.item
    ,TRUNC(ABS(dbms_random.VALUE(10,100)),0) AS prov_inst_qty
    ,s1.cust_id
    ,s1.store_no
    ,s2.std_date AS arrive_date
FROM
    item_mst s0
    ,store_mst s1
    ,make_date s2
;

ALTER TABLE sales_trn ADD  CONSTRAINT sales_trn_pk PRIMARY KEY(seq);

索引を監視対象にする

監視対象前


SELECT
	index_name
	, table_name
	, monitoring
	, used
	, start_monitoring
	, end_monitoring
FROM
	v$object_usage;

監視対象に追加


SELECT 'alter index ' || index_name || ' monitoring usage;' AS SQL FROM user_indexes;

alter index WAREHOUSE_MST_PK monitoring usage;
alter index ITEM_MST_PK monitoring usage;
alter index LOC_MST_PK monitoring usage;
alter index ZONE_MST_PK monitoring usage;
alter index CUST_MST_PK monitoring usage;
alter index BASKET_MST_PK monitoring usage;
alter index CART_MST_PK monitoring usage;
alter index ROUTE_MST_PK monitoring usage;
alter index STORE_MST_PK monitoring usage;
alter index STOCK_PK monitoring usage;
alter index SALES_TRN_PK monitoring usage;

監視対象後


SELECT
	index_name
	, table_name
	, monitoring
	, used
	, start_monitoring
	, end_monitoring
FROM
	v$object_usage;

USE列の値が「NO」の場合は未使用、「YES」の場合は使用。

ここで、インデックスを使用するSQLを発行してみる。

インデックスを使用するSQLの発行


SELECT
	*
FROM
	cust_mst s1
	,store_mst s2
WHERE
	s1.cust_id = s2.cust_id
AND s1.cust_kbn IN ('7','3')
;

SELECT
    *
FROM
    TABLE ( dbms_xplan.display_cursor(format => 'ALIAS PREDICATE') );

SQL_ID  02nta7akrw02m, child number 0
-------------------------------------
SELECT  * FROM  cust_mst s1  ,store_mst s2 WHERE  s1.cust_id = 
s2.cust_id AND s1.cust_kbn IN ('7','3')
 
Plan hash value: 2869855552
 
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |     6 (100)|          |
|   1 |  MERGE JOIN                  |             |     6 |   282 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| CUST_MST    |     2 |    42 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | CUST_MST_PK |     3 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |             |     9 |   234 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | STORE_MST   |     9 |   234 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / S1@SEL$1
   3 - SEL$1 / S1@SEL$1
   5 - SEL$1 / S2@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("S1"."CUST_KBN"='3' OR "S1"."CUST_KBN"='7'))
   4 - access("S1"."CUST_ID"="S2"."CUST_ID")
       filter("S1"."CUST_ID"="S2"."CUST_ID")

SQL発行後


SELECT
	index_name
	, table_name
	, monitoring
	, used
	, start_monitoring
	, end_monitoring
FROM
	v$object_usage;

CUST_MST_PKのUSE列の値が「YES」に変化した。

索引を監視対象からはずす


SELECT 'alter index ' || index_name || ' nomonitoring usage;' AS SQL FROM user_indexes;

alter index WAREHOUSE_MST_PK nomonitoring usage;
alter index ITEM_MST_PK nomonitoring usage;
alter index LOC_MST_PK nomonitoring usage;
alter index ZONE_MST_PK nomonitoring usage;
alter index CUST_MST_PK nomonitoring usage;
alter index BASKET_MST_PK nomonitoring usage;
alter index CART_MST_PK nomonitoring usage;
alter index ROUTE_MST_PK nomonitoring usage;
alter index STORE_MST_PK nomonitoring usage;
alter index STOCK_PK nomonitoring usage;
alter index SALES_TRN_PK nomonitoring usage;

SELECT
	index_name
	, table_name
	, monitoring
	, used
	, start_monitoring
	, end_monitoring
FROM
	v$object_usage;

監視対象からはずした索引のEND_MONITORING列に終了時刻が入っている

Leave a Reply

Your email address will not be published. Required fields are marked *