がんふぃーるど室長の不定期ブログ

ODP.NETを使用して無名ブロックを実行しようとしたときに改行コードLF/CRLFは環境依存？ - Oracle

がんふぃーるど(ganfield@wankuma.com) — Tue, 16 Sep 2008 23:30:00 GMT

Oracle 11g のWindows試用版を入れてみたら、改行コードがCRLFでも無名ブロック（Non-Stored Procedure)の実行がODP.NETで出来てしまった。

Unix版の10.2gや10.1gではCRを認識できないためにエラーが発生して、わざわざCRを空文字に置換したり、\nだけ書いていたのだが…

環境依存な事柄なのかな？？？それとも11gで改心（？）したのかな？

「オブジェクト指向入門　第二版　方法論・実践」が発売されています

がんふぃーるど(ganfield@wankuma.com) — Sat, 30 Aug 2008 08:32:00 GMT

http://seshop.com/detail.asp?pid=9505

すごく・・・ごついです・・・

アプリケーションコンテキストその１ - Oracle

がんふぃーるど(ganfield@wankuma.com) — Mon, 18 Aug 2008 01:38:00 GMT

Oracleにはアプリケーションコンテキストというものがあります。ASP.NETで言うところの「セッション」みたいなもので、基本はKey-Valueで構成される情報を保持しています。Oracleのマニュアルを見るとCREATE CONTEXT文というのがちゃんと用意されており、ユーザ定義のアプリケーションコンテキストを使用すればかなり柔軟なアプリケーションを作成することが可能です。

今回は御触りということで、ユーザ定義のアプリケーションコンテキストは使用せずに組込みのアプリケーションコンテキスト「USERENV」を少しだけ使用します。

アプリケーションコンテキスト「USERENV」

USERENVにはセッションに関する情報がほぼ詰め込まれています。今回はこの中の「CLIENT_IDENTIFIER」を使用します。理由は至って簡単-ODP.NETのOracleConnection.ClientIdからお手軽に設定できるからです。

その他の値については下記のOracleのドキュメントを参照してください。

http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05750-02/functions.htm#11790

アプリケーションコンテキストの確認方法

アプリケーションコンテキスト確認用の関数があるので、それを使用します。

SYS_CONTEXT(namespace, parameter [, length ])

namespaceにUSERENV

parameterにCLIENT_IDENTIFIER

を指定します。lengthはオプショナルな設定値なので今回は無視（デフォルト値は256）

　※USERENV関数というのもありますが下位互換用です。またUSERENV関数にはCLIENT_IDENTIFIERを取得する機能は存在しないので、基本SYS_CONTEXTを使用します。

ODP.NETでCLIENT_IDENTIFIERの設定

先に言ってしまいましたが、ODP.NETの以下のプロパティを設定します。

OracleConnection.ClientId

このプロパティは接続をOpenした後にのみ設定可能です。ClientIdは接続中つねに有効になり、Closeと共に解放されます（nullが設定される）。

using (OracleConnection conn = new OracleConnection("user id=XXXX; password=YYYY; data source=xe"))
{
conn.Open();

    conn.ClientId = "30";
    command = conn.CreateCommand();
    command.CommandText = "SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') FROM DUAL";
    result = command.ExecuteScalar();
    Console.WriteLine(result.Equals(System.DBNull.Value) ? "null" : result.ToString());
}

アプリケーションコンテキストを使用してVIEWへ値を動的に渡す

アプリケーションコンテキストは関数を通して取得できるということで、VIEWなどに対して動的に値を渡すことが可能です。VIEWであれば、使用しているSQL側でWHERE句に条件を設定することで同じことが当然できますが、セキュリティ的な要件をVIEWでコントロールしたい場合などに役立ちます。（Oracleではセキュリティに関して、仮想プライベートデータベースという便利な機能がありますが、今回はそれを使用しません。）

ためしに以下のようなVIEWをOracleのHRスキーマ上に作成します。

-- マネージャ直下の部下を参照できるVIEW
CREATE OR REPLACE VIEW HR.EMPLOYEES_UNDER_MANAGER1
AS
SELECT * FROM HR.EMPLOYEES WHERE MANAGER_ID = SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')

このVIEWはClientIdに社員IDが設定されていることを前提に作られています。ClientIdの値を変えることで返却される行が変更されます。

次回はユーザ定義のアプリケーションコンテキストの作成方法について説明しようと思います。

ウィンドウ関数（分析関数） - SQL Server 2005

がんふぃーるど(ganfield@wankuma.com) — Thu, 31 Jul 2008 00:57:00 GMT

WNDPROCとかじゃアｒ（ｒｙ

ウィンドウ関数はSQL Serverでも使用できます。なんてったって標準SQLですから。

ウィンドウ関数を使ってみる

SQL Server 2005のAdventureWorksを使用して、ウィンドウ関数を使ってみます。やりたいことはOracle版と同じようなことですが、部署IDみたいなものが無かったので性別でウィンドウを区切ってみます。

Employeeテーブルの中で、性別ごとに生年月日順にランク付けする。
ランク付けしたら、同じ性別の中で何人中何位だったかわかるようにする。
取得する項目はEmployeeID, 性別, 順位, 性別ごとの人数

SELECT
a.EmployeeID as 社員ID,
a.Gender as 性別,
Rank() OVER (PARTITION BY a.Gender ORDER BY a.BirthDate) as 順位,
Count(a.Gender) OVER (PARTITION BY a.Gender) as 人数
FROM
HumanResources.Employee a

Oracleと同じですね。実行計画は次の通り

参考文献

SQL Server 2005 Books Online - OVER句

ウィンドウ関数（分析関数） - Oracle

がんふぃーるど(ganfield@wankuma.com) — Tue, 29 Jul 2008 00:35:00 GMT

WNDPROCとかじゃアリマセン。集権関数とちょっと勘違いされそうなモノなんですが、別物です。ひそかにANSI SQLでも定義されている立派な標準SQL構文です。

ウィンドウ関数とは

簡単に言うと「Group byの様にデータのグループを決められる。集計はせず、さらにその範囲の中で特定のデータのみを対象とできる」ものです。

う～ん、いまいち訳分かりませんね。

とりあえず、使ってみましょう。

ウィンドウ関数を使ってみる

OracleのHRスキーマを使用して、ウィンドウ関数を使ってみます。やりたいことは次の通り。

Employeesテーブルの中で、部署（Department_id）ごとに給料の高い順にランク付けする。
ランク付けしたら、部署の中で何人中何位だったわかるようにする。
取得する項目は、Employee_id, 名前, 順位, 部署の人数

部署の人数などは、EmployeesテーブルをDepartment_idでgroup byした結果をさらにEmployeesテーブルのDepartment_idと結合してたり…なんてことでも取れますが、そんな面倒なことをしなくてもウィンドウ関数を使えば楽勝です。

SELECT
a.EMPLOYEE_ID,
a.FIRST_NAME || ', ' || a.LAST_NAME ,
a.DEPARTMENT_ID,
RANK() OVER (PARTITION BY a.DEPARTMENT_ID ORDER BY a.SALARY DESC),
COUNT(a.DEPARTMENT_ID) OVER (PARTITION BY a.DEPARTMENT_ID)
FROM
HR.EMPLOYEES a

ポイントは5行目と6行目。

関数(expr) OVER (PARTITION BY XXX)

というのが、ウィンドウ関数の基本的な構文です。PARTITION BY のXXXX項目でグループを指定します（group byみたいな感じ）

5行目では、部署（Department_id）別にウィンドウを設定し、給料の高い順にランク付けします。6行目では、部署別にカウントを取ります。

これだけで完了。実行計画もすっきり。

SELECT STATEMENT
WINDOW SORT
TABLE ACCESS FULL EMPLOYEES

毎回毎回使うようなものではありませんが、かゆい所に手が届くといった感じで結構便利です。

参考文献

Oracle Database SQL リファレンス 10.2 - 分析ファンクション

CodeZine - 分析関数の衝撃

Flets接続ツール「IPv6リゾルバサービス」のバグ？

がんふぃーるど(ganfield@wankuma.com) — Wed, 23 Jul 2008 00:44:00 GMT

DNSのアドレスが使用しているクライアントPCのアドレスになる…嫌がらせですか？

しかも、サービスを起動している限り何度でも書き換える。

な・ん・ど・で・も♪

普段はどうってことなかったので、二つのネットワーク（有線と無線）を同時に有効にしたから駄目だったのかな？どうでもいいツールだからあまり確認していないけど。

とりあえず、ボトムズを全話見るために入れたのですでに用無し。サービスをさっさと止めて解決。。。

行ロック以外に起因するデッドロック - Oracle

がんふぃーるど(ganfield@wankuma.com) — Wed, 16 Jul 2008 01:21:00 GMT

デッドロック

業務アプリを作成していると、必ずデッドロックというものは意識すると思います。リレーショナルDBを使用しているのであれば尚更だと思います。

基本的にはテーブルの更新順序に対する標準を作っておけば、起こることはありません。

少なくともOracleでは起こらないと思ってました。(；´Д｀)

SQLServerだとロックエスカレーションによってページロックが発生してデッドロック！なんてことが稀にありますが、Oracleでもマルチプロセス、もしくはパラレルDMLによる大量更新時にITLの確保待ちによるデッドロックが発生する場合があります。（記述が紛らわしいですが、SQLServerのページロックとはまったく別物です）

ITL

ITLとは（Interested Transaction List)の略で、変更情報や行ロックに関する情報を管理しています。リストというぐらいなので、当然複数持っています。いくつかというと、初期状態ではCREATE文のINITRANSの個数分だけ確保され、必要に応じて最大MAXTRANSの個数まで拡張されます。

ただし、ブロックに空き容量がない場合は、拡張されません。（拡張できません）

リストの内容はセッションごとに管理されているので、ブロックの情報を2つのセッションで同時に操作する場合は2つ必要です。3つのセッションで同時に操作する場合は3つ必要となります。ただ、ITLは有限個しかないので、大量のセッションから同時に操作されたときに、ITLを拡張できない状態にあるとどうなるかというと、ITLの開放待ちになります。

…開放待ちになります…

デッドロック発生までの流れ

まずは、ブロックに対してITLの拡張ができないほど空きがなく、且つ、ITLが1つしかないものとします。その状態で

セッションAがブロック１の情報を更新
セッションBがブロック２の情報を更新
セッションAがブロック２の情報を更新しようとしてITL開放待ち
セッションBがブロック１の情報を更新しようとしてITL開放待ち

でデッドロックが発生します。ちなみに、セッションA、Bが更新する内容で行レベルのロックは一つも被っていませんが、まったくもって有り難くないORA-00060が発生しちゃいます。

調査方法

正直、分かりにくいです。普通にORA-00060が発生するので、一般的なデッドロックと判別がつきません。V$LOCKやV$LOCKED_OBJECTをリアルタイムに参照するのも難しいことから、唯一分かる情報としては「udump」に吐かれるトレース情報ぐらいです。

ここにはデッドロック時に「待ち」が発生していたセッション情報も含めて、大量の情報が記録されています。この中に「ITL Entry」に対するブロッキングが発生していた旨があった場合、ほぼ間違いなくITLデッドロックだと思います。

また、ITLデッドロックはテーブルだけでなく、インデックスに対しても発生するため、ロック対象のROW IDやOBJECT IDにインデックスのOBJECT IDが含まれていたら、ITLデッドロックなどのアプリデッドロック以外を疑ったほうがよいです。

解決方法

基本的にINITRANSに適切な値を設定することで回避できる問題なので、マルチプロセスでの大量更新・登録系って聞いた瞬間にINITRANSを適切に設定してやることで回避できます。ただし、INITRANSは1つ増やすごとに24バイトのブロックヘッダを消費するので、ご利用は計画的に。

参考文献

SHIFT The Oracle - Interested Transaction List

Oracle parallel DML: Deadlock Detected: ORA-00060

ローカル同一キーパーティション索引

がんふぃーるど(ganfield@wankuma.com) — Tue, 15 Jul 2008 01:21:00 GMT

の落とし穴（？）にハマりました…

パーティションキーを固定（バインド変数）で渡すような場合に、Oracleが強引にローカルパーティション索引の使用とパーティションプルーニングを行おうとした結果、非効率的な実行計画を立ててくれる場合がありました。

駆動表が他にあるので、そいつと結合するときにハッシュ結合すれば高速に動くのですが、わざわざネステッドループをかましてConsitents getsが１千万弱に…嫌がらせでつか？(つД｀)

統計情報の取り直しや、ヒント文の付与、SharedPoolとバッファキャッシュのクリアなどを行ったが、全然効果なし。

ローカルパーティション索引のカーディナリティが高いわけでもなく、結合条件などを含めたら他にカーディナリティの高い索引があるのに…

とりあえず、レコード件数が億を余裕で超えている表だったので、索引項目を増やしたりはせず、索引をグローバルパーティション索引に変更。

原因がいまいちハッキリしていないので気持ち悪いのですが、今回はちょっぴり残念な結果に

NVelocity - テキストのテンプレートエンジン

がんふぃーるど(ganfield@wankuma.com) — Thu, 10 Jul 2008 01:57:00 GMT

.NETで使用できるテキストテンプレートエンジンです。

最近やっている仕事の関係上、開発用ツールを作成したくなる場合が多く、ちょっとしたデータベースのDDL文などのテキストファイルを作成する場合が多く、もうちっと楽＆美しく（？）作りたいな～なんて思っているときに発見しました。

上手に使用すればメール本文を作成するテンプレートエンジンとしても利用可能にできそうです。

ちょっとおしゃれに（無駄にｗ）テキストファイルを作ってみたいときは使ってみて下さい。

NVelocity (SVNリポジトリ)

CodeZine : テンプレートエンジンNVelocityを活用してテキストを生成する

ユーザー定義集計関数…とは微妙に違うCSV形式で文字列を集計 - LINQ

がんふぃーるど(ganfield@wankuma.com) — Wed, 02 Jul 2008 02:05:00 GMT

前回はSQL Serverでその前がOracleだったので、ぢゃあ次はLINQってことで試してみました。

LINQの場合（というかC# 3.0の場合）は、Aggregateメソッドとラムダ式でうにゃうにゃっと書けば結構できてしまうので、細かいとこは省いてしまいます。

基本

基本は以下のラムダ式があればほぼ問題無いみたいです。（表現が間違っていたらごめんなさい＆指摘して下さい）

(TAccumulate, TSource) => 処理

ということで、実際に集計処理をしてみましょう。

集計処理

サンプルでは適当な家族(family)の匿名型を利用して、性別ごとに家族の名前をカンマ区切りで集計します。

var family = new []{
    new { Name = "父", Age = 40, Sex = "男" },
    new { Name = "母", Age = 40, Sex = "女" },
    new { Name = "娘", Age = 10, Sex = "女" },
    new { Name = "爺ちゃん", Age = 70, Sex = "男" },
    new { Name = "曾婆ちゃん", Age = 100, Sex = "女" },
    new { Name = "曾曾婆ちゃん", Age = 125, Sex = "女" }
};

string startVal = string.Empty;
var AggNamePerSex = from f in family
                    group f by f.Sex into g
                    select new {
                        Sex = g.Key,
                        AggName = g.Aggregate(
                            startVal // 初期値
                            , (running, next) => running += next.Name + "," // 集計処理
                            , result => result == string.Empty ? result : result.Substring(0, result.Length -1) // 終了処理
                        )
                    };

foreach (var sexGroup in AggNamePerSex)
{
Console.WriteLine(sexGroup.Sex + " : " + sexGroup.AggName);
}

実行結果はこんな感じ

男 : 父,爺ちゃん
女 : 母,娘,曾婆ちゃん,曾曾婆ちゃん

処理の方をよく見てみると、初期値（初期化）→集計処理→終了処理　とOracleやSQL Serverと似ていますね。パラレル実行などがないため、マージはありませんが（ｗ

P.S. ソースを見てみると、もはやC#のソースに見えない…というか、なぜ「C」って付いているんだろうｗ

がんふぃーるど室長の不定期ブログ

ODP.NETを使用して無名ブロックを実行しようとしたときに改行コードLF/CRLFは環境依存？ - Oracle

「オブジェクト指向入門 第二版 方法論・実践」が発売されています

アプリケーションコンテキスト その１ - Oracle

アプリケーションコンテキスト「USERENV」

アプリケーションコンテキストの確認方法

ODP.NETでCLIENT_IDENTIFIERの設定

アプリケーションコンテキストを使用してVIEWへ値を動的に渡す

ウィンドウ関数（分析関数） - SQL Server 2005

ウィンドウ関数を使ってみる

ウィンドウ関数（分析関数） - Oracle

ウィンドウ関数とは

ウィンドウ関数を使ってみる

Flets接続ツール「IPv6リゾルバサービス」のバグ？

行ロック以外に起因するデッドロック - Oracle

デッドロック

ITL

デッドロック発生までの流れ

調査方法

解決方法

ローカル同一キーパーティション索引

NVelocity - テキストのテンプレートエンジン

ユーザー定義集計関数…とは微妙に違うCSV形式で文字列を集計 - LINQ

基本

集計処理

「オブジェクト指向入門　第二版　方法論・実践」が発売されています

アプリケーションコンテキストその１ - Oracle