Сложный инсерт иногда забирает 90% CPU, почему ?

есть отчет, в нем встречается во такой sql:

INSERT INTO TMP_TABLE (RN, COMP_ID) ( SELECT RN, COMP_ID FROM ( SELECT ROWNUM RN, COMP_ID FROM ( SELECT DISTINCT PR.ID COMP_ID, PR. NAME FROM CONTACTS P, CONTACT_TYPES C, COMPANIES PR, COUNTRIES CO WHERE P.CNTCT_TYPE_ID = C.ID AND PR.ID=P.COMP_ID AND PR.ORIGIN_COUNTRY_ID = CO.ID AND C.TYPE='EVENT' AND C.COMPANY_EVENT='Y' AND PR.IS_LEGAL = 'L' AND P.XDATE <= :B1 AND PR.REGNO||CO.CODE IN (SELECT VALUE FROM VARS) ORDER BY PR. NAME ) X ) WHERE RN >= :B3 AND RN <= :B2 )

(VARS - временная табличка)
иногда (пару раз в месяц) обнаруживаю 2-3 сессии которые висят с этим запросом и забирают 90% CPU. В вебном 10g EM смотрю планы проблемных сесий и убиваю, захожу сам и иногда могу повторить ситуацию, иногда нет.
планы во время проблемы и после нее разные, причем план который я получаю из аналога sqlplus похож именно на проблемный план.
от чего межет менятся план и как с этим боротся ?

план может меняться из-за отсутствия статистики по таблицам, если тебе известен правильный план, пропиши его напрямую с помощью хинтов

Для временных таблиц оптимизатор использует значение по умолчанию для количества строк, если не ошибаюсь, то 8k строк. Соответственно, план может быть не оптимален, если у вас реальное значение сильно отличается от этого.
Выходов есть много, почитайте Тома Кайта.

Или, может, преписать запрос без конкатенации в усливии, без in() и без distinct. Все это дорогие операции.

givanov

Для временных таблиц оптимизатор использует значение по умолчанию для количества строк, если не ошибаюсь, то 8k строк. Соответственно, план может быть не оптимален, если у вас реальное значение сильно отличается от этого. Выходов есть много, почитайте Тома Кайта. Или, может, преписать запрос без конкатенации в усливии, без in() и без distinct. Все это дорогие операции.

Кайта читал, но почему план можен иногда менятся а потом сам возвращатся на место ответа не нашел.

я правильно прописал хинт ?

INSERT INTO TMP_TABLE (RN, COMP_ID) ( SELECT RN, COMP_ID FROM ( SELECT ROWNUM RN, COMP_ID FROM ( SELECT /*+ HASH(VARS P PR) */ DISTINCT PR.ID COMP_ID, PR. NAME FROM CONTACTS P, CONTACT_TYPES C, COMPANIES PR, COUNTRIES CO WHERE P.CNTCT_TYPE_ID = C.ID AND PR.ID=P.COMP_ID AND PR.ORIGIN_COUNTRY_ID = CO.ID AND C.TYPE='EVENT' AND C.COMPANY_EVENT='Y' AND PR.IS_LEGAL = 'L' AND P.XDATE <= :B1 AND PR.REGNO||CO.CODE IN (SELECT VALUE FROM VARS) ORDER BY PR. NAME ) X ) WHERE RN >= :B3 AND RN <= :B2 )

Если помогает - значит правильно.

Но откажитесь хотя бы от конкатенации. Пишите номер компании в одно поле, а страну в другое (потом откажитесь и от страны:), намного проще запрос должен стать, особенно если мало записей в VARS.

так я ж незнаю помогает или нет, проблема проявляется раз в пару недель.

>Пишите номер компании в одно поле, а страну в другое

а как ? пусть будет в vars 2 поля.

Vars - это global temporaty table?
Я правильно понял?

А пришлите текст плана

givanov

Vars - это global temporaty table? Я правильно понял?

да, но это на "where in" не повлияет. как "where in" можно написать ? джоин неохото делать.

автор

А пришлите текст плана

правильный:

INSERT STATEMENT   Cost = 14581
    VIEW   
        COUNT   
            VIEW   
                SORT UNIQUE  
                    HASH JOIN   
                        TABLE ACCESS FULL VARS 
                        HASH JOIN   
                            TABLE ACCESS FULL 
                            HASH JOIN   
                                HASH JOIN   
                                    TABLE ACCESS BY INDEX ROWID 
                                        INDEX RANGE SCAN 
                                    TABLE ACCESS FULL 
                                TABLE ACCESS FULL 

неправильный

автор

INSERT STATEMENT Cost = 879 VIEW COUNT VIEW SORT UNIQUE NESTED LOOPS NESTED LOOPS NESTED LOOPS MERGE JOIN CARTESIAN TABLE ACCESS FULL VARS BUFFER SORT TABLE ACCESS FULL TABLE ACCESS BY INDEX ROWID INDEX UNIQUE SCAN TABLE ACCESS BY INDEX ROWID INDEX RANGE SCAN TABLE ACCESS BY INDEX ROWID INDEX UNIQUE SCAN

повтор.
правильный:

INSERT STATEMENT   Cost = 14581
    VIEW   
        COUNT   
            VIEW   
                SORT UNIQUE  
                    HASH JOIN   
                        TABLE ACCESS FULL VARS 
                        HASH JOIN   
                            TABLE ACCESS FULL 
                            HASH JOIN   
                                HASH JOIN   
                                    TABLE ACCESS BY INDEX ROWID 
                                        INDEX RANGE SCAN 
                                    TABLE ACCESS FULL 
                                TABLE ACCESS FULL 

неправильный

INSERT STATEMENT   Cost = 879
    VIEW   
        COUNT   
            VIEW   
                SORT UNIQUE  
                    NESTED LOOPS   
                        NESTED LOOPS   
                            NESTED LOOPS   
                                MERGE JOIN CARTESIAN  
                                    TABLE ACCESS FULL VARS 
                                    BUFFER SORT  
                                        TABLE ACCESS FULL 
                                TABLE ACCESS BY INDEX ROWID 
                                    INDEX UNIQUE SCAN 
                            TABLE ACCESS BY INDEX ROWID 
                                INDEX RANGE SCAN 
                        TABLE ACCESS BY INDEX ROWID 
                            INDEX UNIQUE SCAN