jdbc获取不到oracle9记录,JDBC Oracle - 获取查询的解释计划

还有一种方法可以通过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));

}

}

}

请注意，需要为用户授予以下权限才能使用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;

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

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

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

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

所以这里是一个准备好使用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 ;n"

+ "GRANT SELECT ON v_$sql_plan TO ;n"

+ "GRANT SELECT ON v_$sql_plan_statistics_all TO ;n"

+ "GRANT SELECT ON v_$sql TO ;n"

);

}

}

一些说明：

在将查询文本存储到v$sql之前，Oracle始终将准备好的语句参数从?转换为:n语法，因此使用?的sql搜索将找不到任何匹配项

v$sql.sql_text(截断到前1000个字符)和v$sql.sql_fulltext(完整CLOB)存储sql文本没有换行符，因此如果您在查询文本中使用它们，可能需要执行与V$SQLTEXT_WITH_NEWLINES的连接

LIKE匹配用于部分模式，因此可能需要转义'％'和'_'特殊字符

我已经检查过Oracle允许在提示注释中包含任何未知字符串，如/*+ labuda FIRST_ROWS(200) */。如果附录是有效的标识符(是字母数字并以字母开头)，它仍然会应用已知的提示。这可以通过将一些哈希码附加到hints子句来跟踪感兴趣的查询。

v@sql可以通过and parsing_schema_id = sys_context('USERENV', 'CURRENT_SCHEMAID')进行额外过滤，但这将排除一些计划，以防数据库实例被不同模式中的几个类似应用程序使用，并且具有完全匹配的sql请求

如果用GATHER_PLAN_STATISTICS提示执行sql，上面的代码会在计划输出中提供其他详细信息

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

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$3")

OUTLINE_LEAF(@"SEL$2")

OUTLINE_LEAF(@"SEL$1")

OUTLINE_LEAF(@"SEL$4")

NO_ACCESS(@"SEL$4" "from$_subquery$_004"@"SEL$4")

NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")

NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")

INDEX_RS_DESC(@"SEL$3" "FR_MESSAGE_PART"@"SEL$3" ("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>