CftClub.ru

[Serj]

Что год назад это ЗАМЕЧАТЕЛЬНО - осталось узнать, что конкретно пофиксилось ? А то от словосочетания "все проблемы" дух захватывает, неужели есть кто-то , кто знает все проблемы ?- -мне такого в суппорте даже не рассказывали

[prog]

У нас после перехода на тесте "испортился" план выполнения запроса у представления VW_CRIT_MAIN_DOCUM (Список документах в платежных документах)

Кто нибудь сталкивался?

Новый план выполнения запроса Oracle 10g
CRIT_RPT_PLAN SELECT STATEMENT Cost=55630
2.1 TABLE ACCESS(BY INDEX ROWID) - STATES(TABLE)
3.1 INDEX(UNIQUE SCAN) - PK_STATES_CLASS_ID_ID(INDEX (UNIQUE))
2.2 FILTER
3.1 NESTED LOOPS(OUTER)
4.1 NESTED LOOPS
5.1 HASH JOIN(RIGHT OUTER)
6.1 TABLE ACCESS(FULL) - Z#PATTERN(TABLE)
6.2 TABLE ACCESS(BY INDEX ROWID) - Z#DOC_IN_FOLD(TABLE)
7.1 NESTED LOOPS
8.1 NESTED LOOPS(OUTER)
9.1 NESTED LOOPS(OUTER)
10.1 HASH JOIN(RIGHT OUTER)
11.1 TABLE ACCESS(FULL) - Z#NAME_PAYDOC(TABLE)
11.2 HASH JOIN(RIGHT OUTER)
12.1 TABLE ACCESS(FULL) - Z#FT_MONEY(TABLE)
12.2 HASH JOIN(RIGHT OUTER)
13.1 TABLE ACCESS(FULL) - Z#FT_MONEY(TABLE)
13.2 TABLE ACCESS(FULL) - Z#MAIN_DOCUM(TABLE)
10.2 TABLE ACCESS(BY INDEX ROWID) - Z#AC_FIN(TABLE)
11.1 INDEX(UNIQUE SCAN) - PK_Z#AC_FIN_ID(INDEX (UNIQUE))
9.2 TABLE ACCESS(BY INDEX ROWID) - Z#AC_FIN(TABLE)
10.1 INDEX(UNIQUE SCAN) - PK_Z#AC_FIN_ID(INDEX (UNIQUE))
8.2 INDEX(RANGE SCAN) - Z#IX_Z#DOC_IN_FOLD_REF3(INDEX)
5.2 TABLE ACCESS(BY INDEX ROWID) - Z#FOLDER_PAY(TABLE)
6.1 INDEX(UNIQUE SCAN) - Z#IX_Z#FOLDER_PAY_COL5(INDEX (UNIQUE))
4.2 TABLE ACCESS(BY INDEX ROWID) - Z#DOCUMENT(TABLE)
5.1 INDEX(UNIQUE SCAN) - PK_Z#DOCUMENT_ID(INDEX (UNIQUE))

Старый план выполнения запроса Oracle 9i
CRIT_RPT_PLAN SELECT STATEMENT Cost=69353
2.1 SORT(AGGREGATE)
3.1 COUNT(STOPKEY)
4.1 INDEX(RANGE SCAN) - IDX_Z#HISTORY_STATES_COLL_STAT(NON-UNIQUE)
2.2 SORT(AGGREGATE)
3.1 COUNT(STOPKEY)
4.1 INDEX(RANGE SCAN) - IDX_Z#4001_COLLECTION(NON-UNIQUE)
2.3 COUNT(STOPKEY)
3.1 TABLE ACCESS(BY INDEX ROWID) - Z#USER_TYPE_REF
4.1 INDEX(RANGE SCAN) - Z#IX_Z#USER_TYPE_REF_COLL(NON-UNIQUE)
2.4 FILTER
3.1 FILTER
4.1 NESTED LOOPS(OUTER)
5.1 NESTED LOOPS(OUTER)
6.1 NESTED LOOPS(OUTER)
7.1 NESTED LOOPS
8.1 NESTED LOOPS(OUTER)
9.1 NESTED LOOPS(OUTER)
10.1 NESTED LOOPS(OUTER)
11.1 NESTED LOOPS(OUTER)
12.1 NESTED LOOPS(OUTER)
13.1 NESTED LOOPS(OUTER)
14.1 NESTED LOOPS(OUTER)
15.1 NESTED LOOPS(OUTER)
16.1 NESTED LOOPS(OUTER)
17.1 NESTED LOOPS(OUTER)
18.1 NESTED LOOPS(OUTER)
19.1 NESTED LOOPS(OUTER)
20.1 NESTED LOOPS(OUTER)
21.1 NESTED LOOPS(OUTER)
22.1 NESTED LOOPS(OUTER)
23.1 NESTED LOOPS(OUTER)
24.1 HASH JOIN(OUTER)
25.1 HASH JOIN
26.1 TABLE ACCESS(FULL) - Z#NAME_PAYDOC
26.2 TABLE ACCESS(FULL) - Z#MAIN_DOCUM
25.2 TABLE ACCESS(FULL) - STATES
24.2 TABLE ACCESS(BY INDEX ROWID) - Z#VID_SEND_DOCUM
25.1 INDEX(UNIQUE SCAN) - PK_Z#VID_SEND_DOCUM_ID(UNIQUE)
23.2 TABLE ACCESS(BY INDEX ROWID) - Z#CL_BANK_N
24.1 INDEX(UNIQUE SCAN) - PK_Z#CL_BANK_N_ID(UNIQUE)
22.2 TABLE ACCESS(BY INDEX ROWID) - Z#FT_MONEY
23.1 INDEX(UNIQUE SCAN) - PK_Z#FT_MONEY_ID(UNIQUE)
21.2 TABLE ACCESS(BY INDEX ROWID) - Z#ACCOUNT
22.1 INDEX(UNIQUE SCAN) - PK_Z#ACCOUNT_ID(UNIQUE)
20.2 TABLE ACCESS(BY INDEX ROWID) - Z#ACCOUNT
21.1 INDEX(UNIQUE SCAN) - PK_Z#ACCOUNT_ID(UNIQUE)
19.2 TABLE ACCESS(BY INDEX ROWID) - Z#USER
20.1 INDEX(UNIQUE SCAN) - PK_Z#USER_ID(UNIQUE)
18.2 TABLE ACCESS(BY INDEX ROWID) - Z#FT_MONEY
19.1 INDEX(UNIQUE SCAN) - PK_Z#FT_MONEY_ID(UNIQUE)
17.2 TABLE ACCESS(BY INDEX ROWID) - Z#DEPART
18.1 INDEX(UNIQUE SCAN) - PK_Z#DEPART_ID(UNIQUE)
16.2 TABLE ACCESS(BY INDEX ROWID) - Z#CLIENT
17.1 INDEX(UNIQUE SCAN) - PK_Z#CLIENT_ID(UNIQUE)
15.2 TABLE ACCESS(BY INDEX ROWID) - Z#SBRF_TYPE_MESS
16.1 INDEX(UNIQUE SCAN) - PK_Z#SBRF_TYPE_MESS_ID(UNIQUE)
14.2 TABLE ACCESS(BY INDEX ROWID) - Z#CL_PART
15.1 INDEX(UNIQUE SCAN) - PK_Z#CL_PART_ID(UNIQUE)
13.2 TABLE ACCESS(BY INDEX ROWID) - Z#CL_PART
14.1 INDEX(UNIQUE SCAN) - PK_Z#CL_PART_ID(UNIQUE)
12.2 TABLE ACCESS(BY INDEX ROWID) - Z#KOD_N_PAY
13.1 INDEX(UNIQUE SCAN) - PK_Z#KOD_N_PAY_ID(UNIQUE)
11.2 TABLE ACCESS(BY INDEX ROWID) - Z#USER
12.1 INDEX(UNIQUE SCAN) - PK_Z#USER_ID(UNIQUE)
10.2 TABLE ACCESS(BY INDEX ROWID) - Z#CLIENT
11.1 INDEX(UNIQUE SCAN) - PK_Z#CLIENT_ID(UNIQUE)
9.2 TABLE ACCESS(BY INDEX ROWID) - Z#CL_BANK_N
10.1 INDEX(UNIQUE SCAN) - PK_Z#CL_BANK_N_ID(UNIQUE)
8.2 TABLE ACCESS(BY INDEX ROWID) - Z#DOCUMENT
9.1 INDEX(UNIQUE SCAN) - PK_Z#DOCUMENT_ID(UNIQUE)
7.2 TABLE ACCESS(BY INDEX ROWID) - Z#RKC_DOC_STATES
8.1 INDEX(UNIQUE SCAN) - PK_Z#RKC_DOC_STATES_ID(UNIQUE)
6.2 TABLE ACCESS(BY INDEX ROWID) - Z#USER
7.1 INDEX(UNIQUE SCAN) - PK_Z#USER_ID(UNIQUE)
5.2 TABLE ACCESS(BY INDEX ROWID) - Z#RKC_DOC_STATES
6.1 INDEX(UNIQUE SCAN) - PK_Z#RKC_DOC_STATES_ID(UNIQUE)
3.2 NESTED LOOPS
4.1 INDEX(RANGE SCAN) - PK_CRR_OBJ_ID_SUBJ_ID_CLASS_ID(UNIQUE)
4.2 INDEX(UNIQUE SCAN) - PK_SUBJ_EQUAL(UNIQUE)
3.3 NESTED LOOPS
4.1 INDEX(RANGE SCAN) - PK_SUBJ_EQUAL(UNIQUE)
4.2 INDEX(UNIQUE SCAN) - PK_CR_OBJ_ID_SUBJ_ID(UNIQUE)

[Serj]

Что делали после переезда? - как собирали статистику и какую ?

[tma]

[prog]

[prog]

[tma]

[Serj]

[tma]

[Serj]

делали именно alter session или в spfile засунули?

[tma]

[Serj]

[Serj]

[tma]

через ибсо
у профиля в ибсо есть параметр alter_session

[bob@pkb.ru]

Системную статистику собирали ?

Запуск статистики желательно выполнять во время загрузки системы:
exec DBMS_STATS.GATHER_SYSTEM_STATS ('START');

и после некоторого периода сбора, скажем от 2 до 5 часов выключаем сбор:
exec DBMS_STATS.GATHER_SYSTEM_STATS ('STOP');