Oracle mechanics

02.09.2014

Запросы к GV$-обзорам в RAC-системах, включая использование GV$-функций

Filed under: Fixed tables,heuristics,Oracle,RAC,SQL — Игорь Усольцев @ 00:03
Tags:

В кластерных системах Oracle 11g:

11.2.0.3@ SQL> @inst

INST_ID INSTANCE_NAME  VERSION    PLATFORM_NAME        DATABASE_STATUS DATABASE_ROLE    STATUS   OPEN_MODE
------- -------------- ---------- -------------------- --------------- ---------------- -------- ----------
1*      my_inst1       11.2.0.3.0 Linux x86 64-bit     ACTIVE          PRIMARY          OPEN     READ WRITE
2       my_inst2       11.2.0.3.0 Linux x86 64-bit     ACTIVE          PRIMARY          OPEN     READ WRITE

можно наблюдать небыстрое выполнение простых запросов к глобальным обзорам типа GV$SQL, GV$SQL_PLAN, например, по SQL_ID:

11.2.0.3@ SQL> select plan_hash_value from gv$sql_plan where sql_id = 'gwupjcvu3afbv' and other_xml is not null
  2  /

no rows selected

Elapsed: 00:00:09.65 -- небыстро

11.2.0.3@ SQL> select * from table(dbms_xplan.display_cursor(format => '+predicate +alias'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

Plan hash value: 2376390657

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |    72 (100)|          |        |      |    |
|*  1 |  PX COORDINATOR      |             | 29159 |    56M|    72 (100)| 00:00:01 |        |      |    |
|   2 |   PX SEND QC (RANDOM)| :TQ10000    | 29159 |    13M|    72 (100)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    VIEW              | GV$SQL_PLAN |       |       |            |          |  Q1,00 | PCWP |    |
|*  4 |     FIXED TABLE FULL | X$KQLFXPL   | 29159 |    13M|    72 (100)| 00:00:01 |  Q1,00 | PCWP |    |         -- *
-----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / GV$SQL_PLAN@SEL$1
   4 - SEL$2 / P@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("SQL_ID"='gwupjcvu3afbv' AND "OTHER_XML" IS NOT NULL))
   3 - filter(("SQL_ID"='gwupjcvu3afbv' AND "OTHER_XML" IS NOT NULL))
   4 - filter("KQLFXPL_OTHER_XML" IS NOT NULL)

Note
-----
   - statement not queuable: gv$ statement

— невысокая скорость выполнения очевидно обусловлена медленной операцией FIXED TABLE FULL(*), не используемой в запросах к локальному обзору V$SQL_PLAN, , построенному на основе того же GV$SQL_PLAN с указанием INST_ID

Такое поведение не связано с наличием/актуальностью статистики фиксированных объектов (fixed table stats), т.к. наблюдалось как при наличии свежей:

11.2.0.3@ SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KQLFXPL'
  2  /

OWNER                          TABLE_NAME                     LAST_ANAL
------------------------------ ------------------------------ ---------
SYS                            X$KQLFXPL                      29-AUG-14

, так и после удалении статистики:

11.2.0.3@ SQL> exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS

PL/SQL procedure successfully completed.

— в последнем случае используется тот же план (Plan hash value) с отличной (завышенной) cardinality:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

Plan hash value: 2376390657

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |    98 (100)|          |        |      |    |
|*  1 |  PX COORDINATOR      |             |   373K|  7657K|    98 (100)| 00:00:02 |        |      |    |
|   2 |   PX SEND QC (RANDOM)| :TQ10000    |   373K|  7657K|    98 (100)| 00:00:02 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    VIEW              | GV$SQL_PLAN |       |       |            |          |  Q1,00 | PCWP |    |
|   4 |     FIXED TABLE FULL | X$KQLFXPL   |   373K|  7657K|    98 (100)| 00:00:02 |  Q1,00 | PCWP |    |         -- тут
-----------------------------------------------------------------------------------------------------------------

Проблема эффектно решается использованием нечастоупоминаемых, но используемых разработчиками Oracle в 12c EM Express, например, GV$-функций, разделяющих выполнение глобальных запросов на локальные с последующим объединением рез-татов на уровне координатора параллельного выполнения QC / PX COORDINATOR:

11.2.0.3@ SQL> select plan_hash_value from TABLE(GV$(CURSOR(select plan_hash_value from v$sql_plan where sql_id = 'gwupjcvu3afbv' and other_xml is not null)))
  2  /

no rows selected

Elapsed: 00:00:00.02 -- быстро

Plan hash value: 3226796116

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |     1 (100)|        |      |            |
|   1 |  PX COORDINATOR              |                   |     1 |    13 |     0   (0)|        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000          |     1 |  2023 |     0   (0)|  Q1,00 | P->S | QC (RAND)  |
|   3 |    VIEW                      |                   |       |       |            |  Q1,00 | PCWP |            |
|   4 |     VIEW                     | V$SQL_PLAN        |     1 |  2023 |     0   (0)|  Q1,00 | PCWP |            | -- использован локальный
|   5 |      VIEW                    | GV$SQL_PLAN       |     1 |  2036 |     0   (0)|  Q1,00 | PCWP |            | -- поверх глобального обзора
|*  6 |       FIXED TABLE FIXED INDEX| X$KQLFXPL (ind:4) |     1 |   479 |     0   (0)|  Q1,00 | PCWP |            | -- с индексным доступом в рез-те
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter(("KQLFXPL_SQLID"='gwupjcvu3afbv' AND "KQLFXPL_OTHER_XML" IS NOT NULL AND
              "INST_ID"=USERENV('INSTANCE')))                                          -- определяющая план часть условия

Другим очевидным вариантом получения быстрого индексного доступа может быть добавление искусственного условия (fake predicate) с номером инстанса:

11.2.0.3@ SQL> select plan_hash_value from gv$sql_plan where sql_id = 'gwupjcvu3afbv' and other_xml is not null and inst_id > 0
  2  /

no rows selected

Elapsed: 00:00:00.02

Plan hash value: 2115402216

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name              | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                   |       |       |     1 (100)|        |      |            |
|*  1 |  PX COORDINATOR            |                   |     1 |  2036 |     0   (0)|        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ10000          |     1 |   479 |     0   (0)|  Q1,00 | P->S | QC (RAND)  |
|*  3 |    VIEW                    | GV$SQL_PLAN       |       |       |            |  Q1,00 | PCWP |            |
|*  4 |     FIXED TABLE FIXED INDEX| X$KQLFXPL (ind:4) |     1 |   479 |     0   (0)|  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

В RAC 12c тот же быстрый план используется автоматически, безо всяких хитростей:

12.1.0.2@ SQL> select plan_hash_value from gv$sql_plan where sql_id = 'gwupjcvu3afbv' and other_xml is not null
  2  /

no rows selected

Elapsed: 00:00:00.05

Plan hash value: 2115402216 -- тот же план

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name              | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                   |       |       |     1 (100)|        |      |            |
|*  1 |  PX COORDINATOR            |                   |       |       |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ10000          |     1 |   740 |     0   (0)|  Q1,00 | P->S | QC (RAND)  |
|*  3 |    VIEW                    | GV$SQL_PLAN       |       |       |            |  Q1,00 | PCWP |            |
|*  4 |     FIXED TABLE FIXED INDEX| X$KQLFXPL (ind:4) |     1 |   740 |     0   (0)|  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("OTHER_XML" IS NOT NULL AND "SQL_ID"='gwupjcvu3afbv'))
   3 - filter(("OTHER_XML" IS NOT NULL AND "SQL_ID"='gwupjcvu3afbv'))
   4 - filter(("KQLFXPL_SQLID"='gwupjcvu3afbv' AND "KQLFXPL_OTHER_XML" IS NOT NULL)) -- **

**) — при этом индексный доступ к структуре X$KQLFXPL стал доступен без указания INST_ID, из чего можно заключить, что структура в новой версии переработана в сторону однозначного улучшения — пора обновляться)

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

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

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