2014-01-17

Cognos crosstab обрезает строки

При выводе в колонки Crosstab обрезает строки на уровне запроса. Выглядит это примерно так:









Если смотреть результаты запроса через Run > View Tabular Data, то результаты так же обрезаются.

Обойти эту неприятность получилось следующим образом:
1. Создаем запрос, который содержит данные Crosstab (Cross)
2. Создаем запрос, который содержит данные с заголовком колонок (Category).
3. Создаем join этих запросов. Условия Join



4. В результирующий запрос Join в колонки с Категорией и именем строки выбираем элементы из запроса Category.
5. В качестве источника данных для таблицы (Crosstab - Data - Query) указываем Join_query
6. Поверяем 

2013-11-27

Open/close period script Oracle EBS

Script uses concurrent GLOOAP
DECLARE -- какой год надо открыть l_year NUMBER := 2014; -- Сколько периодов в 1 году c_periods_in_year_cnt NUMBER := 5; l_iterations_cnt NUMBER; PROCEDURE open_period(p_sob_id gl_sets_of_books.set_of_books_id%TYPE) IS l_code gl_sets_of_books.short_name%TYPE; l_user_id fnd_user.user_id%TYPE; l_app_id fnd_responsibility_vl.APPLICATION_ID%TYPE; l_resp_id fnd_responsibility_vl.RESPONSIBILITY_ID%TYPE; l_req_num NUMBER; BEGIN SELECT b.short_name INTO l_code FROM gl_sets_of_books b WHERE b.set_of_books_id = p_sob_id; select user_id INTO l_user_id from fnd_user where user_name = 'SYSADMIN'; select application_id, Responsibility_id INTO l_app_id, l_resp_id from fnd_responsibility_vl where responsibility_name like l_code || ' Суперпользователь ГК'; fnd_global.APPS_INITIALIZE(user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_app_id); l_req_num := fnd_request.submit_request('SQLGL', 'GLOOAP', '', '', FALSE, p_sob_id, '50268', l_app_id, 'P', 'Y',chr(0)); -- Параметры -- 50268 - chart_of_accounts_id -- определить идентификатор можно таким запросом -- select * From FND_ID_FLEX_STRUCTURES_VL where id_flex_code='GL#'; -- 'P' -- execution_mode = P (хз, что такое) -- 'Y' -- хз, что такое dbms_output.put_line(l_req_num); COMMIT; END; BEGIN FOR rec IN ( SELECT t.set_of_books_id, t.period_year, t.period_num FROM ( select t.*, row_number() OVER (PARTITION BY t.set_of_books_id ORDER BY t.period_year DESC, t.period_num DESC) rn from GL.GL_PERIOD_STATUSES t WHERE t.closing_status = 'O') t WHERE rn = 1 ) LOOP l_iterations_cnt := GREATEST(l_year - rec.period_year, 0) * c_periods_in_year_cnt + (c_periods_in_year_cnt - rec.period_num); dbms_output.put_line('sob_id = ' || rec.set_of_books_id || '; Кол-во периодов ' || l_iterations_cnt); FOR i IN 1 .. l_iterations_cnt LOOP NULL;--open_period(rec.set_of_books_id); END LOOP; END LOOP; END; /

Merge vs Insert/Update

Сравнение времени выполнения merge и insert/update.

Выводы: в рамках использованного в тесте распределения данных, лучше всего использовать один оператор merge для всех строк.

Для аналогичную конструкции INSERT/UPDATE в один (а вернее в два) оператора не получилось дождаться окончания запроса. Если обновление будет производиться не из запроса, а из индексированной таблицы, то вполне вероятно это решение будет так же жизнеспособно, но все равно не так удобно как один MERGE.

Если в один оператор поместиться не получается, то быстрее всего отрабатывает UPDATE/SQL%ROWCOUNT/INSERT с одним условием: из 200 000 строк обновляялась (т.е. не вызывала дальнейшей вставки) половина. Если все строки вставляются и UPDATE работает вхолостую, то производительность построчного MERGE получается выше.

SET SERVEROUTPUT ON SET FEEDBACK OFF SET TIMING OFF DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON BEGIN dbms_output.put_line('************************'); dbms_output.put_line('Merge bulk'); dbms_output.put_line('************************'); MERGE INTO tst USING (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i ON (tst.a = i.rn) WHEN MATCHED THEN UPDATE SET b = i.b WHEN NOT MATCHED THEN INSERT (a, b) VALUES (i.rn, i.b); END; / SET TIMING OFF ------------------------------------- -- Окончания этого теста дождаться не удалось -- ПРоблема в UPDATE. -- Разрешится, если вместо запроса у нас будет таблица, например --DROP TABLE tst PURGE; --CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); -- --INSERT INTO tst --SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual --CONNECT BY LEVEL <= 500000; --COMMIT; -- --EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) -- --SET timing ON --BEGIN -- dbms_output.put_line('************************'); -- dbms_output.put_line('INSERT - UPDATE bulk'); -- dbms_output.put_line('************************'); -- -- INSERT INTO tst -- SELECT i.rn, i.b -- FROM (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i -- WHERE NOT EXISTS ( -- SELECT NULL -- FROM tst t -- WHERE t.a = i.rn); -- -- UPDATE tst -- SET b = (SELECT i.b FROM (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i WHERE tst.a = i.rn) -- WHERE EXISTS (SELECT NULL FROM (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i WHERE tst.a = i.rn); --END; --/ --SET TIMING OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON BEGIN dbms_output.put_line('************************'); dbms_output.put_line('Row by row merge'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP MERGE INTO tst USING (SELECT rec.rn rn, rec.b b FROM dual) i ON (tst.a = i.rn) WHEN MATCHED THEN UPDATE SET b = i.b WHEN NOT MATCHED THEN INSERT (a, b) VALUES (i.rn, i.b); END LOOP; END; / SET timing OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON BEGIN dbms_output.put_line('************************'); dbms_output.put_line('Dup val on index'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP BEGIN INSERT INTO tst VALUES (rec.rn, rec.b); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE tst SET b = rec.b WHERE a = rec.rn; END; END LOOP; END; / SET timing OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON DECLARE lCNT NUMBER; BEGIN dbms_output.put_line('************************'); dbms_output.put_line('Select - insert - update'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP SELECT COUNT(*) INTO lCNT FROM tst WHERE a = rec.rn AND ROWNUM = 1; IF lCNT = 0 THEN INSERT INTO tst VALUES (rec.rn, rec.b); ELSE UPDATE tst SET b = rec.b WHERE a = rec.rn; END IF; END LOOP; END; / SET timing OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON DECLARE lCNT NUMBER; BEGIN dbms_output.put_line('************************'); dbms_output.put_line('SQL%ROWCOUNT'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP UPDATE tst SET b = rec.b WHERE a = rec.rn; IF SQL%ROWCOUNT = 0 THEN INSERT INTO tst VALUES (rec.rn, rec.b); END IF; END LOOP; END; / SET timing OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON DECLARE lCNT NUMBER; BEGIN dbms_output.put_line('************************'); dbms_output.put_line('SQL%ROWCOUNT INSERT ONLY'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 500000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP UPDATE tst SET b = rec.b WHERE a = rec.rn; IF SQL%ROWCOUNT = 0 THEN INSERT INTO tst VALUES (rec.rn, rec.b); END IF; END LOOP; END; / SET timing OFF ************************ Merge bulk ************************ Executed in 5,241 seconds ************************ Row by row merge ************************ Executed in 14,914 seconds ************************ Dup val on index ************************ Executed in 180,681 seconds ************************ Select - insert - update ************************ Executed in 20,483 seconds ************************ SQL%ROWCOUNT ************************ Executed in 14,29 seconds ************************ SQL%ROWCOUNT INSERT ONLY ************************ Executed in 17,425 seconds

2013-11-22

Оптимизация одного запроса с использованием _fix_control

После миграции OEBS на Oracle 11.2.0.4 выполнение Posting (а это одна из ключевых операций в GL) стало занимать от 5 минут до получаса.

Методом пристольного взгляда в окно сессий в PL/SQL Developer был выведен зловредный запрос:
UPDATE /*+ ORDERED INDEX (b, GL_BALANCES_N1) USE_NL (VW_NSO_1, b) */ GL_BALANCES B SET (PERIOD_NET_DR, PERIOD_NET_CR, QUARTER_TO_DATE_DR, QUARTER_TO_DATE_CR, PROJECT_TO_DATE_DR, PROJECT_TO_DATE_CR, BEGIN_BALANCE_DR, BEGIN_BALANCE_CR, PERIOD_NET_DR_BEQ, PERIOD_NET_CR_BEQ, BEGIN_BALANCE_DR_BEQ, BEGIN_BALANCE_CR_BEQ, TRANSLATED_FLAG, LAST_UPDATE_DATE, LAST_UPDATED_BY) = (SELECT /*+ INDEX(pi1, gl_posting_interim_162343_N1) */ NVL(B.PERIOD_NET_DR, 0) + PI1.PERIOD_NET_DR, NVL(B.PERIOD_NET_CR, 0) + PI1.PERIOD_NET_CR, NVL(B.QUARTER_TO_DATE_DR, 0) + PI1.QUARTER_TO_DATE_DR, NVL(B.QUARTER_TO_DATE_CR, 0) + PI1.QUARTER_TO_DATE_CR, NVL(B.PROJECT_TO_DATE_DR, 0) + PI1.PROJECT_TO_DATE_DR, NVL(B.PROJECT_TO_DATE_CR, 0) + PI1.PROJECT_TO_DATE_CR, NVL(B.BEGIN_BALANCE_DR, 0) + PI1.BEGIN_BALANCE_DR, NVL(B.BEGIN_BALANCE_CR, 0) + PI1.BEGIN_BALANCE_CR, NVL(B.PERIOD_NET_DR_BEQ, 0) + PI1.PERIOD_NET_DR_BEQ, NVL(B.PERIOD_NET_CR_BEQ, 0) + PI1.PERIOD_NET_CR_BEQ, NVL(B.BEGIN_BALANCE_DR_BEQ, 0) + PI1.BEGIN_BALANCE_DR_BEQ, NVL(B.BEGIN_BALANCE_CR_BEQ, 0) + PI1.BEGIN_BALANCE_CR_BEQ, PI1.TRANSLATED_FLAG, SYSDATE, :USER_ID FROM GL_POSTING_INTERIM_162343 PI1 WHERE B.SET_OF_BOOKS_ID = PI1.SET_OF_BOOKS_ID AND B.CODE_COMBINATION_ID = PI1.CODE_COMBINATION_ID AND B.ACTUAL_FLAG = PI1.ACTUAL_FLAG AND NVL(B.ENCUMBRANCE_TYPE_ID, -1) = NVL(PI1.ENCUMBRANCE_TYPE_ID, -1) AND NVL(B.BUDGET_VERSION_ID, -1) = NVL(PI1.BUDGET_VERSION_ID, -1) AND B.PERIOD_NAME = PI1.PERIOD_NAME AND B.CURRENCY_CODE = PI1.CURRENCY_CODE AND NVL(B.TEMPLATE_ID, -1) = NVL(PI1.TEMPLATE_ID, -1) AND DECODE(B.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, B.TRANSLATED_FLAG) = DECODE(PI1.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, PI1.TRANSLATED_FLAG)) WHERE (B.CODE_COMBINATION_ID, B.PERIOD_NAME, B.SET_OF_BOOKS_ID, B.CURRENCY_CODE, B.ACTUAL_FLAG, NVL(B.ENCUMBRANCE_TYPE_ID, -1), NVL(B.BUDGET_VERSION_ID, -1), NVL(B.TEMPLATE_ID, -1), DECODE(B.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, B.TRANSLATED_FLAG)) IN (SELECT /*+ FULL(pi2) */ PI2.CODE_COMBINATION_ID, PI2.PERIOD_NAME, PI2.SET_OF_BOOKS_ID, PI2.CURRENCY_CODE, PI2.ACTUAL_FLAG, NVL(PI2.ENCUMBRANCE_TYPE_ID, -1), NVL(PI2.BUDGET_VERSION_ID, -1), NVL(PI2.TEMPLATE_ID, -1), DECODE(PI2.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, PI2.TRANSLATED_FLAG) FROM GL_POSTING_INTERIM_162343 PI2)
В плане запроса появился нехороший full scan по индексу GL_BALANCES_N1.
Так же было установлено, хотя это и не важно, что full scan появлялся из-за
DECODE(B.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, B.TRANSLATED_FLAG) в условии IN.
На этом этапе у нас есть описание проблемы (FULL SCAN) и один запрос, поэтому тестированиие изменений при помощи EXPLAIN PLAN не занимает много времени.

Первое, что попробовал сделать -- убедился, что в прошлой версиии оптимизатора все работало нормально. Добавляем хинт /*+ optimizer_features_enable('9.2.0.8') */ и убеждаемся, что все нормально.

2. Смотрим, в какой версии оптимизатора Oracle поломал свой запрос. Допустимые значения для параметра optimizer_features_enable можно получить запросом
SELECT * FROM v$parameter_valid_values WHERE NAME LIKE '%features%' Используя хинт, убеждаемся, что план портится при переходе с версиии 11.2.0.3 на 11.2.0.4.

Тут можно остановиться, сделав
ALTER SYSTEM SET optimizer_features_enable='11.2.0.3'; Но можно пойти дальше. Найдем, что менялось в версии 11.2.0.4
SELECT * FROM v$system_fix_control WHERE optimizer_feature_enable = '11.2.0.4'; Сгенерируем список хинтов SELECT 'OPT_PARAM(''_fix_control'' ''' || bugno ||':OFF'')' FROM v$system_fix_control WHERE optimizer_feature_enable = '11.2.0.4'; и добавим эти хинты в оптимизируемый запрос. При этом у нас появится хороший план.
Удаляя хинты (можно сразу кучками) и перестраивая план, найдем номер, включение которого портит план.
У меня это Bug 13704562  Suboptimal plan for a query with an =ANY predicate

Теперь можно отключить этот фикс на уровне системы
alter system set "_fix_control"='13704562:OFF'; Проверяем постинг -- все работает

Примечание: изменение скрытого параметра _fix_control не рекомендуется без прямого указания ораклового саппорта. Но для тестовой среды вполне сойдет