Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
Кентурион Крысобой
Member

Откуда:
Сообщений: 202
Здравствуйте.

DDL:
drop table tdetl;
drop table thead;
create table thead(id numeric not null, f01 numeric not null, f02 numeric not null);
create table tdetl(id numeric primary key, hid numeric, h01 numeric, h02 numeric);
alter table thead add constraint thead_pk primary key(id); 
alter table thead add constraint thead_unq unique(f01,f02);
alter table tdetl add constraint tdetl_fk2 foreign key(h01,h02) references thead(f01,f02);
Требуется получить по имени таблицы ('TDETL') информацию о том, есть ли у неё внешние ключи и, если есть, то куда и на какие столбцы в родит. таблицах они ссылаются.
Запускаю на выполнение:
select 
    c.owner
    ,c.table_name
    ,c.constraint_name
    ,c.r_constraint_name
    ,f.column_name
    ,f.position
    ,rf.table_name main_table
    ,rf.column_name main_key
from all_constraints c
    ,all_cons_columns f 
    ,all_constraints r
    ,all_cons_columns rf
where 
    c.table_name='TDETL'
    and c.constraint_type='R'
    and c.owner=f.owner and c.constraint_name=f.constraint_name and c.table_name=f.table_name
    and c.r_constraint_name=r.constraint_name
    and f.position=rf.position
    and r.owner=rf.owner and r.constraint_name=rf.constraint_name and r.table_name=rf.table_name;

Получаю отвратительное время (7..8 сек, после второго и последующих перезапусков оно НЕ уменьшается).
Вот результаты в SQLPlus:
+
SQL> select
  2      c.owner
  3      ,c.table_name
  4      ,c.constraint_name
  5      ,c.r_constraint_name
  6      ,f.column_name
  7      ,f.position
  8      ,rf.table_name main_table
  9      ,rf.column_name main_key
 10  from all_constraints c
 11      ,all_cons_columns f
 12      ,all_constraints r
 13      ,all_cons_columns rf
 14  where
 15      c.table_name='TDETL'
 16      and c.constraint_type='R'
 17      and c.owner=f.owner and c.constraint_name=f.constraint_name and c.table_name=f.table_name
 18      and c.r_constraint_name=r.constraint_name
 19      and f.position=rf.position
 20      and r.owner=rf.owner and r.constraint_name=rf.constraint_name and r.table_name=rf.table_name;

Затрач.время: 00:00:07.65

План выполнения
----------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                 | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                      |     1 |   892 |   253 |
|   1 |  FILTER                                                      |                      |       |       |       |
|   2 |   NESTED LOOPS                                               |                      |     1 |   892 |   253 |
|   3 |    NESTED LOOPS OUTER                                        |                      |     1 |   875 |   252 |
|   4 |     NESTED LOOPS                                             |                      |     1 |   871 |   251 |
|   5 |      NESTED LOOPS                                            |                      |     1 |   760 |   250 |
|   6 |       NESTED LOOPS OUTER                                     |                      |     1 |   738 |   249 |
|   7 |        NESTED LOOPS                                          |                      |     1 |   691 |   248 |
|   8 |         NESTED LOOPS                                         |                      |     1 |   671 |   247 |
|   9 |          NESTED LOOPS                                        |                      |     1 |   635 |   245 |
|  10 |           NESTED LOOPS                                       |                      |     1 |   619 |   243 |
|  11 |            HASH JOIN                                         |                      |     1 |   606 |   242 |
|  12 |             NESTED LOOPS                                     |                      |     1 |   581 |   222 |
|  13 |              NESTED LOOPS                                    |                      |     1 |   559 |   221 |
|  14 |               NESTED LOOPS OUTER                             |                      |     1 |   520 |   219 |
|  15 |                NESTED LOOPS OUTER                            |                      |     1 |   518 |   216 |
|  16 |                 NESTED LOOPS OUTER                           |                      |     1 |   514 |   215 |
|  17 |                  NESTED LOOPS OUTER                          |                      |     1 |   509 |   214 |
|  18 |                   NESTED LOOPS                               |                      |     1 |   500 |   213 |
|  19 |                    HASH JOIN                                 |                      |     1 |   479 |   212 |
|  20 |                     NESTED LOOPS                             |                      |     1 |   454 |   193 |
|  21 |                      NESTED LOOPS                            |                      |     1 |   432 |   192 |
|  22 |                       NESTED LOOPS OUTER                     |                      |     1 |   415 |   191 |
|  23 |                        NESTED LOOPS                          |                      |     1 |   368 |   190 |
|  24 |                         NESTED LOOPS                         |                      |     1 |   348 |   189 |
|  25 |                          NESTED LOOPS                        |                      |     1 |   312 |   187 |
|  26 |                           NESTED LOOPS                       |                      |     2 |   592 |   183 |
|  27 |                            HASH JOIN                         |                      |     2 |   566 |   181 |
|  28 |                             HASH JOIN                        |                      |     2 |   516 |   161 |
|  29 |                              HASH JOIN OUTER                 |                      |     2 |   472 |   160 |
|  30 |                               NESTED LOOPS                   |                      |     2 |   464 |   158 |
|  31 |                                NESTED LOOPS                  |                      |     2 |   242 |   156 |
|  32 |                                 NESTED LOOPS                 |                      |     2 |   192 |   154 |
|  33 |                                  NESTED LOOPS OUTER          |                      |     2 |   142 |   152 |
|  34 |                                   NESTED LOOPS OUTER         |                      |     2 |   134 |   151 |
|  35 |                                    NESTED LOOPS              |                      |     2 |   124 |   149 |
|  36 |                                     NESTED LOOPS OUTER       |                      |    17 |   391 |   115 |
|  37 |                                      TABLE ACCESS FULL       | CDEF$                |    17 |   357 |    64 |
|  38 |                                      VIEW PUSHED PREDICATE   | _CURRENT_EDITION_OBJ |     1 |     2 |     3 |
|  39 |                                       FILTER                 |                      |       |       |       |
|  40 |                                        NESTED LOOPS          |                      |     1 |    34 |     3 |
|  41 |                                         INDEX RANGE SCAN     | I_OBJ1               |     1 |    12 |     2 |
|  42 |                                         INDEX RANGE SCAN     | I_USER2              |     1 |    22 |     1 |
|  43 |                                        NESTED LOOPS          |                      |     1 |    29 |     2 |
|  44 |                                         INDEX FULL SCAN      | I_USER2              |     1 |    20 |     1 |
|  45 |                                         INDEX RANGE SCAN     | I_OBJ4               |     1 |     9 |     1 |
|  46 | WID                                 TABLE ACCESS BY INDEX RO | OBJ$                 |     1 |    39 |     2 |
|  47 |                                      INDEX RANGE SCAN        | I_OBJ1               |     1 |       |     1 |
|  48 |                                    INDEX RANGE SCAN          | I_OBJ1               |     1 |     5 |     1 |
|  49 |                                   TABLE ACCESS CLUSTER       | USER$                |     1 |     4 |     1 |
|  50 |                                    INDEX UNIQUE SCAN         | I_USER#              |     1 |       |     0 |
|  51 |                                  TABLE ACCESS BY INDEX ROWID | CON$                 |     1 |    25 |     1 |
|  52 |                                   INDEX UNIQUE SCAN          | I_CON2               |     1 |       |     0 |
|  53 |                                 TABLE ACCESS BY INDEX ROWID  | CON$                 |     1 |    25 |     1 |
|  54 |                                  INDEX UNIQUE SCAN           | I_CON2               |     1 |       |     0 |
|  55 |                                TABLE ACCESS CLUSTER          | USER$                |     1 |   111 |     1 |
|  56 |                                 INDEX UNIQUE SCAN            | I_USER#              |     1 |       |     0 |
|  57 |                               INDEX FULL SCAN                | I_USER2              |    96 |   384 |     1 |
|  58 |                              INDEX FULL SCAN                 | I_USER2              |    96 |  2112 |     1 |
|  59 |                             TABLE ACCESS FULL                | CON$                 | 14707 |   359K|    19 |
|  60 |                            TABLE ACCESS BY INDEX ROWID       | CDEF$                |     1 |    13 |     1 |
|  61 |                             INDEX UNIQUE SCAN                | I_CDEF1              |     1 |       |     0 |
|  62 |                           TABLE ACCESS BY INDEX ROWID        | CCOL$                |     1 |    16 |     2 |
|  63 |                            INDEX RANGE SCAN                  | I_CCOL1              |     1 |       |     1 |
|  64 |                          TABLE ACCESS BY INDEX ROWID         | OBJ$                 |     1 |    36 |     2 |
|  65 |                           INDEX RANGE SCAN                   | I_OBJ1               |     1 |       |     1 |
|  66 |                         TABLE ACCESS BY INDEX ROWID          | COL$                 |     1 |    20 |     1 |
|  67 |                          INDEX UNIQUE SCAN                   | I_COL3               |     1 |       |     0 |
|  68 |                        TABLE ACCESS CLUSTER                  | ATTRCOL$             |     1 |    47 |     1 |
|  69 |                       TABLE ACCESS BY INDEX ROWID            | USER$                |     1 |    17 |     1 |
|  70 |                        INDEX UNIQUE SCAN                     | I_USER1              |     1 |       |     0 |
|  71 |                      INDEX RANGE SCAN                        | I_USER2              |     1 |    22 |     1 |
|  72 |                     TABLE ACCESS FULL                        | CON$                 | 14707 |   359K|    19 |
|  73 |                    TABLE ACCESS BY INDEX ROWID               | CDEF$                |     1 |    21 |     1 |
|  74 |                     INDEX UNIQUE SCAN                        | I_CDEF1              |     1 |       |     0 |
|  75 |                   TABLE ACCESS BY INDEX ROWID                | CON$                 |     1 |     9 |     1 |
|  76 |                    INDEX UNIQUE SCAN                         | I_CON2               |     1 |       |     0 |
|  77 |                  INDEX RANGE SCAN                            | I_OBJ1               |     1 |     5 |     1 |
|  78 |                 INDEX RANGE SCAN                             | I_USER2              |     1 |     4 |     1 |
|  79 |                VIEW PUSHED PREDICATE                         | _CURRENT_EDITION_OBJ |     1 |     2 |     3 |
|  80 |                 FILTER                                       |                      |       |       |       |
|  81 |                  NESTED LOOPS                                |                      |     1 |    34 |     3 |
|  82 |                   INDEX RANGE SCAN                           | I_OBJ1               |     1 |    12 |     2 |
|  83 |                   INDEX RANGE SCAN                           | I_USER2              |     1 |    22 |     1 |
|  84 |                  NESTED LOOPS                                |                      |     1 |    29 |     2 |
|  85 |                   INDEX FULL SCAN                            | I_USER2              |     1 |    20 |     1 |
|  86 |                   INDEX RANGE SCAN                           | I_OBJ4               |     1 |     9 |     1 |
|  87 |               TABLE ACCESS BY INDEX ROWID                    | OBJ$                 |     1 |    39 |     2 |
|  88 |                INDEX RANGE SCAN                              | I_OBJ1               |     1 |       |     1 |
|  89 |              INDEX RANGE SCAN                                | I_USER2              |     1 |    22 |     1 |
|  90 |             TABLE ACCESS FULL                                | CON$                 | 14707 |   359K|    19 |
|  91 |            TABLE ACCESS BY INDEX ROWID                       | CDEF$                |     1 |    13 |     1 |
|  92 |             INDEX UNIQUE SCAN                                | I_CDEF1              |     1 |       |     0 |
|  93 |           TABLE ACCESS BY INDEX ROWID                        | CCOL$                |     1 |    16 |     2 |
|  94 |            INDEX RANGE SCAN                                  | I_CCOL1              |     1 |       |     1 |
|  95 |          TABLE ACCESS BY INDEX ROWID                         | OBJ$                 |     1 |    36 |     2 |
|  96 |           INDEX RANGE SCAN                                   | I_OBJ1               |     1 |       |     1 |
|  97 |         TABLE ACCESS BY INDEX ROWID                          | COL$                 |     1 |    20 |     1 |
|  98 |          INDEX UNIQUE SCAN                                   | I_COL3               |     1 |       |     0 |
|  99 |        TABLE ACCESS CLUSTER                                  | ATTRCOL$             |     1 |    47 |     1 |
| 100 |       INDEX RANGE SCAN                                       | I_USER2              |     1 |    22 |     1 |
| 101 |      TABLE ACCESS CLUSTER                                    | USER$                |     1 |   111 |     1 |
| 102 |       INDEX UNIQUE SCAN                                      | I_USER#              |     1 |       |     0 |
| 103 |     INDEX RANGE SCAN                                         | I_USER2              |     1 |     4 |     1 |
| 104 |    TABLE ACCESS BY INDEX ROWID                               | USER$                |     1 |    17 |     1 |
| 105 |     INDEX UNIQUE SCAN                                        | I_USER1              |     1 |       |     0 |
| 106 |   NESTED LOOPS                                               |                      |     1 |    22 |     2 |
| 107 |    INDEX RANGE SCAN                                          | I_OBJAUTH1           |     1 |     9 |     2 |
| 108 |    FIXED TABLE FULL                                          | X$KZSRO              |     1 |    13 |     0 |
| 109 |   FIXED TABLE FULL                                           | X$KZSPR              |     1 |    26 |     0 |
| 110 |   NESTED LOOPS                                               |                      |     1 |    22 |     2 |
| 111 |    INDEX RANGE SCAN                                          | I_OBJAUTH1           |     1 |     9 |     2 |
| 112 |    FIXED TABLE FULL                                          | X$KZSRO              |     1 |    13 |     0 |
| 113 |   FIXED TABLE FULL                                           | X$KZSPR              |     1 |    26 |     0 |
| 114 |   NESTED LOOPS                                               |                      |     1 |    22 |     2 |
| 115 |    INDEX RANGE SCAN                                          | I_OBJAUTH1           |     1 |     9 |     2 |
| 116 |    FIXED TABLE FULL                                          | X$KZSRO              |     1 |    13 |     0 |
| 117 |   FIXED TABLE FULL                                           | X$KZSPR              |     1 |    26 |     0 |
| 118 |   NESTED LOOPS                                               |                      |     1 |    22 |     2 |
| 119 |    INDEX RANGE SCAN                                          | I_OBJAUTH1           |     1 |     9 |     2 |
| 120 |    FIXED TABLE FULL                                          | X$KZSRO              |     1 |    13 |     0 |
| 121 |   FIXED TABLE FULL                                           | X$KZSPR              |     1 |    26 |     0 |
| 122 |   NESTED LOOPS                                               |                      |     1 |    29 |     2 |
| 123 |    INDEX FULL SCAN                                           | I_USER2              |     1 |    20 |     1 |
| 124 |    INDEX RANGE SCAN                                          | I_OBJ4               |     1 |     9 |     1 |
| 125 |   NESTED LOOPS                                               |                      |     1 |    29 |     2 |
| 126 |    INDEX FULL SCAN                                           | I_USER2              |     1 |    20 |     1 |
| 127 |    INDEX RANGE SCAN                                          | I_OBJ4               |     1 |     9 |     1 |
| 128 |   NESTED LOOPS                                               |                      |     1 |    29 |     2 |
| 129 |    INDEX FULL SCAN                                           | I_USER2              |     1 |    20 |     1 |
| 130 |    INDEX RANGE SCAN                                          | I_OBJ4               |     1 |     9 |     1 |
| 131 |   NESTED LOOPS                                               |                      |     1 |    29 |     2 |
| 132 |    INDEX FULL SCAN                                           | I_USER2              |     1 |    20 |     1 |
| 133 |    INDEX RANGE SCAN                                          | I_OBJ4               |     1 |     9 |     1 |
---------------------------------------------------------------------------------------------------------------------


Note
-----
   - 'PLAN_TABLE' is old version


Статистика
----------------------------------------------------------
         99  recursive calls
          0  db block gets
        375  consistent gets
          3  physical reads
          0  redo size
        859  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed


При втором и последующих перезапусках этого запроса вижу "Затрач.время: 00:00:00.01", но дальше выводится фраза "План выполнения" (без самого плана), после чего всё опять застревает примерно на 7..8 сек

Как ускорить выполнение ? Подскажите, плз, что делаю не так.

PS.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
16 авг 11, 20:12    [11129136]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
Кентурион Крысобой
Member

Откуда:
Сообщений: 202
Кентурион Крысобой
При втором и последующих перезапусках <...> всё опять застревает примерно на 7..8 сек
Наврал я: застревает только при первом запуске после alter system flush shared_pool (что ожидаемо) и втором (что непонятно). Третий и последующий выполняются "на ура".
В отчете tkprof'a, однако, вижу только статистику по первому и второму, но не по последующим запускам:
call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 7.33 7.34 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.12 0.12 0 2237 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 7.46 7.46 0 2237 0 2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107

< skipped >

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 7.34 7.35 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 10 0.21 0.21 0 5790 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 7.56 7.56 0 5790 0 10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107

В общем, вопрос остаётся в силе: как ускорить запрос в стартовом посте.
16 авг 11, 21:05    [11129302]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
Мир труд жвачка
Member

Откуда:
Сообщений: 1527
Да мало ли чего, dynamic sampling, cardinality feedback и т.д.
Смотри трейс глубже.
16 авг 11, 21:09    [11129312]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
-2-
Member

Откуда:
Сообщений: 15330
Кентурион Крысобой
В общем, вопрос остаётся в силе: как ускорить запрос в стартовом посте.
Во-первых, добавить овнера таблицы.
Во-вторых, использовать DBA_, поскольку ALL_ еще фильтруют доступные права.
В-третьих:
select *
from xmltable('/ROWSET/ROW/*/CON2_LIST/CON2_LIST_ITEM' passing xmltype(dbms_metadata.get_xml('TABLE','REGISTRY$DEPENDENCIES','SYS'))
   columns name
      , col_names varchar2(1000) path 'string-join(SRC_COL_LIST/SRC_COL_LIST_ITEM/COL/NAME, ", ")'
      , "SCHEMA_OBJ/OWNER_NAME", "SCHEMA_OBJ/NAME"
      , ref_names varchar2(1000) path 'string-join(TGT_COL_LIST/TGT_COL_LIST_ITEM/COL/NAME, ", ")'
     );

NAME                 COL_NAMES               SCHEMA_OBJ/OWNER_NAME   SCHEMA_OBJ/NAME            REF_NAMES                  
-------------------- ----------------------- ----------------------- -------------------------- ---------------------------
DEPENDENCIES_FK      NAMESPACE, CID          SYS                     REGISTRY$                  NAMESPACE, CID             
DEPENDENCIES_REQ_FK  REQ_NAMESPACE, REQ_CID  SYS                     REGISTRY$                  NAMESPACE, CID             

Elapsed: 00:00:00.07
16 авг 11, 21:14    [11129321]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
Кентурион Крысобой
Member

Откуда:
Сообщений: 202
-2-,

спасибо, завтра обязательно попробую пп. 1 и 2. Третий пока не смогу - мне его надо будет вкуривать :-)
16 авг 11, 21:23    [11129342]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Кентурион Крысобой,

сабж - вечный, видать, баян
16 авг 11, 22:57    [11129630]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
Кентурион Крысобой
Member

Откуда:
Сообщений: 202
-2-
Во-первых, добавить овнера таблицы.
Во-вторых, использовать DBA_, поскольку ALL_ еще фильтруют доступные права.
Добавил в условия соединения поле owner, заменил ALL_ на DBA_.
Стало лучше, но ненамного: 5 сек против 8.
А еще весьма интересует, почему запрос начинает летать (0.04 сек) только с третьего запуска, а не со второго ?
+
SQL> set timing on
SQL> set autot traceonly statistics
SQL> select
  2      c.owner
  3      ,c.table_name
  4      ,c.constraint_name
  5      ,c.r_constraint_name
  6      ,f.column_name
  7      ,f.position
  8      ,rf.table_name main_table
  9      ,rf.column_name main_key
 10  from dba_constraints c
 11      ,dba_cons_columns f
 12      ,dba_constraints r
 13      ,dba_cons_columns rf
 14  where
 15      c.table_name='TDETL'
 16      and c.constraint_type='R'
 17      and c.owner=f.owner --------------------------- c, f
 18      and c.constraint_name=f.constraint_name
 19      and c.table_name=f.table_name
 20      and c.r_constraint_name=r.constraint_name
 21      and f.owner=r.owner --------------------------- f, r
 22      and f.position=rf.position
 23      and r.owner=rf.owner -------------------------- r, rf
 24      and r.constraint_name=rf.constraint_name
 25      and r.table_name=rf.table_name;

Затрач.время: 00:00:05.11


Статистика
----------------------------------------------------------
       1930  recursive calls
          0  db block gets
       4381  consistent gets
          0  physical reads
          0  redo size
        859  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         58  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> /

Затрач.время: 00:00:04.98

Статистика
----------------------------------------------------------
         99  recursive calls
          0  db block gets
       1185  consistent gets
          0  physical reads
          0  redo size
        859  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> /

Затрач.время: 00:00:00.04

Статистика
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1157  consistent gets
          0  physical reads
          0  redo size
        859  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> /

Затрач.время: 00:00:00.04

Статистика
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1157  consistent gets
          0  physical reads
          0  redo size
        859  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
17 авг 11, 08:15    [11130336]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Тормозит парсинг.
Первый раз парсится, второй репарсится из-за cardinality feedback, третий раз не парсится, поэтому быстро.
17 авг 11, 08:47    [11130417]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Кентурион Крысобой,

запросец то недокрутили вы ни фига
r_owner
17 авг 11, 12:18    [11131892]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
Кентурион Крысобой
Member

Откуда:
Сообщений: 202
orawish
запросец то недокрутили вы ни фига
r_owner
не понял; нельзя ли поподробней ?
17 авг 11, 12:45    [11132199]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Кентурион Крысобой
не понял; нельзя ли поподробней ?

я же вам ссылку давал
17 авг 11, 12:48    [11132226]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
Кентурион Крысобой
Member

Откуда:
Сообщений: 202
orawish,

вижу, спасибо.

Подправил - увы, ничего не изменилось: всё те же 5 сек на первом и втором запусках, после чего взлёт ракеты на третьем и последующих.
+
SQL> set timing on
SQL> set autot traceonly statistics
SQL> select
  2      c.r_owner
  3      ,c.table_name
  4      ,c.constraint_name
  5      ,c.r_constraint_name
  6      ,f.column_name
  7      ,f.position
  8      ,rf.table_name main_table
  9      ,rf.column_name main_key
 10  from dba_constraints c
 11      ,dba_cons_columns f
 12      ,dba_constraints r
 13      ,dba_cons_columns rf
 14  where
 15      c.table_name='TDETL'
 16      and c.constraint_type='R'
 17      -- NB: `r_owner`
 18      and c.r_owner=f.owner --------------------------- c, f
 19      and c.constraint_name=f.constraint_name
 20      and c.table_name=f.table_name
 21      and c.r_constraint_name=r.constraint_name
 22      and f.owner=r.owner --------------------------- f, r
 23      and f.position=rf.position
 24      and r.owner=rf.owner -------------------------- r, rf
 25      and r.constraint_name=rf.constraint_name
 26      and r.table_name=rf.table_name;

Затрач.время: 00:00:05.12

Статистика
----------------------------------------------------------
       1968  recursive calls
          0  db block gets
       4390  consistent gets
          0  physical reads
          0  redo size
        861  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         60  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> /

Затрач.время: 00:00:05.07

Статистика
----------------------------------------------------------
         99  recursive calls
          0  db block gets
       1185  consistent gets
          0  physical reads
          0  redo size
        861  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> /

Затрач.время: 00:00:00.04

Статистика
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1157  consistent gets
          0  physical reads
          0  redo size
        861  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
17 авг 11, 13:00    [11132366]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6712
Кентурион Крысобой,

/*+ opt_param(‘_optimizer_use_feedback’ ‘false’) */
17 авг 11, 14:29    [11133225]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Кентурион Крысобой
orawish,

вижу, спасибо.

Подправил - увы, ничего не изменилось..

да уж.. поаккуратнее.
подправили то вы снова - не то
17 авг 11, 14:34    [11133283]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
Кентурион Крысобой
Member

Откуда:
Сообщений: 202
orawish
подправили то вы снова - не то
Взял за основу ваш запрос отсюда, добавил в него только критерий отбора по имени таблицы и оставил нужные поля в select-разделе:
select --a.*,a2.*,b.*,b2.*
    a.r_owner
    ,a.table_name
    ,a.constraint_name
    ,a2.column_name
    ,a2.position
    ,b2.table_name main_table
    ,b2.column_name main_key
from all_constraints a
    ,all_constraints b
    ,all_cons_columns a2
    ,all_cons_columns b2
where 
  a.table_name='TDETL' and
  -- остальное не менял:
  a.constraint_type = 'R'
  and b.constraint_type in ('P','U')
  and a.r_constraint_name = b.constraint_name
  and a.r_owner           = b.owner
  and a2.constraint_name  = a.constraint_name
  and a2.table_name       = a.table_name
  and a2.owner            = a.owner
  and b2.constraint_name  = b.constraint_name
  and b2.table_name       = b.table_name
  and b2.owner            = b.owner;

Результат: почти взлетело, теперь тормоза в 8 сек только на первом запуске. Второй запуск уже Ок, 0.04 sec :-)
+
SQL> alter system flush shared_pool;

System altered.

SQL> set timing on
SQL> set autot traceonly statistics
SQL> select --a.*,a2.*,b.*,b2.*
  2      a.r_owner
  3      ,a.table_name
  4      ,a.constraint_name
  5      ,a2.column_name
  6      ,a2.position
  7      ,b2.table_name main_table
  8      ,b2.column_name main_key
  9  from all_constraints a
 10      ,all_constraints b
 11      ,all_cons_columns a2
 12      ,all_cons_columns b2
 13  where
 14    a.table_name='TDETL' and
 15    a.constraint_type = 'R'
 16    and b.constraint_type in ('P','U')
 17    and a.r_constraint_name = b.constraint_name
 18    and a.r_owner           = b.owner
 19    and a2.constraint_name  = a.constraint_name
 20    and a2.table_name       = a.table_name
 21    and a2.owner            = a.owner
 22    and b2.constraint_name  = b.constraint_name
 23    and b2.table_name       = b.table_name
 24    and b2.owner            = b.owner;

Затрач.время: 00:00:08.29

Статистика
----------------------------------------------------------
       1958  recursive calls
          0  db block gets
       3209  consistent gets
          0  physical reads
          0  redo size
        712  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         58  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

Затрач.время: 00:00:00.04

Статистика
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1086  consistent gets
          0  physical reads
          0  redo size
        712  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Замена ALL_ на DBA_ приводит к тому же уменьшению, что и ранее: с 8 сек до 5.

Спасибо за помощь!

ЗЫ. Одного понять не могу: почему парсинг этого запроса занимает столь значительное время... %-/
17 авг 11, 15:42    [11134034]     Ответить | Цитировать Сообщить модератору
 Re: Получение для главной и дочерней таблицы данных по PK & FK: как ускорить запрос ?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Кентурион Крысобой
..
ЗЫ. Одного понять не могу: почему парсинг этого запроса занимает столь значительное время... %-/

словарные вьюшки, они такие..
да это еще цветочки - а вот на 8 оракле этот запрос , если его запускать на базе с достаточным количеством объектов и без полного гардероба предикатов мог и исполняться часами. не преувеличиваю.
17 авг 11, 16:25    [11134335]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить