JDBC Oracle - 쿼리에 대한 설명 계획 가져오기

JDBC Oracle - 쿼리에 대한 설명 계획 가져오기

자바를 사용하여 설명 계획을 어떻게 가져올 수 있는지 궁금합니다.제가 이것을 필요로 하는 이유는 특별한 사용자가 보고서를 작성할 수 있는 프레임워크가 있기 때문입니다.이러한 보고서는 때때로 우리가 즉시 설명하고 비용을 저장하고자 하는 거대한 쿼리를 구축합니다.이러한 방식으로 나중에 고비용 쿼리를 분석하고 최적화할 수 있습니다.

잘못된 열 예외를 제공하는 코드 예제:

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).테스트);stmt = oracle.createStatement()stmt.execute("Dual에서 SELECT 1에 대한 계획 설명");rs = stmt.dll쿼리("테이블(dbms_xplan.display())에서 plan_table_output 선택");그러는 동안 (rs. next()){System.out.println(rs.getString(1));}

를 통해 이 세션에서 마지막 쿼리를 실행하는 데 사용되는 실제 계획을 표시하는 방법도 있습니다.관심 쿼리 앞에 다음을 추가할 필요는 없습니다.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()실행된 쿼리 직후에도 다중 스레드 앱이 공유 연결 풀을 사용하는 경우 관련 없는 결과가 반환될 수 있습니다.

이 문제는 가장 최근의 검색을 통해 해결할 수 있습니다.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 <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"
        );
    }
}

참고 사항:

• Oracle에서 된 문 합니다.?:n를 리쿼텍저전구의문에 v$sql 로서그 sql검래색다로 합니다.?일치하는 항목을 찾을 수 없습니다.
• 둘다요.v$sql.sql_text) 및 (1,000자 미만) 및v$sql.sql_fulltext(전체 CLOB) 줄 바꿈 없이 SQL 텍스트를 저장하므로 쿼리 텍스트에서 사용하는 경우 조인을 수행해야 할 수 있습니다.
• LIKE일치는 부분 모드에서 사용되므로 '%' 및 '_' 특수 문자를 이스케이프해야 할 수 있습니다.
• Oracle이 다음과 같은 힌트 댓글에 알 수 없는 문자열을 포함할 수 있음을 확인했습니다./*+ labuda FIRST_ROWS(200) */부록이 유효한 식별자(영숫자이며 문자로 시작함)인 경우에도 알려진 힌트를 적용합니다.이는 일부 해시 코드를 힌트 절에 추가하여 관심 쿼리를 추적하는 데 유용할 수 있습니다.
• v@sql추가로 필터링할 수 있음and parsing_schema_id = sys_context('USERENV', 'CURRENT_SCHEMAID')그러나 DB 인스턴스가 서로 다른 스키마의 여러 유사한 앱에서 정확히 일치하는 sql 요청을 사용하는 경우에는 일부 계획이 제외됩니다.
• 위의 코드는 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))

언급URL : https://stackoverflow.com/questions/4376329/jdbc-oracle-fetch-explain-plan-for-query