Oracle・PostgreSQL・MySQL・SQLServer・H2DatabseそれぞれでJDBCを利用して実行計画を取得する

Oracle・PostgreSQL・MySQL・SQLServer・H2Databseで、JDBCを使って実行計画を取得する方法を調べたのでさらすことにします。
SQLServerだけ少し手こずりましたが他は簡単でした。

とりあえず取得しているだけで、僕はOracle以外は全く読めないので間違っていたらごめんなさい。

Oracle

oracle

	public static void explainOracle(String sql) throws SQLException {
		System.out.println("■Oracle 実行計画取得：" + sql);

		try (Connection connection = DriverManager.getConnection(ORACLE_URL, ORACLE_USER, ORACLE_PASSWORD)) {
			connection.setAutoCommit(false);
			//explain plan forを付与してSQL実行
			try (PreparedStatement ps = connection.prepareStatement("explain plan for " + sql)) {
				ps.execute();
			}

			//実行計画を取得する
			try (PreparedStatement ps = connection.prepareStatement("select * from table(DBMS_XPLAN.DISPLAY())")) {
				try (ResultSet rs = ps.executeQuery()) {
					resultSetPrint(rs);
				}
			}
			connection.rollback();
		}
	}

個人的には一番慣れ親しんでいる

PostgreSQL

PostgreSQL

	public static void explainPostgresSql(String sql) throws SQLException {
		System.out.println("■Postgres 実行計画取得：" + sql);

		try (Connection connection = DriverManager.getConnection(POSTGRES_URL, POSTGRES_USER, POSTGRES_PASSWORD)) {
			connection.setAutoCommit(false);
			//explainを付与して実行計画を取得する
			try (PreparedStatement ps = connection.prepareStatement("explain " + sql)) {
				try (ResultSet rs = ps.executeQuery()) {
					resultSetPrint(rs);
				}
			}
			connection.rollback();
		}
	}

Postgresは簡単でとても良い。

MySQL

MySQL

	public static void explainMySql(String sql) throws SQLException {
		System.out.println("■MySql 実行計画取得：" + sql);

		try (Connection connection = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD)) {
			connection.setAutoCommit(false);
			//explainを付与して実行計画を取得する
			try (PreparedStatement ps = connection.prepareStatement("explain " + sql)) {
				try (ResultSet rs = ps.executeQuery()) {
					resultSetPrint(rs);
				}
			}
			connection.rollback();
		}
	}

MySQLも簡単でとても良い。

H2Database

H2Database

	public static void explainH2(String sql) throws SQLException {
		System.out.println("■H2Database 実行計画取得：" + sql);

		try (Connection connection = DriverManager.getConnection(H2_URL, H2_USER, H2_PASSWORD)) {
			connection.setAutoCommit(false);
			//explainを付与して実行計画を取得する
			try (PreparedStatement ps = connection.prepareStatement("explain " + sql)) {
				try (ResultSet rs = ps.executeQuery()) {
					resultSetPrint(rs);
				}
			}
			connection.rollback();
		}
	}

この辺はみんな一緒。

SQL Server

SQLServer

	public static void explainSqlServer(String sql) throws SQLException {
		System.out.println("■SqlServer 実行計画取得：" + sql);

		try (Connection connection = DriverManager.getConnection(SQLSERVER_URL, SQLSERVER_USER, SQLSERVER_PASSWORD)) {
			connection.setAutoCommit(false);
			//参考 http://sourceforge.net/p/jtds/discussion/104389/thread/92402a82
			try (Statement st = connection.createStatement()) {
				//SHOWPLAN_ALL ON
				st.executeUpdate("SET SHOWPLAN_ALL ON");

				//そのままSQL実行すると実行計画が取得できる
				try (ResultSet rs = st.executeQuery(sql)) {
					resultSetPrint(rs);
				}

				//SHOWPLAN_ALL OFF
				st.executeUpdate("SET SHOWPLAN_ALL OFF");
			}
			connection.rollback();
		}
	}

Statementを使わないといけないことになかなか気が付けなかった。

---

使ってるソース

	//ResultSetをフェッチして全行をコンソールに吐き出す
	private static void resultSetPrint(ResultSet rs) throws SQLException {
		ResultSetMetaData meta = rs.getMetaData();
		int count = meta.getColumnCount();
		//カラムのprint
		System.out.println(IntStream.rangeClosed(1, count)
				.mapToObj(i -> {
					try {
						return meta.getColumnLabel(i);
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
				})
				.collect(Collectors.joining("\t")));
		while (rs.next()) {
			//各行のprint
			System.out.println(IntStream.rangeClosed(1, count)
					.mapToObj(i -> {
						try {
							return rs.getObject(i);
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}).map(o -> o == null ? "" : o.toString())
					.collect(Collectors.joining("\t")));
		}
	}

結果

以下、コンソールに出力した結果を張ります。
（全部「select sysdate from dual」的なSQLで検証してます。）

コンソールOracle

■Oracle 実行計画取得：select sysdate from dual
PLAN_TABLE_OUTPUT
Plan hash value: 1388734953
 
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

コンソールPostgreSQL

■Postgres 実行計画取得：select now()
QUERY PLAN
Result  (cost=0.00..0.01 rows=1 width=0)

コンソールMySQL

■MySql 実行計画取得：select sysdate() from dual
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE										No tables used

コンソールSQLServer

■SqlServer 実行計画取得：select getDate()
StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
select getDate()	1	1	0			1									SELECT WITHOUT QUERY	false	

コンソールH2Database

■H2Database 実行計画取得：select CURRENT_TIMESTAMP
PLAN
SELECT
    CURRENT_TIMESTAMP()
FROM SYSTEM_RANGE(1, 1)
    /* PUBLIC.RANGE_INDEX */

---

こんなのも書いています
Oracle・PostgreSQL・MySQL・SQLServer・H2Databseそれぞれでテーブル・カラム・プライマリキー・インデックスを取得するSQL

参考

http://use-the-index-luke.com/ja/sql/explain-plan
http://sourceforge.net/p/jtds/discussion/104389/thread/92402a82/