JDBC Oracle-Fetch expliquer le plan de requête
je me demande comment je peux récupérer le plan expliquer en utilisant Java. La raison pour laquelle j'en ai besoin est que nous disposons d'un cadre dans lequel les utilisateurs spéciaux peuvent élaborer des rapports. Ces rapports construisent parfois d'énormes requêtes dans lesquelles nous voulons expliquer à la volée et stocker le coût de. De cette façon, nous pouvons analyser les requêtes à coût élevé plus tard et optimiser.
exemple de code qui me donne l'exception de colonne illégale:
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));
}
11
demandé sur
Vadzim
2010-12-07 14:59:46
2 réponses
utilisez ceci:
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));
}
17
répondu
a_horse_with_no_name
2010-12-07 12:36:22
il y a aussi un moyen de montrer le vrai plan utilisé pour exécuter la dernière requête dans cette session via DBMS_XPLAN.DISPLAY_CURSOR . La requête d'intérêt n'a pas besoin d'être préparée avec 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));
}
}
}
notez que l'utilisateur doit obtenir les permissions suivantes pour utiliser 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;
crédits passer à https://myoracledbablog.wordpress.com/2016/07/26/dbms_xplan-and-the-user-has-no-select-privilege-on-v-error / .
Voir aussi https://blogs.oracle.com/optimizer/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement .
mais j'ai expérimenté que l'appel dbms_xplan.display_cursor() juste après la requête exécutée peut toujours retourner des résultats sans rapport a dans le cas où une application multi-thread utilise un piscine de connexion partagée.
cela peut être contourné en recherchant le plus récent sql_id dans v$sql vue système et en Le fournissant comme paramètre à dbms_xplan.display_cursor .
voici donc un code java prêt à être utilisé pour enregistrer le plan d'exécution d'une requête récemment exécutée par son sql (peut-être partiel).
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"
);
}
}
quelques notes:
- Oracle convertit toujours préparation d'une déclaration param de
?à:nsyntaxe avant de stocker le texte de la requête dansv$sql, donc la recherche par sql avec?'s ne trouvera pas de correspondance - à la fois
v$sql.sql_text(tronqué à la première 1000 chars) etv$sql.sql_fulltext(full CLOB) stocker le texte sql sans sauts de ligne, il peut être nécessaire d'effectuer une jointure avecV$SQLTEXT_WITH_NEWLINESdans le cas où vous les utilisez dans le texte de requête -
LIKEl'appariement est utilisé en mode partiel, il peut donc être nécessaire de escape ' % ' et ' _ 'caractères spéciaux - J'ai vérifié que Oracle permet d'inclure n'importe quelles chaînes inconnues dans le commentaire d'indices comme
/*+ labuda FIRST_ROWS(200) */. Il s'appliquerait toujours aux indices connus dans le cas où l'appendice est un identificateur valide (alphanumérique et commençant par une lettre). Ceci peut être utile pour suivre les requêtes d'intérêt en ajoutant du hashcode à la clause hints. -
v@sqlpourrait en outre être filtré parand parsing_schema_id = sys_context('USERENV', 'CURRENT_SCHEMAID')mais cela exclurait certains plans dans le cas où l'instance de DB est utilisée par plusieurs applications similaires dans différents schémas avec exactement les requêtes sql correspondant - le code ci-dessus fournit des détails supplémentaires dans le plan de sortie dans le cas où sql a été exécuté avec
GATHER_PLAN_STATISTICSindice
voici un exemple de code de sortie ci-dessus pour une requête de mon autre réponse :
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))
2
répondu
Vadzim
2018-05-11 15:20:05