Oracle mechanics

15.04.2016

Рекурсивно-адаптивный SQL_ID «frjd8zfy2jfdq» версии 12.1.0.2

Filed under: Oracle,shared pool — Игорь Усольцев @ 23:45
Tags:

Максим Филатов обратил внимание на заметно High Version Count запрос:

SQL ordered by Version Count         DB/Inst: ORCL/ORCL1  Snaps: 101565-101567
-> Only Statements with Version Count greater than 20 are displayed

 Version                           
  Count   Executions     SQL Id    
-------- ------------ -------------
     916          N/A frjd8zfy2jfdq
...

, судя по множеству источников / MODULE-й:

SQL> select decode(session_type, 'BACKGROUND', REGEXP_SUBSTR(program, '\([^\)]+\)'), nvl2(qc_session_id, 'PX', 'USER')) as PROGRAM_TYPE,
  2         count(*),
  3         count(distinct module)
  4    from gv$active_session_history
  5   where sql_id = 'frjd8zfy2jfdq'
  6   group by decode(session_type, 'BACKGROUND', REGEXP_SUBSTR(program, '\([^\)]+\)'), nvl2(qc_session_id, 'PX', 'USER'))
  7  /
 
PROGRAM_TYPE                                       COUNT(*) COUNT(DISTINCTMODULE)
------------------------------------------------ ---------- ---------------------
PX                                                       66                    43

— похожий на рекурсивный, выполняемый, в основном, PX-slave процессами

Как оказалось, запрос этот удостоен отдельной ноты Frequent Execution of SQL_ID «frjd8zfy2jfdq» in 12.1.0.2 (Doc ID 2059121.1):

The query (SQL_ID "frjd8zfy2jfdq") is executed as part of the sql metrics collector as a result of new Adaptive Optimization Features in 12.1.0.2

— где для избавления от последствий этого запроса рекомендуют

Disable Adaptive Optimization Features
или
Disable SQL Directives (в части использования SPD директив параметром _optimizer_dsdir_usage_control=0)

В случае ERP-системы, запрос этот особых проблем не создавал, за исключением формального высокого High Version Count:

SQL> select inst_id, count(*) from gv$sql where sql_id = 'frjd8zfy2jfdq' group by inst_id;
 
INST_ID   COUNT(*)
------- ----------
      1       1517
      2       3208

, но сам по себе оказался любопытным, например, в причинах High Version Count можно видеть несколько разнонаправленных факторов:)

SQL> @shared_cu12s_noxml frjd8zfy2jfdq
 
INST_ID PLAN_HASH_VALUE OPTIMIZER_MODE LAST_ACTIVE_TIME    BIND_SENSE BIND_AWARE FEEDBACK_STATS OPT_STATS REOPT ADAPT BIND_EQ_FAIL BIND_MISMATCH LANGUAGE_MISMATCH STATS_ROW_MISMATCH PQ_SLAVE_MISMATCH REASON#1                           REASON#2                          FIX_CONTROL#1 CURSOR_COUNT  PHV_COUNT FPHV_COUNT      EXECS USERS_OPENING
------- --------------- -------------- ------------------- ---------- ---------- -------------- --------- ----- ----- ------------ ------------- ----------------- ------------------ ----------------- ---------------------------------- --------------------------------- ------------- ------------ ---------- ---------- ---------- -------------
      2       510421217 CHOOSE         15.04.2016 11:43:49 N          N          N              N         N           N            N             Y                 N                  N                 NLS Settings(2)                    PQ Slave mismatch(2)                                     341          1          1          0             0
      2       510421217 CHOOSE         15.04.2016 11:50:02 N          N          N              N         N           N            N             Y                 N                  N                 Optimizer mismatch(12)             NLS Settings(1)                                          333          1          1          0             0
      1       510421217 CHOOSE         15.04.2016 12:27:55 N          N          N              N         N           N            N             Y                 N                  N                 PQ Slave mismatch(2)               NLS Settings(2)                                          327          1          1          0             0
      1       510421217 CHOOSE         15.04.2016 12:09:48 N          N          N              N         N           N            N             Y                 N                  N                 Optimizer mismatch(3)              Optimizer mismatch(12)                                   324          1          1       7987             2
      2       510421217 CHOOSE         15.04.2016 10:33:08 N          N          N              N         N           N            N             N                 N                  Y                 Statistics Row Source mismatch(0)  PQ Slave mismatch(2)                                     309          1          1          0             0
      2       510421217 CHOOSE         14.04.2016 12:05:22 N          N          N              N         N           N            N             Y                 N                  N                 PQ Slave mismatch(2)               Optimizer mismatch(12)                                   305          1          1          0             0
      2       510421217 CHOOSE         15.04.2016 12:32:23 N          N          N              N         N           N            N             Y                 N                  N                 NLS Settings(1)                    NLS Settings(2)                                          305          1          1          0             0
      2       510421217 CHOOSE         15.04.2016 12:27:55 N          N          N              N         N           N            N             Y                 N                  N                 PQ Slave mismatch(2)               NLS Settings(2)                                          303          1          1          0             0
      1       510421217 CHOOSE         15.04.2016 12:08:36 N          N          N              N         N           N            N             N                 N                  Y                 Optimizer mismatch(3)              Optimizer mismatch(12)                                   243          1          1          0             0
      2       510421217 CHOOSE         15.04.2016 11:27:16 N          N          N              N         N           N            N             Y                 N                  N                 Optimizer mismatch(12)             NLS Settings(2)                                          165          1          1          0             0
      2       510421217 CHOOSE         15.04.2016 11:20:42 N          N          N              N         N           N            N             N                 N                  Y                 Optimizer mismatch(12)             NLS Settings(2)                                          160          1          1          0             0
      1       510421217 CHOOSE         14.04.2016 11:32:26 N          N          N              N         N           N            N             Y                 N                  N                 Optimizer mismatch(12)             PQ Slave mismatch(2)                                     158          1          1          0             0
      2       510421217 CHOOSE         14.04.2016 07:12:19 N          N          N              N         N           N            N             N                 N                  Y                 PQ Slave mismatch(2)               Optimizer mismatch(12)                                   137          1          1          0             0
      1       510421217 CHOOSE         15.04.2016 12:27:57 N          N          N              N         N           N            N             N                 N                  Y                 Statistics Row Source mismatch(0)  PQ Slave mismatch(2)                                     134          1          1          0             0
      2       510421217 CHOOSE         14.04.2016 12:08:04 N          N          N              N         N           N            N             Y                 N                  N                 Statistics Row Source mismatch(0)  PQ Slave mismatch(2)                                     126          1          1       2669             1
      1       510421217 CHOOSE         15.04.2016 10:15:16 N          N          N              N         N           N            N             N                 N                  Y                 Optimizer mismatch(12)             Statistics Row Source mismatch(0)                        120          1          1          0             0
      1       510421217 CHOOSE         14.04.2016 07:12:20 N          N          N              N         N           N            N             N                 N                  Y                 Optimizer mismatch(12)             PQ Slave mismatch(2)                                     117          1          1          0             0
      2       510421217 CHOOSE         15.04.2016 12:13:12 N          N          N              N         N           N            N             Y                 N                  N                 PQ Slave mismatch(2)               NLS Settings(1)                                          106          1          1          0             0
      2       510421217 CHOOSE         15.04.2016 00:01:30 N          N          N              N         N           N            N             N                 N                  Y                 Optimizer mismatch(12)             Statistics Row Source mismatch(0)                        104          1          1          0             0
      1       510421217 CHOOSE         15.04.2016 12:27:43 N          N          N              N         N           N            N             N                 N                  Y                 PQ Slave mismatch(2)               NLS Settings(2)                                          103          1          1          0             0
...
 
138 rows selected

— одним из основных является LANGUAGE_MISMATCH / NLS Settings(2), что ожидаемо из кол-ва исходных Module-й

Различия NLS Settings можно понаблюдать по содержимому V$SQL_SHARED_CURSOR.REASON:

SQL> select
  2      inst_id,
  3      s.PLAN_HASH_VALUE,
  4      REGEXP_SUBSTR ( dbms_lob.substr(reason,4000), '<(NLS_TERRITORY)>([^<]+)</\1>', 1, 1, NULL, 2)     as NLS_TERRITORY,
  5      REGEXP_SUBSTR ( dbms_lob.substr(reason,4000), '<(NLS_CURRENCY)>([^<]+)</\1>', 1, 1, NULL, 2)      as NLS_CURRENCY,
  6      count(*) as CURSOR_COUNT,
  7      sum(s.EXECUTIONS)    as EXECS,
  8      sum(s.users_opening) as users_opening
  9    from gv$sql_shared_cursor sc join gv$sql s
 10         using (inst_id, sql_id, child_address)
 11   where sql_id = 'frjd8zfy2jfdq'
 12     and language_mismatch = 'Y'
 13   group by
 14      inst_id,
 15      s.PLAN_HASH_VALUE,
 16      REGEXP_SUBSTR ( dbms_lob.substr(reason,4000), '<(NLS_TERRITORY)>([^<]+)</\1>', 1, 1, NULL, 2),
 17      REGEXP_SUBSTR ( dbms_lob.substr(reason,4000), '<(NLS_CURRENCY)>([^<]+)</\1>', 1, 1, NULL, 2)
 18   order by inst_id, count(*) desc
 19  /
 
INST_ID PLAN_HASH_VALUE NLS_TERRITORY            NLS_CURRENCY   CURSOR_COUNT      EXECS USERS_OPENING
------- --------------- ------------------------ -------------- ------------ ---------- -------------
      1       510421217                                                  466      33965            16
      1       510421217 'CIS'->'RUSSIA'                                  143       1741             7
      1       510421217 'RUSSIA'->'CIS'                                  138     180146            78
      1       510421217 'AMERICA'->'RUSSIA'      '$'->'р.'                37       4161             8
      1       510421217 'RUSSIA'->'AMERICA'      'р.'->'$'                36       8480             1
      1       510421217 'AMERICA'->'CIS'         '$'->'р.'                19       1809             3
      1       510421217 'CIS'->'AMERICA'         'р.'->'$'                13        184             0
      1       510421217 'RUSSIA'->'SWITZERLAND'  'р.'->'SFr.'             12         69             0
      1       510421217 'RUSSIA'->'FINLAND'      'р.'->'€'                11     131631             1
...

76 rows selected

Далее, несмотря на вышеприведённый V$SQL.OPTIMIZER_MODE=CHOOSE, запрос выполняется по RULE правилам:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('frjd8zfy2jfdq', 1,format => 'all allstats +outline -projection -predicate'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
SQL_ID  frjd8zfy2jfdq, child number 1
-------------------------------------
SELECT executions, end_of_fetch_count,
elapsed_time/px_servers elapsed_time,        cpu_time/px_servers
cpu_time,            buffer_gets/executions  buffer_gets   FROM (SELECT
sum(executions)   as executions,                            sum(case
when px_servers_executions > 0                              then
px_servers_executions                                  else executions
end) as px_servers,                sum(end_of_fetch_count) as
end_of_fetch_count,              sum(elapsed_time) as elapsed_time,
            sum(cpu_time)     as cpu_time,
sum(buffer_gets)  as buffer_gets            FROM   gv$sql
                       WHERE executions > 0
    AND sql_id = :1                                    AND
parsing_schema_name = :2)

Plan hash value: 510421217
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Starts |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |    112 |        |      |            |    112 |00:00:01.04 |
|   1 |  VIEW                        |                           |    112 |        |      |            |    112 |00:00:01.04 |
|   2 |   SORT AGGREGATE             |                           |    112 |        |      |            |    112 |00:00:01.04 |
|*  3 |    PX COORDINATOR            |                           |    112 |        |      |            |   2699 |00:00:00.84 |
|   4 |     PX SEND QC (RANDOM)      | :TQ10000                  |      0 |  Q1,00 | P->S | QC (RAND)  |      0 |00:00:00.01 |
|*  5 |      VIEW                    | GV$SQL                    |      0 |  Q1,00 | PCWP |            |      0 |00:00:00.01 |
|*  6 |       FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) |      0 |  Q1,00 | PCWP |            |      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2 / from$_subquery$_001@SEL$1
   2 - SEL$2
   6 - SEL$3 / X$KGLCURSOR_CHILD@SEL$3
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      RBO_OUTLINE                                   -- RBO
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$3" "X$KGLCURSOR_CHILD"@"SEL$3")
      END_OUTLINE_DATA
  */
Note
-----
   - rule based optimizer used (consider using cbo) -- Oracle рекурсивно рекомендует использовать CBO

При попытке снять Optimizer trace можно столкнуться с определённым казусом:

SQL> begin
  2  dbms_sqldiag.dump_trace(
  3     p_sql_id=>'frjd8zfy2jfdq',
  4     p_child_number => 0,
  5     p_component=>'Optimizer',
  6     p_file_id=>'RBO_Trace');
  7  end;
  8  /
 
begin
dbms_sqldiag.dump_trace(
   p_sql_id=>'frjd8zfy2jfdq',
   p_child_number => 0,
   p_component=>'Optimizer',
   p_file_id=>'RBO_Trace');
end;
 
ORA-01422: exact fetch returns more than requested number of rows -- как же так?
ORA-06512: at "SYS.DBMS_SQLDIAG", line 1379
ORA-06512: at line 3

, объясняемым тем, что для этого запроса (пока только для него) на один CHILD_NUMBER в V$SQL приходится по несколько записей с разными CHILD_ADDRESS (!):

SQL> select inst_id,
  2         sql_id,
  3         child_number,
  4         count(distinct child_address),
  5         count(*)
  6    from gv$sql
  7   group by inst_id, sql_id, child_number
  8  having count(*) > 1
  9   order by count(*) desc
 10  /
 
INST_ID SQL_ID        CHILD_NUMBER COUNT(DISTINCTCHILD_ADDRESS)   COUNT(*)
------- ------------- ------------ ---------------------------- ----------
      2 frjd8zfy2jfdq           37                            5          5
      2 frjd8zfy2jfdq            0                            5          5
      1 frjd8zfy2jfdq            8                            5          5
      2 frjd8zfy2jfdq           42                            5          5
      2 frjd8zfy2jfdq            3                            5          5
      2 frjd8zfy2jfdq          867                            4          4
      2 frjd8zfy2jfdq          958                            4          4
      2 frjd8zfy2jfdq           61                            4          4
...
 
1936 rows selected

, что, видимо, не было предусмотрено создателями DBMS_SQLDIAG, посему выбирать CHILD_NUMBER следует внимательно:)

SQL> select *
  2    from (select sql_id, child_number, count(*)
  3            from v$sql
  4           where sql_id = 'frjd8zfy2jfdq'
  5           group by sql_id, child_number
  6          having count(*) = 1)
  7   where rownum <= 1
  8  /
 
SQL_ID        CHILD_NUMBER   COUNT(*)
------------- ------------ ----------
frjd8zfy2jfdq          647          1

SQL> begin
  2  dbms_sqldiag.dump_trace(
  3     p_sql_id=>'frjd8zfy2jfdq',
  4     p_child_number => 647,
  5     p_component=>'Optimizer',    --Valid values are "Optimizer" and "Compiler"
  6     p_file_id=>'RBO_Trace');
  7  end;
  8  /
 
PL/SQL procedure successfully completed

И уже из полученного трейса можно видеть:

$ less ORCL1_ora_19701_RBO_Trace.trc

**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
kkopqSetForceParallelProperties: Hint:no
Query: compute:yes forced:no forceDop:0
kkopqSetDopReason: Reason why we chose this DOP is: table property. -- т.е. кроме того что запрос к X$KGLCURSOR_CHILD является глобальным в RAC
table property forces parallelism                                   --  есть ещё и table property

============
Plan Table
============
-----------------------------------------------------------------+-----------------------------------+-------------------------+
| Id  | Operation                     | Name                     | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib |
-----------------------------------------------------------------+-----------------------------------+-------------------------+
| 0   | SELECT STATEMENT              |                          |       |       |     1 |           |      |      |           |
| 1   |  VIEW                         |                          |     1 |    78 |     0 |           |      |      |           |
| 2   |   SORT AGGREGATE              |                          |     1 |   103 |       |           |      |      |           |
| 3   |    PX COORDINATOR             |                          |       |       |       |           |      |      |           |
| 4   |     PX SEND QC (RANDOM)       | :TQ10000                 |     1 |   103 |     0 |           |:Q1000| P->S |QC (RANDOM)|
| 5   |      VIEW                     | GV$SQL                   |       |       |       |           |:Q1000| PCWP |           |
| 6   |       FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2)|     1 |   103 |     0 |           |:Q1000| PCWP |           |
-----------------------------------------------------------------+-----------------------------------+-------------------------+
Predicate Information:
----------------------
3 - filter(("PARSING_SCHEMA_NAME"=:2 AND "SQL_ID"=:1 AND "EXECUTIONS">0))
5 - filter(("PARSING_SCHEMA_NAME"=:2 AND "SQL_ID"=:1 AND "EXECUTIONS">0))
6 - filter(("KGLOBT03"=:1 AND "KGLOBTS4"=:2 AND "KGLOBT05">0))

Content of other_xml column
===========================
  db_version     : 12.1.0.2
  parse_schema   : SYS
  plan_hash_full : 2617420261
  plan_hash      : 510421217
  plan_hash_2    : 2617420261
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      NO_ACCESS(@"SEL$2" "GV$SQL"@"SEL$2")
      FULL(@"SEL$3" "X$KGLCURSOR_CHILD"@"SEL$3")
    END_OUTLINE_DATA
  */

— собственно запрос вполне CBO-совместим в отличие от реально выполняемых курсоров, использующих RBO и отображаемых с V$SQL.OPTIMIZER_MODE=CHOOSE, что как бы намекает, что рекурсивные запросы sql_id «frjd8zfy2jfdq» запускаются в RBO окружении на уровне сессии с модифицированным параметром optimizer_mode = RULE

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

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

RSS feed for comments on this post. TrackBack URI

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

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: