JDBC Oracle - 获取查询的解释计划

JDBC Oracle - Fetch explain plan for query

提问by Chris Dale

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.

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

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

回答by a_horse_with_no_name

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

回答by Vadzim

There is also a way to show real plan used to run last query in this session via DBMS_XPLAN.DISPLAY_CURSOR. The query of interest doesn't need to be prepended with EXPLAIN PLAN FOR.

还有一种方法可以通过DBMS_XPLAN.DISPLAY_CURSOR. 感兴趣的查询不需要以EXPLAIN PLAN FOR.

try (Statement st = connection.createStatement()) {
    try (ResultSet rs = st.executeQuery(
            "select plan_table_output from table(dbms_xplan.display_cursor())")) {
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }
}

Note that the user needs to be granted the following permissions in order to use DBMS_XPLAN.DISPLAY_CURSOR:

请注意，用户需要被授予以下权限才能使用DBMS_XPLAN.DISPLAY_CURSOR：

GRANT SELECT ON v_$session TO USER;
GRANT SELECT ON v_$sql_plan TO USER;
GRANT SELECT ON v_$sql_plan_statistics_all TO USER;
GRANT SELECT ON v_$sql TO USER;

Credits go to https://myoracledbablog.wordpress.com/2016/07/26/dbms_xplan-and-the-user-has-no-select-privilege-on-v-error/.

积分转到https://myoracledbablog.wordpress.com/2016/07/26/dbms_xplan-and-the-user-has-no-select-privilege-on-v-error/。

See also https://blogs.oracle.com/optimizer/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement.

另请参阅https://blogs.oracle.com/optimizer/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement。

---

---

But I've experienced that calling dbms_xplan.display_cursor()right after the executed query may still return unrelated results a in case a multi-thread app uses a shared connection pool.

但我经历过，dbms_xplan.display_cursor()在执行查询之后立即调用可能仍会返回不相关的结果，以防多线程应用程序使用共享连接池。

This can be worked around by searching the most recent sql_idin v$sqlsystem view and providing it as parameter to dbms_xplan.display_cursor.

这可以通过sql_id在v$sql系统视图中搜索最新的并将其作为参数提供给 来解决dbms_xplan.display_cursor。

So here is a ready to use java code to log actual execution plan of recently executed query by its sql (maybe partial).

所以这里是一个随时可用的java代码，通过它的sql（可能是部分的）记录最近执行的查询的实际执行计划。

public void explainActualPlan(String sql, boolean sqlIsPartial, Logger log) {
    if (!log.isTraceEnabled()) return;
    try (Connection connection = dataSource.getConnection()) {
        String sqlId;
        String sqlFilter = sqlIsPartial
                ? "sql_text like '%' || ? || '%'"
                //+ " and parsing_schema_id = sys_context('USERENV', 'CURRENT_SCHEMAID')"
                : (sql.length() <= 1000 ? "sql_text = ?" : "dbms_lob.compare(sql_fulltext, ?) = 0");
        try (PreparedStatement st = connection.prepareStatement(
                "select sql_id from v$sql where " + sqlFilter +
                        " order by last_active_time desc fetch next 1 row only")) {
            st.setString(1, sql);
            try (ResultSet rs = st.executeQuery()) {
                if (rs.next()) {
                    sqlId = rs.getString(1);
                } else {
                    log.warn("Can't find sql_id for sql '{}'. Has it really been just executed?", sql);
                    return;
                }
            }
        }
        String planFormat = "TYPICAL";
        if (sql.contains("GATHER_PLAN_STATISTICS")) {
            planFormat += " ALLSTATS LAST +cost +bytes OUTLINE";
        }
        try (PreparedStatement st = connection.prepareStatement(
                "select plan_table_output from table(dbms_xplan.display_cursor(" +
                        "sql_id => ?, format => '" + planFormat + "'))")) {
            st.setString(1, sqlId);
            try (ResultSet rs = st.executeQuery()) {
                StringBuilder sb = new StringBuilder("Last query plan:\n");
                while (rs.next()) {
                    sb.append(rs.getString(1)).append('\n');
                }
                log.trace(sb.toString());
            }
        }
    } catch (Exception e) {
        log.warn("Failed to explain query plan for '{}'", sql, e);
        log.warn("Check that permissions are granted to the current db user:\n"
                + "GRANT SELECT ON v_$session TO <USER>;\n"
                + "GRANT SELECT ON v_$sql_plan TO <USER>;\n"
                + "GRANT SELECT ON v_$sql_plan_statistics_all TO <USER>;\n"
                + "GRANT SELECT ON v_$sql TO <USER>;\n"
        );
    }
}

Some notes:

一些注意事项：

• Oracle always converts prepared statement params from ?to :nsyntax before storing the query text in v$sql, so searching by sql with ?'s won't find any matches
• both v$sql.sql_text(truncated to first 1000 chars) and v$sql.sql_fulltext(full CLOB) store sql text without line breaks, so it may be needed perform a joinwith V$SQLTEXT_WITH_NEWLINESin case you use them in the query text
• LIKEmatching is used in partial mode, so it may be needed to escape'%' and '_' special chars
• I've checked that Oracle allows to include any unknown strings in the hints comment like /*+ labuda FIRST_ROWS(200) */. It would still apply known hints in case the appendix is valid identifier (is alphanumeric and starts with letter). This may be useful for tracking queries of interest by appending some hashcode to hints clause.
• v@sqlcould be additionally filtered by and parsing_schema_id = sys_context('USERENV', 'CURRENT_SCHEMAID')but this would exclude some plans in case DB instance is used by several similar apps in different schemas with exactly matching sql requests
• the code above supplies additional details in plan output in case sql was executed with GATHER_PLAN_STATISTICShint

• 在将查询文本存储在 中之前，Oracle 总是将准备好的语句参数从 转换?为:n语法v$sql，因此通过带有?' 的sql 搜索不会找到任何匹配项
• 两个v$sql.sql_text（截断至前1000个字符）和v$sql.sql_fulltext（全CLOB）不换行存储SQL文本，因此可能需要对其执行联接用V$SQLTEXT_WITH_NEWLINES的情况下，你在查询文本中使用它们
• LIKE匹配用于部分模式，因此可能需要转义'%' 和 '_' 特殊字符
• 我已经检查过 Oracle 是否允许在提示注释中包含任何未知字符串，例如/*+ labuda FIRST_ROWS(200) */. 如果附录是有效标识符（是字母数字并以字母开头），它仍将应用已知提示。通过将一些哈希码附加到提示子句，这对于跟踪感兴趣的查询可能很有用。
• v@sql可以另外过滤，and parsing_schema_id = sys_context('USERENV', 'CURRENT_SCHEMAID')但这将排除一些计划，以防万一数据库实例被不同架构中的几个类似应用程序使用，并具有完全匹配的 sql 请求
• 上面的代码在计划输出中提供了额外的细节，以防 sql 用GATHER_PLAN_STATISTICS提示执行

Here is an example of above code output for a query from my another answer:

这是我的另一个答案中查询的上述代码输出示例：

22:54:53.558 TRACE o.f.adminkit.AdminKitSelectorQuery - Last query plan:
SQL_ID  c67mmq4wg49sx, child number 0
-------------------------------------
select * from (select * from (select /*+ FIRST_ROWS(200) 
INDEX_RS_DESC("FR_MESSAGE_PART" ("TS")) GATHER_PLAN_STATISTICS */ "ID", 
"MESSAGE_TYPE_ID", "TS", "REMOTE_ADDRESS", "TRX_ID", 
"PROTOCOL_MESSAGE_ID", "MESSAGE_DATA_ID", "TEXT_OFFSET", "TEXT_SIZE", 
"BODY_OFFSET", "BODY_SIZE", "INCOMING" from "FR_MESSAGE_PART" where 
"TS" + 0 >= :1 and "TS" < :2 and "ID" >= 376894993815568384 and "ID" < 
411234940974268416 order by "TS" DESC) where ROWNUM <= 200) offset 180 
rows

Plan hash value: 2499404919

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                  | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                       |      1 |        |       |       |   640K(100)|          |       |       |     20 |00:00:00.01 |     322 |       |       |          |
|*  1 |  VIEW                                     |                       |      1 |    200 |   130K|       |   640K  (1)| 00:00:26 |       |       |     20 |00:00:00.01 |     322 |       |       |          |
|   2 |   WINDOW NOSORT                           |                       |      1 |    200 |   127K|       |   640K  (1)| 00:00:26 |       |       |    200 |00:00:00.01 |     322 |   142K|   142K|          |
|   3 |    VIEW                                   |                       |      1 |    200 |   127K|       |   640K  (1)| 00:00:26 |       |       |    200 |00:00:00.01 |     322 |       |       |          |
|*  4 |     COUNT STOPKEY                         |                       |      1 |        |       |       |            |          |       |       |    200 |00:00:00.01 |     322 |       |       |          |
|   5 |      VIEW                                 |                       |      1 |    780K|   487M|       |   640K  (1)| 00:00:26 |       |       |    200 |00:00:00.01 |     322 |       |       |          |
|*  6 |       SORT ORDER BY STOPKEY               |                       |      1 |    780K|    68M|    89M|   640K  (1)| 00:00:26 |       |       |    200 |00:00:00.01 |     322 | 29696 | 29696 |26624  (0)|
|   7 |        PARTITION RANGE ITERATOR           |                       |      1 |    780K|    68M|       |   624K  (1)| 00:00:25 |     3 |     2 |    400 |00:00:00.01 |     322 |       |       |          |
|*  8 |         COUNT STOPKEY                     |                       |      2 |        |       |       |            |          |       |       |    400 |00:00:00.01 |     322 |       |       |          |
|*  9 |          TABLE ACCESS BY LOCAL INDEX ROWID| FR_MESSAGE_PART       |      2 |    780K|    68M|       |   624K  (1)| 00:00:25 |     3 |     2 |    400 |00:00:00.01 |     322 |       |       |          |
|* 10 |           INDEX RANGE SCAN DESCENDING     | IX_FR_MESSAGE_PART_TS |      2 |    559K|       |       | 44368   (1)| 00:00:02 |     3 |     2 |    400 |00:00:00.01 |       8 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      OPT_PARAM('_optimizer_dsdir_usage_control' 0)
      FIRST_ROWS(200)
      OUTLINE_LEAF(@"SEL")
      OUTLINE_LEAF(@"SEL")
      OUTLINE_LEAF(@"SEL")
      OUTLINE_LEAF(@"SEL")
      NO_ACCESS(@"SEL" "from$_subquery$_004"@"SEL")
      NO_ACCESS(@"SEL" "from$_subquery$_001"@"SEL")
      NO_ACCESS(@"SEL" "from$_subquery$_002"@"SEL")
      INDEX_RS_DESC(@"SEL" "FR_MESSAGE_PART"@"SEL" ("FR_MESSAGE_PART"."TS"))
      END_OUTLINE_DATA
  */

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

   1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber">180)
   4 - filter(ROWNUM<=200)
   6 - filter(ROWNUM<=200)
   8 - filter(ROWNUM<=200)
   9 - filter("ID">=376894993815568384)
  10 - access("TS"<:2)
       filter((INTERNAL_FUNCTION("TS")+0>=:1 AND "TS"<:2))