Oracle Connect с помощью и хеш-соединения

У меня есть запрос 1:

select *
from (
select 'null' parentcode, u.unititemcode childcode, null unititempartnbr, null partoid,
null proccode, null unititemtype, null qualcat, null unititemgm2, null rsncode
from wi_unititem u where
u.prodproccode='PM11' and
u.prodendtime>sysdate-10
union all
select p.unititemcode parentcode, u.unititemcode childcode, 0 unititempartnbr, p.oid partoid,
p.proccode, u.unititemtype, u.qualcat, u.unititemgm2, u.rsncode
from wi_uitempart p, wi_unititem u where
p.unititemparttype='USEDSET' and
u.prodproccode=p.proccode and
u.setid=p.setid    
) m
where m.unititemtype<>'SKID'
start with m.parentcode='null'
connect by prior m.childcode=m.parentcode

объяснить план для запроса 1:

SELECT STATEMENT  ALL_ROWSCost: 64  Bytes: 2 745  Cardinality: 9                                    
23 FILTER                               
    22 CONNECT BY WITH FILTERING                            
        9 VIEW TIPSMSY. Cost: 12  Bytes: 610  Cardinality: 2                        
            8 UNION-ALL                     
                2 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 4  Bytes: 29  Cardinality: 1                  
                    1 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_PROCCODE_ENDTIME Cost: 3  Cardinality: 1           
                7 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 3  Bytes: 46  Cardinality: 1                  
                    6 NESTED LOOPS  Cost: 8  Bytes: 113  Cardinality: 1             
                        4 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UITEMPART Cost: 5  Bytes: 67  Cardinality: 1         
                            3 INDEX RANGE SCAN INDEX (UNIQUE) TIPSMSY.WI_UITEMPART_PK Cost: 4  Cardinality: 1   
                        5 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_SETID Cost: 2  Cardinality: 1          
        21 VIEW TIPSMSY. Cost: 64  Bytes: 2 745  Cardinality: 9                         
            20 UNION-ALL                    
                12 FILTER               
                    11 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 4  Bytes: 29  Cardinality: 1             
                        10 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_PROCCODE_ENDTIME Cost: 3  Cardinality: 1          
                19 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 3  Bytes: 46  Cardinality: 1                 
                    18 NESTED LOOPS  Cost: 60  Bytes: 904  Cardinality: 8           
                        16 NESTED LOOPS         
                            13 CONNECT BY PUMP      
                            15 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UITEMPART Cost: 18  Bytes: 938  Cardinality: 14     
                                14 INDEX RANGE SCAN INDEX (UNIQUE) TIPSMSY.WI_UITEMPART_PK Cost: 4  Cardinality: 43  
                        17 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_SETID Cost: 2  Cardinality: 1

И запрос 2:

select *
from (
select 'null' parentcode, u.unititemcode childcode, null unititempartnbr, null partoid,
null proccode, null unititemtype, null qualcat, null unititemgm2, null rsncode
from wi_unititem u where
u.prodproccode='PM11' and
u.prodendtime>sysdate-20
union all
select p.unititemcode parentcode, u.unititemcode childcode, 0 unititempartnbr, p.oid partoid,
p.proccode, u.unititemtype, u.qualcat, u.unititemgm2, u.rsncode
from wi_uitempart p, wi_unititem u where
p.unititemparttype='USEDSET' and
u.prodproccode=p.proccode and
u.setid=p.setid    
) m
where m.unititemtype<>'SKID'
start with m.parentcode='null'
connect by prior m.childcode=m.parentcode

объяснить план для запроса 2:

SELECT STATEMENT  ALL_ROWSCost: 122  Bytes: 82 655  Cardinality: 271                                    
23 FILTER                               
    22 CONNECT BY WITH FILTERING                            
        9 VIEW TIPSMSY. Cost: 70  Bytes: 80 520  Cardinality: 264                       
            8 UNION-ALL                     
                2 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 62  Bytes: 7 627  Cardinality: 263                
                    1 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_PROCCODE_ENDTIME Cost: 4  Cardinality: 263             
                7 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 3  Bytes: 46  Cardinality: 1                  
                    6 NESTED LOOPS  Cost: 8  Bytes: 113  Cardinality: 1             
                        4 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UITEMPART Cost: 5  Bytes: 67  Cardinality: 1         
                            3 INDEX RANGE SCAN INDEX (UNIQUE) TIPSMSY.WI_UITEMPART_PK Cost: 4  Cardinality: 1   
                        5 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_SETID Cost: 2  Cardinality: 1          
        21 VIEW TIPSMSY. Cost: 122  Bytes: 82 655  Cardinality: 271                         
            20 UNION-ALL                    
                12 FILTER               
                    11 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 62  Bytes: 7 627  Cardinality: 263           
                        10 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_PROCCODE_ENDTIME Cost: 4  Cardinality: 263        
                19 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 3  Bytes: 46  Cardinality: 1                 
                    18 NESTED LOOPS  Cost: 60  Bytes: 904  Cardinality: 8           
                        16 HASH JOIN        
                            13 CONNECT BY PUMP      
                            15 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UITEMPART Cost: 18  Bytes: 938  Cardinality: 14     
                                14 INDEX FULL SCAN INDEX (UNIQUE) TIPSMSY.WI_UITEMPART_PK Cost: 4  Cardinality: 43  
                        17 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_SETID Cost: 2  Cardinality: 1

Запрос 1 содержит "sysdate-10", а запрос 2 содержит "sysdate-20", это единственная разница, Запрос 1 выполняется довольно быстро, а Запрос 2 - очень медленно.

Если сравнивать план выполнения, то разницу можно найти в строках, начинающихся с 14 и 16: Запрос 1 использует соединение вложенным циклом + сканирование диапазона индекса Запрос 2 использует соединение хешированием + полное сканирование индекса

Можно ли использовать план выполнения запроса 1 и для запроса 2?


oracle nested-loops hierarchical oracle10g
person Alex    schedule 06.01.2016    source источник
comment
Запрос 2 имеет дело с дополнительными данными. Что заставляет вас думать, что план запроса 1 является правильным планом для увеличенных данных запроса 2?   -  person Shannon Severance    schedule 06.01.2016
comment
Запрос 2 возвращает примерно в 2 раза больше строк, чем Запрос 1. План Запроса 1 правильный, так как таблица WI_UITEMPART, в которой происходит полное сканирование индекса, содержит миллионы таких индексов. Запрос 1 создает только около 10 000 строк, это означает, что запрос 2 создает около 20 000 строк.   -  person Alex    schedule 07.01.2016
comment
Первый выбор обоих запросов имеет столбец null unititemtype, позже where m.unititemtype<>'SKID'. Поскольку NULL никогда не равно и не равно NULL, ни одна из строк из первого запроса не будет включена в окончательный результат, если только начало с connect by не вернет то, что исключено предложением where. Кроме того, вы пытались параметризовать запрос так, чтобы он был sysdate - :bind_variable? Запустите один раз с 10, затем 20, затем 20 следует повторно использовать план. Эксперимент, а не решение.   -  person Shannon Severance    schedule 07.01.2016
comment
По поводу NULL: вы правы, но на скорость выполнения это не влияет. Я уже пытался использовать переменную связывания, как вы сказали - план такой же, как и для запроса 2 (плохой план)   -  person Alex    schedule 07.01.2016

Ответы (1)


arrow_upward
0
arrow_downward

Самый простой способ скопировать план выполнения — использовать полный набор подсказок структуры.

Используйте эти два оператора, чтобы сгенерировать план объяснения со структурными данными. Это также лучший способ создать план объяснения по нескольким причинам, описанным в моем ответе здесь.

explain plan for select * from dual; --Add your real query here.
select * from table(dbms_xplan.display(format => '+outline'));

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "DUAL"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Используйте Outline Data как подсказку, чтобы гарантировать, что план выполнения останется прежним. Хотя «гарантия» может быть сильным словом; эти недокументированные подсказки могут не работать в разных версиях или если запрос отличается семантически.

select 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "DUAL"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
* from dual;

Это должно работать, но немного некрасиво. В идеале вы хотите добраться до первопричины того, почему Oracle приняла неправильное решение, — исследовать оценочные и фактические кардинальности, статистику и т. д. Но есть много способов сделать это, и это занимает много времени. В качестве компромисса вы можете поиграть с подсказками и попытаться сузить их до одной или двух, необходимых для решения проблемы с производительностью.

person Jon Heller    schedule 06.01.2016
comment
Привет Джон, большое спасибо за ответ. Я попробовал ваш способ отображения плана выполнения - он показывает ту же информацию, что и мои планы выполнения. Не могли бы вы дать несколько советов, как можно найти основную причину проблемы, или посоветовать, какие подсказки использовать (я пробовал USE_NL, но это было просто проигнорировано)? - person Alex; 06.01.2016
comment
Вы запускали эти два оператора как обычный SQL? Не запускайте их через какой-либо инструмент планирования IDE. - person Jon Heller; 07.01.2016
comment
Может быть, попробуйте эту подсказку: select /*+ index(p) */ p.unititemcode parentcode ..... Без полных определений таблиц и индексов трудно воспроизвести планы. - person Jon Heller; 07.01.2016
comment
Подсказка /*+ index(p) */ не работает. Я не могу размещать здесь полные определения таблиц и индексов, так как таблицы очень большие (более 100 столбцов) и содержат 10-30 индексов. Причина неправильного плана, очевидно, в увеличенном количестве возвращаемых строк, но это количество намного меньше, чем количество строк в таблицах. Я начинаю думать, что это поведение является своего рода внутренним соединением оракула по поведению, поскольку результаты, возвращаемые рекурсией (см. CONNECT BY PUMP в плане), объединяются с помощью соединения Hash. Я не нашел подсказок, влияющих на это поведение, есть ли подсказки, которые могли бы повлиять на иерархический запрос? - person Alex; 07.01.2016
comment
Я только что попытался использовать подсказку Outline Data, но безуспешно. План запроса 2 не изменился. - person Alex; 11.01.2016
comment
Это странно. Вы поместили подсказку в верхний оператор SELECT? Эти подсказки аналогичны тому, как Oracle использует схемы, должен быть способ заставить их работать. - person Jon Heller; 11.01.2016