本格SEもすなる、技術ブログといふものを、駄目SEもしてみむとて、するなり

JAWS FESTA Kansai 2013 で一番長いセッションをやってきた

JAWS Festa Kansa 2013?

先日の9月28日にJAWS FESTA Kansai 2013に運営として参加してきました。
JAWS(日本AWSユーザーグループ)の全国規模のイベントを開催しました。今までJAWSのイベントは、いくつも開催してきたのですが本当の意味でのユーザーグループ主催のイベントはこれが初めてとなります。これまでは、良い意味でも悪い意味でもAWSの中の人の影響が大きい所があったのですが、ユーザーグループ自身のイベントを開こうとなって、開催しました。(ユーザーグループにはADSJの中の人が多数いらっしゃるのですが、運営主体はADSJ外の方が多数でした)

何を運営した?

私は「設計・移行ワークショップ(仮想RFPから提案書の作成)」という、仮想RFPを元に、参加者がグループで提案書を作ろうというセッションをやっていました。これは、JAWS横浜支部で2回実施した内容を全国イベントでもやってみよう、と言う事でやってみました。
このワークショップは、@maroon1st、@satotech さん、@ijin さん、@hashiva さん、あとリーダー(@iara)にも参加者のフォローをして頂きました。

どんな内容?

今回は、ECサイトに「WB〇」からの取材があり、1か月後に放映されるんだけど、どうやったら対応できるの?みんなで提案して、という仮想RFPをお題にしました。
内容はこんな感じです。

今回は、参加者が合計9名だったので、3人3チームとして提案書を策して頂きました。

提案書をみんな作成!

RFPの説明をして、参加者の方々に提案書を作成して頂きました。
提案書の内容は、テンプレートを提供していて、このテンプレートの内容に沿って作成頂きました。本来の提案書からするとまったく項目が足らないのですが、時間が限られているため、この内容でも多すぎるくらいです。

RFPの説明の時に、「実際に構築は行わないので、尖がった提案をして下さい!」と訴えたので、期待しながら皆さんの提案書作りを見回っていました。
横浜支部でやっている時より1チームの人数が少ないからなのか、全参加者が活発にディスカッションしている事が印象的でした。AWS未経験の方をベテランの方がうまくサポートしていたりしていました。
各チームのチームビルディングが良かったのかなあ、と思います。これは、「結果的に」なので、運営側はもっとファシリテーションをちゃんとやるべきでした。反省。

また、途中でRFPの最初の内容から要件追加をして見ました。内容は、ECサイトだから実は入会時とか購入時とかにメールをしているので、要件に追加をやりました。実は、やってみたら面白いんじゃないかと言ってその場で決めました。

各チームはどんな提案書を作成したの?

提案書の作成時間は2時間程度だったので、はっきり言って時間が足りません。そのため、最低限の
発表順は、速く提出したチームが最後とした結果、Cチーム→Bチーム→Aチームとなりました。

Cチームの発表

先ずはCチームです。こんな提案をして頂きました。

移行や監視をきっちり考えているところが、高ポイントですね!

Bチームの発表

続いてBチームの発表です。

移行時のアプリケーション変更の凍結まで言及して頂いてます。

Aチームの発表

最後はAチームの発表です。
AWS設計・移行のテンプレート - Google Drive
DBの負荷対策にDynamoDBを入れてきた意欲的な提案です。
「尖がった提案をして下さい!」と言ったので、それに応えて頂きました。「移行期間が短いけど大丈夫?」と聞いたら「我々に任せて頂ければ大丈夫です!」(笑)と言っていたので、まあ大丈夫でしょう!

各チームの発表に対して、お客に扮した運営者が質問をしていきます。
一番印象的な質問は@hashiva さんの
「AutoScalingを使うって本当に大丈夫ですか?」
でした。全チームがWebサーバをAutoScaling構成にしていたのですが、全チームに必ず聞いていました。
AutoScaling構成で運用を成り立たせるのは、難易度が高いんですよね~

結果は?

各チームのシステム構成、プレゼンテーション、チームワークに対して様々な項目に点数をつけて、採点を行いました。
チームワークに関しては、各チーム文句なしで差が付きませんでした。
システム構成はAチームとCチームがリードし、プレゼンテーションはBチームがリードしていました。
注目の優勝は
Cチーム!
でした。

運営側の回答例は?

こんな感じで回答例を作成しました。

これは、参加者と違って時間制限無しで作成したので、チートです。
ポイントは、以下の通りです。
・テレビを見てアクセスする人だから、ほとんどが参照処理で更新処理(商品の購入とか)が比較的少ないはず。
・多数のアクセスを裁くため、リクエストを別のサービス(CDNとかDNS)に分散させる。
・準備期間が短いので、簡略化のためAutoScalingはあえて使用しない。
・AWS以外の外部サービスもどんどん使っていく。
・DBアクセスの負荷低減のため、セッションストレージをElastiCacheのRedis Engineを使用し、RDBの負荷を下げる。
・RDSのMulti-AZ、RDSのRead Replicaの冗長化、セッションストレージのElastiCache Redis Engineの冗長化により、SPOFを排除。
・NATも冗長化する。
こんなところでしょうか。
このレベルの内容は、とても2時間では不可能ですね、、、
ちなみに、回答例についても質問を受け付けたら、参加者の質問より運営者のキッツい質問が飛びまくりました。@ijinさん@satotechさんとかガチのマサカリ怖い!

運営してみた感想/反省

このワークショップは、JAWS横浜で2回開催済みなので、大体の準備や当日の動きを把握していたので、準備不足でしたがなんとか運営は回りました。
しかし、このワークショップは4時間30分かかっているので、午後のセッションは他に全く参加できません。にも拘らず参加頂いた方々には感謝しています。ワークショップ後に「実際にAWSの仕事をやることになったので、このワークショップに参加しに来た」という方までいらっしゃいました。参加者の方も勉強になったと思いますが、運営側もとっても勉強になりました。
懇親会で、ワークショップの内容でLTをやったら、他の支部でもやりたいという声を頂きましたので、提供できるネタは提供致します。全国の支部で開催して頂けると良いですね。

最初、京セラドームでイベントをやろう!となった時は、本当に参加者が来るのかと不安になっていましたが、申込みで850人程度、実際の参加者で600人以上と大盛況でした。JAWSの関西メンバーの活躍のおかげですね。

最後に

翌日に大阪の新世界で串揚げをJAWSメンバーと食べたけど、これだけは忘れません。
https://fbcdn-sphotos-h-a.akamaihd.net/hphotos-ak-prn2/1267997_370478359749476_1291928069_o.jpg串揚げは サソリ より コオロギ が旨い!

「AWS Game Day Tokyo 2013」に行ってきたら、AWS界隈でMost EVILになってました。

先日「AWS Game Day Tokyo 2013」に行ってきました。

「AWS Game Day Tokyo 2013」とは?

アメリカ大統領選挙のオバマ陣営「Obama for America」が堅牢なシステムを作り上げるために、「Game Day」という手法を用いました。「Game Day」とは、敵味方に分かれてシステムを攻撃、防御/修復を行い、脆弱性の発見とその修復方法を考えるものです。
これを、日本で初めて行おうというのが「AWS Game Day Tokyo 2013」です。世界でも、「Obama for America」、南アメリカに続きまだ3回目で、これまでで最大規模で開催されました。

Game Start !

朝10時に、会場に着くとAWS界隈の濃いメンバーが集まっており、効果的な攻撃方法を話し合ってました。

  • Reserved Instanceを大量購入する破産攻撃
  • 使用するアカウントでEnterprise Support(最低$15,000!)に入ってしまう
  • etc

等々、効果的な作戦が上がってましたよ。

皆が集まるとルール説明がありました。

  • 全体参加者で3チームに分かれ、別の部屋で行う。
  • チーム毎に。さらに2、3人でチームに分かれて、別の部屋のチームと対戦する。
  • まずシステムを構築する。対戦相手のシステムを攻撃しあう。攻撃された自分のシステムを修復する。
  • 単に消すような攻撃はつまらない。分からないように壊して下さい。
  • お金がかかる攻撃はNG!

ルール説明の後、チーム分けがされました。チームが発表されると、参加者に国家公務員の所属とかセキュリティ企業の人がおり、ザワザワしました。
自分のチームは、AWSに慣れている方は少なめで、セキュリティ関連の方の割合が多かったです。組んだ方は、#自宅ラック勉強会の@さんでした。

Prepare to Game !

最初に守るシステムを構築します。簡単に説明するとQueueに入っている複数URLの画像を取り出して、画像を結合し、S3へ出力するというシステムです。実際に処理をするサーバはAuto Scaling構成になっています。システムの内容はnginksさんのブログが詳しいです。
手順書に従ってシステムを構築するのですが、対象のリージョンが決まってたり、既定の時間が足らなくなってしまい、最終的には用意されていたCloudFormationで一発でStackを構築してしまいました。今思うと、自分で構築した方がカスタマイズできて、より面白かったかなと思います。

周りを見渡すと、自分のシステムを守るためにnmapの実施確認をされている方もいましたが、AWSでは事前申請が必要なのでNGとなり、ションボリされていました。

自分のチームでは、時間がなかったため防御のためにAuto Scaling Groupにに仕込みを入れておきました。

Let's Attack !

昼食後に、「いつ攻撃するの?」(玉川さん)「今でしょ!」(Miles Ward)の掛け声で、攻撃を開始しました。
攻撃の内容、基本的に以下の方針を考えていました。意地が悪い内容ですね!

  • なるべく、AWS Management Consoleからは見えない部分を叩く。
  • 直したつもりになっても戻らない。
  • 一度直しても、いつの間にか元に戻る。

その結果、以下の攻撃を行いました。

  1. Auto Scaling GroupをScheduled Actionで毎分max size、min sizeを0台に上書きする!
  2. Auto ScalingのLaunch Configurationを別の物に変更して、マシンが上がっても動作しないようにする。
  3. Launch Configurationの元の設定を削除して、元の名前でダミーの設定を用意しておく。
  4. S3の権限を全て消しておく。
  5. S3のBucket Policyでアクセスを拒否しておく。
  6. SQSをダミーで大量に作成しておく。

Auto Scaling GroupをScheduled Actionは、実は防御の仕込でも使ってました。

攻撃中に他のチームは、課題のアプリケーションの脆弱性を付いてSQSにコマンドを投げ込むコマンドインジェクション(名付けて"SQS Injection"?)等の高度な攻撃をされていました。一番驚いたのが、相手のIAMのPower Userしかないのに、KeyPair無しでrootの奪取をしたチームが有りました。
SQS経由でコマンドインジェクションでどうにかしたんでしょうか?私の拙いスキルでは、あまり想像つきません。

Repair

攻撃の次は、修復を行います。このシステムで使用している以下の主要サービスを見ていきました。

  • Auto Scaling Group
  • S3
  • SQS
  • EC2

最初はAuto Scaling Groupです。Auto Scaling Groupを確認しましたが、台数やAMIは正常でした。また、Launch ConfigurationもIAM Roleが設定されているので正常と判断しました(Power UserではIAM Roleが設定できないのです)。また、Scheduled Actionも設定されていませんでした。
次に、S3ですが、ユーザのパーミッションもBucket Policyを問題ありませんでした。
そして、SQS。キューのパーミッションはDefault Policyの設定で、Queueもからの状態でした。
最後にEC2です。Auto Scaling構成なので、とりあえずインスタンスをTerminateして、勝手にインスタンスが上がってくるのを待ちました。その後、SSHでログインが正常にできました。
どこに攻撃がされているのか分からないまま、Queueに画像のURLを投入すると、結果の画像がS3へ正常に出力されました。
何所に攻撃されたの???
攻撃された箇所が分からず、動作に関係が無いSecurity Groupの設定や別リージョンを確認しましたが何もわからずに、時間が過ぎていきタイムアップとなりました。

Discussion

最後は、対戦相手と攻撃の修復の内容を話し合います。相手に何所を攻撃したのか聞くと、SQS経由でコマンドを投入し、サービスをいじったりや巨大ファイルを作成したりと大胆な攻撃をしたそうです。しかし、こちらの修復方法がEC2をTerminateすると勝手にインスタンスが立ち上がるという事なので、きょっとショックを受けていたような。
そして、こちらの攻撃の説明をした後に修復方法を聞くと、結局CloudFormationで修復したそうです。どんなに頑張っても、最強のCloudFormationで一発で直りますよね。正しい修復方です。
他のチームの攻撃を聞くと、SQSに巨大なファイルを突っ込んだ上でタイムアウトを短くし、次に投入するQueueを消してしまうという凄い攻撃や、出力先S3のLifecycle Ruleを変更して1日後にGlacierへ移動してしまうというお洒落な攻撃がありました。

Evaluation

Miles氏からの講評あり、なんと我チームが「Most EVIL break!賞」を受賞しました。正直、SQS関連への攻撃で皆高度な攻撃を仕掛けていたので無理かなと思っていたのですが、Management Consoleから見えない部分を突いたのが評価されたみたいです。Milesには、これで「Most EVLI!」覚えてもらったようです。
あと、最も優れた修復を行った「Most AWESOME fix!賞」は@さんが受賞されました。
Miles氏が言っていたことで最も印象的だったのが、「RepairよりRebuildの方が簡単にできるようになる」、「あるべき状態の振る舞いを定義を保存しておき、現状と比較し、自己治癒し続けていくというのが最高のシステム」という2つです。
最近では、Chef、PuppetとServerSpecでServer Provisioningが普及し始めていますが、今度はCloudFormationやOpsWorksでSystem Provisioningが進んでいくと言う事なのでしょう。System as Code(Not Application)とでもいうのでしょうか?

最後に

システムを攻撃すると言う事はあまり経験できることではないので、今回のGame Dayは非常に勉強になりました。
今後の改善点かなと思うところは、

  • 攻撃、修復のターンを複数回のが良い。
  • VPC環境のシステムにする(ネットワーク系の設定ができるので)。
  • 修復する時の手段を制限する(CloudFormationは強力すぎるので、作り直せば何でも直せてしまう)。
  • 攻撃の前に防御策を行う時間をもう少し取った方が良い。

次のre:InventでもGame Dayを行うようなので、東京リージョン代表として是非参加したいと思います。問題は渡航費をどうやって捻出するかだなあ。
Milesを始め、AWSのSAの皆様有難うございました。
あと同じチームになった@さん、有難うございました。

温泉ハッカソンでCloudFormationの勉強してみました

さてさて、今日はAWSのCloudFormationを勉強してみました。

CloudFormation?

AWSのサイトには以下の様に書いています。

AWS CloudFormation は、関連する AWS リソースの集約を整った予測可能な方法でプロビジョニングおよび更新し、開発者やシステム管理者が容易にそれらを作成・管理できるようにします。

これだけ書かれても、あまり良く分かりませんね。
簡単に言うと、事前にjson形式で各コンポーネントの構成を定義すると、一気にその構成を作ってくれる機能です。

何勉強したの?

CloudFormationは、サンプルテンプレートがたくさんあるし、@さんが最近すごい勢いで書いているので、参考にするものが多いです。でも今回は、勉強のために公式ドキュメントだけを見てテンプレートを作ってみました。
公式ドキュメント:Welcome - AWS CloudFormation


*どんな内容?

CloudFormation初心者がCloudFormationを勉強するので、構成は簡単にしました。
「EIP付きのEC2インスタンスを既存VPC内に作成しよう!」です。
なお、前提条件として、VPC、セキュリティグループ、IAM Role等は事前に作成済みと言う事にしました。
で、作成したテンプレートが以下の内容です。

{
	"AWSTemplateFormatVersion" : "2010-09-09",

	"Description" : "Web Server Stack for maroon1st-Formation",

	"Parameters" : {
		"AMI" : {
            "Default": "ami-", 
            "Description": "AMI ID", 
            "Type": "String"
		},
		"WebServerAZ" : {
            "Default": "AAZ", 
            "Description": "AZ for Web Server ", 
            "Type": "String",
			"AllowedValues" : [
				"AAZ",
				"BAZ",
				"CAZ"
			]
		},
		"IAMRole" : {
            "Default": "", 
            "Description": "IAM Role", 
            "Type": "String"
		},
		"InstanceType" : {
            "Default": "t1.micro", 
            "Description": "Instance Type For Launching EC2", 
            "Type": "String",
			"AllowedValues" : [
				"t1.micro",
				"m1.small",
				"m1.medium",
				"m1.large",
				"m1.xlarge",
				"m2.xlarge",
				"m2.2xlarge",
				"m2.4xlarge",
				"c1.medium",
				"c1.xlarge",
				"cc1.4xlarge",
				"cc2.8xlarge",
				"cg1.4xlarge"
			]
		},
		"KeyPair" : {
            "Default": "", 
            "Description": "Key Pair", 
            "Type": "String"
		},
		"SecurityGroup" : {
            "Default": "sg-", 
            "Description": "This is SecurityGroup ID", 
            "Type": "CommaDelimitedList"
		},
		"InstanceName" : {
            "Default": "Maroon1st Web Server", 
            "Description": "Instance Name for EC2 Tag", 
            "Type": "String"
		},
		"UserData" : {
            "Default": "", 
            "Description": "UserData as Text", 
            "Type": "String"
		}

	},
	"Mappings" : {

		"SubnetMapPublic" : {
			"AAZ" : { "SubnetID" : "subnet-0f0f2066"},
			"BAZ" : { "SubnetID" : "subnet-020f206b"},
			"CAZ" : { "SubnetID" : "subnet-190f2070"}
		},

		"TokyoRegionAZ" : {
			"AAZ" : { "AZName" : "ap-northeast-1a"},
			"BAZ" : { "AZName" : "ap-northeast-1b"},
			"CAZ" : { "AZName" : "ap-northeast-1c"}
		}

	},

	"Resources" : {
		"WebServers" : {
			"Type" : "AWS::EC2::Instance",
			"Properties" : {
				"AvailabilityZone" : {
					"Fn::FindInMap" : [
						"TokyoRegionAZ" ,
						{ "Ref" : "WebServerAZ"},
						"AZName"
					]
				},
				"BlockDeviceMappings" : [], 
				"DisableApiTermination" : true,
				"EbsOptimized" : false,
				"IamInstanceProfile" : { "Ref" : "IAMRole" },
				"ImageId" : { "Ref" : "AMI" },
				"InstanceType" : { "Ref" : "InstanceType" },
				"KeyName" : { "Ref" : "KeyPair" },
				"Monitoring" : false,
				"SecurityGroupIds" : { "Ref" : "SecurityGroup" },
				"SourceDestCheck" : false,
				"SubnetId" : {
					"Fn::FindInMap" : [
						"SubnetMapPublic",
						{ "Ref" : "WebServerAZ" },
						"SubnetID"
					]
				},
				"Tags" : [
						{ "Key" : "Name", "Value": { "Ref" : "InstanceName" } },
						{ "Key" : "Server Type", "Value" : "Public Web Server" }
				],
				"Tenancy" : "default",
				"UserData" : {"Fn::Base64" : { "Ref" : "UserData" } }
			}
		},
		"WebServerIP" : {
		    "Type" : "AWS::EC2::EIP",
		    "Properties" : {
				"InstanceId" : { "Ref" : "WebServers" },
                "Domain" : "vpc"
		    }
		}
	},

	"Outputs" : {
		"InstanceID" : {
			"Description" : "ID for Launched Instance",
			"Value" : { "Ref" : "WebServers" }
		},
		"PublicIP" : {
			"Description" : "Public IP for Launched Instance",
			"Value" : { "Ref" : "WebServerIP" }
		},
		"AvailabilityZone" : {
			"Description" : "Launched AZ",
			"Value" : {"Fn::GetAtt" : [ "WebServers", "AvailabilityZone"]}
		},
		"PrivateIPAddress" : {
			"Description" : "Private IP Address Launched Instance",
		     "Value" : {"Fn::GetAtt" : [ "WebServers", "PrivateIp"]}
		 }
	}
}

でも、これってサンプルテンプレートに似てるのありました。orz
↓の名前でありました。
VPC_EC2_Instance_with_EIP_and_Security_Group.template

でもイイんです(川平栄治の感じで)
勉強だから!

作ってみて大変だった事

CloudFormationで一番は異変なのは、CloudFormationのファンクションを使う事だと分かりました。
"Fn::FindInMap"の中で、Fn:Joinの稀有t号した文字列は扱えなかったりするので嵌りました。

まとめると、、、

CloudFormationは、
まあ、RightScaleだとServer TemplateとかMacroがあるので同等のことができるんですが、CLOUDFORMATION*作ってみて大変だった事
CloudFormationはAWS謹製なので、すぐ新規機能がる使えるのが良いですね。

次は、Auto Scaling Group でEC2インスタンス出来るまで頑張るぞ!

qpstudyに参加してきた

先日qpstudyに参加してきました。
「DevOpsをぶち壊せ(仮) ~DevOps言うな(仮)~」
http://atnd.org/event/201301qpstudy

元々の「DevOps」のイメージ

DevOpsというのは、インフラエンジニアもコードも書こうよ!っていうムーブメントというイメージがありました。
具体的には、最近流行のChef/Puppetとか、Run book Automationを進めてアジャイルなインフラを進めるためにコードを書いて行こうという考え方だと考えていました。
まあ、ツールとかプロセスではないよなあ、とは思っていました。余り強いイメージは無かったですね。

ディスカッションを聞いてみて

最初はmizzyさんのプレゼンでした。
http://www.slideshare.net/mizzy/dev-ops-qpstudy
「DevとOpsの関係性に関するムーブメント」という言葉がDevOpsを端的に表しているだと納得しました。アジャイルインフラとかはDevOpsの結果でしかなくて、簡単にいうと「DevとOpsで対立しないで仲良くやろうよ!」なのかなという印象でした。

しかし、その後のディスカッションは途中から1人がDevとOpsの両方をやる必要があるか?という議論になっていきました。mizzyさんは関係性のカルチャーについて論じていたのに、ほかのパネリストは方法論やプロセスの話をしていたので、レイヤーが違っていて噛み合っていなかった様に思えました。

また、しょっさんが違う視点から掻き回してくれていましたが、顧客やマネージャの立場からは関係無くて効率的に進めば良いというのは、ある意味、的を射ていると思いましたね。

結局「DevOps」って何なの?

個人的な理解ですが、以下の2点だと思いました。

  1. Devな人がOpsの考え方を理解して、Opsな人がDevの考え方を理解する事
  2. 理解する事で産まれる、互いのモチベーション

先ずは、Dev、Opsの互いを知って理解する事が重要なんだろうな、という感じでした。

qpstudyとしてのまとめは「自分が信じるDevOps」だったけど、、、

SQLの分析関数はちゃんと覚えよう!

本日はJPOUG Advent Calendar 2012のエントリを書きます。
他の日は錚々たる方々が担当しているので、今日は大したことがない内容です。
、、、とハードルを下げておく。
実は、分析関数を3年位前まで存在を知らなかったんですね。SQLの入門書に記載されていなかったり、分析関数/ウィンドウ関数という名称で何だか難しいイメージがあり、全然勉強をしてなかった、、、大体最初に触ったDB(Oracle 7.3.4)には未だ搭載されていなかったし。

そんな訳で、存在を知る3年以上前までは、とんでもなく大変なSQLを書いたこともありました。
そんなSQLと分析関数で書き直したSQLを比較してみます。

検証環境

マシン:Amazon Web Service RDS(db.m1.small)
OracleOracle Database Standard Edition One 11.2.0.2.v5
サンプルデータ:JdbcRunner テストキット Tiny TPC-Cの tpcc_load.js、tpcc.js で作成。
@さん有難うございます。

昔はこう書いてた

各顧客の最新の注文で、一番高い金額を取得してみます。
昔は、こんな感じで書いていました。

SELECT o.o_id,
       o.o_d_id,
       o.o_w_id,
       MAX(o.o_id) as latest_order_date,
       (SELECT MAX(i.i_price)
         FROM item i, order_line ol2
        WHERE i.i_id = ol2.ol_i_id
          AND ol2.ol_o_id = o.o_id
          AND ol2.ol_d_id = o.o_d_id
          AND ol2.ol_w_id = o.o_w_id) as highest_price
FROM orders o, order_line ol
WHERE o.o_c_id = '1124'
  AND o.o_d_id = '2'
  AND o.o_w_id = '13'
  AND o.o_id   = ol.ol_o_id
  AND o.o_d_id = ol.ol_d_id
  AND o.o_w_id = ol.ol_w_id
GROUP BY o.o_id, o.o_d_id, o.o_w_id
ORDER BY o.o_id, o.o_d_id, o.o_w_id

う~ん、、、 カラムの中にSELECT文があるのが、かなり嫌な感じがします。

書き直してみる

カラムの中にあるSELECT文が嫌なので、FIRST関数を使ってみます。
まず、itemテーブルを普通にFROM句に持ってきて、FIRST関数のORDER BY句でオーダーID順でソートします。

SELECT o.o_id,
       o.o_d_id,
       o.o_w_id,
       MAX(o.o_id) as latest_order_date,
       MAX(i.i_price)KEEP(DENSE_RANK FIRST ORDER BY o.o_id DESC) as highest_price
FROM orders o, order_line ol, item i
WHERE o.o_c_id = '1124'
  AND o.o_d_id = '2'
  AND o.o_w_id = '13'
  AND o.o_id   = ol.ol_o_id
  AND o.o_d_id = ol.ol_d_id
  AND o.o_w_id = ol.ol_w_id
  AND ol.ol_i_id = i.i_id
GROUP BY o.o_id, o.o_d_id, o.o_w_id
ORDER BY o.o_id, o.o_d_id, o.o_w_id

こんな感じでしょうか。余計なSELECT文が無くなってスッキリしました。

実行コストは?

SELECT文はスッキリしましたが、実行コストはどの程度改善させるのでしょう?
比較します。

昔のSQLの実行計画

とりあえず、autotrace を設定します。

SQL> set autotrace traceonly explain


昔のSQLの実行計画を確認します。

Execution Plan
----------------------------------------------------------
Plan hash value: 4157488715

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     1 |    33 |     7  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE                 |               |     1 |    24 |            |          |
|   2 |   NESTED LOOPS                  |               |       |       |            |          |
|   3 |    NESTED LOOPS                 |               |    10 |   240 |    14   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | ORDER_LINE    |    10 |   150 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN           | ORDER_LINE_PK |    10 |       |     3   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN           | ITEM_PK       |     1 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID  | ITEM          |     1 |     9 |     1   (0)| 00:00:01 |
|   8 |  SORT GROUP BY                  |               |     1 |    33 |     7  (15)| 00:00:01 |
|   9 |   NESTED LOOPS                  |               |    10 |   330 |     7  (15)| 00:00:01 |
|  10 |    VIEW                         | VW_GBC_5      |     1 |    23 |     5  (20)| 00:00:01 |
|  11 |     HASH GROUP BY               |               |     1 |    14 |     5  (20)| 00:00:01 |
|  12 |      TABLE ACCESS BY INDEX ROWID| ORDERS        |     1 |    14 |     4   (0)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN          | ORDERS_IX1    |     1 |       |     3   (0)| 00:00:01 |
|* 14 |    INDEX RANGE SCAN             | ORDER_LINE_PK |    10 |   100 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("OL2"."OL_W_ID"=:B1 AND "OL2"."OL_D_ID"=:B2 AND "OL2"."OL_O_ID"=:B3)
   6 - access("I"."I_ID"="OL2"."OL_I_ID")
  13 - access("O"."O_W_ID"=13 AND "O"."O_D_ID"=2 AND "O"."O_C_ID"=1124)
  14 - access("ITEM_1"="OL"."OL_W_ID" AND "ITEM_2"="OL"."OL_D_ID" AND
              "ITEM_3"="OL"."OL_O_ID")

あんまり重いデータではないので、こんなもんでしょうか?

書き直したSQLの実行計画

こちらも実行計画を確認します。

Execution Plan
----------------------------------------------------------
Plan hash value: 1892011866

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     1 |    38 |    17   (6)| 00:00:01 |
|   1 |  SORT GROUP BY                  |               |     1 |    38 |    17   (6)| 00:00:01 |
|   2 |   NESTED LOOPS                  |               |       |       |            |          |
|   3 |    NESTED LOOPS                 |               |     9 |   342 |    16   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |               |     9 |   261 |     7   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| ORDERS        |     1 |    14 |     4   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | ORDERS_IX1    |     1 |       |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| ORDER_LINE    |     9 |   135 |     3   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | ORDER_LINE_PK |     9 |       |     2   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN           | ITEM_PK       |     1 |       |     0   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | ITEM          |     1 |     9 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("O"."O_W_ID"=13 AND "O"."O_D_ID"=2 AND "O"."O_C_ID"=1124)
   8 - access("OL"."OL_W_ID"=13 AND "OL"."OL_D_ID"=2 AND "O"."O_ID"="OL"."OL_O_ID")
   9 - access("OL"."OL_I_ID"="I"."I_ID")

書き直したSQLの方が%CPUは低いけどCostは大きいですね。各々のコストが小さいので余り良く分からない、、、

もう少し重いSQLで比較する

WHERE句から、以下の顧客を特定する条件を抜かして比較してみます。

      o.o_c_id = '1124'
  AND o.o_d_id = '2'
  AND o.o_w_id = '13'
昔のSQLの実行計画 その2
Execution Plan
----------------------------------------------------------
Plan hash value: 414388587

--------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |   240K|  4687K|       | 14470   (2)| 00:02:54 |
|   1 |  SORT AGGREGATE                |               |     1 |    24 |       |            |          |
|   2 |   NESTED LOOPS                 |               |       |       |       |            |          |
|   3 |    NESTED LOOPS                |               |    10 |   240 |       |    14   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ORDER_LINE    |    10 |   150 |       |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | ORDER_LINE_PK |    10 |       |       |     3   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | ITEM_PK       |     1 |       |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID | ITEM          |     1 |     9 |       |     1   (0)| 00:00:01 |
|   8 |  SORT GROUP BY                 |               |   240K|  4687K|  7544K| 14470   (2)| 00:02:54 |
|*  9 |   HASH JOIN                    |               |   240K|  4687K|  5160K| 13008   (2)| 00:02:37 |
|  10 |    VIEW                        | VW_GBF_5      |   240K|  2343K|       | 11887   (2)| 00:02:23 |
|  11 |     HASH GROUP BY              |               |   240K|  2343K|    91M| 11887   (2)| 00:02:23 |
|  12 |      INDEX FAST FULL SCAN      | ORDER_LINE_PK |  4799K|    45M|       |  4399   (1)| 00:00:53 |
|  13 |    INDEX FAST FULL SCAN        | ORDERS_PK     |   480K|  4687K|       |   365   (1)| 00:00:05 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("OL2"."OL_W_ID"=:B1 AND "OL2"."OL_D_ID"=:B2 AND "OL2"."OL_O_ID"=:B3)
   6 - access("I"."I_ID"="OL2"."OL_I_ID")
   9 - access("O"."O_ID"="ITEM_3" AND "O"."O_D_ID"="ITEM_2" AND "O"."O_W_ID"="ITEM_1")
書き直したSQLの実行計画 その2
Execution Plan
----------------------------------------------------------
Plan hash value: 1398604944

---------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |   240K|  7968K|       | 43683   (1)| 00:08:45 |
|   1 |  SORT GROUP BY         |            |   240K|  7968K|   201M| 43683   (1)| 00:08:45 |
|*  2 |   HASH JOIN            |            |  4760K|   154M|    10M| 26928   (1)| 00:05:24 |
|   3 |    INDEX FAST FULL SCAN| ORDERS_PK  |   480K|  4687K|       |   365   (1)| 00:00:05 |
|*  4 |    HASH JOIN           |            |  4760K|   108M|  2056K| 17923   (1)| 00:03:36 |
|   5 |     TABLE ACCESS FULL  | ITEM       |   100K|   878K|       |   308   (1)| 00:00:04 |
|   6 |     TABLE ACCESS FULL  | ORDER_LINE |  4799K|    68M|       | 11352   (1)| 00:02:17 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O"."O_ID"="OL"."OL_O_ID" AND "O"."O_D_ID"="OL"."OL_D_ID" AND
              "O"."O_W_ID"="OL"."OL_W_ID")
   4 - access("OL"."OL_I_ID"="I"."I_ID")

ん?

改めて比較すると不思議なことに、不細工な昔のSQLの方が3倍程度低Cost!!
実行計画から類推するとFIRST関数の場合は、FROM一度全データを全て展開してから集約するので、処理対象行数が最後まで少なくならないようですね。

まとめ

まあ、何でも使える関数を使うだけでなく、ちゃんと実行計画は確認しろって事ですね。
必ずしも、無暗に使える関数でSQLを書くなと。

明日のJPOUG Advent Calendarは、@さんです。よろしくお願いします。


何とか JPOUG Advent Calendar の記事が終わったけど、ブログ書き続けていけるかな?

初めての記事です!

皆様、はじめまして!!

まあ、こんな記事を読んでいる人がいるとは思いませんが、、、

 

今まで、サイトもブログもやったことがないのですけと、SIerに勤務して10年目なのに今さらながら始めようかと思っています。きっかけとしては、@ さんと、先日お会いしたときに JPOUG Advent Calendar 誘われたのですがブログもやっていないので、どうしようかと思って始めました。

 

JPOUG Advent Calendar は12/16の担当なんですが、まだ考えてないなあ。残り2週間あるし、まだ大丈夫だよね、、、

ネタは、Oracleのウィンドウ関数にしようかな。大した内容は書けないんですけど。

 

なるべく記事を書いて行ければと思います。たぶん、合間が長くなると思いますが、よろしくお願いします。