10 Replies Latest reply: Dec 5, 2013 9:32 AM by user130038 RSS

    Query with same explain-plan but slower in one env

    user130038

      Hi there

       

      I have a stored procedure which is executed from a web application. It contains a query (insert-select-from statement). When this stored procedure is called by the web application in PROD, it takes 13sec but it takes 19sec in TEST env. I checked the explain plan for this insert statement in both instances and it is same (see below). Actually, the cost is lower in TEST env.

       

      ENV: Oracle 10gR2 EE, on ASM - RHEL 64bit

      The TEST server is on a better/faster hardware and will become the new PROD in near future (faster and 16 CPUs  vs 8 in PROD, high performance SAN, 132GB RAM vs 96GB in PROD, etc). The TEST database has exact same init parameter and version/patch level as current PROD. So the application is being tested against it at the moment.

       

      Here are the explain-plans from both environments:

       

      From PROD Server

      Plan

      INSERT STATEMENT ALL_ROWS Cost: 143 Bytes: 696 Cardinality: 3

       

      18 SORT ORDER BY Cost: 143 Bytes: 696 Cardinality: 3

       

       

      17 HASH UNIQUE Cost: 142 Bytes: 696 Cardinality: 3

       

       

       

      16 WINDOW SORT Cost: 143 Bytes: 696 Cardinality: 3

       

       

       

       

      15 HASH JOIN Cost: 141 Bytes: 696 Cardinality: 3

       

       

       

       

       

      13 HASH JOIN Cost: 128 Bytes: 519 Cardinality: 3

       

       

       

       

       

       

      11 TABLE ACCESS BY INDEX ROWID TABLE MKTG.SATDATAIMPORT Cost: 125 Bytes: 1,728 Cardinality: 12

       

       

       

       

       

       

       

      10 NESTED LOOPS Cost: 125 Bytes: 1,992 Cardinality: 12

       

       

       

       

       

       

       

       

      3 HASH JOIN Cost: 5 Bytes: 22 Cardinality: 1

       

       

       

       

       

       

       

       

       

      1 TABLE ACCESS FULL TABLE MKTG.TMPG_CLICKS_HDGS Cost: 2 Bytes: 12 Cardinality: 1

       

       

       

       

       

       

       

       

       

      2 TABLE ACCESS FULL TABLE MKTG.TMPG_CLICKS_DIRS Cost: 2 Bytes: 10 Cardinality: 1

       

       

       

       

       

       

       

       

      9 BITMAP CONVERSION TO ROWIDS

       

       

       

       

       

       

       

       

       

      8 BITMAP AND

       

       

       

       

       

       

       

       

       

       

      5 BITMAP CONVERSION FROM ROWIDS

       

       

       

       

       

       

       

       

       

       

       

      4 INDEX RANGE SCAN INDEX MKTG.SATDATAIMPORT_HEADINGNO Cost: 19 Cardinality: 4,920

       

       

       

       

       

       

       

       

       

       

      7 BITMAP CONVERSION FROM ROWIDS

       

       

       

       

       

       

       

       

       

       

       

      6 INDEX RANGE SCAN INDEX MKTG.SATDATAIMPORT_DIRNO Cost: 89 Cardinality: 4,920

       

       

       

       

       

       

      12 TABLE ACCESS FULL TABLE MKTG.MONTHS12 Cost: 2 Bytes: 84 Cardinality: 12

       

       

       

       

       

      14 TABLE ACCESS FULL TABLE MKTG.REF_WEST_CATEGORY Cost: 12 Bytes: 191,809 Cardinality: 3,251

       

      From TEST Server

      Plan

      INSERT STATEMENT ALL_ROWS Cost: 107 Bytes: 232 Cardinality: 1

       

      18 SORT ORDER BY Cost: 107 Bytes: 232 Cardinality: 1

       

       

      17 HASH UNIQUE Cost: 106 Bytes: 232 Cardinality: 1

       

       

       

      16 WINDOW SORT Cost: 107 Bytes: 232 Cardinality: 1

       

       

       

       

      15 HASH JOIN Cost: 105 Bytes: 232 Cardinality: 1

       

       

       

       

       

      13 HASH JOIN Cost: 93 Bytes: 173 Cardinality: 1

       

       

       

       

       

       

      11 TABLE ACCESS BY INDEX ROWID TABLE MKTG.SATDATAIMPORT Cost: 89 Bytes: 864 Cardinality: 6

       

       

       

       

       

       

       

      10 NESTED LOOPS Cost: 89 Bytes: 996 Cardinality: 6

       

       

       

       

       

       

       

       

      3 HASH JOIN Cost: 7 Bytes: 22 Cardinality: 1

       

       

       

       

       

       

       

       

       

      1 TABLE ACCESS FULL TABLE MKTG.TMPG_CLICKS_HDGS Cost: 3 Bytes: 12 Cardinality: 1

       

       

       

       

       

       

       

       

       

      2 TABLE ACCESS FULL TABLE MKTG.TMPG_CLICKS_DIRS Cost: 3 Bytes: 10 Cardinality: 1

       

       

       

       

       

       

       

       

      9 BITMAP CONVERSION TO ROWIDS

       

       

       

       

       

       

       

       

       

      8 BITMAP AND

       

       

       

       

       

       

       

       

       

       

      5 BITMAP CONVERSION FROM ROWIDS

       

       

       

       

       

       

       

       

       

       

       

      4 INDEX RANGE SCAN INDEX MKTG.SATDATAIMPORT_HEADINGNO Cost: 9 Cardinality: 2,977

       

       

       

       

       

       

       

       

       

       

      7 BITMAP CONVERSION FROM ROWIDS

       

       

       

       

       

       

       

       

       

       

       

      6 INDEX RANGE SCAN INDEX MKTG.SATDATAIMPORT_DIRNO Cost: 59 Cardinality: 2,977

       

       

       

       

       

       

      12 TABLE ACCESS FULL TABLE MKTG.MONTHS12 Cost: 3 Bytes: 84 Cardinality: 12

       

       

       

       

       

      14 TABLE ACCESS FULL TABLE MKTG.REF_WEST_CATEGORY Cost: 12 Bytes: 191,868 Cardinality: 3,252

       

      What else can I check to find out why the query is slower in TEST env?

       

      Please advise.

       

      Best regards

        • 1. Re: Query with same explain-plan but slower in one env
          user130038

          Here is some more info. The query is below:

           

          select distinct dr.line_num 

                           ,row_number() over (partition by di.HEADINGNO,di.DIRECTORYNO order by reportyear,to_number(di.monthno)) monthposition

                           ,di.SATID,di.REPORTYEAR,di.MONTHNO,di.MONTHEN,di.MONTHFR,di.HEADINGNO,hn.NAME_EN,hn.NAME_FR,di.DIRECTORYNO

                           ,di.SUPERDIRECTORYNO,di.PRINTDIRCODE,di.DIRECTORYNAME,round(to_number(di.IMPTTOTAL)) imptotal

                           ,round(to_number(di.IMPBEST)) impbest ,round(to_number(di.IMPTAVERAGE)) imptaverage

                           ,round(to_number(di.CLICKTOTAL)) clicktotal,round(to_number(di.CLICKBEST)) clickbest

                           ,round(to_number(di.CLICKAVERAGE)) clickaverage

                           ,round(avg(to_number(impttotal)) over(partition by di.HEADINGNO,di.DIRECTORYNO)) avgimp

                     from satdataimport di,tmpg_clicks_hdgs hd,tmpg_clicks_dirs dr, months12 m12, ref_west_category hn

                     where di.headingno   = hd.id

                       and di.directoryno = dr.id

                       and dr.line_num=hd.line_num

                       and di.reportyear  = m12.year

                       and di.monthno     = m12.month

                       and hn.CATEGORY_CODE = di.headingno

                     order by di.headingno, di.directoryno,di.reportyear,to_number(di.monthno)

           

           

          The largest table is "satdataimport" in the query has "12274818" rows. Rest of the tables are very small containing few rows to less than 4000 rows.

           

          I have refreshed the statistics of the larger table but this did not help either. Even a simple query like "select count(*) from satdataimport" is taking 15sec in TEST while it takes 4Sec in PROD when I run it from TOAD.

           

          The other strange thing is that when I run this stored procedure from TOAD, it takes 200 milli sec to complete. There is a logging table to which the stored procedure records the elapsed time taken by this INSERT statement.

           

          Since this query is in a stored procedure being called from the web app, the QA team wants quicker response. Current PROD is faster.

           

          The tables have same indexes, etc and contain identical data as that in PROD (were refreshed from PROD yesterday).

           

          What else can I check?

           

          Best regards

          • 2. Re: Query with same explain-plan but slower in one env
            user130038

            I hope I provided enough info. This has become a hot issue and I m in the hot seat :-)

             

            Any ideas/clues are highly appreciated?

             

             

            mktg@sat> set timing on;

            mktg@sat> alter index MKTG.SATDATAIMPORT_DIRNO rebuild;

            Index altered.

            Elapsed: 00:00:16.84

            mktg@sat> alter index MKTG.SATDATAIMPORT_HEADINGNO rebuild;

            Index altered.

            Elapsed: 00:00:15.89

            mktg@sat> analyze table satdataimport compute statistics;

            Table analyzed.

            Elapsed: 00:04:39.60

            mktg@sat> select count(0) from satdataimport;

              COUNT(0)

            ----------

              12274818

            Elapsed: 00:00:23.50

            mktg@sat>

             

             

            On PROD it takes 4sec for this query.

             

             

            Best regards

            • 3. Re: Query with same explain-plan but slower in one env
              Hemant K Chitale

              >select count(0) from satdataimport

              Check if this query does a FullTableScan or an IndexFastFullScan.

              Check the size of the table segment (BLOCKS in USER_TABLES and BLOCKS in USER_SEGMENTS).

              Check db_file_multiblock_read_count.

               

              Hemant K Chitale


              • 4. Re: Query with same explain-plan but slower in one env
                Aliyev Chinar

                If plans are same(even in test environment cost is lower than prod) then it is not indicate that in test environment SQL need execute faster(even in test environment has less data). Here is main matter  is hardware characteristics(including single and multi block read time) , where and how your data located?

                Does your data locate in ASM on both environments? and redundancy of ASM disk groups are same?

                 

                You can compare cumulative statistics  before and after execution of your SQL statement(in both environment). And see how many times oracle reads(physical&logical). Like

                 

                select

                       name

                      ,value

                from

                    v$statname join v$mystat using(statistic#)

                    where (name) like '%read%'

                order by name

                 

                Also you can compare wait event statistics from v$session_event or can enable SQL trace to obtain difference.

                • 5. Re: Query with same explain-plan but slower in one env
                  Nikolay Savvinov

                  Hi,

                   

                  if the plans are same, then one of the following is different:

                  1) disk I/O performance

                  2) database-level caching (buffer cache hit ratio)

                  3) low-level caching (OS or h/w level -- not directly measurable from database layer)

                  4) data

                   

                  To find out which of the possibility is taking place, run two simple statements (like table rowcounts)

                  with autotrace on, makesure returned values are similar and the plans are identical, and then

                  compare autotrace output row by row, first of all consistent gets and disk reads.

                   

                  Best regards,

                    Nikolay

                  • 6. Re: Query with same explain-plan but slower in one env
                    Jonathan Lewis

                    From an sql*plus session:

                     

                    set serveroutput off

                    set linesize 512

                    set pagesize 120

                    set trimspool on

                     

                    alter session set statistics_level = all;

                     

                    --execute your query

                     

                    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

                     

                    Post the two sets of results here using a fixed format font (courier new from the advanced editor option seems to work best).

                    This option for display_cursor, with statistics_level set to all, will show you how much work each line did, and how much time it took.

                     

                     

                    Regards

                    Jonathan Lewis

                    • 7. Re: Query with same explain-plan but slower in one env
                      saratpvv

                      If the data is same in prod as well as test - I would do is - I will take backup of stats in test enviornment - and i will copy production stats to test - and will check the explain plan

                      • 8. Re: Query with same explain-plan but slower in one env
                        Nikolay Savvinov


                        Hi,

                         

                        one thing I forgot to mention is that on one environment there could be more DML against queried tables than on the other, so it's slower because it needs to read more undo.

                        Checking I/O stats should confirm or refute that (and the best way to do it is by using display_cursor with statistics_level = all like Jonathan described above).

                         

                        Best regards,

                          Nikolay

                        • 9. Re: Query with same explain-plan but slower in one env
                          Mark D Powell

                          user130038, why are you using analyze to update the statistics?  Is analyze rather than dbms_stats being used in production to generate the statistics?

                           

                          Is your test system capable of matching the performance of your production system?  How many cpu's, how much memory, and what kind of IO performance are available to each instance?  Is the test instance allocated with the same database parameter values as production?

                           

                          As pointed out already the explain plan output may not actually match how Oracle runs the query.  You can query v$sql_plan to see the actual run time plan.  Features like bind variable peeking can result in the plans differing.

                           

                          You may also want to double check if any SQL Outlines are in use in either system.

                           

                          HTH -- Mark D Powell --

                          • 10. Re: Query with same explain-plan but slower in one env
                            user130038

                            Thank you so much everyone for your response. I was wrong in my initial assessment that plans were same when SQL is run from web app and when run manually. The plan I posted are both from TOAD - my bad. There was one difference (big one) which I missed. I found out that when the the SQL was run from web-app, one of the largest table was being full scanned. I doubled the sga and pga which helped reduce the elapsed time to almost half but I still do not know why the plan is different when I run the query manually vs when called from the web-app.

                             

                            Thank you so much everyone again!

                             

                            Best regards