6 Replies Latest reply: Aug 15, 2014 12:25 AM by sulimo RSS

    Performance on the view..

    newbiegal

      Hi Friends,

       

      We are troubleshooting a bunch of performance issues on the application, notice that several queries that is slow(most time spent in parsing the SQL) involve the below view. Please give me your thoughts if it can be tuned or change the definition for better performance..


      create or replace view st_view as

      select sst.s_id,

             sst.mdr,

             sst.goc,

             sst.s_item,

             sst.b_item,

             sst.s_ityp,

             je.jkey || '/' || je.ed_no o_cde,

             sst.i_code,

             je.j_tle title,

             je. js_tle sub,

             je.jsh_tle short,

             je.j_pbs pubsr,

             sst.created_user,

             sst.created_date,

             sst.mod_user,

             sst.mod_date,

             myp_func('pubs', je.p_stat, sst.mdr, '', 'trm') status,

             je.s_date as s_date,

             sst.o_id o_id,

             je.med med,

             je.med2 med2,

             je.rkey as rkey,

             null as ibn,

             null as ibn13,

             null as o_no,

             null as pn_al,

             to_number(null) as im_no

        from si_tble sst, j_edi je

      where sst.s_id = je.s_id

         and sst.s_ityp = 'je'

      union

      select distinct sst.s_id,

                      sst.mdr,

                      sst.goc,

                      sst.s_item,

                      sst.b_item,

                      sst.s_ityp,

                      decode(pi.ibn,

                             null,

                             pi.o_no,

                             pi.ibn || '-' || pi.ibn_ctrl) o_cde,

                      sst.i_cde,

                      pe.tle title,

                      pe.subtle sub,

                      pe.sh_tle short,

                      pe.p_hse pubsr,

                      sst.create_user,

                      sst.create_date,

                      sst.mod_user,

                      sst.mod_date,

                      myp_stat.status,

                      null as s_date,

                      sst.o_id o_id,

                      pe.med med,

                      pe.med2 med2,

                      null as rkey,

                      decode(who_func.al_cd1,

                             'y',

                             decode(pi.ibn,

                                    null,

                                    null,

                                    pi.ibn || '-' || pi.ibn_ctrl),

                             decode(pi.ibn_old,

                                    null,

                                    null,

                                    pi.ibn_old || '-' || pi.ibn_ctrl_old)) as ibn,

                      decode(pi.ibn,

                             null,

                             null,

                             pi.ibn || '-' || pi.ibn_ctrl) as ibn13,

                      pi.o_no as o_no,

                      pi.pn_al as pn_al,

                      pi.im_no as im_no

        from si_tble sst,

             p_imp pi,

             p_edit pe,

             (select myp.mdr, myp.code, myp.trm as status

                from myp mdt

               where myp.type = 'stat') myp_stat,

             (select myp.mdr, myp.al_cd1

                from myp mdt

               where myp.type = 'whoami'

                 and myp.code = 'ibn13') who_func

      where pi.im_id = sst.o_id

         and sst.s_ityp in ('prd', 'osp')

         and pi.ed_id = pe.ed_id

         and (myp_stat.mdr is null or myp_stat.mdr = sst.mdr)

         and myp_stat.code(+) = pi.status

         and (who_func.mdr is null or who_func.mdr = sst.mdr);

      THank you so much.. Really appreciate your help

        • 1. Re: Performance on the view..
          sybrand_b

          You are asking for help, without posting ANY INFO?

          Not even a four digit version number and platform info?

          How do you guess we can derive the relationships between the various tables? Where indices are? the Explain plan?

          Do you think this is a chat room?

          Trust me: it isn't. This type of question is just VERY RUDE!

          Basically you are asking for free consultancy

           


          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: Performance on the view..
            newbiegal

            Hi..I certainly understand it is difficult to help without enough details.. But, just wanted to get some generic suggestions/thoughts, if any in fine tuning the definition of the view..   Database is 11.2.0.2 on Windows 2008 R2.

            Table definition is big for all the tables involved in the view so I couldn't post it here.. Appreciate any help..

             

            THanks a lot

            • 3. Re: Re: Performance on the view..
              sulimo

              I remember some parsing issues in 11.2.0.2, does it also tank parsing if you downgrade optimizer?

              i.e: SQL> alter session set optimizer_features_enable='10.2.0.5';

              • 4. Re: Performance on the view..
                newbiegal

                Oh.That could be the problem.. Currently, optimizer_features_enable is set to 11.2.0.2 in the database. Since the program(SQl's) are run from application I don't think we can alter at the session level... In that case, should we alter this parameter at system level in init.ora?  I’m going to test the same or similar programs(SQL’s) on lower database versions and compare the results.. 

                 

                Though some of the SQL’s spend long time on fetching records as well, most SQL’s  take longer on parsing them.. Thank you for the update.

                • 5. Re: Performance on the view..
                  Girish Sharma

                  Why you did not replied all the info which asked by Sybrand i.e. relationships between the various tables? Where indices are? the Explain plan?

                  We are here to you help, but please help us to make some good points by knowing the info.

                  I too have faced many performance issues, but almost all just solved when I reads below link:

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

                   

                  Regards

                  Girish Sharma

                  • 6. Re: Performance on the view..
                    sulimo

                    I’m going to test the same or similar programs(SQL’s) on lower database versions and compare the results.


                    That'd be a good call...

                     

                    Parameter is dynamic, so you can do scope=memory without touching the spfile. But you ought to try it in DEV first as it will impact all SQLs

                    Alternatively, if not a shared account, you can create a logon trigger for a give user to test

                    Best would be to capture the SQL and execute in a session of your own...

                    And it would always be just to narrow the problem down...

                     

                    cheers