Oracle mechanics

22.02.2017

Hintset Object

Filed under: Oracle,Oracle 12c,parameters,shared pool — Игорь Усольцев @ 18:52
Tags: ,

The plan_baseline hintset is just the list of hints SPM tried to use to reproduce the plan

В ТОПе Library Cache периодически появлялся непривычный объект:

SQL> select *
  2    from (select hash_value,
  3                 substr(name, 1, 100) as object_name,
  4                 namespace,
  5                 type,
  6                 kept,
  7                 count(*),
  8                 min(timestamp),
  9                 sum(locked_total),
 10                 sum(pinned_total),
 11                 sum(loads),
 12                 sum(executions),
 13                 sum(sharable_mem)
 14            from v$db_object_cache
 15           group by hash_value,
 16                    substr(name, 1, 100),
 17                    namespace,
 18                    type,
 19                    kept
 20           order by sum(sharable_mem) desc)
 21   where rownum <= 3
 22  /
 
HASH_VALUE OBJECT_NAME          NAMESPACE      TYPE           KEPT COUNT(*) MIN(TIMESTAMP)      SUM(LOCKED_TOTAL) SUM(PINNED_TOTAL) SUM(LOADS) SUM(EXECUTIONS) SUM(SHARABLE_MEM)
---------- -------------------- -------------- -------------- ---- -------- ------------------- ----------------- ----------------- ---------- --------------- -----------------
2209848120 6004199150836888961  HINTSET OBJECT HINTSET OBJECT NO          1                                 14872             14872          4               0         625277440
 838046317 select               SQL AREA       CURSOR         NO        459 2016-11-02/10:54:33            442985            288445      79518          180659         155325176
 269081421 select               SQL AREA       CURSOR         NO        627 2016-10-31/00:07:03            273128            218432     143717           91650         154551720
...

необычайно крупного размера SHARABLE_MEM > 600MB с цифровым наименованием, под коим числилось 2 объекта:

SQL> select * from V$DB_OBJECT_CACHE where name = '6004199150836888961';
 
OWNER NAME                DB_LINK NAMESPACE              TYPE                   SHARABLE_MEM      LOADS EXECUTIONS      LOCKS       PINS KEPT CHILD_LATCH INVALIDATIONS HASH_VALUE LOCK_MODE PIN_MODE  STATUS PREVIOUS_TIMESTAMP  LOCKED_TOTAL PINNED_TOTAL
----- ------------------- ------- ---------------------- ---------------------- ------------ ---------- ---------- ---------- ---------- ---- ----------- ------------- ---------- --------- --------- ------ ------------------- ------------ ------------
      6004199150836888961         SQL TUNING BASE OBJECT SQL TUNING BASE OBJECT         4096        298          0          0          0 NO         81370             0  618479066 NONE      NONE      VALID  2017-02-04/14:58:57         8243         8243
      6004199150836888961         HINTSET OBJECT         HINTSET OBJECT            629924992          7          0          0          0 NO        105272             0 2209848120 NONE      NONE      VALID                              3413         3413

— судя по наименованям типов, относящихся к SQL Management Base, где в качестве NAME используется SIGNATURE SPM объекта, в этом случае — Baseline-а, которых для этой SIGNATURE оказалось:

SQL> select count(*) from dba_sql_plan_baselines where signature = 6004199150836888961;
 
  COUNT(*)
----------
       667

SQL> select * from dba_sql_plan_baselines where signature = 6004199150836888961;
 
 SIGNATURE SQL_HANDLE           SQL_TEXT PLAN_NAME                      ORIGIN       VERSION    CREATED            LAST_MODIFIED      LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE ADAPTIVE OPTIMIZER_COST MODULE ACTION EXECUTIONS ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED    FETCHES END_OF_FETCH_COUNT
---------- -------------------- -------- ------------------------------ ------------ ---------- ------------------ ------------------ ------------- ------------- ------- -------- ----- ---------- --------- -------- -------------- ------ ------ ---------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ------------------
6,00419915 SQL_53533350ea55b581 INSERT   SQL_PLAN_56ntma3p5bdc100b27ad6 AUTO-CAPTURE 12.1.0.2.0 17-SEP-16 11.09.09 17-SEP-16 11.09.09                             YES     NO       NO    YES        YES       NO               249230                        0            0          0           0          0             0              0          0                  0
...
667 rows selected

— в основном в статусе UNACCEPTED

Определив SQL_ID для этих Baseline-ов:

SQL> @bl_find_SQL_ID SQL_53533350ea55b581 SQL_PLAN_56ntma3p5bdc100b27ad6
 
SQL_ID        PLAN_HASH_VALUE SQL_TYPE        SQL_EXEC_TIMESTAMP   SQL_HANDLE           PLAN_NAME                      ENABLED ACCEPTED FIXED REPRODUCED BL_LAST_EXECUTED
------------- --------------- --------------- -------------------- -------------------- ------------------------------ ------- -------- ----- ---------- ----------------
4twn1z5sqfrrm               0 Baseln not used                      SQL_53533350ea55b581 SQL_PLAN_56ntma3p5bdc100b27ad6 YES     NO       NO    YES

, можно видеть, что что для этого запроса когда-то 26.05.2016 вручную с благими целями был создан SQL Plan Baseline в статусе ACCEPTED:

SQL> @spm_check4sql_id 4twn1z5sqfrrm
 
SIGNATURE             SPM_TYPE          SQL_HANDLE           PATCH_NAME                     ORIGIN      VERSION    CREATED             LAST_MODIFIED       LAST_EXECUTED       LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ----------------- -------------------- ------------------------------ ----------- ---------- ------------------- ------------------- ------------------- ------------- ------- -------- ----- ---------- ---------
  6004199150836888961 SQL Plan Baseline SQL_53533350ea55b581 SQL_PLAN_56ntma3p5bdc1691f8954 MANUAL-LOAD 12.1.0.2.0 26.05.2016 14:30:40 29.01.2017 02:25:28 29.01.2017 02:25:28               YES     YES      NO    YES        YES

— к сожалению, незафиксированный FIXED = NO, что по правилам SPM за несколько месяцев существования/использования позволило автоматически по мере генерации подгрузить/сгенерировать 666 новых Baseline-ов в статусе ACCEPTED = NO:

SQL> select count(distinct extractvalue(xmlval, '/*/info[@type = "plan_hash"]')) as DISTINCT_PHV
  2  from
  3   (select xmltype(other_xml) as xmlval
  4                     from sys.sqlobj$plan op, sys.sqlobj$ o
  5                    where o.obj_type = 2
  6                      and op.obj_type = 2
  7                      and o.name in (select plan_name from dba_sql_plan_baselines where signature = 6004199150836888961)
  8                      and o.signature = op.signature
  9                      and o.plan_id   = op.plan_id
 10                      and op.other_xml is not null)
 11  /
 
DISTINCT_PHV
------------
         667

SQL> select count(distinct PLAN_NAME) as DISTINCT_PHV2 from dba_sql_plan_baselines where signature = 6004199150836888961;
 
DISTINCT_PHV2
-------------
          667

— с 667 различными планами выполнения (судя по PLAN_HASH_VALUE и PHV2), что неудивительно для запроса типа:

SQL> @spb12 SQL_PLAN_56ntma3p5bdc1691f8954
 
SKIP DISP OPERATION                          OPTIMIZER    COST CARDINALITY TIME
---- ---- ---------------------------------- --------- ------- ----------- ----
        0   INSERT STATEMENT                 ALL_ROWS   221156             
        1     LOAD TABLE CONVENTIONAL                        0             
...
      616                 TABLE ACCESS FULL                666      149075    1
                                                                                                                       
 
 
617 rows selected            -- строк в плане
 
Notes
--------------------------------------------------------------------------------
   sql_profile:
   sql_patch:
   baseline:
   outline:
   dyn_sampling:    2        -- Adaptive Dynamic Sampling
   dop:
   dop_reason:
   card_feedback:   yes      -- Statistics Feedback
   perf_feedback:
   adaptive_plan:            -- не хватает)
   spd_used:        4        -- SQL Plan Directives
   spd_valid:       33
   gtt_sess_stat:
   db_version:      12.1.0.2
   plan_hash_full:  3237297515
   plan_hash:       3938539332
   plan_hash_2:     1763674452
 
OUTLINE_HINTS
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
...
PQ_DISTRIBUTE(@"SEL$DEBAA3C7" "CL"@"SEL$17" HASH HASH)
PQ_DISTRIBUTE(@"SEL$DEBAA3C7" "CL0"@"SEL$20" NONE BROADCAST)
PQ_DISTRIBUTE(@"SEL$DEBAA3C7" "CLT"@"SEL$18" NONE BROADCAST)
PQ_DISTRIBUTE(@"SEL$DEBAA3C7" "CAT"@"SEL$21" NONE BROADCAST)
SWAP_JOIN_INPUTS(@"SEL$DEBAA3C7" "CL0"@"SEL$20")
SWAP_JOIN_INPUTS(@"SEL$DEBAA3C7" "CLT"@"SEL$18")
SWAP_JOIN_INPUTS(@"SEL$DEBAA3C7" "CAT"@"SEL$21")
 
1463 rows selected           -- Outline Hints count

Одновременно с этим Сергей Щукин наблюдал симптоматичные предупреждения с виде ORA-600 по тому же поводу:

Tue Feb 07 10:40:54 2017
Memory Notification: Library Cache Object loaded into SGA
Heap size 591819K exceeds notification threshold (1048576K)
Details in trace file /opt/oracle/base/diag/rdbms/orcl/orcl2/trace/orcl2_p00c_30349.trc
Tue Feb 07 10:40:54 2017
KGL object name :6004199150836888961 DEFAULT --<<<<<<<<<<<<<< SPB signature = Kernel General Library object name
Errors in file /opt/oracle/base/diag/rdbms/orcl/orcl2/trace/orcl2_p00c_30349.trc  (incident=864715):
ORA-00600: код внутренней ошибки, аргументы: [KGL-heap-size-exceeded], [0x26B7347F10], [2], [606023512], [], [], [], [], [], [], [], []

, регулируемые параметрами — Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded] (Doc ID 330239.1):

SQL> @param+ _kgl_large_heap_%_threshold
 
NAME                                 SESS_VALUE INST_VALUE IS_SESS_MOD IS_SYS_MOD DSC
------------------------------------ ---------- ---------- ----------- ---------- -------------------------------------------------------------
_kgl_large_heap_warning_threshold    1073741824 1073741824 FALSE       IMMEDIATE  maximum heap size before KGL writes warnings to the alert log
_kgl_large_heap_assert_threshold     524288000  524288000  FALSE       IMMEDIATE  maximum heap size before KGL raises an internal error

После быстрого/очевидного решения в виде:

SQL> exec :n := DBMS_SPM.alter_sql_plan_baseline('SQL_53533350ea55b581','SQL_PLAN_56ntma3p5bdc1691f8954','fixed','yes');
 
PL/SQL procedure successfully completed

SQL> exec :n := DBMS_SPM.alter_sql_plan_baseline('SQL_53533350ea55b581','SQL_PLAN_56ntma3p5bdc1691f8954','autopurge','no');
 
PL/SQL procedure successfully completed

SQL> declare
  2    n number;
  3  begin
  4    for reco in (select SQL_HANDLE, PLAN_NAME
  5                   from dba_sql_plan_baselines
  6                  where signature = 6004199150836888961
  7                    and accepted = 'NO')
  8    loop
  9      :n := dbms_spm.drop_sql_plan_baseline(reco.sql_handle, reco.plan_name);
 10    end loop;
 11  end;
 12  /
 
PL/SQL procedure successfully completed

ситуация с проблемным KGL объектом заметно улучшилась:

SQL> select * from V$DB_OBJECT_CACHE where name = '6004199150836888961';
 
OWNER NAME                NAMESPACE              TYPE                   SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEPT CHILD_LATCH INVALIDATIONS HASH_VALUE LOCK_MODE PIN_MODE  STATUS LOCKED_TOTAL PINNED_TOTAL
----- ------------------- ---------------------- ---------------------- ------------ ----- ---------- ----- ---- ---- ----------- ------------- ---------- --------- --------- ------ ------------ ------------
      6004199150836888961 SQL TUNING BASE OBJECT SQL TUNING BASE OBJECT         4096     2          0     0    0 NO         81370             0  618479066 NONE      NONE      VALID           702          702
      6004199150836888961 HINTSET OBJECT         HINTSET OBJECT               788672     1          0     0    0 NO        105272             0 2209848120 NONE      NONE      VALID           160          160

— объект SPB, состоящий из одного Baseline, занимает в Shared Pool менее 1MB, что согласуется с предыдущими наблюдениями, когда тот же SPB объект из 667 Baseline-ов занимал 600+MB и, вероятно, подгружался в KGL «целиком» для использования и/или обновления, например, в случае добавления очередного Baseline к той же signature

Реклама

1 комментарий »

  1. замечательно!!!

    комментарий от vesselova — 17.10.2017 @ 12:25 | Ответить


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 такие блоггеры, как: