一意制約違反

システムの「心臓」こと、Oracle Databaseに日々携わるたろーちゃん。
ある日、隣の部署のDさんがたろーちゃんの元に訪れました。
何かあったのでしょうか？

Dさん「ごめん、たろーちゃん。また Statspack のことで聞いてもいいかな？」
たろー「はい、どうされました？」
Dさん「この前、診てもらったシステムの Statspack なんだけど、
今度はスナップショット取得時に ORA-00001 が発生して困ってるんだ。」
※Dさんのシステムについては、以前の記事『Statspackの領域』を参照してください。

たろー「Statspack で、 ORA-00001 ですか？」
Dさん「そうなんだ。これを見てくれ。」
 

SQL>exec statspack.snap

BEGIN statspack.snap; END;
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(PERFSTAT.STATS$MUTEX_SLEEP_PK)に反しています
ORA-06512: "PERFSTAT.STATSPACK", 行5264
ORA-06512: "PERFSTAT.STATSPACK", 行104
ORA-06512: 行1

 
Dさん「こんな風に、一意制約違反って言われるんだ。スナップショットの取得で一意制約違反と言われてもなぁ・・・どうしろって言うんだろ？
過去のスナップショットを全て消さないとダメなんだろうか。もしそうなら、困るんだが・・・。」
たろー「あー、これですか。これはStatspackの不具合ですね。」
Dさん「なに！不具合？！」
たろー「はい。StatspackにSTATS$MUTEX_SLEEPっていうテーブルがあるんですけど、statspack.snapプロシージャでそのテーブルにINSERTするときに、主キーが重複してしまうことがあるんです。ちょっと失礼します。」
 

■主キー制約とインデックスの確認
SQL> SELECT OWNER,CONSTRAINT_NAME,INDEX_NAME,STATUS
  FROM DBA_CONSTRAINTS
 WHERE TABLE_NAME='STATS$MUTEX_SLEEP'
   AND CONSTRAINT_NAME='STATS$MUTEX_SLEEP_PK'
;

OWNER      CONSTRAINT_NAME                INDEX_NAME                     STATUS
---------- ------------------------------ ------------------------------ --------
PERFSTAT   STATS$MUTEX_SLEEP_PK           STATS$MUTEX_SLEEP_PK           ENABLED


■対象インデックスが一意インデックスであることを確認
SQL> SELECT INDEX_NAME, UNIQUENESS
  FROM DBA_INDEXES
 WHERE TABLE_OWNER='PERFSTAT'
   AND TABLE_NAME='STATS$MUTEX_SLEEP'
;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
STATS$MUTEX_SLEEP_PK           UNIQUE

■対象インデックスを構成しているカラムを確認
SQL> SELECT COLUMN_POSITION, COLUMN_NAME
  FROM DBA_IND_COLUMNS
 WHERE INDEX_OWNER='PERFSTAT'
   AND INDEX_NAME='STATS$MUTEX_SLEEP_PK'
 ORDER BY COLUMN_POSITION
;

COLUMN_POSITION COLUMN_NAME
--------------- --------------------
              1 SNAP_ID
              2 DBID
              3 INSTANCE_NUMBER
              4 MUTEX_TYPE
              5 LOCATION

■NOT NULL 制約を確認
SQL> desc STATS$MUTEX_SLEEP
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 SNAP_ID                                   NOT NULL NUMBER
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 MUTEX_TYPE                                NOT NULL VARCHAR2(32)
 LOCATION                                  NOT NULL VARCHAR2(40)
 SLEEPS                                             NUMBER
 WAIT_TIME                                          NUMBER

 
たろー「このように、STATS$MUTEX_SLEEPテーブルはSNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATIONで主キーが構成されているんですよ。」
Dさん「どう対処すればいいのかな？」
たろー「主キーが重複してしまっているので、それを無効にしてやるしかありません。
今、主キーは有効になっています。」
 

SQL> SELECT OWNER,CONSTRAINT_NAME,STATUS
  FROM DBA_CONSTRAINTS
 WHERE TABLE_NAME='STATS$MUTEX_SLEEP'
   AND CONSTRAINT_NAME='STATS$MUTEX_SLEEP_PK'
;

   OWNER      CONSTRAINT_NAME                STATUS
   ---------- ------------------------------ --------
   PERFSTAT   STATS$MUTEX_SLEEP_PK           ENABLED

 
Dさん「うむ。」
たろー「これを無効にしてやるんです。」
 

SQL> alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;

SQL> SELECT OWNER,CONSTRAINT_NAME,STATUS
  FROM DBA_CONSTRAINTS
 WHERE TABLE_NAME='STATS$MUTEX_SLEEP'
   AND CONSTRAINT_NAME='STATS$MUTEX_SLEEP_PK'
;

   OWNER      CONSTRAINT_NAME                STATUS
   ---------- ------------------------------ --------
   PERFSTAT   STATS$MUTEX_SLEEP_PK           DISABLED

 
Dさん「ほう。」
たろー「こうすれば、スナップショットが取得できますよ。」
 

SQL> exec statspack.snap
PL/SQLプロシージャが正常に完了しました。

 
Dさん「本当だ。でも主キーを無効にしてしまって大丈夫なのか？」
たろー「主キーを無効にするということは、重複行が出来てしまうことになります。
具体的な影響としてはStatspackレポート出力時に、以下のように重複した項目が表示されてしまうんです。」
 

Mutex Sleep  DB/Inst: DB32/DB32  Snaps: 101-102
-> ordered by Wait Time desc

                                                                Wait
Mutex Type         Location                     Sleeps          Time (s)
------------------ ---------------------------- -------------- ------------
Cursor Pin         kkslce [KKSCHLPIN2]                      21          0.0
Cursor Pin         kkslce [KKSCHLPIN2]                     121          0.0

 
Dさん「うーむ、見栄えは悪いけど、仕方ないのか・・・。」
たろー「・・・これは推測なんですけど、statspack.snapプロシージャの中で STATS$MUTEX_SLEEP に INSERT している所がある筈なんですが、本来ならINSERTじゃなくてUPSERT(MERGE)にしなければならないんじゃないですかね？
さすがにプロシージャまでは直せませんが・・・。」
Dさん「オラクル社は直してくれないのかな？」
たろー「何か直せない事情があるのだと思いますよ。」
Dさん「うーむ、分かった。とりあえず、これで運用していくよ。」
たろー「あ！待って下さい、先輩。
主キーを無効にしたので、インデックスが利かないので、今度は Statspack レポートを作成する時に時間がかかるようになります。なので、My Oracle Support のドキュメントID 1743857.1の方法で非一意インデックスを作成するようにしてください。」
Dさん「分かった。ありがとう、たろーちゃん！」

---

こうしてDさんのシステムでは、Statspack で ORA-00001 が発生することは無くなりました。

今回の「心臓外科医の術式」いかがだったでしょうか？
長い間 Statspack を運用していると、このような事象に遭遇されるDBAの方もいらっしゃるのではないでしょうか？
Standard Edition では AWR を使うことが出来ず、Statspack のみがパフォーマンス分析を行う上で生命線となってきます。
その Statspack が使えないとなると死活問題です。

そんな時は今回ご紹介した方法をお試し下さい。
次回も頑張りますので、応援よろしくお願い致します。

投稿者プロフィール

DBひとりでできるもん運営チーム

「DBひとりでできるもん」運営チームです。
「親しみやすさと技術力」をテーマに、技術情報・サービス・インフラ系資格取得に役立つ情報、社員等の情報をお届けします。
70名弱の事業部員で鋭意、執筆中です。
少しでも当社を知って頂けるよう、愛情込めて頑張ります！
※facebook、X(旧twitter)、インスタグラムでは「DBひとりでできるもん」の更新情報を発信しています。

• Dbvisit Standby2024年11月18日【再掲載】【Dbvisit Standby】DRテスト機能のご紹介
• Dbvisit Standby2024年11月15日【再掲載】【Dbvisit Standby】メール通知機能のご紹介
• Dbvisit Standby2024年11月14日【再掲載】【Dbvisit Standby】製品の保守(サポート)について
• Dbvisit Standby2024年11月13日【再掲載】【Dbvisit Standby】製品アーキテクチャ（Auto Failover）