目的
テーブルの統計情報をLOCK_TABLE_STATSを使用してロックすることはよくあるが、 特定のパーティションの統計情報をロックする方法のメモ。
マニュアル
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c
171.7.79 LOCK_PARTITION_STATSプロシージャ
このプロシージャによって、ユーザーはパーティションの統計情報をロックできます。
構文
DBMS_STATS.LOCK_PARTITION_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2);
パラメータ
表171-83 LOCK_PARTITION_STATSプロシージャのパラメータ
パラメータ 説明
ownname ロックするスキーマの名前。
tabname 表の名前
partname (サブ・)パーティション名。
事前準備
パーティション表の準備
SQL> DROP TABLE T1 PURGE; Table dropped. SQL> CREATE TABLE T1 (C1 NUMBER, C2 DATE, C3 VARCHAR2(100) , CONSTRAINT PK_T4 PRIMARY KEY (C1)) PARTITION BY RANGE(C2) SUBPARTITION BY HASH (C1) SUBPARTITIONS 4 ( PARTITION PRT_202104 VALUES LESS THAN(TO_DATE('202105','YYYYMM')), PARTITION PRT_202105 VALUES LESS THAN(TO_DATE('202106','YYYYMM')), PARTITION PRT_202106 VALUES LESS THAN(TO_DATE('202107','YYYYMM')), PARTITION PRT_202107 VALUES LESS THAN(TO_DATE('202108','YYYYMM')) ); 2 3 4 5 6 7 Table created. SQL> CREATE INDEX T1IDX ON T1 (C2) LOCAL (PARTITION I1 (SUBPARTITION I1_H1, SUBPARTITION I1_H2, SUBPARTITION I1_H3, SUBPARTITION I1_H4), PARTITION I2 (SUBPARTITION I2_H1, SUBPARTITION I2_H2, SUBPARTITION I2_H3, SUBPARTITION I2_H4), PARTITION I3 (SUBPARTITION I3_H1, SUBPARTITION I3_H2, SUBPARTITION I3_H3, SUBPARTITION I3_H4), PARTITION I4 (SUBPARTITION I4_H1, SUBPARTITION I4_H2, SUBPARTITION I4_H3, SUBPARTITION I4_H4)); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Index created. SQL> CREATE INDEX T1GIDX ON t1 (C3); Index created. SQL> drop sequence s1; Sequence dropped. SQL> create sequence s1; Sequence created. SQL> declare varDate date; varSeq NUMBER; begin varDate := to_date('2021-04-01', 'YYYY-MM-DD'); for j in 1..4 loop for i in 1..10000 loop select s1.nextval into varSeq from dual; insert into T1 values(varSeq, varDate, lpad(to_char(varSeq), 100, '0')); end loop; commit; varDate := add_months(varDate, 1); end loop; end; / 2 3 4 5 6 7 8 9 10 11 12 13 14 15 PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'T1',DEGREE => 10,NO_INVALIDATE=>FALSE); PL/SQL procedure successfully completed.
実行例
特定のパーティションの統計をロック
SQL> SELECT OWNER,OBJECT_TYPE,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYY/MM/DD HH24:MI:SS') LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'T1' ORDER BY PARTITION_NAME,SUBPARTITION_NAME; 2 OWNER OBJECT_TYPE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS LAST_ANALYZED STATTYPE_LOCKED STALE_STATS -------------------- ------------ -------------------- -------------------- -------------------- ---------- ------------------- -------------------- -------------------- SCOTT SUBPARTITION T1 PRT_202104 SYS_SUBP3579 SCOTT SUBPARTITION T1 PRT_202104 SYS_SUBP3580 SCOTT SUBPARTITION T1 PRT_202104 SYS_SUBP3581 SCOTT SUBPARTITION T1 PRT_202104 SYS_SUBP3582 SCOTT PARTITION T1 PRT_202104 10000 2022/07/15 20:21:19 NO SCOTT SUBPARTITION T1 PRT_202105 SYS_SUBP3583 SCOTT SUBPARTITION T1 PRT_202105 SYS_SUBP3584 SCOTT SUBPARTITION T1 PRT_202105 SYS_SUBP3585 SCOTT SUBPARTITION T1 PRT_202105 SYS_SUBP3586 SCOTT PARTITION T1 PRT_202105 10000 2022/07/15 20:21:19 NO SCOTT SUBPARTITION T1 PRT_202106 SYS_SUBP3587 SCOTT SUBPARTITION T1 PRT_202106 SYS_SUBP3588 SCOTT SUBPARTITION T1 PRT_202106 SYS_SUBP3589 SCOTT SUBPARTITION T1 PRT_202106 SYS_SUBP3590 SCOTT PARTITION T1 PRT_202106 10000 2022/07/15 20:21:19 NO SCOTT SUBPARTITION T1 PRT_202107 SYS_SUBP3591 SCOTT SUBPARTITION T1 PRT_202107 SYS_SUBP3592 SCOTT SUBPARTITION T1 PRT_202107 SYS_SUBP3593 SCOTT SUBPARTITION T1 PRT_202107 SYS_SUBP3594 SCOTT PARTITION T1 PRT_202107 10000 2022/07/15 20:21:19 NO SCOTT TABLE T1 40000 2022/07/15 20:21:19 NO 21 rows selected. SQL> exec dbms_stats.lock_partition_stats(ownname=> 'SCOTT', tabname=> 'T1', partname=> 'PRT_202104'); PL/SQL procedure successfully completed. SQL> SELECT OWNER,OBJECT_TYPE,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYY/MM/DD HH24:MI:SS') LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'T1' ORDER BY PARTITION_NAME,SUBPARTITION_NAME; 2 OWNER OBJECT_TYPE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS LAST_ANALYZED STATTYPE_LOCKED STALE_STATS -------------------- ------------ -------------------- -------------------- -------------------- ---------- ------------------- -------------------- -------------------- SCOTT SUBPARTITION T1 PRT_202104 SYS_SUBP3579 ALL ★ SCOTT SUBPARTITION T1 PRT_202104 SYS_SUBP3580 ALL ★ SCOTT SUBPARTITION T1 PRT_202104 SYS_SUBP3581 ALL ★ SCOTT SUBPARTITION T1 PRT_202104 SYS_SUBP3582 ALL ★ SCOTT PARTITION T1 PRT_202104 10000 2022/07/15 20:21:19 ALL ★ NO SCOTT SUBPARTITION T1 PRT_202105 SYS_SUBP3583 SCOTT SUBPARTITION T1 PRT_202105 SYS_SUBP3584 SCOTT SUBPARTITION T1 PRT_202105 SYS_SUBP3585 SCOTT SUBPARTITION T1 PRT_202105 SYS_SUBP3586 SCOTT PARTITION T1 PRT_202105 10000 2022/07/15 20:21:19 NO SCOTT SUBPARTITION T1 PRT_202106 SYS_SUBP3587 SCOTT SUBPARTITION T1 PRT_202106 SYS_SUBP3588 SCOTT SUBPARTITION T1 PRT_202106 SYS_SUBP3589 SCOTT SUBPARTITION T1 PRT_202106 SYS_SUBP3590 SCOTT PARTITION T1 PRT_202106 10000 2022/07/15 20:21:19 NO SCOTT SUBPARTITION T1 PRT_202107 SYS_SUBP3591 SCOTT SUBPARTITION T1 PRT_202107 SYS_SUBP3592 SCOTT SUBPARTITION T1 PRT_202107 SYS_SUBP3593 SCOTT SUBPARTITION T1 PRT_202107 SYS_SUBP3594 SCOTT PARTITION T1 PRT_202107 10000 2022/07/15 20:21:19 NO SCOTT TABLE T1 40000 2022/07/15 20:21:19 NO 21 rows selected.
指定したパーティションと紐づくサブパーティションがロックされている
