1 2 Previous Next 16 Replies Latest reply: May 27, 2012 11:45 PM by 936064 RSS

    Need help in tuning the query

    939833
      Hi ,

      Below are the complete details.Need assistance in tuning or rewriting the update statement.

      Currently the query used is

      update s_projects a set flag='N' where itemno_pk <(select max(b.itemno_pk) from s_projects b where a.assetid=b.assetid and a.project=b.project and b.flag='Y');


      itemno_pk     assetid     project     name     flag               
      1          101     LG     TV     Y               
      2          101     LG     PHONE     Y               
      3          102     LG     TV     Y               
      4          103     HP     TV     Y               
      5          104     SONY     CAMERA     Y               
      6          105     APPLE     IPAD     Y               
      7          105     APPLE     IPAD     Y               
                                         
      Create table pi(itemno_pk number,assetid number,project varchar(15),name varchar(10),flag char(2))                                   
                                         
      itemno_pk     assetid     project     name     flag               
      1          101     LG     TV     N               
      2          101     LG     PHONE     Y               
      3          102     LG     TV     Y               
      4          103     HP     TV     Y               
      5          104     SONY     CAMERA     Y               
      6          105     APPLE     IPAD     N               
      7          105     APPLE     IPAD     Y               
                                         
      Highest value with pk id should by Yes and asseit and project id should be same and it has be in flag='Y' condition
        • 1. Re: Need help in tuning the query
          Paulie
          936830 wrote:
          Hi ,

          Below are the complete details.Need assistance in tuning or rewriting the update statement.

          Currently the query used is

          update s_projects a set flag='N' where itemno_pk <(select max(b.itemno_pk) from s_projects b where a.assetid=b.assetid and a.project=b.project and b.flag='Y');
          First, welcome to the forum.

          However, you should read the FAQ and also the post by BluShadow at the top of the thread list and repost.

          Please use the [code ] and [code ] tags. Give us a CREATE TABLE... and INSERT INTO Table_name VALUES. We're not
          here to do the leg work for you. Help us to help you!


          HTH,


          Paul...

          Edited by: Paulie on 25-May-2012 18:40
          • 2. Re: Need help in tuning the query
            BSalesRashid
            Hi.
            You should Get a trace from the query.
            • 3. Re: Need help in tuning the query
              Hoek
              offtopic

              Paulie, if you put your code tags between the noformat tags, then you don't need to add a space...
              If you press reply and the Quote Original button (the "-sign above the message field) my text, you'll see what I mean below:
              {noformat}
               null; 
              {noformat}
              • 4. Re: Need help in tuning the query
                Paulie
                Hoek wrote:
                offtopic

                Paulie, if you put your code tags between the noformat tags, then you don't need to add a space...
                If you press reply and the Quote Original button (the "-sign above the message field) my text, you'll see what I mean below:
                {noformat}
                 null; 
                {noformat}
                {noformat}
                Thanks
                {noformat}

                ;)


                Wierd that code tags work with both [ ] and { } but noformat tags only work with curly brackets.


                Paul...

                Edited by: Paulie on 25-May-2012 19:18
                • 5. Re: Need help in tuning the query
                  Hoek
                  Oh well, there are more weird things going on on this forum ;)
                  • 6. Re: Need help in tuning the query
                    Tubby
                    936830 wrote:
                    Hi ,

                    Below are the complete details.Need assistance in tuning or rewriting the update statement.

                    Currently the query used is

                    update s_projects a set flag='N' where itemno_pk <(select max(b.itemno_pk) from s_projects b where a.assetid=b.assetid and a.project=b.project and b.flag='Y');


                    itemno_pk     assetid     project     name     flag               
                    1          101     LG     TV     Y               
                    2          101     LG     PHONE     Y               
                    3          102     LG     TV     Y               
                    4          103     HP     TV     Y               
                    5          104     SONY     CAMERA     Y               
                    6          105     APPLE     IPAD     Y               
                    7          105     APPLE     IPAD     Y               
                                                       
                    Create table pi(itemno_pk number,assetid number,project varchar(15),name varchar(10),flag char(2))                                   
                                                       
                    itemno_pk     assetid     project     name     flag               
                    1          101     LG     TV     N               
                    2          101     LG     PHONE     Y               
                    3          102     LG     TV     Y               
                    4          103     HP     TV     Y               
                    5          104     SONY     CAMERA     Y               
                    6          105     APPLE     IPAD     N               
                    7          105     APPLE     IPAD     Y               
                                                       
                    Highest value with pk id should by Yes and asseit and project id should be same and it has be in flag='Y' condition
                    In the future it would be nice if you could set up the example data like i have below. Makes it a lot less work for us, and people are more likely to respond in a timely fashion (less work equates to more motivation :)

                    Should probably mention that using the CHAR data type isn't a good idea (your flag column). You should stick with VARCHAR2, and in this case since flag can only be Y or N ... it would be better to have it declared as a varchar2(1) instead of a char(2).
                    Create table pi(itemno_pk number,assetid number,project varchar(15),name varchar(10),flag char(2)) ;
                    insert into pi values (1 ,101, 'LG', 'TV', 'Y'           );
                    insert into pi values (2 ,101, 'LG', 'PHONE', 'Y'        );
                    insert into pi values (3 ,102, 'LG', 'TV', 'Y'           );
                    insert into pi values (4 ,103, 'HP', 'TV', 'Y'           );
                    insert into pi values (5 ,104, 'SONY', 'CAMERA', 'Y'     );
                    insert into pi values (6 ,105, 'APPLE', 'IPAD', 'Y'      );
                    insert into pi values (7 ,105, 'APPLE', 'IPAD', 'Y'      );
                    
                    ME_XE? merge into pi target using
                      2  (
                      3     select
                      4        itemno_pk
                      5     from
                      6     (
                      7        select
                      8           max(itemno_pk) over (partition by assetid) as max_itemno,
                      9           itemno_pk,
                     10           flag
                     11        from pi
                     12        where flag = 'Y'
                     13     )
                     14     where itemno_pk != max_itemno
                     15  ) source
                     16  on
                     17  (source.itemno_pk = target.itemno_pk)
                     18  when matched then update
                     19     set target.flag = 'N';
                    
                    2 rows merged.
                    
                    Elapsed: 00:00:00.00
                    ME_XE?select * from pi;
                    
                             ITEMNO_PK            ASSETID PROJECT                                       NAME                           FLAG
                    ------------------ ------------------ --------------------------------------------- ------------------------------ ------
                                     1                101 LG                                            TV                             N
                                     2                101 LG                                            PHONE                          Y
                                     3                102 LG                                            TV                             Y
                                     4                103 HP                                            TV                             Y
                                     5                104 SONY                                          CAMERA                         Y
                                     6                105 APPLE                                         IPAD                           N
                                     7                105 APPLE                                         IPAD                           Y
                    
                    7 rows selected.
                    
                    Elapsed: 00:00:00.01
                    Cheers,
                    • 7. Re: Need help in tuning the query
                      939833
                      Thanks for rewriting below query....I would follow the process now on.........

                      Thanks
                      Avin
                      • 8. Re: Need help in tuning the query
                        939833
                        Hey Tubby/All,

                        Can we rewrite the query in other way....DBA's said its not going work either....
                        What they suggested is to find a way to replace < and MAX but functionality should remain the same....


                        Appreciate any help on this.


                        Thanks
                        Avin
                        • 9. Re: Need help in tuning the query
                          Tubby
                          936830 wrote:
                          Hey Tubby/All,

                          Can we rewrite the query in other way....DBA's said its not going work either....
                          What does that mean? "not going to work" .... meaning what exactly?
                          936830 wrote:
                          What they suggested is to find a way to replace < and MAX but functionality should remain the same....
                          How does what i posted not meet that specification?
                          • 10. Re: Need help in tuning the query
                            939833
                            Hey Tubby...

                            They have confirmed verbally : They are seeing high rollbacks ....Rollback Segment (RBS) should be 2048 MB but it almost double(5028 MB) occupied and Undospace it is using 5GB where as it should be 2GB.

                            Looking forward for you suggestions.


                            Thanks,
                            Avin
                            • 11. Re: Need help in tuning the query
                              sb92075
                              936830 wrote:
                              Hey Tubby...

                              They have confirmed verbally : They are seeing high rollbacks ....Rollback Segment (RBS) should be 2048 MB but it almost double(5028 MB) occupied and Undospace it is using 5GB where as it should be 2GB.
                              post SQL & results that show how "they" arrived at these expected values
                              • 12. Re: Need help in tuning the query
                                Nikolay Savvinov
                                936830 wrote:
                                Hey Tubby...

                                They have confirmed verbally : They are seeing high rollbacks ....Rollback Segment (RBS) should be 2048 MB but it almost double(5028 MB) occupied and Undospace it is using 5GB where as it should be 2GB.

                                Looking forward for you suggestions.
                                Hi,

                                this doesn't sound right. "High rollbacks" means rolling back transactions many times, that's obviously not what they meant. What they probably meant was high undo generated -- but I don't think this is something you can tune.

                                The purpose of tuning is to get an efficient plan (the one that provides desired results within minimal time or with minimum consumption of I/O and other resources), but the amount of UNDO/REDO generated by a DML statement doesn't depend on the plan. If you are making a change that requires 5 Gb of UNDO information to be reversed, there's nothing you can do about it (except for breaking a big changes into a bunch of smaller changes or tuning your UNDO retention parameters so adequately handle the UNDO generated).

                                Best regards,
                                Nikolay
                                • 13. Re: Need help in tuning the query
                                  Tubby
                                  936830 wrote:
                                  Hey Tubby...

                                  They have confirmed verbally : They are seeing high rollbacks ....Rollback Segment (RBS) should be 2048 MB but it almost double(5028 MB) occupied and Undospace it is using 5GB where as it should be 2GB.

                                  Looking forward for you suggestions.


                                  Thanks,
                                  Avin
                                  Please pass this thread along to your DBA's and have them post exactly what their issue is on this forum (along with some metrics to back it up please).

                                  I suspect that either
                                  1) they don't know what they are talking about
                                  2) you have misquoted them

                                  Cheers,
                                  • 14. Re: Need help in tuning the query
                                    939833
                                    Hey Tubby ,

                                    I am sorry to post information in bits....What you mentioned is correct they are not giving the proper information in written but complaining verbally....I have requested them to send me the issue in detail along with sql trace file etc....

                                    And on the merge statement mentioned we cant consider data is partitioned over assetid_ . Can we rewrite the update statement in any other way....


                                    Thanks,
                                    Avin
                                    1 2 Previous Next