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

问题描述

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

给我非法列异常的示例代码:

ResultSet rs = null;尝试 {oracle = ConnectionManager.getConnection(ConnectionManager.Test);pstmt = oracle.prepareStatement("立即开始执行'解释 SELECT 1 from Dual 的计划';结尾;");rs = pstmt.executeQuery();而(rs.next()){System.out.println(rs.getString(1));}

解决方案

使用这个:

<前>oracle = ConnectionManager.getConnection(ConnectionManager.Test);stmt = oracle.createStatement()stmt.execute("解释从 Dual SELECT 1 的计划");rs = stmt.executeQuery("从表中选择plan_table_output(dbms_xplan.display())");而(rs.next()){System.out.println(rs.getString(1));}

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));
        }

解决方案

Use this:

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屋！