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/