Oracle mechanics

14.02.2024

Oracle 19: Сравнение Query Block Registry

Filed under: CBO,Oracle,Scripts,SQL Tuning — Игорь Усольцев @ 00:37
Tags: ,

Для ускорения/оптимизации выполнения запроса коллега Сергей Перегудин проанализировал и предложил поправить/зафиксировать план важного, но замедлившегося запроса:

SQL> @sql_profile_from_sql 947wjnz21sdv1 3090864955 947wjnz21sdv1_3090864955 947wjnz21sdv1_3090864955

PL/SQL procedure successfully complete

, что привело к хорошему ожидаемому рез-ту:

SQL> @shared_cu12 947wjnz21sdv1 0 11

INST EXECS USERS_OPENING FIRST_LOAD_TIME      LAST_LOAD_TIME       PARSE_USER LAST_ACTIVE_TIME    ROWS_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC PARSES_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC PLSQL_PER_EXEC CURSOR_STATUS       PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE CBO_MODE   OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  CF  REOPT REOPT_HINTS V$SQL.Adapt XML.Adapt SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON NOT_SHARED_BY REASON SQL_PROFILE               IS_OBSOLETE
---- ----- ------------- -------------------- -------------------- ---------- ------------------- ------------- ------------ ------------ --------------- ------------- -------------- ------------ ------------- ------------ -------------- ------------------- --------------- -------------------- ---------- -------------- ----- ---------- ---------- ---------- --- ----- ----------- ----------- --------- --------- --------- -------- --- ---------- ------------- ------ ------------------------- -----------
   1     3             0 2024-02-09/15:30:26  2024-02-09/15:30:26  APPS       09.02.2024 16:00:39             0       472888       305120               1           958              5         1098        193184         7265              0 INVALID_UNAUTH           3090864955           1605889768 ALL_ROWS           246634     0 N          N          Y              N                 N                                                                                                                N          
   3     2             0 2024-02-09/15:29:44  2024-02-09/15:29:44  APPS       09.02.2024 16:00:47             0       696668       448275               1          1494             66         9580        286532         7219              0 INVALID_UNAUTH           3090864955           1605889768 ALL_ROWS           246636     0 N          N          Y              N                 N                                                                                                                N          
   2     1             1 2024-02-09/17:30:34  2024-02-09/17:30:34  APPS       09.02.2024 17:31:55             0     81856619     21530087               1        898437         286982     62166268        637855      5207870          52903 VALID                    3090864955           1605889768 ALL_ROWS          1130752     0 N          N          Y              N                 N                                                                                      947wjnz21sdv1_3090864955  N          
   4     3             1 2024-02-09/10:41:27  2024-02-09/10:41:27  APPS       09.02.2024 17:31:56             0   8218294609   8177073682               1     174802168         183115     11756834        179604      3877313           6539 INVALID_UNAUTH           2165674671           2799055495 ALL_ROWS           657548     0 N          N          Y              N                 N                                                                                                                N          

--------------------------------------------------------------
SQL_ID=947wjnz21sdv1 Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------

INST PARSING_SCHEMA EXECS PLAN_HASH_VALUE LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PARSES_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC PLSQL_PER_EXEC FETCH_PER_EXEC ROWS_PER_EXEC AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PROFILE
---- -------------- ----- --------------- -------------------- ------------------- ------------ ------------ --------------- ------------- -------------- ------------ ------------- ------------ -------------- -------------- ------------- ------------ ---------- ---------- ---------- ----- ----- -------------------------
   1 APPS               3      3090864955 2024-02-09/15:30:26  09.02.2024 16:00:39       472888       305120               1           958              5         1098        193184         7265              0              1             0       246634          1 N          N          N     N     
   3 APPS               2      3090864955 2024-02-09/15:29:44  09.02.2024 16:00:47       696668       448275               1          1494             66         9580        286532         7219              0              1             0       246636          1 N          N          N     N     
   2 APPS               1      3090864955 2024-02-09/17:30:34  09.02.2024 17:31:55     81856619     21530087               1        898437         286982     62166268        637855      5207870          52903              0             0      1130752          1 N          N          N     N     947wjnz21sdv1_3090864955
   4 APPS               3      2165674671 2024-02-09/10:41:27  09.02.2024 17:31:56   8218294609   8177073682               1     174802168         183115     11756834        179604      3877313           6539              1             0       657548          1 N          N          N     N     

--------------------------------------------------------------
ASH TOP11 SQL_ID=947wjnz21sdv1 Executions by Elapsed Time
--------------------------------------------------------------

INST_ID    SID    SERIAL# SQL_ID        SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE SQL_EXEC_ID CHILD C   ASH_ROWS         PX SQL_EXEC_START            DURATIONs                 MIN_SAMPLE_TIME           MAX_SAMPLE_TIME           TEMP_SPACE_ALLOCATED
------- ------ ---------- ------------- ------------------- ------------------------ ----------- ----- - ---------- ---------- ------------------------- ------------------------- ------------------------- ------------------------- --------------------
      4   8738      53404 947wjnz21sdv1          2165674671               2799055495    67108864     0 *      24575          1 09.02.2024 10:41:28       +000000000 06:50:27.654   09-ФЕВ-24 10.41.29.271 AM 09-ФЕВ-24 05.31.56.925 PM           2764046336
      1  11580      11427 947wjnz21sdv1          2165674671               2799055495    16777216     0         7631          1 08.02.2024 10:32:51       +000000000 02:07:26.089   09-ФЕВ-24 09.27.24.942 AM 09-ФЕВ-24 11.34.51.031 AM           1519910912
      2   4026      26231 947wjnz21sdv1          3090864955               1605889768    33554433     0 *         82          1 09.02.2024 17:30:34       +000000000 00:01:21.187   09-ФЕВ-24 05.30.35.794 PM 09-ФЕВ-24 05.31.56.981 PM              1048576
      4   6265      53412 947wjnz21sdv1          2165674671               2799055495    67108866     0           20          1 09.02.2024 15:32:04       +000000000 00:00:19.039   09-ФЕВ-24 03.32.05.614 PM 09-ФЕВ-24 03.32.24.653 PM              1048576
      4   7912      31730 947wjnz21sdv1          2165674671               2799055495    67108865     0            6          1 09.02.2024 15:30:39       +000000000 00:00:05.010   09-ФЕВ-24 03.30.40.439 PM 09-ФЕВ-24 03.30.45.449 PM            459800576
...

— видно, что зафиксированный PHV 3090864955 , предварительно на пару порядков быстрее чем прежний PHV 2165674671 (подтвердилось впоследствии)

Внимание привлекло различие длин «хорошего» и плохого планов:

SQL> select plan_hash_value, max(id)
  2    from dba_hist_sql_plan
  3   where sql_id = '947wjnz21sdv1'
  4     and plan_hash_value in (2165674671, 3090864955)
  5     and dbid = sys_context('userenv', 'DBID')
  6   group by plan_hash_value;

PLAN_HASH_VALUE    MAX(ID)
--------------- ----------
     3090864955        207
     2165674671       1392

Сравнивать планы по содержимому Outline в подобном случае не очень удобно по причине объёмов различий:

SQL> @sql_plan_diff_outl_v2 947wjnz21sdv1          2165674671 947wjnz21sdv1          3090864955 %

-------------------------------
SQL Plan "Outline" sections diff
-------------------------------

PHV_2165674671
----------------------------------------------------------------------------------------------------
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$A0674A9D_1" "CB"@"SEL$58")
...
USE_CONCAT(@"SEL$A0674A9D" 8 OR_PREDICATES(17)) -- *
...
USE_NL(@"SEL$A0674A9D_2" "T_0002"@"SEL$A0674A9D_2")
----------------------------------------------------------------------------------------------------
PHV_3090864955
----------------------------------------------------------------------------------------------------
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$A0674A9D" "FDP"@"SEL$45")
...

136 rows selected

(хотя и таким способом можно найти признаки применения OR-expansion > Concatenation (*) среди сотни других различий)

Логичнее и удобнее использовать скрипт анализа различий использованных при построении планов Oracle Optimizer Transformations, основанный на сравнении секций Query Block Registry (qbregistry_query.sql by Jonathan Lewis), и получить в наблюдаемом случае простой ответ:

SQL> @qbregistry19-2 947wjnz21sdv1 2165674671 947wjnz21sdv1 3090864955

QBREG
--------------------------------------------------------------------------------------
      SEL$A0674A9D_1 (OR EXPANSION (USE_CONCAT) SEL$A0674A9D ) [final]
      SEL$A0674A9D_2 (OR EXPANSION (USE_CONCAT) SEL$A0674A9D ) [final]

, отражающий различия «карт трансформаций» (transformation maps):

SQL> @qbregistry19 947wjnz21sdv1 2165674671

ORDSEQ QBREG
------ -----------------------------------------------------------------------------------------------------------------------
...
    73   SEL$22E677CA (VIEW MERGE (MERGE) SEL$45 ; SEL$0ACB5ABB,SEL$0C1B02A2,SEL$28447C5D,SEL$55,SEL$56,SEL$57,SEL$68,SEL$84)
    74     SEL$A0674A9D (JOIN REMOVED FROM QUERY BLOCK (ELIMINATE_JOIN) SEL$22E677CA ) [final]
    75       SEL$A0674A9D_1 (OR EXPANSION (USE_CONCAT) SEL$A0674A9D ) [final]
    76       SEL$A0674A9D_2 (OR EXPANSION (USE_CONCAT) SEL$A0674A9D ) [final]
...

146 rows selected

SQL> @qbregistry19 947wjnz21sdv1 3090864955

ORDSEQ QBREG
------ -----------------------------------------------------------------------------------------------------------------------
...
    73   SEL$22E677CA (VIEW MERGE (MERGE) SEL$45 ; SEL$0ACB5ABB,SEL$0C1B02A2,SEL$28447C5D,SEL$55,SEL$56,SEL$57,SEL$68,SEL$84)
    74     SEL$A0674A9D (JOIN REMOVED FROM QUERY BLOCK (ELIMINATE_JOIN) SEL$22E677CA ) [final]
...

144 rows selected

, которые и приводят к различию кол-ва строк планов выполнения

P.S. Но для преобразования OR-expansion это не предел:)
и если EXPLAIN PLAN того же запроса с фиксированным планом даёт предсказуемый/закреплённый рез-т:

SQL> explain plan for
  2  SELECT
  3   *
  4    FROM (SELECT (SELECT CC.SEGMENT1
...
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3090864955

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                         |     1 |  3567 |   277K  (1)| 00:00:11 |
|   1 |  TABLE ACCESS BY INDEX ROWID                | GL_CODE_COMBINATIONS    |     1 |    11 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN                         | GL_CODE_COMBINATIONS_U1 |     1 |       |     2   (0)| 00:00:01 |
...
|*205 |      FILTER                                 |                         |       |       |            |          |
|*206 |       CONNECT BY WITHOUT FILTERING (UNIQUE) |                         |       |       |            |          |
| 207 |        FAST DUAL                            |                         |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

  14 -  SEL$22 / EMP@SEL$22
         U -  OLD_PUSH_PRED(@"SEL$22" "EMP"@"SEL$22" ("PER_ALL_PEOPLE_F"."PERSON_ID"))
         U -  USE_NL(@"SEL$22" "EMP"@"SEL$22") / hint overridden by another in parent query block

Note
-----
   - SQL profile "947wjnz21sdv1_3090864955" used for this statement

, даже несмотря на пару Unused hints из содержимого SQL profile

, то план запроса с использованием неограниченного OR-Expansion только для того же Query block SEL$A0674A9D даёт уже 2542 строк плана:

SQL> explain plan for
  2  SELECT /*+ use_concat(@"SEL$A0674A9D") */
  3   *
  4    FROM (SELECT (SELECT CC.SEGMENT1
...
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3111441362

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                         |     1 |  3567 |   248K  (1)| 00:00:10 |
|   1 |  TABLE ACCESS BY INDEX ROWID                | GL_CODE_COMBINATIONS    |     1 |    11 |     3   (0)| 00:00:01 |
|   2 |   INDEX UNIQUE SCAN                         | GL_CODE_COMBINATIONS_U1 |     1 |       |     2   (0)| 00:00:01 |
...
|2540 |      FILTER                                 |                         |       |       |            |          |
|2541 |       CONNECT BY WITHOUT FILTERING (UNIQUE) |                         |       |       |            |          |
|2542 |        FAST DUAL                            |                         |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      ...
      USE_CONCAT(@"SEL$A0674A9D" 8 OR_PREDICATES(17 80 251))
      ...
      END_OUTLINE_DATA
  */

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Оставьте комментарий

Блог на WordPress.com.