JDBC Oracle - 获取查询的解释计划 [英] JDBC Oracle - Fetch explain plan for query

问题描述

我想知道如何使用Java获取解释计划。我需要这个的原因是因为我们有一个特殊用户可以制作报告的框架。这些报告有时会构建大量查询，我们希望在其中动态解释并存储成本。这样我们就可以在以后分析高成本查询并进行优化。

Im wondering how I can fetch the explain plan using Java. Reason I need this is because we have a framework where special users can craft reports. These reports sometimes build huge queries in which we want to explain on the fly and store the cost of. This way we can analyse the high cost queries later on and optimize.

给出非法列异常的示例代码：

Example code which gives me illegal column exception:

ResultSet rs = null;
   try {
        oracle = ConnectionManager.getConnection(ConnectionManager.Test);
        pstmt = oracle.prepareStatement("begin execute immediate 
        'explain plan for SELECT   1 from Dual'; end;");
        rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }

推荐答案

使用此：

oracle = ConnectionManager.getConnection(ConnectionManager.Test);
stmt = oracle.createStatement()
stmt.execute("explain plan for SELECT   1 from Dual");
rs = stmt.executeQuery("select plan_table_output from table(dbms_xplan.display())");
while (rs.next()) 
{
  System.out.println(rs.getString(1));
}

这篇关于JDBC Oracle - 获取查询的解释计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！