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

Откуда: Калуга
Сообщений: 615
Запрос:
SELECT SYS_CONNECT_BY_PATH(B.NAME||' '||A.VALUE,'/') FROM A, B 
WHERE A.B_N=B.N  CONNECT BY   A.ANCESTOR_N = PRIOR A.N START WITH A.N=29701000;

Получаем вот такой план:
SELECT STATEMENT  ALL_ROWSCost : 167 Bytes : 1 547 420 Cardinality : 55 265                    
    16 CONNECT BY WITH FILTERING  "A"."N"=29701000                
        5 FILTER  "A"."N"=29701000            
            4 COUNT          
                3 HASH JOIN  "A"."B_N"="B"."N"Cost : 167 Bytes : 1 547 420 Cardinality : 55 265    
                    1 TABLE ACCESS FULL PGMI_AR.B Cost : 2 Bytes : 927 Cardinality : 103
                    2 TABLE ACCESS FULL PGMI_AR.A Cost : 164 Bytes : 1 050 035 Cardinality : 55 265
        11 HASH JOIN  "A"."ANCESTOR_N"=NULL            
            6 CONNECT BY PUMP          
            10 COUNT          
                9 HASH JOIN  "A"."B_N"="B"."N"Cost : 167 Bytes : 1 547 420 Cardinality : 55 265    
                    7 TABLE ACCESS FULL PGMI_AR.B Cost : 2 Bytes : 927 Cardinality : 103
                    8 TABLE ACCESS FULL PGMI_AR.A Cost : 164 Bytes : 1 050 035 Cardinality : 55 265
        15 COUNT              
            14 HASH JOIN  "A"."B_N"="B"."N"Cost : 167 Bytes : 1 547 420 Cardinality : 55 265        
                12 TABLE ACCESS FULL PGMI_AR.B Cost : 2 Bytes : 927 Cardinality : 103    
                13 TABLE ACCESS FULL PGMI_AR.A Cost : 164 Bytes : 1 050 035 Cardinality : 55 265    


Как избавиться от TABLE ACCESS FULL - незнаю.
Все индексы есть , свежепостроенные. Статистика тоже свежесобрана.
Время выполнения непристойно долгое - 4 сек. Как-то не очень ...
PS не пойму от куда берется count
14 окт 07, 13:36    [4791734]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с иерархическим запросом  [new]
Тынц.
Guest
Версия какая?
В старших релизах девятки наблюдается деградация производительности иерархических запросов по сравению с более ранними и с восьмёркой.
Для проверки попробуйте alter session set "_old_connect_by_enabled"=true
Но учтите, что это только для проверки, поскольку параметр скрытый и его использование чревато... (на 9.2.0.8, например, были проблемы при импорте).

Вроде бы в 10.2.0.3 проблему убрали, поищите по форуму, тема не раз обсуждалась. Да и на металинке что-то было по этой проблеме, если это она...
14 окт 07, 20:59    [4792251]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с иерархическим запросом  [new]
Vladimir Sitnikov
Member

Откуда: Moscow, NetCracker
Сообщений: 407
lft
Запрос:
SELECT SYS_CONNECT_BY_PATH(B.NAME||' '||A.VALUE,'/') FROM A, B 
WHERE A.B_N=B.N  CONNECT BY   A.ANCESTOR_N = PRIOR A.N START WITH A.N=29701000;

Указывать несколько таблиц во from'е иерархического запроса mauvais ton. Oracle обижается и делает только fullscan'ы.

Соответсвенно, нужно любой ценой добиться того, чтобы во from'е была лишь одна таблица (скалярными подзапросами, pl/sql и т.п.)
SELECT SYS_CONNECT_BY_PATH(
         (select name from b where n=a.b_nB)||' '||A.VALUE,'/'
       )
  FROM A
CONNECT BY  A.ANCESTOR_N = PRIOR A.N
 START WITH A.N=29701000;
14 окт 07, 22:04    [4792326]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с иерархическим запросом  [new]
Vladimir Sitnikov
Member

Откуда: Moscow, NetCracker
Сообщений: 407
Тынц.
Версия какая?

А это что-нибудь решает?

тынц

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02

Oracle processes hierarchical queries as follows:

  • A join, if present, is evaluated first,...

  • Так и получается: джойн присутсвует, а после его выполнения ни о каких индексах говорить и не приходится.
    14 окт 07, 22:13    [4792336]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    lft
    Member

    Откуда: Калуга
    Сообщений: 615
    Vladimir Sitnikov
    [quot lft]
    Указывать несколько таблиц во from'е иерархического запроса mauvais ton. Oracle обижается и делает только fullscan'ы.


    Спасибо! Не знал...
    Вообще то об этом нигде не упоминается.
    Попробую.

    PS Версия 10gR2 (10.2.0.1.0)
    14 окт 07, 22:20    [4792344]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    Тынц.
    Guest
    Vladimir Sitnikov
    А это что-нибудь решает?

    Да, не тот случай... просто наелся недавно этих "деревянных" проблем, вот и вспомнилось...
    14 окт 07, 22:36    [4792357]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    lft
    Member

    Откуда: Калуга
    Сообщений: 615
    Да действительно
    SELECT SYS_CONNECT_BY_PATH((SELECT B.NAME FROM B WHERE A.B_N = B.N) || ' ' || A.VALUE, '/') FROM A 
    CONNECT BY A.ANCESTOR_N = PRIOR A.N  START WITH A.N = 29701000
    
    И план :
    SELECT STATEMENT  ALL_ROWSCost : 31 Bytes : 969 Cardinality : 51                
        2 TABLE ACCESS BY INDEX ROWID B Cost : 1 Bytes : 9 Cardinality : 1            
            1 INDEX UNIQUE SCAN XPKB "B"."N"=:B1Cost : 0 Cardinality : 1        
        11 CONNECT BY WITH FILTERING  "A"."N"=29701000            
            4 TABLE ACCESS BY INDEX ROWID A         
                3 INDEX UNIQUE SCAN XPKA "A"."N"=29701000 Cost : 1 Bytes : 5 Cardinality : 1    
            9 NESTED LOOPS          
                6 BUFFER SORT      
                    5 CONNECT BY PUMP  
                8 TABLE ACCESS BY INDEX ROWID A Cost : 31 Bytes : 969 Cardinality : 51    
                    7 INDEX RANGE SCAN A_ANCESTOR_N_IDX "A"."ANCESTOR_N"=NULL Cost : 1 Cardinality : 51
    		10 TABLE ACCESS FULL A "A"."ANCESTOR_N"=NULL Cost : 31 Bytes : 969 Cardinality : 51		
    

    Но один фулскан остался, чо с ним делать?
    Да и интересно: откуда count-ы брались в том плане?

    PS: Время выполнения 1сек. строк в таблице 55265
    14 окт 07, 22:47    [4792369]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    Vladimir Sitnikov
    Member

    Откуда: Moscow, NetCracker
    Сообщений: 407
    lft
    Но один фулскан остался, чо с ним делать?

    Ничего с ним делать не нужно. Он не влияет на время работы.

    lft
    Да и интересно: откуда count-ы брались в том плане?

    10053 вам в помощь

    lft
    PS: Время выполнения 1сек. строк в таблице 55265

    А здесь уже 10046
    14 окт 07, 23:03    [4792388]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    lft
    Member

    Откуда: Калуга
    Сообщений: 615
    Спасибо, понял.
    Насчет времени -думаю, потология. Все таки таблица иерархическая, не плоская, наверное больше не выжать. В любом случае вы мне уже сильно помогли.

    попробую оттрасировать, думаю ничего нового не получу.
    14 окт 07, 23:09    [4792393]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    lft
    Member

    Откуда: Калуга
    Сообщений: 615
    Vladimir Sitnikov

    Ничего с ним делать не нужно. Он не влияет на время работы.


    Да кстати, а откуда он вообще берется?
    14 окт 07, 23:10    [4792395]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    Тынц.
    Guest
    lft
    Vladimir Sitnikov

    Ничего с ним делать не нужно. Он не влияет на время работы.


    Да кстати, а откуда он вообще берется?

    Был похожий случай. В плане full scan был, но при выполнении, судя по статистике "table scan rows gotten", его не было (на форуме можно найти несколько обсуждений с аналогичными симптомами). Зато "sorts (rows)"/"sorts (memory)" зашкаливали. При этом попавшееся под руку ядро CPU съедалось на время выполнения на 99%. По видимому, проблема в куче мелких сортировок (в плане есть buffer sort), причём наблюдалась нелинейная зависимость времени выполнения от количества сортировок. Включение _old_conect_by_enabled на пару порядков уменьшало количество сортировок, но при этом на порядок росло lio. Получалось так, что _old_conect_by_enabled давал значительный выигрыш на больших деревьях и незначительный (менее значительный) проигрыш на маленьких.
    14 окт 07, 23:45    [4792461]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    Vladimir Sitnikov
    Member

    Откуда: Moscow, NetCracker
    Сообщений: 407
    lft
    Vladimir Sitnikov

    Ничего с ним делать не нужно. Он не влияет на время работы.


    Да кстати, а откуда он вообще берется?
    А кто его знает? Пока мне не доводилось видеть случаи, когда третья нода в плане иерархического запроса влияла на что либо.
    14 окт 07, 23:53    [4792473]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    Владимир Бегун
    Member

    Откуда: Redwood Shores, CA USA
    Сообщений: 1707
    lft
    чо с ним делать?
    Александр Герцен
    Кто виноват?
    Николай Чернышевский
    Что делать?
    Vladimir Sitnikov
    А кто его знает?
    15 окт 07, 07:04    [4792678]     Ответить | Цитировать Сообщить модератору
     Re: Помогите с иерархическим запросом  [new]
    lft
    Member

    Откуда: Калуга
    Сообщений: 615
    trace вроде бы не показал ничего необычного:
    PARSING IN CURSOR #4 len=199 dep=0 uid=124 oct=3 lid=124 tim=1164479781117026 hv=1344841727 ad='82b72d4c'
    SELECT SYS_CONNECT_BY_PATH((SELECT B.NAME FROM B WHERE A.B_N = B.N) || ' ' || A.VALUE, '/') FROM A
    CONNECT BY A.ANCESTOR_N = PRIOR A.N  START WITH A.N = 29701000
    END OF STMT
    PARSE #4:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1164479781117015
    BINDS #4:
    EXEC #4:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1164479781117299
    WAIT #4: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1164479781117363
    WAIT #4: nam='SQL*Net message from client' ela= 1981 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1164479781119460
    WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1164479782018111
    WAIT #4: nam='SQL*Net more data to client' ela= 53 driver id=1413697536 #bytes=2025 p3=0 obj#=-1 tim=1164479782018841
    WAIT #4: nam='SQL*Net more data to client' ela= 51 driver id=1413697536 #bytes=1977 p3=0 obj#=-1 tim=1164479782019407
    WAIT #4: nam='SQL*Net more data to client' ela= 15 driver id=1413697536 #bytes=2016 p3=0 obj#=-1 tim=1164479782019738
    WAIT #4: nam='SQL*Net more data to client' ela= 32 driver id=1413697536 #bytes=1990 p3=0 obj#=-1 tim=1164479782020132
    WAIT #4: nam='SQL*Net more data to client' ela= 16 driver id=1413697536 #bytes=2007 p3=0 obj#=-1 tim=1164479782020583
    WAIT #4: nam='SQL*Net more data to client' ela= 15 driver id=1413697536 #bytes=2003 p3=0 obj#=-1 tim=1164479782020951
    WAIT #4: nam='SQL*Net more data to client' ela= 14 driver id=1413697536 #bytes=2040 p3=0 obj#=-1 tim=1164479782021264
    WAIT #4: nam='SQL*Net more data to client' ela= 15 driver id=1413697536 #bytes=1963 p3=0 obj#=-1 tim=1164479782021538
    WAIT #4: nam='SQL*Net more data to client' ela= 17 driver id=1413697536 #bytes=2035 p3=0 obj#=-1 tim=1164479782021833
    WAIT #4: nam='SQL*Net more data to client' ela= 15 driver id=1413697536 #bytes=2015 p3=0 obj#=-1 tim=1164479782022111
    WAIT #4: nam='SQL*Net more data to client' ela= 16 driver id=1413697536 #bytes=2001 p3=0 obj#=-1 tim=1164479782022333
    WAIT #4: nam='SQL*Net more data to client' ela= 15 driver id=1413697536 #bytes=1940 p3=0 obj#=-1 tim=1164479782022569
    WAIT #4: nam='SQL*Net more data to client' ela= 15 driver id=1413697536 #bytes=2006 p3=0 obj#=-1 tim=1164479782022820
    WAIT #4: nam='SQL*Net more data to client' ela= 31 driver id=1413697536 #bytes=2054 p3=0 obj#=-1 tim=1164479782023099
    WAIT #4: nam='SQL*Net more data to client' ela= 16 driver id=1413697536 #bytes=2032 p3=0 obj#=-1 tim=1164479782023274
    WAIT #4: nam='SQL*Net more data to client' ela= 14 driver id=1413697536 #bytes=1928 p3=0 obj#=-1 tim=1164479782023456
    FETCH #4:c=924058,e=904033,p=0,cr=68657,cu=0,mis=0,r=500,dep=0,og=1,tim=1164479782023555
    
    15 окт 07, 11:39    [4793516]     Ответить | Цитировать Сообщить модератору
    Все форумы / Oracle Ответить