Для ускорения/оптимизации выполнения запроса коллега Сергей Перегудин проанализировал и предложил поправить/зафиксировать план важного, но замедлившегося запроса:
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 */
Оставьте комментарий