タグ: SQL

投稿日:

Oracleに負荷をかけているプログラムを探す

Oracleに負荷をかけているプログラムを探す

OracleサーバのCPUの1つのコアが100%利用されている。
LinuxのtopコマンドでoracleORCL(LOCAL=NO)が利用しているのはわかったが、どこからのアクセスでそうなっているのかわからない。
LinuxのプロセスIDからアクセス元を特定できないものか。

V$PROCESS

V$PROCESS.SPIDがプロセスIDのようだ。

1
2
3
4
5
6
SELECT
    *
FROM
    V$PROCESS
WHERE
    SPID IN (15254)

SPIDに指定しているのはtopでCPUを100%使用していたプロセスのプロセスID。
しかし、V$PROCESSだけでは、プログラム名や接続元が取得できない。
V$SESSIONを結合する。

V$SESSION

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    S.PROGRAM,
    S.USERNAME, -- ORACLE接続ユーザ
    S.OSUSER, -- OSユーザ
    S.MACHINE, -- 接続元ホスト名
    S.PROCESS, -- 接続元ホストでのプロセスID（取得できれば）
    S.EVENT
FROM
    V$PROCESS P
    JOIN V$SESSION S
    ON P.ADDR = S.PADDR
WHERE
    P.SPID = 19391;

これで、接続元の情報が取得できた。
なお、動的ディクショナリビュー同士の結合は推奨されないようだ。

MACHINE列に表示されたサーバに接続し、そのサーバでプロセスプロセスIDを検索する。これで犯人がわかった。
ちなみに、プロセスIDの取得ができない場合は1234が設定されるようだ。

接続元情報を得る

この際、負荷の高いプロセスの接続元だけではなく、すべて表示してみる。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    S.PROGRAM,
    S.USERNAME,
    S.OSUSER,
    S.MACHINE,
    S.PROCESS,
    S.EVENT,
    P.PROGRAM,
    P.SPID
FROM
    V$PROCESS P
    JOIN V$SESSION S
    ON P.ADDR = S.PADDR
ORDER BY
    S.PROGRAM,
    S.MACHINE,
    S.PROCESS;

意外と接続されていることが分かった。

まとめ

運用始めてから5年以上経つOracleが、思いもよらないサーバからアクセスされていることに気が付いてしまった。今回の高負荷の原因とは関係ないアクセスだったが、一応ブロックしておこうか。

同じタグの記事
同じカテゴリの記事
投稿日:

OracleのFLASHBACKテーブルをPURGEする

OracleのFLASHBACKテーブルをPURGEする。

Oracle10gからFLASHBACKが有効になっている場合は、単純にDROP TABLEしても元のテーブルがゴミ箱（RECYCLEBIN）に残っている。
しかし、DROP TABLEなんてテスト環境でしか実行しないので、FLASHBACKはあまり必要ない。
なので、通常はゴミ箱を経由しないようにDROP TABLEしている。

FLASHBACKしないDROP TABLE

1
DROP TABLE TABLE_TEST_01 PURGE;

これで、RECYCLEBINに保存せずにDROPできる。

PURGEを忘れてしまった場合

たまにPURGEを付けるのを忘れてしまい、BIN$…なテーブルが残ってしまう。それを消したいとき。

1
PURGE TABLE TABLE_TEST_01;

とすることで削除することができる。
ゴミ箱内のテーブルがすべて不要であれば、ゴミ箱ごとPURGEできる。

1
PURGE RECYCLEBIN;

まとめ

もし、DROP TABLEが本当に誤りだった場合、FLASHBACK TABLE文でテーブルを元に戻せる。

1
FLASHBACK TABLE TABLE_TEST_01 TO BEFORE DROP;

これで助かることがあるかもしれない。やっぱりFLASHBACHは残しておくべきだろうか・・・。

同じタグの記事
同じカテゴリの記事
投稿日:

sqlite3のデータ型

sqlite3のデータ型

今まで、適当に使っていたsqlite3だが、sqlite3のデータ型は5種類あるようだ。

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

数値はintやnumberとして定義してしまっていたが、これは自動でINTEGERと判断されるようだ。
また、charやvarcharと定義した場合は、TEXTとして扱われるようだ。

sqlite3のデータ型の定義は厳密ではなく、可能な限り定義したタイプに変換して登録される。
データ型が異なるからといって、INSERTやUPDATEがエラーになることはない。

INTEGERの項目にはINTEGERとして登録し、
TEXTの項目にはTEXTとして登録される。
INTEGERの項目に’A’を登録しようとするとTEXTの’A’として登録される。
1.5を登録しようとすると、REALの1.5として登録される。
TEXTの項目は、TEXTに変換され登録される。
INTEGERに’1.5 ‘を登録すると、REAL 1.5 として登録され、
TEXTに’1.5 ‘を登録すると、TEXT ‘1.5 ‘として登録される。

かなり上手に型変換してくれるので、余程間違った登録さえしなければ、
数値は数値として、文字は文字として処理できる。

試しに、いろいろな値を入れてみた

データ投入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    a TEXT,
    b INTEGER,
    c REAL,
    d BLOB,
    e
);
INSERT INTO t1 VALUES ('A',  'A',  'A',  'A',  'A'  );
INSERT INTO t1 VALUES ('B ', 'B ', 'B ', 'B ', 'B ' );
INSERT INTO t1 VALUES ('1',  '1',  '1',  '1',  '1'  );
INSERT INTO t1 VALUES ('2 ', '2 ', '2 ', '2 ', '2 ' );
INSERT INTO t1 VALUES (' 3', ' 3', ' 3', ' 3', ' 3' );
INSERT INTO t1 VALUES (' 4 ',' 4 ',' 4 ',' 4 ',' 4 ');
INSERT INTO t1 VALUES (5,    5,    5,    5,    5    );
INSERT INTO t1 VALUES (-6,   -6,   -6,   -6,   -6   );
INSERT INTO t1 VALUES (7.1,  7.1,  7.1,  7.1,  7.1  );
INSERT INTO t1 VALUES (-8.2, -8.2, -8.2, -8.2, -8.2 );
INSERT INTO t1 VALUES (0.009,0.009,0.009,0.009,0.009);
INSERT INTO t1 VALUES (NULL, NULL, NULL, NULL, NULL );

検索

1
2
3
4
5
6
7
8
9
10
11
.header ON
.mode COLUMN
SELECT
    '|' || a || '|' AS TEXT,    typeof(a) AS T_TEXT,
    '|' || b || '|' AS INTEGER, typeof(b) AS T_INTEGER,
    '|' || c || '|' AS REAL,    typeof(c) AS T_REAL,
    '|' || d || '|' AS BLOB,    typeof(d) AS T_BLOB,
    '|' || e || '|' AS NONE,    typeof(e) AS T_NONE
FROM
    t1
;

結果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
TEXT        T_TEXT      INTEGER     T_INTEGER   REAL        T_REAL      BLOB        T_BLOB      NONE        T_NONE
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
|A|         text        |A|         text        |A|         text        |A|         text        |A|         text
|B |        text        |B |        text        |B |        text        |B |        text        |B |        text
|1|         text        |1|         integer     |1.0|       real        |1|         text        |1|         text
|2 |        text        |2|         integer     |2.0|       real        |2 |        text        |2 |        text
| 3|        text        |3|         integer     |3.0|       real        | 3|        text        | 3|        text
| 4 |       text        |4|         integer     |4.0|       real        | 4 |       text        | 4 |       text
|5|         text        |5|         integer     |5.0|       real        |5|         integer     |5|         integer
|-6|        text        |-6|        integer     |-6.0|      real        |-6|        integer     |-6|        integer
|7.1|       text        |7.1|       real        |7.1|       real        |7.1|       real        |7.1|       real
|-8.2|      text        |-8.2|      real        |-8.2|      real        |-8.2|      real        |-8.2|      real
|0.009|     text        |0.009|     real        |0.009|     real        |0.009|     real        |0.009|     real
            null                    null                    null                    null                    null

まとめ

やはり、なかなかの精度で変換してくれている。
これからも使い続けようと思う。

同じタグの記事
同じカテゴリの記事
投稿日:

手軽なのに強力なsqlite3

手軽なのに強力なsqlite3

適当なツールにsqlite3をよく使う。
pythonを使っていると、最初からpython3ライブラリが使えるので、テキストファイルの集計なども :memory: データベースに投入して出力すると、簡単に実装できる。

データベースを:memory:ではなく、ファイルにした場合、データの再利用が可能だ。
そのデータをちょっと見たいとき、わざわざpythonを経由して見るのは面倒なので、sqlite3コマンドでアクセスしている。

SELECTの結果

デフォルトの出力形式は見ずらい。
効率的といえば効率的かもしれないが、すべてのカラムがパイプ区切りでべったりくっついて表示されてしまう。
この設定は.modeで行う。

.mode csv

カンマ区切りで出力される。特定のカラムに絞ったデータをCSVで出力して、次の処理を行うのも良いかもしれない。

.mode tabs

タブ区切りで出力される。データにカンマが含まれることも多いと思うので、こちらで出力しておいた方が、次の利用に問題が出ないかもしれない。

.mode ascii

カラムの区切り文字が0x1F、行の区切り文字が0x1Eで出力される。
データにカンマもタブも含まれている場合は、この形式が良いと思う。

.mode html

html形式で出力される。使えないこともない。

.mode insert TABLENAME

insert文として出力される。これをファイルに出力して、特定のデータのみを移行するというのにも使える。
TABLENAMEはINSERT文のINSERT先のテーブル名として利用される。
指定しないとINSERT先がTABLEになってしまう。

.mode column

コンソール出力で見栄えが良いように出力してくれる。
OracleのSQL*Plusのような感じか。
しかし、.widthと組み合わせて使わないと、カラムのデータが途中で区切れてしまうことがある。要注意。
.widthはcolumnの先頭から文字数をスペース区切りで指定していく。
.width 10 10 10 20 20 20

日本語が登録されたカラムがあると、文字数でカウントされ縦位置がずれてしまうので注意。
数値とコードのみの、トランザクションテーブルなら使いやすい。

.header on

1行目にカラム名が表示されるようになる。
これはよく使う。

.output FILENAME

SELECT結果の出力先をファイルにする。
.modeをasciiやinsert、htmlに設定した場合は、.outputをファイルにしておく。
終わったら、「.output stdout」として、出力先をコンソールに戻しておく。

その他、たまに利用する設定

.timer on

処理時間が表示されるようになる。
SQL*PlusのSET TIMING ONのような感じ。
sqlite3で性能問題に当たるような、大作SQLを作ったことがないので、あまり気にしないが。

.show

変更した設定値の一覧が確認できる。

.table

テーブル一覧の表示。

.read FILENAME

外部ファイルを読み込んで実行する。
.mode insertで出力した結果を取り込むのに使える。

普段、何も考えずによく使う使い方

1
2
3
4
sqlite> .header on
sqlite> .mode column
sqlite> select * from test_table limit 10;
sqlite> ...

まとめ

簡単に使えてとても便利。それにINSERTとSELECTはとても速い。
INSERTは1件コミットにするととても遅いので注意。
SELECTはINDEXを効かせれば、数万レコードのテーブルでも一瞬で返ってくる。
ツールもなかなか使えるし、手放せないデータベースになってきた。

最後に、sqlite3コマンド抜けるのは.quit（または.exit）。

同じタグの記事
同じカテゴリの記事
投稿日:

ORACLEのMERGE文 補足

ORACLEのMERGE文 補足

WHEN MATCHED THENとWHEN NOT MATCHED THENの順番は、どちらを先に書いても良い。

存在しないときはインサート、存在したら更新というイメージであれば、
WHEN NOT MATCHED THEN、WHEN MATCHED THENの順番で書けば良い。

WHEN MATCHED THENまたはWHEN NOT MATCHED THENのどちらかだけを書いても良い。

WHEN NOT MATCHED THENのみを使うことで、レコードが存在しなかったらインサート、存在した場合は何もしないというのが書ける。
少し乱暴な気がするけど、ちょっとしたツールであればこれで十分だと思う。

WHEN MATCHED THENのみ使うのは、なかなか想定できない。
UPDATE文はもともとから打ちしても、更新レコード数が0になるだけだし。

同じタグの記事
同じカテゴリの記事
投稿日:

Oracle MERGE文

OracleのMERGE文

最近では、すっかりおなじみとなったOracleのMERGE文。
条件に一致した場合はUPDATEが発行され、一致しなかった場合はINSERTが発行される。

テーブルからテーブルへ値を移す時に使われるサンプルが多いが、
実際には、プログラムからデータを登録するときにも利用できる。

たとえば、下記のようなテーブルが存在した時、

1
2
3
4
5
6
7
8
CREATE TABLE WK01 (
    CODE CHAR(3)      NOT NULL,
    NAME VARCHAR2(20) NOT NULL,
    CNT  NUMBER(5)    NOT NULL,
    INS  DATE,
    UPD  DATE,
    PRIMARY KEY (CODE)
);

下記のようなMERGEを発行することができる。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
MERGE INTO WK01 T1
USING (
    SELECT
        ? AS CODE,
        ? AS NAME
    FROM
        DUAL
) T2
ON (
    T1.CODE = T2.CODE
)
WHEN MATCHED THEN
    UPDATE SET
        NAME = T2.NAME,
        CNT  = CNT + 1,
        UPD  = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (
        CODE,
        NAME,
        CNT,
        INS
    ) VALUES (
        T2.CODE,
        T2.NAME,
        1,
        SYSDATE
    );

?にはバインド変数を設定する。

また、WK01.NAMEにはWK02.NAMEを設定しなくてはならない場合、
USINGにSELECTを追加する。

1
2
3
4
5
CREATE TABLE WK02 (
    CODE CHAR(3)     NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    PRIMARY KEY (CODE)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
MERGE INTO WK01 T1
USING (
    SELECT
        ? AS CODE,
        (SELECT T3.NAME FROM WK02 T3 WHERE T3.CODE = ?) AS NAME
    FROM
        DUAL
) T2
ON (
    T1.CODE = T2.CODE
)
WHEN MATCHED THEN
    UPDATE SET
        NAME = T2.NAME,
        UPD  = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (
        CODE,
        NAME,
        INS
    ) VALUES (
        T2.CODE,
        T2.NAME,
        SYSDATE
    );

構文はよく忘れるけど、判定無しにupdateとinsertを使い分けてくれるので楽にロジックが組めるようになる。
厳密に、更新と挿入でロジックを分けている場合は、この方法は使えないけれど。

同じタグの記事
同じカテゴリの記事
投稿日:

Oracleのパスワードの有効期限

Oracleのパスワードの有効期限

パスワードの有効期限を確認する。

1
2
SET LIN 1000
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

パスワードの有効期限を無制限にする。

1
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

パスワードを再設定する。

もし、パスワードの有効期限が来てしまったら、パスワードを再設定する。

1
ALTER USER %USER_NAME% IDENTIFIED BY NEW_PASSWORD;
同じタグの記事
同じカテゴリの記事