1 2 3 4 Previous Next 75 Replies Latest reply on Oct 7, 2016 7:19 AM by ANURAG DBA INDIA Go to original post
      • 15. Re: Long running job
        Balajidba

        Hi Stevens,

         

        I think show parameter control_management_pack_access this parameter tells that both the packs diagnostic and tuning can be used for license procurement.

        But it returned no rows when I executed the following in the database.

         

        SQL>

        SQL>  show parameter control_management_pack_access;

        SQL>

        SQL>

         

        Does above output indicates the database doesn't have license to generate AWR report? Could you please clarify?

        The purpose I am asking is I generated AWR report but not sure if I could generate AWR report when it is disabled. Could you please comment?

         

        Regards,

        Bala

        • 16. Re: Long running job
          happy10319

          Hi,

          Any way licensed or not for AWR you have already run it.

          Run AWR for the time your job was running (Wednesday 4AM 4PM; in text mode)

          And copy/past the report here :

           

          http://burleson-dba.com/SP/

           

          Or attach it into your post.

          Regards.

          • 17. Re: Long running job
            John Stegeman

            I think show parameter control_management_pack_access this parameter tells that both the packs diagnostic and tuning can be used for license procurement.

            But it returned no rows when I executed the following in the database.

            It means no such thing.

             

            Regardless of whether the management packs are enabled or disabled in the database, if you didn't buy a license, you cannot legally use them. No one forces you to disable them, so just because they are enabled doesn't mean you bought the license.

            • 18. Re: Long running job
              Balajidba

              Thanks John,

               

              Run AWR for the time your job was running (Wednesday 4AM 4PM; in text mode)

              And copy/past the report here

               

              This report analyzer is not true I guess. I have used this several times but didn't give useful output.

               

               

              Regards,

              Bala

              • 19. Re: Long running job
                AndrewSayer

                3247241 wrote:

                 

                Thanks John,

                 

                Run AWR for the time your job was running (Wednesday 4AM 4PM; in text mode)

                And copy/past the report here

                 

                This report analyzer is not true I guess. I have used this several times but didn't give useful output.

                 

                 

                Regards,

                Bala

                What is useful output to you? (I hope the irony isn't lost )

                 

                Is the job really one insert statement that gets executed 280k times?

                 

                How is the job run?

                 

                What is that sqlplus session doing during that time? Holding a lock?

                 

                 

                The awr report would be able to tell you this, if you are licenced to use it.

                 

                 

                What instrumentation do you actually have?

                 

                Ask whoever is in charge of your licence purchasing if you have diagnostic pack, it is stored NOWHERE on the db.

                • 20. Re: Long running job
                  Balajidba

                  Hi,

                   

                  Is the job really one insert statement that gets executed 280k times?

                  Not that I am aware of. But my guess is second highest sql query i.e. package that executed on issue day isn't found on normal day.

                  Here is the package --"BEGIN hrsap.Hr_Org_Position_Pkg.load_org_position; END;"

                   

                  How is the job run?

                  via Control M

                   

                  What is that sqlplus session doing during that time? Holding a lock?

                  I have no idea on this.

                   

                  The awr report would be able to tell you this, if you are licenced to use it.

                  Could you please tell me how to check if this is licence is not?

                   

                  What instrumentation do you actually have?

                  May I know what instrumentation is?

                   

                  Regards,

                  Bala

                  • 21. Re: Long running job
                    AndrewSayer

                    3247241 wrote:

                     

                    Hi,

                     

                    Is the job really one insert statement that gets executed 280k times?

                    Not that I am aware of. But my guess is second highest sql query i.e. package that executed on issue day isn't found on normal day.

                    Here is the package --"BEGIN hrsap.Hr_Org_Position_Pkg.load_org_position; END;"

                     

                    How is the job run?

                    via Control M

                     

                    What is that sqlplus session doing during that time? Holding a lock?

                    I have no idea on this.

                     

                    The awr report would be able to tell you this, if you are licenced to use it.

                    Could you please tell me how to check if this is licence is not?

                     

                    What instrumentation do you actually have?

                    May I know what instrumentation is?

                     

                    Regards,

                    Bala

                    Do you have any DBAs you could work with to assist you and learn from?

                     

                    You have access to the code to that package not us, it exists in the database. You could query dba_source (you could also use google to find out how you could query it)

                     

                    What is Control M?

                     

                     

                    As I said before, ask someone in your company that is in charge of licencing, it may be your dbas, it may be someone else, they should be able to point you in the direction or they may (should) know themselves.

                     

                     

                    See https://en.m.wikipedia.org/wiki/Instrumentation and have a look at Cary Millsap's quick video trace your code - all of it Trace Your Code - All of It | Cary Millsap [VIDEO]

                     

                    From the sound of your response though, it's too late for that.

                     

                    If you didn't store any diagnostic information and you haven't paid for the licence to use what Oracle has obtained then the information is not available to you. You may simply have to  just accept it happened and be prepared for the future with proper instrumentation so you can actually investigate the real problem.

                     

                     

                     

                    If you want a guess then I'd say that sqlplus session was holding a lock blocking the insertion job. That could be completely wrong - and without seeing any supporting evidence it shouldn't be accepted.  

                    • 22. Re: Long running job
                      Pavan Kumar

                      Do you have any DBAs you could work with to assist you and learn from?

                      Good question -- I have asked  many times same question in this previous threads (previous post) -- they are EXADATA DBA's Group - not sure what is their scope of work and OP never consult their own team.

                      Andrew - you can spoon feed him

                       

                      Bala,

                       

                      What is the best thing we can understand from your attachment, which  you have uploaded -- only plsql block of code call - that's it

                      1. What we can understanding from that ?  On what basis we should analyze it ?

                      2. We didn't new the behavior of your application at all

                      3. We can't communicate with your developer or apps team

                      4. when such issue happens we should host team call / meeting to check how stuff goes on -- from apps team to DBA   to knew what is happening (flow of work). Have you done that  - I hope you failed to do so?

                      5. you never posted execution plan at all

                      6. whether you environment is licensed or not- we never knew it ? You need to study all these stuff

                      7. How many hours do you spend for your own education / growth from tech perspective ? --- we can't spoon each stuff from basics - just put forward in our steps and think..

                      Some one coming to you all the time --- how to do this and that -- very much basics.

                       

                      You need first consult your colleagues who are good at stuff - if you are thinking on your second thought  that asking some thing to your colleagues (basics) you are stepping down in front of them -- then you will never grow (trust me on that).

                      You can report as abuse - not an problem at all (since what ever things I told you for your own growth and improvement).

                       

                      - Pavan Kumar N

                      • 23. Re: Long running job
                        AndrewSayer

                        Pavan Kumar wrote:

                         

                        Do you have any DBAs you could work with to assist you and learn from?

                        Good question -- I have asked  many times same question in this previous threads (previous post) -- they are EXADATA DBA's Group - not sure what is their scope of work and OP never consult their own team.

                        Andrew - you can spoon feed him

                         

                        I've just taken a look at the thread history, if I had known that these were all the same person I probably wouldn't have said anything other than sit down with the documentation-take some training-hire a consultant.

                         

                        The company has obviously paid a lot of money for the Exadata box, they should have also invested in staff that know how to work with it. It may have just been a very expensive band-aid thought to give automatic fast=true mode.

                         

                        I have no Exadata experience (sadly) so there should be no spoon feeding from me. I think, however, we can all agree that instrumentation would have gone a long way here, if you have no idea what happened then you have no idea what happened wrongly.

                        • 24. Re: Long running job
                          John Thorton

                          You  can lead some folks to knowledge, but you can't make them think.

                          • 25. Re: Long running job
                            Balajidba

                            Hi All,

                             

                            I found one package had been used by logging in to database. That had caused contention on the table the process used by App team and informed same to them.

                             

                             

                            Regards,

                            Bala

                            • 26. Re: Long running job
                              William Robertson

                              Andrew Sayer wrote:

                               

                              What is Control M?

                               

                              It's a job scheduler.

                              • 27. Re: Re: Long running job
                                William Robertson

                                3247241 wrote:

                                What instrumentation do you actually have?

                                May I know what instrumentation is?

                                 

                                 

                                Logging or other status reporting allowing you (production support teams etc) to see what it did and how long it took. Is there a log table, for example?

                                 

                                If you are licensed for the Oracle Diagnostics Pack, the dba_hist_ views and v$active_session_history can tell you what any session was doing at any time in the recent past. One approach would be to run reports for the slow batch run and a normal run and see what stands out as different. Or just looking at the slow run, see if any of the top cursors took longer, processed more rows or used a different execution plan.

                                 

                                In my experience, having worked on a number of systems where this used to happen routinely, the issue was usually an execution plan changing from hash joins to nested loops due to the system using a new or different partition of some large table involved in a complex query. There are other possible causes, of course. (Another favourite is duplicated date being loaded accidentally but not noticed, causing further duplication in other batch loads, and this still not being noticed because some queries have a DISTINCT or GROUP BY clause and thus continue to report a normal number of rows.)

                                • 28. Re: Re: Long running job
                                  AndrewSayer

                                  William Robertson wrote:

                                   

                                  3247241 wrote:

                                  What instrumentation do you actually have?

                                  May I know what instrumentation is?

                                   

                                   

                                  Logging or other status reporting allowing you (production support teams etc) to see what it did and how long it took. Is there a log table, for example?

                                   

                                  If you are licensed for the Oracle Diagnostics Pack, the dba_hist_ views and v$active_session_history can tell you what any session was doing at any time in the recent past. One approach would be to run reports for the slow batch run and a normal run and see what stands out as different. Or just looking at the slow run, see if any of the top cursors took longer, processed more rows or used a different execution plan.

                                   

                                  In my experience, having worked on a number of systems where this used to happen routinely, the issue was usually an execution plan changing from hash joins to nested loops due to the system using a new or different partition of some large table involved in a complex query. There are other possible causes, of course. (Another favourite is duplicated date being loaded accidentally but not noticed, causing further duplication in other batch loads, and this still not being noticed because some queries have a DISTINCT or GROUP BY clause and thus continue to report a normal number of rows.)

                                  >I found one package had been used by logging in to database. That had caused contention on the table the process used by App team and informed same to them.

                                   

                                  If you keep guessing at different options that could be going wrong then you may eventually find the actual cause, if you trace and instrument your code then you can see exactly what happened and why.

                                  • 29. Re: Long running job
                                    Balajidba

                                    Hi All,

                                     

                                    Thanks for your input.

                                     

                                    User again complaint one more long running job on another database. The application job runs every Wednesday at 4 AM and normally finishes at  around 7 AM PST. On July 13, it ran till 4PM PST.

                                     

                                    My findings

                                     

                                    I compared AWR report at good and bad condition and I found one insert statement was the cause of  the issue. I even shared that insert statement to them.

                                    Now user is asking

                                    1)"can you explain how this insert statement is affecting the load now. Shouldn’t this insert statement be existing from initial stage?

                                    2)How come this insert statement works fine on 6th July and for previous  loads , and then on 13th and 20th July, it runs longer than usual? "

                                    Could you please comment on how to reply them?

                                     

                                    Here is the insert statement

                                     

                                    INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "PS_W2_AMOUNTS" ("CALENDAR_YEAR", "COMPANY", "EMPLID", "TAXFORM_ID", "PRINTED_DTTM", "MEDIA_CREATED_DTTM", "SEQUENCE_NUMBER", "BOX", "STATE", "LOCALITY", "TAX_ENTITY", "TAX_TYPE", "STATE_CNTL", "PROCESS_FLAG", "AMOUNT") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15);

                                     

                                    Regards,

                                    Bala

                                    1 2 3 4 Previous Next