5 Replies Latest reply: Sep 30, 2013 2:46 AM by Principiante RSS

    I need to optimize a MATERIALIZED VIEW

    Principiante

      Hello,

      I have a problem with a MATERIALIZED VIEW, I need to optimize it.

      This query takes 4 hours, should decrease this time

      Is there anyone who can help me?

       

      I attach script MATERIALIZED VIEW

       

       

       

      CREATE MATERIALIZED VIEW "QTSA_CONTRACT_CONDITION"

       

        ORGANIZATION HEAP PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP NOLOGGING STORAGE

        ( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

        TABLESPACE "LTR_ODS_DT"

        PARTITION BY HASH ("CONTRACT_ITEM_ID")

        (PARTITION "SYS_P55162" TABLESPACE "XXXX" COMPRESS FOR OLTP ,

        PARTITION "SYS_P55163" TABLESPACE "XXXX" COMPRESS FOR OLTP ,

         PARTITION "SYS_P55164" TABLESPACE "XXXX" COMPRESS FOR OLTP ,

         PARTITION "SYS_P55165" TABLESPACE "XXXX" COMPRESS FOR OLTP )

        

         PARALLEL 2 BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND

         USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE

        

      AS

        SELECT cc.* ,

          greatest( NVL(cc.list_price_value,0) ,

                    NVL(cc.bc_vehicle_price_value,0) ,

                    NVL(cc.ec_vehicle_price_value,0) )           

                AS unique_list_price_value ,

          greatest( NVL(cc.opt_tot_list_price_value,0) ,

                    NVL(cc.opt_tot_list_price_value,0) ,

                    NVL(cc.ec_optional_price_value,0) )

                AS unique_opt_list_price_value

        FROM

          (

         

         SELECT

            /*+ PARALLEL(A 4) PARALLEL(B 4) */

            A.CONTRACT_ID ,

            CASE

              WHEN GROUPING(A.POSITION_ID) = 1

              THEN '?'

              ELSE A.POSITION_ID

            END AS CONTRACT_ITEM_ID,

            -- Financial Value - 41X1    ,

            SUM(

            CASE

              WHEN B.CONDITION_TYPE_FK = '41X1'

              THEN B.TOTAL_AMT/10

              ELSE 0

            END) AS FINANCIAL_VALUE_PERCENTAGE ,

            SUM(

            CASE

              WHEN B.CONDITION_TYPE_FK = '41X1'

              THEN B.VALUE_CONDITION_AMT

              ELSE 0

            END) AS FINANCIAL_VALUE_AMOUNT ,

            MAX(

            CASE

              WHEN B.CONDITION_TYPE_FK = '41X1'

              THEN B.CURRENCY

              ELSE NULL

            END) AS FINANCIAL_VALUE_CURRENCY,

            -- CONT Total Instalment - ZIN4    ,

            SUM(

            CASE

              WHEN B.CONDITION_TYPE_FK = 'ZIN4'

              THEN B.VALUE_CONDITION_AMT

              ELSE 0

            END) AS INSTALLMENT_AMOUNT ,

            MAX(

            CASE

              WHEN B.CONDITION_TYPE_FK = 'ZIN4'

              THEN B.CURRENCY

              ELSE NULL

            END) AS INSTALLMENT_CURRENCY,

            --    CONT: Last Instllmnt - 41NT,

            SUM(

            CASE

              WHEN B.CONDITION_TYPE_FK IN ('41NT','ZIV3')

              THEN B.VALUE_CONDITION_AMT

              ELSE 0

            END) AS LAST_INSTALLMENT_AMOUNT ,

          FROM QTST_CONTRACT_PRICE_COND A

          INNER JOIN

            (SELECT

              /*+ PARALLEL(B 4) */

              b.* ,

              row_number() over (partition BY price_condition_id, position_id, condition_type_fk order by condition_counter_id DESC) rn

            FROM COPE_ORDER_CONDITION B

            WHERE B.CONDITION_TYPE_FK IN ('41X1', 'ZIN4', '41NT','ZIV3', 'ZK15','ZK05', 'ZK03', 'ZK04', 'ZK02', 'ZK00', 'ZK01', 'ZIC7', 'ZIC8', 'ZI62', 'ZID7', 'ZID8', 'ZIB1', 'ZIO5', 'ZIO6', 'ZIO7', 'ZID2','ZIT0', '41Y1', 'ZI92', '41N4', 'ZI37', 'ZID1', 'ZI38', 'ZI39', 'ZI14', 'ZID0', 'ZK06', 'ZIG9', 'ZIH0', 'ZIH1', 'ZIHC', 'ZIH4', 'ZI01', 'ZI0O', 'ZI05', 'ZIT6', 'ZIE0', 'ZIG8', 'ZIL0', 'ZIJ7', 'ZIA9', 'ZI35', 'ZI36', 'ZIN9','ZI40', 'ZI54', 'ZI55', 'ZI57', 'ZIA7', 'ZI56', 'ZI19', 'ZI18', 'ZI58', 'ZIA8', 'ZII9', 'ZIA6', '41N1', 'ZIT5', 'ZIT6', '28EP', '28ED', 'ZI14', 'ZI15', 'ZI22', 'ZI23', 'ZI24', 'ZI25', 'ZI26', 'ZI27', 'ZI28', 'ZI29', 'ZI30', 'ZI31', 'ZI32', 'ZI33', 'ZIG6', 'ZII8', 'ZIP2', 'ZI50', 'ZI52', 'ZIF3', 'ZIE7', 'ZII4', 'ZIP3', 'ZIJ8', 'ZIU0', 'ZI65', 'ZIJ3', 'ZI53', 'ZI83', 'ZI93', 'MWST', 'ZIH8', 'ZID5', 'ZID9', 'ZIL8', 'ZIP0', 'ZIP1', 'ZI16', 'ZI17', 'ZI21', 'ZI60', 'ZIB2', 'ZIO8', 'ZIV5', 'ZI09', 'ZIR1' )

            ) B

          ON (A.PRICE_CONDITION_ID   = B.PRICE_CONDITION_ID

          AND A.POSITION_ID          = B.POSITION_ID

          AND A.LEVEL_ID             = B.LEVEL_ID

          AND A.CONDITION_COUNTER_ID = B.CONDITION_COUNTER_ID

          AND B.RN                   = 1 )   

          GROUP BY A.CONTRACT_ID,

            rollup(A.POSITION_ID)

          ) cc ;

        • 1. Re: I need to optimize a MATERIALIZED VIEW
          Peter vd Zwan

          Hi,

           

          Not much info you give us.

          No tables structures, oracle version etc etc.

          See HOW TO: Post a SQL statement tuning request - template posting

          I would start by changing this:

           

                END) AS LAST_INSTALLMENT_AMOUNT ,

              FROM QTST_CONTRACT_PRICE_COND A

          into this:

                END) AS LAST_INSTALLMENT_AMOUNT --, take out the comma

              FROM QTST_CONTRACT_PRICE_COND A

          Then I would make a table and index for the items in the in list:

          ('41X1', 'ZIN4', '41NT','ZIV3', 'ZK15','ZK05',

                  'ZK03', 'ZK04', 'ZK02', 'ZK00', 'ZK01', 'ZIC7', 'ZIC8', 'ZI62', 'ZID7', 'ZID8', 'ZIB1', 'ZIO5', 'ZIO6', 'ZIO7', 'ZID2','ZIT0', '41Y1', 'ZI92', '41N4', 'ZI37', 'ZID1', 'ZI38', 'ZI39', 'ZI14', 'ZID0', 'ZK06', 'ZIG9', 'ZIH0', 'ZIH1',

                  'ZIHC', 'ZIH4', 'ZI01', 'ZI0O', 'ZI05', 'ZIT6', 'ZIE0', 'ZIG8', 'ZIL0', 'ZIJ7', 'ZIA9', 'ZI35', 'ZI36', 'ZIN9','ZI40', 'ZI54', 'ZI55', 'ZI57', 'ZIA7', 'ZI56', 'ZI19', 'ZI18', 'ZI58', 'ZIA8', 'ZII9', 'ZIA6', '41N1', 'ZIT5', 'ZIT6',

                  '28EP', '28ED', 'ZI14', 'ZI15', 'ZI22', 'ZI23', 'ZI24', 'ZI25', 'ZI26', 'ZI27', 'ZI28', 'ZI29', 'ZI30', 'ZI31', 'ZI32', 'ZI33', 'ZIG6', 'ZII8', 'ZIP2', 'ZI50', 'ZI52', 'ZIF3', 'ZIE7', 'ZII4', 'ZIP3', 'ZIJ8', 'ZIU0', 'ZI65', 'ZIJ3',

                  'ZI53', 'ZI83', 'ZI93', 'MWST', 'ZIH8', 'ZID5', 'ZID9', 'ZIL8', 'ZIP0', 'ZIP1', 'ZI16', 'ZI17', 'ZI21', 'ZI60', 'ZIB2', 'ZIO8', 'ZIV5', 'ZI09', 'ZIR1' )

          And list this table to COPE_ORDER_CONDITION table.

           

          Then when you send us the info needed and shown in the link above, maybe we can help more.

           

          Regards,

           

          Peter

          • 2. Re: I need to optimize a MATERIALIZED VIEW
            Principiante

            Hi Peter,

             

            here is some information that you asked me


             

            SQL> show parameter optimizer

             

            NAME                                 TYPE        VALUE

            ------------------------------------ ----------- ------------------------------

            optimizer_capture_sql_plan_baselines boolean     FALSE

            optimizer_dynamic_sampling           integer     2

            optimizer_features_enable            string      11.2.0.3

            optimizer_index_caching              integer     0

            optimizer_index_cost_adj             integer     100

            optimizer_mode                       string      ALL_ROWS

            optimizer_secure_view_merging        boolean     TRUE

            optimizer_use_invisible_indexes      boolean     FALSE

            optimizer_use_pending_statistics     boolean     FALSE

            optimizer_use_sql_plan_baselines     boolean     TRUE

            SQL>

            SQL>

            SQL> show parameter db_file_multi

             

            NAME                                 TYPE        VALUE

            ------------------------------------ ----------- ------------------------------

            db_file_multiblock_read_count        integer     128

            SQL>

            SQL>

            SQL> show parameter db_block_size

             

            NAME                                 TYPE        VALUE

            ------------------------------------ ----------- ------------------------------

            db_block_size                        integer     16384

             

            SQL> show parameter cursor_sharing

             

            NAME                                 TYPE        VALUE

            ------------------------------------ ----------- ----------------------------

            cursor_sharing                       string      EXACT

             

             

             

             

            Plan

            SELECT STATEMENT  ALL_ROWSCost : 10.770 Bytes : 8.133.477.312 Cardinality : 520.976                                                                   

                22 PX COORDINATOR                                                                 

                    21 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10004 Cost : 10.770 Bytes : 8.133.477.312 Cardinality : 520.976                                                           

                        20 VIEW PARALLEL_COMBINED_WITH_PARENT LTR_ODS_D. Cost : 10.770 Bytes : 8.133.477.312 Cardinality : 520.976                                                       

                            19 SORT GROUP BY ROLLUP PARALLEL_COMBINED_WITH_PARENT Cost : 10.770 Bytes : 197.970.880 Cardinality : 520.976                                                   

                                18 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost : 3.435 Bytes : 197.970.880 Cardinality : 520.976                                               

                                    17 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10003 Cost : 3.435 Bytes : 197.970.880 Cardinality : 520.976                                           

                                        16 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT "A"."PRICE_CONDITION_ID"="B"."PRICE_CONDITION_ID" AND "A"."POSITION_ID"="B"."POSITION_ID" AND "A"."LEVEL_ID"="B"."LEVEL_ID" AND "A"."CONDITION_COUNTER_ID"="B"."CONDITION_COUNTER_ID"Cost : 3.435 Bytes : 197.970.880 Cardinality : 520.976                                       

                                            10 JOIN FILTER CREATE PARALLEL_COMBINED_WITH_PARENT SYS.:BF0000 Cost : 1.905 Bytes : 139.024.038 Cardinality : 507.387                                   

                                                9 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost : 1.905 Bytes : 139.024.038 Cardinality : 507.387                               

                                                    8 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10001 Cost : 1.905 Bytes : 139.024.038 Cardinality : 507.387                           

                                                        7 VIEW PARALLEL_COMBINED_WITH_PARENT LTR_ODS_D. "B"."RN"=1Cost : 1.905 Bytes : 139.024.038 Cardinality : 507.387                       

                                                            6 WINDOW SORT PUSHED RANK PARALLEL_COMBINED_WITH_PARENT ROW_NUMBER() OVER ( PARTITION BY NLSSORT("PRICE_CONDITION_ID",'nls_sort=''WEST_EUROPEAN'''),NLSSORT("POSITION_ID",'nls_sort=''WEST_EUROPEAN'''),NLSSORT("CONDITION_TYPE_FK",'nls_sort=''WEST_EUROPEAN''') ORDER BY INTERNAL_FUNCTION("CONDITION_COUNTER_ID") DESC )<=1Cost : 1.905 Bytes : 48.201.765 Cardinality : 507.387                   

                                                                5 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost : 1.905 Bytes : 48.201.765 Cardinality : 507.387               

                                                                    4 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10000 Cost : 1.905 Bytes : 48.201.765 Cardinality : 507.387           

                                                                        3 WINDOW CHILD PUSHED RANK PARALLEL_COMBINED_WITH_PARENT ROW_NUMBER() OVER ( PARTITION BY NLSSORT("PRICE_CONDITION_ID",'nls_sort=''WEST_EUROPEAN'''),NLSSORT("POSITION_ID",'nls_sort=''WEST_EUROPEAN'''),NLSSORT("CONDITION_TYPE_FK",'nls_sort=''WEST_EUROPEAN''') ORDER BY INTERNAL_FUNCTION("CONDITION_COUNTER_ID") DESC )<=1Cost : 1.905 Bytes : 48.201.765 Cardinality : 507.387       

                                                                            2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost : 1.893 Bytes : 48.201.765 Cardinality : 507.387Partition # : 16 Partitions accessed #1 - #4   

                                                                                1 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT LTR_ODS_D.COPE_ORDER_CONDITION "B"."CONDITION_TYPE_FK"='28ED' OR "B"."CONDITION_TYPE_FK"='28EP' OR "B"."CONDITION_TYPE_FK"='41N1' OR "B"."CONDITION_TYPE_FK"='41N4' OR "B"."CONDITION_TYPE_FK"='41NT' OR "B"."CONDITION_TYPE_FK"='41X1' OR "B"."CONDITION_TYPE_FK"='41Y1' OR "B"."CONDITION_TYPE_FK"='MWST' OR "B"."CONDITION_TYPE_FK"='ZI01' OR "B"."CONDITION_TYPE_FK"='ZI05' OR "B"."CONDITION_TYPE_FK"='ZI09' OR "B"."CONDITION_TYPE_FK"='ZI0O' OR "B"."CONDITION_TYPE_FK"='ZI14' OR "B"."CONDITION_TYPE_FK"='ZI15' OR "B"."CONDITION_TYPE_FK"='ZI16' OR "B"."CONDITION_TYPE_FK"='ZI17' OR "B"."CONDITION_TYPE_FK"='ZI18' OR "B"."CONDITION_TYPE_FK"='ZI19' OR "B"."CONDITION_TYPE_FK"='ZI21' OR "B"."CONDITION_TYPE_FK"='ZI22' OR "B"."CONDITION_TYPE_FK"='ZI23' OR "B"."CONDITION_TYPE_FK"='ZI24' OR "B"."CONDITION_TYPE_FK"='ZI25' OR "B"."CONDITION_TYPE_FK"='ZI26' OR "B"."CONDITION_TYPE_FK"='ZI27' OR "B"."CONDITION_TYPE_FK"='ZI28' OR "B"."CONDITION_TYPE_FK"='ZI29' OR "B"."CONDITION_TYPE_FK"='ZI30' OR "B"."CONDITION_TYPE_FK"='ZI31' OR "B"."CONDITION_TYPE_FK"='ZI32' OR "B"."CONDITION_TYPE_FK"='ZI33' OR "B"."CONDITION_TYPE_FK"='ZI35' OR "B"."CONDITION_TYPE_FK"='ZI36' OR "B"."CONDITION_TYPE_FK"='ZI37' OR "B"."CONDITION_TYPE_FK"='ZI38' OR "B"."CONDITION_TYPE_FK"='ZI39' OR "B"."CONDITION_TYPE_FK"='ZI40' OR "B"."CONDITION_TYPE_FK"='ZI50' OR "B"."CONDITION_TYPE_FK"='ZI52' OR "B"."CONDITION_TYPE_FK"='ZI53' OR "B"."CONDITION_TYPE_FK"='ZI54' OR "B"."CONDITION_TYPE_FK"='ZI55' OR "B"."CONDITION_TYPE_FK"='ZI56' OR "B"."CONDITION_TYPE_FK"='ZI57' OR "B"."CONDITION_TYPE_FK"='ZI58' OR "B"."CONDITION_TYPE_FK"='ZI60' OR "B"."CONDITION_TYPE_FK"='ZI62' OR "B"."CONDITION_TYPE_FK"='ZI65' OR "B"."CONDITION_TYPE_FK"='ZI83' OR "B"."CONDITION_TYPE_FK"='ZI92' OR "B"."CONDITION_TYPE_FK"='ZI93' OR "B"."CONDITION_TYPE_FK"='ZIA6' OR "B"."CONDITION_TYPE_FK"='ZIA7' OR "B"."CONDITION_TYPE_FK"='ZIA8' OR "B"."CONDITION_TYPE_FK"='ZIA9' OR "B"."CONDITION_TYPE_FK"='ZIB1' OR "B"."CONDITION_TYPE_FK"='ZIB2' OR "B"."CONDITION_TYPE_FK"='ZIC7' OR "B"."CONDITION_TYPE_FK"='ZIC8' OR "B"."CONDITION_TYPE_FK"='ZID0' OR "B"."CONDITION_TYPE_FK"='ZID1' OR "B"."CONDITION_TYPE_FK"='ZID2' OR "B"."CONDITION_TYPE_FK"='ZID5' OR "B"."CONDITION_TYPE_FK"='ZID7' OR "B"."CONDITION_TYPE_FK"='ZID8' OR "B"."CONDITION_TYPE_FK"='ZID9' OR "B"."CONDITION_TYPE_FK"='ZIE0' OR "B"."CONDITION_TYPE_FK"='ZIE7' OR "B"."CONDITION_TYPE_FK"='ZIF3' OR "B"."CONDITION_TYPE_FK"='ZIG6' OR "B"."CONDITION_TYPE_FK"='ZIG8' OR "B"."CONDITION_TYPE_FK"='ZIG9' OR "B"."CONDITION_TYPE_FK"='ZIH0' OR "B"."CONDITION_TYPE_FK"='ZIH1' OR "B"."CONDITION_TYPE_FK"='ZIH4' OR "B"."CONDITION_TYPE_FK"='ZIH8' OR "B"."CONDITION_TYPE_FK"='ZIHC' OR "B"."CONDITION_TYPE_FK"='ZII4' OR "B"."CONDITION_TYPE_FK"='ZII8' OR "B"."CONDITION_TYPE_FK"='ZII9' OR "B"."CONDITION_TYPE_FK"='ZIJ3' OR "B"."CONDITION_TYPE_FK"='ZIJ7' OR "B"."CONDITION_TYPE_FK"='ZIJ8' OR "B"."CONDITION_TYPE_FK"='ZIL0' OR "B"."CONDITION_TYPE_FK"='ZIL8' OR "B"."CONDITION_TYPE_FK"='ZIN4' OR "B"."CONDITION_TYPE_FK"='ZIN9' OR "B"."CONDITION_TYPE_FK"='ZIO5' OR "B"."CONDITION_TYPE_FK"='ZIO6' OR "B"."CONDITION_TYPE_FK"='ZIO7' OR "B"."CONDITION_TYPE_FK"='ZIO8' OR "B"."CONDITION_TYPE_FK"='ZIP0' OR "B"."CONDITION_TYPE_FK"='ZIP1' OR "B"."CONDITION_TYPE_FK"='ZIP2' OR "B"."CONDITION_TYPE_FK"='ZIP3' OR "B"."CONDITION_TYPE_FK"='ZIR1' OR "B"."CONDITION_TYPE_FK"='ZIT0' OR "B"."CONDITION_TYPE_FK"='ZIT5' OR "B"."CONDITION_TYPE_FK"='ZIT6' OR "B"."CONDITION_TYPE_FK"='ZIU0' OR "B"."CONDITION_TYPE_FK"='ZIV3' OR "B"."CONDITION_TYPE_FK"='ZIV5' OR "B"."CONDITION_TYPE_FK"='ZK00' OR "B"."CONDITION_TYPE_FK"='ZK01' OR "B"."CONDITION_TYPE_FK"='ZK02' OR "B"."CONDITION_TYPE_FK"='ZK03' OR "B"."CONDITION_TYPE_FK"='ZK04' OR "B"."CONDITION_TYPE_FK"='ZK05' OR "B"."CONDITION_TYPE_FK"='ZK06' OR "B"."CONDITION_TYPE_FK"='ZK15'Cost : 1.893 Bytes : 48.201.765 Cardinality : 507.387Partition # : 16 Partitions accessed #1 - #1048575

                                            15 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710                                   

                                                14 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10002 Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710                               

                                                    13 JOIN FILTER USE PARALLEL_COMBINED_WITH_PARENT SYS.:BF0000 Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710                           

                                                        12 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710Partition # : 21 Partitions accessed #1 - #4                       

                                                            11 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT LTR_ODS_D.QTST_CONTRACT_PRICE_COND SYS_OP_BLOOM_FILTER(:BF0000,"A"."PRICE_CONDITION_ID","A"."POSITION_ID","A"."LEVEL_ID","A"."CONDITION_COUNTER_ID")Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710Partition # : 21 Partitions accessed #1 - #4                   

            • 3. Re: I need to optimize a MATERIALIZED VIEW
              Principiante

              Any suggestions?

              • 4. Re: I need to optimize a MATERIALIZED VIEW
                Nikolay Savvinov

                Hi,

                 

                I would start by adding the actual rowcount and autotrace dump to the thread. Also, if your license allows it (i.e. it includes the Diagnostic and Tuning Pack), paste SQL real-time monitor output (the great thing about it is that you don't have to wait until the entire query completes, you can let it run for 10 minutes and then take the report, it would still contain useful information).

                 

                After you post all this (and give us something to work with) trace the query. Be sure to do it in such a way that all diagnostic information is included into the trace file, i.e.:

                 

                1) enable waits (dbms_monitor.trace_session_enable(waits=>true, binds=>false) or ALTER SESSION SET EVENTS '10046 trace name context forever, level 8')

                2) close the cursor before finishing tracing:

                - enable trace

                - run statement

                - exit

                that way you make sure that plan statistics will be in the trace file.

                 

                 

                Process the resulting trace file with tkprof and post the output here.

                 

                Best regards,

                  Nikolay

                • 5. Re: I need to optimize a MATERIALIZED VIEW
                  Principiante

                  I solved it by changing the select containing constant values.


                  I followed the advice of Peter:

                   
                  Before:
                  SELECT
                  /*+ PARALLEL(B 4) */
                  b.* ,
                  row_number() over (partition BY price_condition_id, position_id, condition_type_fk order by condition_counter_id DESC) rn
                  FROM COPE_ORDER_CONDITION B
                  WHERE B.CONDITION_TYPE_FK IN ('41X1', 'ZIN4', '41NT','ZIV3', 'ZK15','ZK05', 'ZK03', 'ZK04', 'ZK02', 'ZK00', 'ZK01', 'ZIC7', 'ZIC8', 'ZI62', 'ZID7', 'ZID8', 'ZIB1', 'ZIO5', 'ZIO6', 'ZIO7', 'ZID2','ZIT0', '41Y1', 'ZI92', '41N4', 'ZI37', 'ZID1', 'ZI38', 'ZI39', 'ZI14', 'ZID0', 'ZK06', 'ZIG9', 'ZIH0', 'ZIH1', 'ZIHC', 'ZIH4', 'ZI01', 'ZI0O', 'ZI05', 'ZIT6', 'ZIE0', 'ZIG8', 'ZIL0', 'ZIJ7', 'ZIA9', 'ZI35', 'ZI36', 'ZIN9','ZI40', 'ZI54', 'ZI55', 'ZI57', 'ZIA7', 'ZI56', 'ZI19', 'ZI18', 'ZI58', 'ZIA8', 'ZII9', 'ZIA6', '41N1', 'ZIT5', 'ZIT6', '28EP', '28ED', 'ZI14', 'ZI15', 'ZI22', 'ZI23', 'ZI24', 'ZI25', 'ZI26', 'ZI27', 'ZI28', 'ZI29', 'ZI30', 'ZI31', 'ZI32', 'ZI33', 'ZIG6', 'ZII8', 'ZIP2', 'ZI50', 'ZI52', 'ZIF3', 'ZIE7', 'ZII4', 'ZIP3', 'ZIJ8', 'ZIU0', 'ZI65', 'ZIJ3', 'ZI53', 'ZI83', 'ZI93', 'MWST', 'ZIH8', 'ZID5', 'ZID9', 'ZIL8', 'ZIP0', 'ZIP1', 'ZI16', 'ZI17', 'ZI21', 'ZI60', 'ZIB2', 'ZIO8', 'ZIV5', 'ZI09', 'ZIR1' )
                   
                  After:
                  I made a table and index for the items in the in list:
                   
                  SELECT
                  /*+ PARALLEL(B 4) PARALLEL(T 4) */
                  b.* ,
                  row_number() over (partition BY b.price_condition_id, b.position_id, b.condition_type_fk order by b.condition_counter_id DESC) rn
                  FROM COPE_ORDER_CONDITION B, COPE_CONDITION_TYPE T
                  WHERE B.CONDITION_TYPE_FK = T.CONDITION_TYPE_FK