1 2 Previous Next 17 Replies Latest reply on Apr 10, 2015 9:46 PM by jihuyao

    Performance issue will insert table.

    1007945

      Hi Team ,

       

      I am getting performance issue as below insert stmt.

       

      INSERT INTO SA_REPORT_DATA
      (REPORT_ID, CUTOFF_DATE, COL_1, COL_2, COL_3)
      (
      SELECT 'ISRP-734', to_date('&DateTo', 'YYYY-MM-DD'),
             SNE.ID AS HLR
      ,      SNR.FROM_NUMBER||' - '||SNR.TO_NUMBER AS NUMBER_RANGE
      ,      COUNT(M.MSISDN) AS AVAILABLE_MSISDNS
      FROM
             SA_NUMBER_RANGES SNR
      ,      SA_SERVICE_SYSTEMS SSS
      ,      SA_NETWORK_ELEMENTS SNE
      ,      SA_MSISDNS M
      WHERE
             SSS.SEQ = SNR.SRVSYS_SEQ
      AND    SSS.SYSTYP_ID = 'OMC HLR'
      AND    SNE.SEQ = SSS.NE_SEQ
      AND    SNR.ID_TYPE = 'M'
      AND    M.MSISDN  >= SNR.FROM_NUMBER
      AND    M.MSISDN  <= SNR.TO_NUMBER
      AND    M.STATE  = 'AVL'
      GROUP BY
             SNE.ID,SNR.FROM_NUMBER||' - '||SNR.TO_NUMBER
      )
      

      currently taking around 7hours to complete.  and Total number of 37650 Records are there if we query the Select stmt.

       

      Please let me know any further information about table. Look like if we you Hints is it you full...?

        • 1. Re: Performance issue will insert table.
          Karthick2003

          To start with following information could help us

           

          1. DB Version

          2. Execution plan of the SQL

          3. When the statistics where gathered for the tables

          4. Number of rows in each table

          5. Available index/partition on all the tables

          6. Cardinality of the key columns

           

          And a wild shot would be, did you consider Materialized view for your summarized report?

          • 2. Re: Re: Performance issue will insert table.
            1007945

            Thanks

             

            Version :-

            Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

            PL/SQL Release 11.2.0.2.0 - Production

            "CORE 11.2.0.2.0 Production"

            TNS for HPUX: Version 11.2.0.2.0 - Production

            NLSRTL Version 11.2.0.2.0 - Production

             

            Number of rows in each table


            SA_MSISDNS  -- > 72206134 (total number of records with filter any conditions)

            SA_NUMBER_RANGES --10227

            SA_SERVICE_SYSTEMS --1643

            SA_NETWORK_ELEMENTS --200


            Main Table :- SA_MSISDNS


            Table Description :-

            SA_MSISDNS.JPG


            Index :-

            Index Name-------------------Column Name--------------

            SA_MSISDN_PK-------------- MSISDN --------------  Unique

            MSISDN_C_FK_I ON SA_MSISDNS (C_ID)

            MSISDN_DO_POOL_YN_I ON SA_MSISDNS (DO_POOL_YN)

            MSISDN_INDEX_YN_IDX ON SA_MSISDNS (INDEX_YN)



            Table Constraints

            SA_MSISDNS.JPG


            Execution plan of the SQL

            PFA Execution Plan.


            • 3. Re: Performance issue will insert table.
              Hoek

              Have you tried the APPEND and/or PARALLEL hint?

               

              +edit+

               

              Also, based on the cardinality, your execution plan expects the query will retrieve 5,410,843,776 rows....

              Is this realistic? I think not.

              Are your table and index statistics up-to-date?

              • 4. Re: Performance issue will insert table.
                Martin Preiss

                could you add the plan in a text version (created with dbms_xplan.display_cursor)? In the html version it's hard to sort the indentions. Looking at the number in the plan I see that the optimizer expects the result set to contain > 5,000,000,000 rows - is that an accurate expectation?

                • 5. Re: Performance issue will insert table.
                  Karthick2003

                  I would prefer to have the execution plan pasted here from you sql plus window. I am not able to access the zip file (Organization restriction).

                   

                  So out of 72 million rows you are filtering and summarizing it into 32000 rows STATE = 'AVL' and how many distinct STATE are there in the table? Can this be considered as a candidate for partition?

                  • 6. Re: Re: Performance issue will insert table.
                    1007945
                    EXPLAIN PLAN FOR
                    SELECT count(*)
                    FROM
                           SA_NUMBER_RANGES SNR
                    ,      SA_SERVICE_SYSTEMS SSS
                    ,      SA_NETWORK_ELEMENTS SNE
                    ,      SA_MSISDNS M
                    WHERE
                           SSS.SEQ = SNR.SRVSYS_SEQ
                    AND    SSS.SYSTYP_ID = 'OMC HLR'
                    AND    SNE.SEQ = SSS.NE_SEQ
                    AND    SNR.ID_TYPE = 'M'
                    AND    M.MSISDN  >= SNR.FROM_NUMBER
                    AND    M.MSISDN  <= SNR.TO_NUMBER
                    AND    M.STATE  = 'AVL'
                    GROUP BY
                           SNE.ID,SNR.FROM_NUMBER||' - '||SNR.TO_NUMBER;
                           
                    SELECT * 
                    FROM   TABLE(DBMS_XPLAN.DISPLAY);       
                    
                    
                    
                    
                    plan FOR succeeded.
                    PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                          
                    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    Plan hash value: 3683232305                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                                                 
                    ------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
                    | Id  | Operation                    | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                                 
                    ------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
                    |   0 | SELECT STATEMENT             |                     |    53M|  3108M|       |    26M  (2)| 87:31:44 |                                                                                                                                                                                                 
                    |   1 |  HASH GROUP BY               |                     |    53M|  3108M|   164G|    26M  (2)| 87:31:44 |                                                                                                                                                                                                 
                    |   2 |   MERGE JOIN OUTER           |                     |  2438M|   138G|       |   195K (15)| 00:39:03 |                                                                                                                                                                                                 
                    |   3 |    SORT JOIN                 |                     |  1066 | 51168 |       |    21  (15)| 00:00:01 |                                                                                                                                                                                                 
                    |*  4 |     HASH JOIN                |                     |  1066 | 51168 |       |    20  (10)| 00:00:01 |                                                                                                                                                                                                 
                    |*  5 |      HASH JOIN               |                     |   328 |  8528 |       |    10  (20)| 00:00:01 |                                                                                                                                                                                                 
                    |   6 |       TABLE ACCESS FULL      | SA_NETWORK_ELEMENTS |   146 |  1460 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                 
                    |*  7 |       VIEW                   | index$_join$_002    |   328 |  5248 |       |     7  (15)| 00:00:01 |                                                                                                                                                                                                 
                    |*  8 |        HASH JOIN             |                     |       |       |       |            |          |                                                                                                                                                                                                 
                    |*  9 |         HASH JOIN            |                     |       |       |       |            |          |                                                                                                                                                                                                 
                    |* 10 |          INDEX RANGE SCAN    | SRVSYS_SYSTYP_FK_I  |   328 |  5248 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                 
                    |* 11 |          INDEX FAST FULL SCAN| E_NE_FK_I           |   328 |  5248 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
                    |  12 |         INDEX FAST FULL SCAN | SRVSYS_PK           |   328 |  5248 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
                    |* 13 |      TABLE ACCESS FULL       | SA_NUMBER_RANGES    |  2219 | 48818 |       |    10   (0)| 00:00:01 |                                                                                                                                                                                                 
                    |* 14 |    FILTER                    |                     |       |       |       |            |          |                                                                                                                                                                                                 
                    |* 15 |     SORT JOIN                |                     |    13M|   167M|   622M|   169K  (2)| 00:34:00 |                                                                                                                                                                                                 
                    |* 16 |      TABLE ACCESS FULL       | SA_MSISDNS          |    13M|   167M|       |   104K  (2)| 00:20:58 |                                                                                                                                                                                                 
                    ------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                                                 
                    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
                    ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                 
                       4 - access("SSS"."SEQ"="SNR"."SRVSYS_SEQ")                                                                                                                                                                                                                                                                
                       5 - access("SSS"."NE_SEQ"="SNE"."SEQ")                                                                                                                                                                                                                                                                    
                       7 - filter("SSS"."SYSTYP_ID"='OMC HLR')                                                                                                                                                                                                                                                                   
                       8 - access(ROWID=ROWID)                                                                                                                                                                                                                                                                                   
                       9 - access(ROWID=ROWID)                                                                                                                                                                                                                                                                                   
                      10 - access("SSS"."SYSTYP_ID"='OMC HLR')                                                                                                                                                                                                                                                                   
                      11 - filter("SSS"."NE_SEQ" IS NOT NULL)                                                                                                                                                                                                                                                                    
                      13 - filter("SNR"."ID_TYPE"='M')                                                                                                                                                                                                                                                                           
                      14 - filter("M"."MSISDN"(+)<="SNR"."TO_NUMBER")                                                                                                                                                                                                                                                            
                      15 - access("M"."MSISDN"(+)>="SNR"."FROM_NUMBER")                                                                                                                                                                                                                                                          
                           filter("M"."MSISDN"(+)>="SNR"."FROM_NUMBER")                                                                                                                                                                                                                                                          
                      16 - filter("M"."STATE"(+)='AVL')                                                                                                                                                                                                                                                                          
                    
                    
                     39 rows selected 
                    
                    
                    • 7. Re: Re: Performance issue will insert table.
                      Hoek

                      Martin specifically asked for the results of DISPLAY_CURSOR, so we can see the difference between the number of E(xpected)-rows and A(ctual)-rows.

                      Use:

                      1. SELECT *  
                      2. FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null, 'ALLSTATS LAST'));     

                       

                      For more on this subject see:

                      About Oracle: dbms_xplan.display_cursor

                      • 8. Re: Re: Performance issue will insert table.
                        1007945

                        distinct STATE are there in the table?  are

                        STATE  IN ('PND', 'RES', 'ALL', 'RTN', 'AVL', 'USE', 'UNV', 'DEL', 'RPR', 'OUT', 'TRF')


                        • 9. Re: Re: Performance issue will insert table.
                          1007945

                          Thanks Add dbms_xplan.display_cursor

                          • 10. Re: Performance issue will insert table.
                            Geert Gruwez

                            have you tried changing the group by to

                            GROUP BY  SNE.ID,SNR.FROM_NUMBER, SNR.TO_NUMBER


                            and adding an index on the number_ranges(id_type, from_number, to_number) ?

                            if there is only 1 value for id_type or not many values, you could try it without the id_type

                            • 11. Re: Performance issue will insert table.
                              Geert Gruwez

                              arrh ... edit doesn't work anymore ...

                               

                              also add index (from_number, to_number) on table SA_MSISDNS

                              • 12. Re: Re: Performance issue will insert table.
                                Martin Preiss

                                the optimizer expects most of the join operations to be quite cheap, but the final join of every thing else with SA_MSISDNS to be extremely expensive - since this is a (outer) join with an inequality condition a HASH JOIN is not an option and a MERGE JOIN (step 2) has to be used: and given the nature of the MERGE JOIN this means that both sets have to be ordered (step 3 and 15). The join is also responsible for the extreme number of estimated rows.

                                 

                                But if your query indeed returns just 37K rows the optimizer is somewhere missing the point - and this would be visible in a plan with rowsource statistics.

                                 

                                By the way: the plan is not identical to the one in your html file (though I guess that both suffer from the same basic error).

                                • 13. Re: Re: Performance issue will insert table.
                                  1007945

                                  Thanks Geert.

                                   

                                  FROM_NUMBER, TO_NUMBER column from SA_NUMBER_RANGES table no in SA_MSISDNS table.

                                   

                                  SA_NUMBER_RANGES Index.

                                  SA_MSISDNS.JPG

                                  • 14. Re: Performance issue will insert table.
                                    Jonathan Lewis

                                    It's interesting that the optimizer has introduced an outer join into the execution plan. Are you sure that you picked the right cut-n-paste.

                                    Guessing the intent of the query, the major threat seems to be that you have a set of number ranges and want to count how many available MSISDNs there are available for each range.

                                    Since you start with 10,000 ranges, and 72M numbers this could take a lot of work.

                                     

                                    First step.

                                    Trim down the query - eliminate the MSISDN table - to find out how many number ranges you will be checking and the size of the ranges involved. This may inspire some clever ideas.

                                     

                                    I suspect this may be a case where the smart move is to create a working table for the report by selecting the available MSISDNs with the analytic rownumber() over (order by msisdn); then a unique index on the MSISDN will allow you to join a number range to this working table twice - once to find the lowest msisdn inside the range, once to find the highest. The difference between the two rownumber() values, plus 1, will be the number of rows.

                                     

                                    Regards

                                    Jonathan Lewis

                                    1 2 Previous Next